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:
Limit headings to one row or column for each table or data range.
Remove subheadings.
Move totals and subtotals away from the table and into a separate location
Avoid multiple headings and totals in your table:
Keep one heading row and move totals away from the table into a separate location:
When working with individual calculated metrics:
Perform calculations in a separate area, away from your raw data.
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!