The 2021 Guide on Building Live Dashboards on Google Sheets
Did you know you can build live, custom dashboards on Google Sheets for free? Learn how to import data and use relevant features (pivot table, slicer, etc) to improve your data reporting skills in no time.
Several services such as Google Analytics and Shopify offer you ways of displaying important metrics on a single screen or page.
The thing is, the built-in dashboards by these services may not be enough for your needs, so you have to customize your own. One way to do so is by using Google Sheets.
At this point, you may ask yourself:
Will a customized Google Sheets dashboard improve my productivity?
Here are some advantages of a Google Sheets dashboard over the built-in dashboard offered by individual platforms:
You can calculate metrics not offered by the platform
You can customize the display your own way
You can store the data in another server
The main disadvantage, of course, is that you have to spend some time constructing one. (This still assumes that you won’t encounter any significant technical problems when doing so.)
... Well, we're here to help out with the whole building process. If you have decided to create a dashboard yourself, then this guide is for you!
Planning out your dashboard
The first thing you need to do is… to plan! Even though saying “plan first” or “when you fail to plan, you plan to fail” sounds cliche, there is a good reason to plan first.
You need to know first what kind of information do you want to get from the dashboard.
You can’t just put everything you can on the dashboard. Putting too much data in it will render it useless. Choosing the right combination of data will make your dashboard indispensable in helping you observe the growth of your business.
According to Datapine, there are four types of dashboards:
Strategic dashboards - focused on long-term strategies and high-level metrics
Operational dashboards - focused on tracking operational processes, shorter time frames
Analytic dashboards - focused on uncovering trends by processing huge amounts of historical data
Tactical dashboards - focused on tracking the performance of the company
Identifying what type of dashboard you will use will help you identify what data will be included in the dashboard.
Additionally, it will help you see what features of Google Sheets you will use. These are some of the important questions to answer that will help you decide on the amount of work needed:
Where will data come from? Services such as Google Analytics and Shopify have their own API with their complementary documentation where the structure of the data stored in their servers is described in detail. There are small but significant differences in the way the data is stored for each of these services.
What kind of data will be retrieved? Most of the time, the databases of these services contain as much data as they can store. You will need to filter the data that you will need so that the dashboard will not be overwhelmed.
How frequently should the dashboard be updated? Should the dashboard be updated every minute? Every 15 minutes? Or every hour? This will also help your dashboard not be overwhelmed with the data coming from your services.
Is visualization enough? Or does the data still need to be processed? Some common and important metrics are automatically calculated by the services and stored in databases, but others that you find relevant may not be offered. You will still need to calculate them in Google Sheets.
Do you want to store the displayed data in separate sheets? There are advantages in storing the data in separate sheets (or files), but these make the dashboard more complicated. You will need to do this if you still process the data to calculate important metrics, to store them for future audit and analysis.
Once you address these questions, you will now have an idea of what should appear in your dashboard.
Here are other tips in designing the layout of the dashboard, taken from Geckoboard:
Only include what’s important
Use size and position to show hierarchy
Give your numbers context
Group your related metrics
Use clear labels for your audience
Round your numbers
Keep evolving your dashboards
Taking note of these tips, you will arrive at a design that will be useful to you and to other users of the dashboard. Once you are done with designing it, you can now start implementing it! Continue reading to learn the more technical side of dashboard creation in Google Sheets.
Importing data to Google Sheets
Your answers to Questions 1 and 2 (Where will data come from? What kind of data will be retrieved?) dictate how your raw data is imported to Google Sheets. Here are some of the possible solutions to the problem of importing data to Google Sheets, depending on the data source:
From an external source via IMPORT functions
If the data is publicly accessible, Google Sheets has a set of IMPORT functions depending on the format of the data source:
IMPORTDATA imports a .csv (comma-separated value) or .tsv (tab-separated value) format
IMPORTFEED imports an RSS or ATOM feed
IMPORTHTML imports data from a table or list within an HTML page
IMPORTRANGE imports a range of cells from a specified spreadsheet.
IMPORTXML imports data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds
The disadvantage here is that the data must be publicly accessible. For the case of IMPORTRANGE, you need to authorize access to the Google Sheets spreadsheet. (However, this is easy if you own the spreadsheet, as Google Sheets will prompt you to give access to it.)
From Google Forms
Google Forms is well integrated into the G Suite, which also includes Google Sheets. If there is already an existing Google Form or Google Forms with their corresponding Google Sheets spreadsheet where the results are continuously being stored, you can import the data through the IMPORTRANGE function. The IMPORTRANGE function is discussed here: 3 Easy Ways to Import Data from Another Workbook in Google Sheets
If you are yet to create a Google Form, you should create it and create the corresponding spreadsheet to store the data separately from the dashboard. This is to keep the data from being accidentally overwritten.
Chances are, there are already existing Google Sheets add-ons for a specific service you are using. If not, you can dive a little into the technical side of things by checking the API connector add-ons available, and then connecting them to the Google Sheets by using the API.
There is a disadvantage to this method, however. This is if the developer of the add-on updated it in such a way that it affects the operation of your dashboard.
Here are some of the options you can use to import the data to your Google Sheets dashboard. We can now go to the next section where we list down
Google Sheets functions and features you need
Once you manage to import relevant data to your spreadsheet, how would you be able to visualize it? Here are some of the Google Sheets functions and features you will find useful.
Pivot Table is a powerful feature that can summarize long worksheets according to the data present. It allows you to summarize information by date, by category, and by specific data. To add a Pivot Table, here are the steps:
Click on any occupied cell in the sheet containing the data.
Click Data on the main menu, then select Pivot table.
A small pop-up window will appear, asking you whether to insert the Pivot Table to a separate new sheet or to an existing sheet.
We have prepared a set of tutorials for you to further harness the power of the Pivot Table:
The mainstay of any and every dashboard, charts help you quickly understand the patterns in the data, especially in the important metrics.
To insert a chart in a sheet, click Insert in the main menu and then click Chart.
A blank area for a chart will appear in the center with a sidebar on the right side. You can add the data range covered by the chart by going to the Data range portion of the sidebar and then entering the data range manually by typing or by clicking on the symbol on the right end of the textbox, where you can select the sheet and then select the cells.
We also have some guides to help you further harness the power of the charts in Google Sheets:
One disadvantage of using charts is that they often overlap with the sheet and can cover other cells that may contain important data. If you do not want the full-blown functionality of the Charts but still want basic visualization of the data through one of the following forms:
A line chart to visualize the changes over time
A column chart to visualize the values of different objects compared to each other
A bar chart to visualize how the contribution of different sources relative to each other
A win-loss chart to easily see which ones had positive values and negative values
Then the Sparkline chart function is the perfect fit for you! The Sparkline chart function inserts a small chart in a cell where the formula is added.
The dimensions of the chart follow the dimensions of the cell where it is included. This gives you more flexibility in laying out your dashboard: you can precisely set the cell or cells covered by the sparkline chart, even more so than a typical chart.
To insert a sparkline chart, use the following syntax:
Where the <array_of_cells> contain the data to be visualized, while the <attributes> contain certain variables that will control the display of the sparkline chart. </attributes></array_of_cells>
For our simple example, we want to plot the per annum total from the sheet below:
We note that the range of values is on the array C3:C9, and we use it as the source array for the formula:
Producing the following chart:
The SPARKLINE function outputs the plot of the given values without annotations such as the title of the plot or the legend (especially used in charts and charts that use multiple sets of similar data).
This is just some of the things you need to know before you start working on creating your own Google Sheets dashboard. If you think that these are quite intensive work, then yes you got it right! Nonetheless, the final result should give you a sweet taste of success, and hopefully, makes your job easier. In the end, you should consider what your business needs.
If you decide to customize your own dashboard, you might not have enough time to do this. We do have something else in store for you…
Consider trying Lido. With a few clicks on your laptop, you can now access all the relevant metrics without having to construct your own dashboard in Google Sheets. Let our platform do it for you!
Sign up for more articles like this
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.