Steps to build the Live Excel (or Google Sheets) Dashboard Example

This article is a step-by-step guide for building the Excel Dashboard example here:
https://www.geckoboard.com/learn/dashboard-examples/excel-dashboard-example/

live excel dashboard geckoboard 


Our Spreadsheets integration supports both Google Sheets and Excel files. In this article, we'll be using an Excel file to build the dashboard. However, things would be pretty similar if you're planning to use Google Sheets too.

In this article:

  1. Some helpful pointers before we get started
  2. First Steps - Adding a new dashboard
  3. Connecting your Spreadsheet to Geckoboard
  4. Adding your first widget - the 'Sales this month' Leaderboard
  5. 'Sales this year ($000s)' Column Chart widget
  6. 'Sales this year ($000s)' Line Chart widget
  7. 'Current leads' Geck-O-Meter widget
  8. 'Current leads' Number widget
  9. 'Units shipped today' Number widget
  10. Full-width header widget for the dashboard
  11. Arranging the widgets on your dashboard
  12. A useful tip: Creating 'dynamic' visualizations
  13. Another useful tip: How to pick visualizations for your data
  14. Displaying your dashboard on a TV screen to your team!

Let's go! 🚀 

Some helpful pointers before we get started

1) The Excel Spreadsheet: We'll be using this Excel sheet to build our dashboard: https://www.dropbox.com/s/eao35o9upmbws6o/SAMPLE-DATA-geckoboard-dashboard-example.xlsx?dl=1

You can download the Excel sheet using the link and have a look at the data. You can then upload it to your own Dropbox, OneDrive for Business or Google Drive account and use it to build this example dashboard. Or you can go through this article to understand how things work and then get cracking using your own Excel spreadsheet. 

Some useful links: 


Note 1: We only support the .XLSX extension for Excel files. The file size can be up to 5 MB.

Note 2: There’s no special trick to how the source data is structured in the spreadsheet. Inside the sample Excel file, you’ll see the numbers are arranged in columns, with titles at the top of each column.

Note 3: You cannot edit your spreadsheets from the Geckoboard UI. You'll have to continue editing your spreadsheets as you normally do and then wait for the changes to be picked up by Geckoboard or manually refresh the widget. Both these methods are explained further in the next couple of points. 

2) Widget Refresh Rate: All of our Spreadsheets widgets refresh every 10 minutes and pick up changes from your spreadsheet. These refreshes occur automatically and require no manual intervention. If you check in between those refreshes, the latest changes may not show on Geckoboard until the next subsequent widget refresh. You can rest assured that the numbers will update every time the widget refreshes.

You can find the refresh rates for all our different integrations here

3) Manually refreshing a Spreadsheets widget (for testing purposes only): While setting up the Spreadsheets widgets and testing things, you may want to make changes to the data in your spreadsheet and observe the effects right away (instead of having to wait for the next widget refresh). You can do that by manually refreshing the widget.

manually refresh spreadsheet widgets

 

4) Why this article?: Our Excel Dashboard Example article explains setting up of the widgets using GIFs. In this article, though, we'll cover each step thoroughly using images and share some useful tips and deeper insights. After you've gone through this article, you should be quite comfortable with setting up dashboards powered by your Excel spreadsheets (or Google Sheets).

back to index

First steps — Adding a new dashboard

1) If you haven't already, you can sign up for a free 30-day Geckoboard trial here.

2) After you've logged into your account, click on the 'Dashboards' menu on the top left and add a new dashboard by clicking on '+ Add dashboard'. Give your dashboard a name.

3) On your newly created dashboard, click on 'Settings' in the header and then 'Dashboard settings'.

4) The dashboard's timezone settings will not affect the Spreadsheets widgets. If you plan on adding widgets from other integrations as well on the same dashboard, however, say widgets from Google Analytics, Facebook, Mixpanel, etc., then set the Location and Timezone to match the timezone set within those services. In other words, the timezone set on your dashboard should match the timezone set within your Google Analytics, Facebook, Mixpanel, etc., accounts. This is needed to ensure there's no lag in the data being fetched from these services and that you see up-to-date, real-time information on your dashboard.

5) Set the dashboard width to 3 columns (710px). If necessary, you can adjust the dashboard width later on to match that of your TV screen.

6) The default settings should be good for the remaining options on this page. Click 'Save Changes'.

geckoboard dashboard settings for excel spreadsheets dashboard

 

 

back to index

Connecting your Spreadsheets to Geckoboard

Geckoboard has pre-built integrations with Dropbox, OneDrive for Business and Google Drive. You can simply create a connection and start displaying data from your Excel files stored on these services. 

Alternatively, you can also use direct links to your Excel files to power your widgets.

Let's check out both options: 

a) Creating a connection to your Dropbox / OneDrive for Business / Google Drive account using our pre-built integrations (recommended method):

1. On your dashboard, click the '+Add widget' button.

2. Search for the 'Spreadsheets' integration using the search bar. You can also scroll down the alphabetically ordered list until you find the Spreadsheets integration.

3. Click on the integration. You'll be prompted to 'Select a data source' and to create a connection. 

4. Based on where you have your Excel files, choose the corresponding service and click on 'Connect'.

5. A window will pop up prompting you to log in to your Dropbox / OneDrive for Business / Google Drive account. If you're already logged into your account in your browser, Geckoboard will automatically pick that up and proceed with the authentication. Next, 'Allow' Geckoboard access to your account. 

6. Your connection should be set up!

You only need to do this once. Going forward all the widgets you add will be able to access this connection and pull in data from your spreadsheets. If necessary, you can also connect multiple accounts to Geckoboard - more details here.

creating a connection to your spreadsheets - Dropbox / OneDrive for Business / Google Drive account

 

b) Using the link to file method:

This is a useful option if you want to host your Excel files on your own servers or if you're using a storage service we don't integrate with. That is, services other than Dropbox, OneDrive for Business and Google Drive such as https://www.box.com/. (On that note, here's a short guide on creating direct download links for files stored in Box).

1. On your dashboard, click the '+Add widget' button.

2. Search for the 'Spreadsheets' integration using the search bar. You can also scroll down the alphabetically ordered list until you find the Spreadsheets integration.

3. Click on the integration. You'll be prompted to 'Select a data source' and to create a connection.

4. Select the 'link' icon (4th option). Enter the URL / link to your Excel file here. 

create a direct link to Excel file for Geckoboard's Spreadsheets integration


A few points to keep in mind: 

  • The URL of your file will act as the data source. Our integration will do the rest.
  • As with all Spreadsheets widgets, in this method too, the widget will refresh your URL every 10 minutes to fetch and display new data.
  • We support the following protocols in this method: File (file://), HTTP (http://) and HTTPS (https://). The 'Connect' button will only be displayed if your link begins with one of these. 
  • Just a note again to mention that only .XLSX  files are supported. 

5) Here's an example direct link to an Excel file. Note that the Excel file should directly download when the link is opened in a browser:
http://spreadsheetpage.com/downloads/xl/worksheet%20functions.xlsx

You could also use Dropbox to create direct links to Excel files, like the link we're using for our sample Excel file mentioned earlier: 
https://www.dropbox.com/s/eao35o9upmbws6o/SAMPLE-DATA-geckoboard-dashboard-example.xlsx?dl=1

However, if you already have the file in your Dropbox, we'd recommend creating a connection between Geckoboard and your Dropbox account (the previous method). We recommend using our pre-built Dropbox integration as things are just simpler that way.

back to index

 

Adding your first widget - the 'Sales this month' Leaderboard


Leaderboards are a great way to motivate teams that have a competitive spirit, and to help individuals benchmark their performance against others in the team. 

Geckoboard leaderboard widget using Excel spreadsheets
  

Here's how you can set up this widget: 

1) Head over to your dashboard, click on '+Add widget' and search for the Spreadsheets integration. Click on the integration to proceed further. 

2) You'll see a list of your recently modified spreadsheet files. Select your desired Excel file. You can use the search box if the file you're interested in doesn't show up in the list by default.

You can also switch between accounts if you've connected more than one Dropbox, OneDrive for Business and / or Google Drive accounts. 

For the link to file method, select the 'link' icon and simply enter the link as explained in the previous section

3) Now, set up the widget with the following configurations:

widget set up for Geckoboard leaderboard widget using Excel spreadsheets 

  • Sheet picker: If you have multiple sheets (tabs) within your main spreadsheet (workbook), use the sheet picker drop down towards the bottom to select the required sheet. 
  • Leaderboard visualization: Choose the Leaderboard visualization from the list of visualizations on the top right.
  • Use row 1 as header: Enabled. As the first cells in columns A ('Rep') and B ('Sales this month') are indeed headers and not values that we want to display on our Leaderboard, we'll leave this enabled. 
  • Labels: The 'names' for the items to display on your Leaderboard. In this case, the labels are the names of the different sales reps. Select the entire column A by clicking on 'column A' — just as you would in Excel (or Google Sheets). You can also drag and select specific cells (e.g. A1:A11). Another option would be to manually enter A:A in the field next to 'Labels'.
  • Values: The values or amounts for each item. In this case, the number of sales made this month by individual reps. Select the entire column B for this one. 
  • Fine-tune: After you're done with building your widget, click on the 'Fine-tune' tab, towards the top-left, to configure: 
    1. Abbreviation: Can be set to Auto (widget will automatically pick the best fit), None (widget will show complete numbers without any abbreviations), K (thousands), M (millions) and B (billions). 
    2. Decimal places: Set the number of decimal places you want the numbers on the widget to display.
    3. Unit: You can specify any desired unit, eg: kg, £ or even use emojis💰. You can choose to display the unit as a prefix or suffix. 
  • Title: Sales this month (or anything else you like)
  • Size: 1x2 (this size will show a maximum of 10 items in the Leaderboard) 

4) A preview will start showing up to help you with building the widget. Once you're happy with all the configurations and settings, click on 'Add to dashboard'. You can always edit the widget anytime later on if you want to change something. 

You now have a real-time Leaderboard on your dashboard! 

back to index

 

'Sales this year ($000s)' Column Chart widget


This widget displays sales by calendar week for the current year. A goal of $75,000 has also been set. The columns will automatically highlight in green for the weeks where this goal is hit.

Geckoboard column chart widget using Excel spreadsheets 

Here's how you can set up this widget:

1) As we already have a Spreadsheets widget on our dashboard, we can go ahead and make a copy. This would be a faster way to add a new Spreadsheets widget to the dashboard. You can, of course, also add a new widget from scratch like we did for the previous 'Sales this month' Leaderboard widget. 

copy an existing Geckoboard widget 

2) Now, edit your new widget and set it up with the following configurations:

widget set up for Geckoboard column chart widget using Excel spreadsheets

  • Sheet picker: If you have multiple sheets (tabs) within your main spreadsheet (workbook), use the sheet picker drop down towards the bottom to select the required sheet.
  • Column chart visualization: Choose the Column chart visualization from the list of visualizations on the top right.
  • Use row 1 as header: Enabled. As the first cells are indeed headers (Weeks and Sales, Actual ($000s)) and not values that we want to display on our column chart, we'll leave this option enabled.
  • X-Axis: We're displaying data for just the first 6 weeks in this example. So, select the cells A1:A7, just as you would in Excel (or Google Sheets). You can, of course, select a different range of cells or even an entire row or column as per your requirements.
  • Series: The corresponding column values for each week. Select the cells B1:B7.
  • Goal: Enable the option and set a goal of 75000. You can also use a value from your spreadsheet as the goal — just select the desired cell.
  • Title: Sales this year ($000s) or anything else you like
  • Size: 2x1
  • Fine-tune: By default, the widget will automatically work out and use the best abbreviation for the numbers — in this case, 'K' to show thousands. You can use the options under 'Fine-tune' to change the abbreviation, along with the decimal places and unit to display, as per your requirements. 

    (Note: If your spreadsheet has currency values, the widget will automatically pick them up. For example, if the spreadsheet contains $124.50, the widget will correctly display the number with a $ symbol — you will not need to use the 'Unit' control under Fine-tune in this case.)

    abbreviation control, decimal places and adding units to your Geckoboard widgets using the fune-tune option
     

4) A preview will start showing up to help you with building the widget. Once you're happy with all the configurations and settings, click on 'Add to dashboard' or 'Save' (if you created the widget by copying). 

You now have a column chart on your dashboard displaying data from your Excel sheet!

back to index

 

'Sales this year ($000s)' Line Chart widget

This line chart widget displays the same data as the column chart above. An additional line (in yellow) shows the sales targets. 

Geckoboard line chart widget using Excel spreadsheets

 

Here's how you can set up this widget:

1) Create a copy of one of your existing Spreadsheets widgets or add a new one using '+Add widget'.

 widget set up for Geckoboard line chart widget using Excel spreadsheets

  • Sheet picker: If you have multiple sheets (tabs) within your main spreadsheet (workbook), use the sheet picker drop down towards the bottom to select the required sheet.
  • Line chart visualization: Choose the Line chart visualization from the list of visualizations on the top right.
  • Use row 1 as header: Enabled. As the first cells are indeed headers (Weeks, Sales Actual ($000s) and Sales Target ($000s)) and not values that we want to display on our line chart, we'll leave this option enabled.
  • X-Axis: As with the previous column chart widget, we're again displaying data only for the first 6 weeks in this example line chart widget. So, go ahead and select the cells A1:A7, just as you would in Excel (or Google Sheets). You can, of course, select a different range of cells or even an entire row or column as per your requirements.
  • Series A (Actual): This corresponds to the first line series and will display in blue on the chart. Select the cells B1:B7. 
  • Series B (Target): Add the second line series using '+ Add series'. Select the cells C1:C7. This will display in yellow. 

    Note 1: The color of each line series is shown with a circle next to the series name. You can find the order of the colors here.

    Note 2: If the 'Use row 1 as header' option is enabled, the value in the first cell from the selected range will be used as the series name. Also, a legend with the series names will be displayed whenever there's more than one line series on the widget. 

    You can also manually set the name for each series. For our widget, we've set them to 'Actual' and 'Target'. 

    setting line series name and legend on geckoboard line chart widgets

  • Goal: Disable the Goal. We're not looking to set a goal on this widget as we're already using the 'Target' series. 
  • Chart Options: This option lets you adjust the Y-Axis range which starts at 0 by default. It effectively lets you zoom in or zoom out on the Y-Axis range. An example use case would be setting the Y-min and Y-max to a few points above and below the current USD value. You can then effectively monitor the minor daily currency fluctuations.

    For the purposes of our widget, though, we'll leave this option disabled.

  • Fine-tune: As with the previous column chart widget, you can specify the abbreviation, decimal places and unit as per your requirements for the widget. Go through the Fine-tune section of the column chart widget if you'd like to read a more detailed explanation. 
  • Title: Sales this year ($000s) or anything else you like
  • Size: 2x1

2) A preview will start showing up to help you with building the widget. Once you're happy with all the configurations and settings, click on 'Add to dashboard' or 'Save' (if you created the widget by copying).

You now have a line chart powered by Excel data on your dashboard!  

back to index

'Current leads' Geck-O-Meter widget


This widget displays the current number of sales leads this week with a goal of achieving 7 leads. The gauge will turn green if and when the target is hit.

Geckoboard Geck-O-Meter widget using Excel spreadsheets 

 

Here's how you can set up this widget:

1) Create a copy of one of your existing Spreadsheets widgets or add a new one using '+Add widget'. 

widget set up for Geckoboard Geck-o-Meter widget using Excel spreadsheets 

  • Sheet picker: If you have multiple sheets (tabs) within your main spreadsheet (workbook), use the sheet picker drop down towards the bottom to select the required sheet.
  • Geck-O-Meter visualization: Choose the Geck-O-Meter visualization from the list of visualizations on the top right.
  • Geck-O-Meter selection: The main (primary) metric to be displayed. In our case, it's the number of leads for the current week. We'll go ahead and select the entire column B. This will display the last available value from that column. 
  • Options: Min: 0, Max: 10
  • Goal: We're setting a goal of 7. You can also use a value from your spreadsheet for the goal — just select the required cell.
  • Fine-tune: Set the abbreviation, decimal places and unit as per your requirements. For this widget, we're leaving all three on the default 'Auto'. 
  • Title: Current leads (or anything else you want)
  • Size: 1x1

2) A preview will start showing up to help you with building the widget. Once you're happy with all the configurations and settings, click on 'Add to dashboard' or 'Save' (if you created the widget by copying).

You now have a Geck-O-Meter showing your current leads! 

back to index

'Current leads' Number widget 


This Number widget displays the same data as the Geck-O-Meter above — the number of sales leads this week along with the goal to achieve. Instead of a gauge, though, the Number widget displays a single (primary) number. The progress towards the goal is shown as a strip of blue across the bottom of the widget. As the number of current leads increases, the strip of blue will edge towards the goal towards the right, and show a higher % completion, before ultimately turning green when the goal is hit.

Geckoboard Number widget using Excel spreadsheets

 

Here's how you can set up this widget:

1) Create a copy of one of your existing Spreadsheets widgets or add a new one using '+Add widget'. 

widget set up for Geckoboard Number widget using Excel spreadsheets 

 

  • Sheet picker: If you have multiple sheets (tabs) within your main spreadsheet (workbook), use the sheet picker drop down towards the bottom to select the required sheet.
  • Number visualization: Choose the Number visualization from the list of visualizations on the top right.
  • Number selection: The main (primary) metric to be displayed. In our case, it's the number of leads for the current week. As we did for the Geck-O-Meter, go ahead and select the entire column B. This will display the last available value from that column. 
  • Comparison Visualization: Enable the option (if not already enabled). Select Goal and set it to 7. You can also use a value from your spreadsheet for the goal — just select the required cell. 
  • Fine-tune: Set the abbreviation, decimal places and unit as per your requirements. For this widget, we're leaving all three on the default 'Auto'. 
  • Title: Current leads (or anything else you want)
  • Size: 1x1

2) A preview will start showing up to help you with building the widget. Once you're happy with all the configurations and settings, click on 'Add to dashboard' or 'Save' (if you created the widget by copying).

You now have a Number widget showing your current leads!

back to index

 

'Units shipped today' Number widget


This widget, which is also a number widget like the previous one, displays the number of units shipped today as it's main (primary) metric. There's also a secondary number displaying the comparison with / change from yesterday's performance. 

Geckoboard Number widget with comparison metric using Excel spreadsheets 

 

Here's how you can set up this widget:

1) Create a copy of one of your existing Spreadsheets widgets or add a new one using '+Add widget'. 

widget set up for Geckoboard number widget with comparison metric using Excel spreadsheets 

  • Sheet picker: If you have multiple sheets (tabs) within your main spreadsheet (workbook), use the sheet picker drop down towards the bottom to select the required sheet.
  • Number visualization: Choose the Number visualization from the list of visualizations on the top right.
  • Number selection: We're going to select the cells B5:B6 here which contain the numbers for yesterday and today. 
  • Comparison Visualization: Enable the option (if not already enabled). Choose the Number option. Since we want the number of units shipped to be increasing each day, select the 'up arrow' icon which stands for 'Up is good'. This is also the default selection. As the number has decreased compared to yesterday, the change (difference) displays in red.  

    We can set up this widget to be 'dynamic' such that the values from the last two cells in the column are always picked up even as you continue adding new rows of data each day. You can find out how to do this in our 'Creating dynamic visualizations' help article.

  • Fine-tune: Set the abbreviation, decimal places and unit to display as per your requirements. For this widget, we're leaving all three on the default 'Auto'. 
  • Title: Units shipped today (or anything else you want)
  • Size: 1x1

2) A preview will start showing up to help you with building the widget. Once you're happy with all the configurations and settings, click on 'Add to dashboard' or 'Save' (if you created the widget by copying).

You now have a Number widget showing the units shipped today + the comparison with yesterday's performance!

back to index

 

Full-width header widget for the dashboard

This widget acts as a header and provides some context when viewing your dashboard on a TV screen.

1) Click on '+Add widget' and search for the 'Text' widget. Select it to proceed ahead.

2) Under 'Message one', enter: Spreadsheets Dashboard

3) Set the size to 'full-width' and click on 'Add widget'.

back to index

Arranging the widgets on your dashboard

1) Now that all the widgets are ready on your dashboard, it's easy to arrange them — just drag and drop them into place! 

The grid lines that show up when dragging a widget will help you get a pixel perfect alignment. 

arranging widgets on your Geckoboard dashboards 

2) You can also move multiple widgets at a time! Click, drag and select the widgets you want to move as shown in the GIF below. You can then move those widgets around together.

Once you're done, click and drag your mouse over an empty space on the dashboard or over a single widget to de-select the widgets. This, too, is shown in the GIF. 

This feature is especially handy when you want to, say, move the full-width 'Spreadsheets Dashboard' header widget to the top of the dashboard and then adjust the remaining widgets accordingly. 

moving multiple widgets together on your Geckoboard dashboard

 

Your Excel Dashboard is now ready! 🎉 😊

back to index 

 

A useful tip: Creating 'dynamic' visualizations


You can set up your Spreadsheets widgets in such a way that they automatically incorporate any new rows of data you add or always display the last value from a column. This way, you will not have to manually edit and reconfigure the widget each time you add a new row of data. 

Some example scenarios: 

  • A Line Chart where you add a new row of data each week.
  • Leaderboard displaying the top 10 scorers where new players and new high scores are constantly being created and updated in the spreadsheet. 
  • Number widget that always shows the current week's sales and compares it with the performance from the previous week, even as data for each new week is added as a new row in the spreadsheet regularly. This is a use case similar to the 'Units shipped today' Number widget in our example dashboard above.

You can learn how to set up widgets with 'dynamic' visualizations here

back to index

 

Another useful tip: How to pick visualizations for your data


We have a handy flowchart to help you find the best way to visualize your data. You can read more here: https://www.geckoboard.com/blog/best-way-visualize-data-handy-flowchart/

back to index

Displaying your dashboard on a TV screen to your team!


You can find our recommended approach, devices and screens for displaying your dashboard(s) to your team in our guide here.

back to index

You should now have some good ideas on how to turn your key metrics in an Excel spreadsheet into something much more intuitive and actionable. Happy Dashboarding! 📈

 

Was this article helpful?
👍

Thank you for your feedback!