Create visualizations from your spreadsheets

Once you've connected your Google Sheet or Excel file to Geckoboard, in this article you'll create visualizations from your spreadsheet data. First you'll need to select the data you want to visualize.

Before you start

We're currently testing two versions of our spreadsheets data source. To make sure the instructions are correct, please select the article that matches the user interface matches what you're seeing in Geckoboard. chooose which spreadsheets UI you can see

Select data from your spreadsheet

To select the data to visualize from your spreadsheet, follow these steps.

  1. With your spreadsheet connected, click drag to select the spreadsheet cells you want to visualize.click drag to select the cells you want to visualizeAlternatively, you can select entire rows and columns or enter the data range into the field.select whole columns you want to visualize To add additional ranges, click the + button to the right of the field.

    If your data is found on a different sheet of your file you can select a different sheet by clicking the selector in the bottom left corner of the screen. You can also manually refresh your sheet to reflect any changes you've just made to your spreadsheet.change the sheet by clicking in the bottom right corner

    Tip

    You can create dynamic visualizations that automatically update as you add new data to your spreadsheet.

    By including cells from columns or rows in your spreadsheet that you'll populate in the future, you won't need to return to extend your data range.

    This works particularly well with line charts, column charts and bar charts that use dates or datetimes for their axis labels. create dynamic visualizations by selecting cells you'll populate in the future

  2. When you're happy with your selection, click > Confirm selection.
  3. Depending on your selection, you'll see one or more highlighted visualization options.Based on your selection, choose from the highlighted visualization types Hovering over each visualization type will tell you why it was, or wasn’t, suitable for your selection.Hover over each visualization type to find out you why it was, or wasn’t,
      suitable for your selection To edit the selection again, hover over the spreadsheet and click to edit selection.

    By default, Geckoboard reads the data in columns. When your data series is arranged in rows, click the check box next to Transpose columns into rows.
  4. Hover over your chosen visualization type and click the + button that replaces it to start configuring your visualization.

Specifications for each visualization type

The following table lists the data selection requirements for each of the visualization types:

For help formatting your spreadsheet for a particular visualization type, read our section on formatting your spreadsheet in our preparing your spreadsheet article.

Visualization
Specification
Number widget icon

Number

  • The selection is a single numeric value.
Gauge widget icon

Gauge

  • The selection is a single numeric value.
line chart widget icon

Line Chart

  • The first column is a date/datetime or a string (for X-axis labels).
  • The remaining columns are all numeric.
  • Each column is a line of data and you can select up to 10 of them.
column chart widget icon

Column Chart

  • The first column is a date, datetime or string (for X-axis labels).
  • The remaining columns are all numeric.
  • Each column is a line of data and you can select up to 3 of them.
bar chart widget icon

Bar Chart

  • There are exactly two columns.
  • The first column is a date, datetime or string (for Y-axis labels).
  • The second column is numeric.
leaderboard widget icon

Leaderboard

  • There are exactly two columns.
  • The first column is a string.
  • The second column is numeric.
table widget icon

Table

  • There are ten columns or fewer.
text widget icon

Text

  • The selection is one row, one column, or one cell.

Configure visualizations from your spreadsheet

From the following visualization types, click the one you want to configure:

number widget icon
gauge widget icon
line chart widget icon
column chart widget icon
bar chart widget icon
leaderboard widget icon
table widget icon
text widget icon

 

 

Number

With the number visualization selected, you'll next see the widget configuration screen. Here you'll be able to add status indicators, comparison visualizations, and any additional formatting information about the data being displayed. You'll see a preview of the widget as you create it.

Learn more with our introductory guide to the number visualization.

Status indicators for number widgets

Adding status indicators allows you to call attention to your number widget on your dashboard when it's performing above and below expectations. Status indicators will overwrite any goals you set on this widget.status indicators panel on the spreadsheets config screen

By setting a Warning value, when the primary metric is above or below the warning value your widget to turn red.

By setting a Success value, when the primary metric is above or below the success value your widget instead turns green.

For more details, see our article on how to add status indicators to number widgets.

Comparison visualizations for number widgets

The number widget allows you to visually compare the primary metric with a choice of four comparison visualizations: goal, number, percentage, and sparkline.

Click the Comparison visualization panel to display the options. Your spreadsheet canvas will automatically appear for you to select your comparison range.

Goal

Displays a goal progress bar below the primary metric.

Tracking goals creates a feeling of progression as you edge closer towards your targets. They are great for keeping a team motivated, particularly when working towards longer term objectives.spreadsheets number widget showing progress towards a goal

Enter your goal target value and, optionally, your starting value to define where your goal progress starts from.setting a goal target and a starting value

Number

Displays the number change from your initial selected cell. Click the up or down arrows to indicate whether an increased or deceased change is good (displays in green).spreadsheets number widget with a secondary number change viz

Percentage

Displays the percentage change from the your initial selected cell as a percentage. Click the up or down arrows to indicate whether an increased or deceased percentage change is good (displays in green).spreadsheets number widget with a secondary percentage change viz

Sparkline

Displays a sparkline (a very small line chart, typically drawn without axes or coordinates) of the values in the selected range (top to bottom or left to right). The more cells in your selected range, the more detailed your sparkline will be.spreadsheets number widget with a secondary sparkline viz

Formatting number widgets

The formatting menu allows you to change the number of decimal places used or add additional information about the value(s) being displayed on your number widget.formatting panel on the spreadsheets config screen

For more details, see our article on how to using the number formatting menu.

Dynamic selections for number widgets

The Dynamic selection menu enables you expand your initial single cell selection to a whole column or row. Your gauge widget will always display the last numeric value added. dynamic selection panel on the spreadsheets config screen

Gauge

With the gauge visualization selected, you'll next see the widget configuration screen. Here you'll be able to add status indicators and any additional formatting information about the data being displayed. You'll see a preview of the widget as you create it.

Add a Min and a Max value to your gauge. If no Min and/or Max values are added, your gauge will calculate it automatically using the lowest and highest value the selected data range.

Learn more with our introductory guide to the gauge visualization.

Status indicators for gauge widgets

Adding status indicators allows you to call attention to your gauge widget on your dashboard when it's performing above and below expectations. status indicators panel on the spreadsheets config screen

By setting a Warning value, when the primary metric needle is above or below the red warning line that section of your gauge turns red.

By setting a Success value, when the primary metric needle is above or below the green success line the section of your gauge instead turns green.spreadsheets gauge widget with success and warning indicators

For more details, see our article on how to add status indicators to gauge widgets.

Formatting gauge widgets

The formatting menu allows you to change the number of decimal places used or add additional information about the value(s) being displayed on your gauge widget.formatting panel on the spreadsheets config screen

For more details, see our article on how to using the number formatting menu.

Dynamic selection for gauge widgets

The Dynamic selection menu enables you expand your initial single cell selection to a whole column or row. Your number widget will always display the last numeric value added. dynamic selection panel on the spreadsheets config screen

Line Chart

The line chart widget plots multiple series of values and visualizes these trends over a period. Line charts also have an optional goal field.

Learn more with our introductory guide to the line chart visualization.

Setting Y-axis Min and Max on line charts

Enter minimum and maximum values for your line chart's Y-axis. Your line chart preview will change to reflect this.y-axis minimum and maximun panel on the spreadsheets config screen

Setting goals on line charts

Enter a goal value, then select whether the goal value is set for less than or more than.goal panel on the spreadsheets config screenGrey shading will be added to your line chart above the goal line to indicate the target area.spreadsheets line chart widget with goal area shaded in grey

Tip

Remember our tip about dynamic visualizations at the data selection stage? If your column or row selection included pre-populated cells your line chart will now automatically respond as you add data to your connected Google Sheet or Excel workbook.spreadsheets line chart widget that shows how selecting pre-populated cells can create dynamic visualizations

Formatting line charts

The formatting menu allows you to change the number of decimal places used or add additional information about the value(s) being displayed on your line chart.formatting panel on the spreadsheets config screen

For more details, see our article on how to using the number formatting menu.

Column Chart

Column charts allow you to plot a series of data into vertical columns, with optional labels for each column. Column charts also have an optional goal field which can be set to highlight values above a specific target.

Learn more with our introductory guide to the column visualization.

Setting goals on column charts

Enter a goal value, then select whether the goal value is set for less than or more than.goal panel on the spreadsheets config screenGrey shading will be added to columns that don't meet the goal to reflect this and any columns that meet the goal will turn green in color.spreadsheets column chart widget with goal area shaded in grey

Tip

Remember our tip about dynamic visualizations at the data selection stage? If your column or row selection included pre-populated cells your column chart will now automatically respond as you add data to your connected Google Sheet or Excel workbook.spreadsheets column chart widget that shows how selecting pre-populated cells can create dynamic visualizations

Formatting column charts

The formatting menu allows you to change the number of decimal places used or add additional information about the value(s) being displayed on your column chart.formatting panel on the spreadsheets config screen

For more details, see our article on how to using the number formatting menu.

Bar Chart

Similar to column charts, but landscape. The bar chart widget plots a series of data into horizontal bars, with optional labels for each column. Bar charts also have an optional goal field which can be set to highlight values above a specific target.

Setting goals on bar charts

Enter a goal value, then select whether the goal value is set for less than or more than.goal panel on the spreadsheets config screenGrey shading will be added to bars that don't meet the goal to reflect this and any bars that meet the goal will turn green in color.spreadsheets bar chart widget with goal area shaded in grey

Tip

Remember our tip about dynamic visualizations at the data selection stage? If your column or row selection included pre-populated cells your bar chart will now automatically respond as you add data to your connected Google Sheet or Excel workbook.spreadsheets bar chart widget that shows how selecting pre-populated cells can create dynamic visualizations

Formatting bar charts

The formatting menu allows you to change the number of decimal places used or add additional information about the value(s) being displayed on your bar chart.formatting panel on the spreadsheets config screen

For more details, see our article on how to using the number formatting menu.

Leaderboard

Leaderboards consist of a ranked list of items, each with a label and a value.spreadsheets leaderboard widget

Learn more with our introductory guide to the leaderboard visualization.

Reverse sort order on leaderboards

Toggle the switch to rank the leaderboard by lowest value first.

Tip: Use CONCATENATE for multiple columns

If the information you want to include about the ranked items in your leaderboard span multiple cells in your spreadsheet, you can use the CONCATENATE function. Available in Google Sheets and Excel, CONCATENATE enables you combine the contents of two or more cells into a third separate cell.

In our example, we want to add information about the sales team each rep belongs to. Column A lists the name of each rep, B includes a dash separator (to avoid a result like NathanRed), and C lists the team they belong to.google sheet showing example of concatentate function

For each cell in column D starting from A2, we'll use the formula =concatenate(A2, B2, C2) to return Nathan – Red. Now when you select Column D as part of your leaderboard's data selection you'll see your merged cells.spreadsheets leaderboard widget with merged columns using concatenate

Formatting leaderboards

The formatting menu allows you to change the number of decimal places used or add additional information about the value(s) being displayed on your leaderboard.formatting panel on the spreadsheets config screen

For more details, see our article on how to using the number formatting menu.

Table

The Table widget is used to display data from up to 10 columns from a spreadsheet. You can also change the column order on your widget without having to edit your spreadsheet.

Selection contains headers

Toggle this switch to treat the first row in your selection as the header.

Column order on table widgets

In the config panel you'll see a list of your columns. Click drag each column to change the order that they'll appear on your widget.spreadsheets table widget can display up to 10 columns

Formatting table widgets

The formatting menu allows you to change the number of decimal places used or add additional information about the value(s) being displayed on your table widget.formatting panel on the spreadsheets config screen

For more details, see our article on how to using the number formatting menu.

Text

The Text widget is used to display non-numerical data from a cell, or multiple cells, from a spreadsheet.

If you're visualizing text from a spreadsheet for the sole purpose of displaying it in Geckoboard, you may want to try out our dedicated Text widget instead.

Message loop on text widgets

If you select a column or multiple cells, you can either select Only show last message to display the last cell in your selection (if you have A1:A10 selected, only the message in cell A10 will be displayed).

Or select Loop through messages to display the text content of up to 5 cells in a 10 second loop (if you have A1:A10 selected, the messages in cells A6:A10 will be displayed).

If you have a column title included in your cell selection, you can toggle the switch to Ignore the first selected cell.spreadsheets text widget with a loop of 5 messages

Spreadsheet widgets refresh rate

Spreadsheet widgets automatically refresh within 3 minutes of a change being made to the sheet containing your data.

The exceptions to the above rule are visualizations powered by import formulas such as "IMPORTRANGE", "IMPORTDATA", etc. or by pivot tables, which automatically refresh every 15 minutes.

Was this article helpful?

Awesome! 👍 Thanks so much for your feedback!

Sorry about that! Start a conversation now.

We're ready to help, 24 hours a day, 5 days a week

Get support
  • Fernanda Customer Success Avatar

    Megan

    USA
  • Hariharan Customer Success Avatar

    Hariharan

    India
  • Fernanda Customer Success Avatar

    Yasmin

    Spain
  • Luis Customer Success Avatar

    Luis

    UK
  • Richard Customer Success Avatar

    Richard

    UK
  • Fernanda Customer Success Avatar

    Fernanda

    Brazil
  • Heather Customer Success Avatar

    Heather

    USA