Skip to main content

Prepare your spreadsheet before connecting to Geckoboard

Optimize your spreadsheet data before connecting it to Geckoboard by fetching just the data you need for your metrics.

Updated this week

In this guide, we'll provide some context about how Geckoboard connects with your data and help you prepare your spreadsheet before connecting with the Spreadsheets data source to ensure you can visualize your data as expected. We'll cover how data can shift when changes are made to your spreadsheet, how to clean your data, and how to create a Geckoboard-specific worksheet to power your widgets and keep your raw data separate.

Before we begin, please be aware that Geckoboard supports two spreadsheet formats:

  • Google Sheets

  • Excel files with the .xlsx file type (.xls and .xlsm are not supported)

How Geckoboard connects to your spreadsheet

When building widgets with the Spreadsheets data source, you will select cells from your sheet that you wish to visualize. This selection creates a direct link between the cells within your sheet and your widget in Geckoboard. The content within the cell is what will be shown within your widget.

With this knowledge, there are some important things to keep in mind:

  • If the data in your spreadsheet cells changes, your widget data will update automatically

  • If you move cells, columns, or rows around in your spreadsheet, whatever content ends up in the original location that is selected in your widget configuration will be displayed on your widget

  • Entire columns or rows can be selected within a widget configuration, and any new data added to that range will automatically be added to your widgets

  • If you add cells, columns, or rows to your spreadsheet, it’s possible that your data will shift

Data shift: what it is and how to avoid it

Here is a column chart with a data selection of A2:C5:

Here's what this widget would look like with that cell selection:

Imagine that you add three rows to the spreadsheet above the current cell selection:

Within the widget configuration where we select a range, notice how the data has shifted down three rows:

The result is that the Geckoboard chart still uses the original A2:C5 selection since it has not been changed yet, and as a result, there is no data to display:

If your underlying sheet data shifts as described above, you may need to update your widget configuration within Geckoboard. However, continue reading, as cleaning and organizing the data outlined in the next sections can help prevent data shift from occurring.

Clean your data

Data cleaning refers to removing or replacing incomplete, duplicate, or irrelevant data. It also means tidying up the existing data by verifying the accuracy of any numbers and text formatting. This is essential to maintaining the integrity of the information you want to leverage on your dashboards.

Be consistent with cell formats

Geckoboard will recognize and use the formatting in each cell of your spreadsheet. The formatting directly influences the types of visualizations you can build within Geckoboard. These formats include:

  • Text

  • Number

  • Date

  • Datetime

  • Currency

  • Percentage

If the values of your data in Geckoboard do not display how you expect or you're unable to select the visualization type you expect, start by verifying that the formatting matches your data type using the formatting menu within Google Sheets or Excel.

Currency and percentage formats

Be mindful when formatting currencies and percentage values to ensure they are consistently formatted so your data is displayed as expected. Below, you can see how formatting impacts currency and percentage values:

Date formats

If one format does not display how you expect on your dashboard, try changing to another date format. See below on how varying date formats and how formatting changes impact the data. The text October 31, 2026 10:30 AM is written out in each row, and only the formatting of the cell is changed:

Organize your tables and calculations

Keeping your data organized is essential, and there are a few important steps to help Geckoboard interpret your data accurately and precisely.

When working with tables of data or pivot tables:

  1. Limit headings to one row or column for each table or data range.

  2. Remove subheadings.

  3. Move totals and subtotals away from the table and into a separate location

    1. Avoid multiple headings and totals in your table:

    2. Keep one heading row and move totals away from the table into a separate location:

    3. When working with individual calculated metrics:

      1. Perform calculations in a separate area, away from your raw data.

      2. Set the number formatting for the calculated cells.

Create a Geckoboard-specific worksheet tab

Best practice is to separate your formulas and calculated metrics from your raw data. Our recommended option is to create a Geckoboard-specific worksheet tab to connect to your dashboard like so:

  • Add a new Geckoboard-specific tab to your sheet that contains your formulas or pivot tables to create metrics

  • Use references or import formulas as needed to bring data into the new Geckoboard-specific tab (or sheet)

  • Power your widgets with the Geckoboard-specific tab and continue using the original worksheet tab as you always have, keeping the raw data separate

Raw sales data example:

Geckoboard-specific tab for metrics:

Next steps

With your sheet optimized for Geckoboard, you're all set to turn your data into an easy-to-understand dashboard!

Did this answer your question?