Most people don’t know that Google Sheets can be utilized to make dashboards. So most of the time, companies end up spending a hefty chunk of their finances on dashboards when they can use Google Sheets for this purpose.
Creating a dashboard in Google Sheets is one of the most valuable things a small business owner can do when working with spreadsheets. Your productivity should increase greatly if you have a reliable and suitable dashboard to push your company to new heights.
This article will explain how to build a dashboard in Google Sheets and how to utilize a Google Sheets dashboard template.
Looking for a Google Sheets annual budget dashboard template? You can find one in the template gallery when you open Google Sheets in your browser.
We also have our own template so you can follow along with our below explanations here.
What Is a Google Sheets Dashboard?
A project dashboard provides you with a birds-eye view of your entire project, and it helps you make future decisions based on the data you have. Most dashboards serve a lot of purposes and are very flexible to use.
Dashboards consist of graphs and charts that help the viewer understand the data and its representation with ease. This data can also be displayed within a table, and dashboards usually have a lot of colors which helps define the data much more easily.
A great example of this is where companies can use dashboards to show the word trends of their employees. The same is very difficult to achieve using a spreadsheet as it becomes very hard on the eyes, and a lot of data can be skipped.
Simply put, a dashboard makes data easier to read by making it appealing and concise.
How to Create a Dashboard in Google Sheets
There are 3 main steps you need to follow when making a dashboard in Google Sheets.
- Importing or gathering data
- Parsing data (using formulas)
- Visualizing data
The best way to have a usable dashboard is to have all of the raw and parsed data on one sheet within a spreadsheet and all the visual data on the first sheet. You could have several sheets of visual, processed,or raw data if you wanted to as well.
The simplest way to get the charts and tables into your dashboard is:
- Highlight the cells you want to visualize data from in the data sheet(s)
- Click the chart button in the menu
- Decide the chart type you want
- When you finish, highlight the chart and press Ctrl+x
- Navigate to the dashboard sheet and press Ctrl+v
- Then you can adjust the size in the sheet as you please
Let’s take a closer look at each of the 3 big steps to making a Google Sheets Dashboard.
Importing or Gathering Data
Unless you’re already using Google Sheets as your preferred spreadsheet platform, you will need to have some raw data before you can start to work on it. You can even import directly into a template to save yourself from building the entire dashboard. Here are a few ways you can import your data into your Sheets.
Using IMPORT Functions
Suppose the data you wish to import into Sheets is accessible. In that case, you can use the IMPORT functions to your advantage depending on your information’s format.
- IMPORTDATA: This can be used to import data in a .csv or a .tsv format.
- IMPORTRANGE: This can be used to import a specified range of cells from a specific spreadsheet.
- IMPORTFEED: This can be used to import an ATOM or RSS feed
- IMPORTXML: This can be used to import data from several data types that include HTML, TSV, CSV, XML, RSS, and ATOM feeds. Basically, this is a mixture of the IMPORTFEED, IMPORTHTML, and the IMPORTDATA formula.
- IMPORTHTML: This can be used to import data from an HTML page.
One thing to note here is that the data needs to be publicly available for this to work.
Using Google Forms
Google Forms are generally a great way to have a lot of information for your spreadsheets. You can easily import data from Google Forms into your Google Sheets using the IMPORTRANGE function. You can also combine the IMPORTRANGE function with the QUERY function to have a more versatile workflow to filter the data before you import it to the dashboard.
If you are creating a new Google Form, you should make one and join a spreadsheet to store the data separately from the dashboard. This can be a great way to secure your data from being overwritten.
Using an External Source via an Add On
This can be a slightly demanding way to do things, as it requires technical knowledge. You can check out API connector add-ons for Google Sheets and connect sheets using the API.
However, this can have a disadvantage if the add-on developer updates it so that it breaks the functionality of your dashboard. It’s not fun to start all over again.
Once you’re done importing data into Google Sheets, now is the time to organize it.
Now that you have your data in the sheet, it’s time to analyze and organize it correctly. What you may see could be a mess of numbers and irrelevant text. Still, with some time, patience, and knowledge on using formulas, you can transform your raw data into clean, easy-to-understand data.
Now you don’t need to be a programmer to do any of this, nor do you need to have a programming degree, but this step is a bit trickier than the other two steps.
First, you need to know how formulas work and how the data can be manipulated using these formulas. An introductory tutorial or even a course can help you with that.
As an example, the SUMIF formula can be used, it can sum up the values in a specific column of your spreadsheet only when a particular condition is met by those cells.
Here are a few functions to help you get started:
- AVERAGE: This is used to find the average value of the data in a specific column.
- COUNTIF: This counts the number of times a specific criteria is met in a particular column.
- COUNTIFS: This is pretty much the same as the COUNTIF function. The only difference is that COUNTIF only has one condition while COUNTIFS can have multiple conditions.
- SUM: This can be used, sum up all the data in a specific column.
- SUMIF: This is used to sum up the data in a column as long as it fulfills a specific condition.
- SUMIFS: This is the same as the SUMIF function, but this can have multiple conditions.
- SORT: This is used to sort a specific range by ascending or descending order.
- UNIQUE: This is used to identify unique values in the data set.
This is the final step in creating a dashboard in Google Sheets. Here, you add elements to your sheet to help visualize your data. This can include sparklines, charts, and tables. Here are a few spreadsheets features that fit great in a dashboard.
A pivot table is a great way to summarize massive spreadsheets using the data available. You can summarize the available information by name, category, dates, and other identifiers. Creating a pivot table is simple. Just follow these steps:
- Click on the cells in the sheet that contains the needed table.
Click on Insert in the heading bar at the top and then click on Pivot table.
- A pop-up window will show up, which allows you to select your Data range and gives you the option to insert the pivot table to either a new sheet or an existing one.
- After you choose your desired options, the pivot table should display. You should also see a sidebar appear, which will allow you to tweak various elements in your table.
A slicer is a great way to organize your spreadsheet. It essentially works by slicing away parts of your spreadsheet to show only the needed data. It helps in controlling what is displayed on your spreadsheet. You can add a slicer to your table by following these easy steps:
- Select a heading cell in your spreadsheet, click on Data on the heading bar, and click Add a slicer.
- A pop-up window will appear that allows you to select a data range. Here, you can manually add the data range or choose one from the list of suggested contents.
- You should see your slicer appear with a sidebar on the right that allows you to customize the parameters of the slicer. You can filter by condition or by values.
- Once you’re done with the customization, click away, and your slicer should be finalized.
And you’ll notice the data that was “sliced” is now taken away from the raw data.
Charts are crucial if you want to represent data in your spreadsheet correctly, as they help you make out patterns in your data.
To insert a chart into your spreadsheet, follow these steps:
- Click on Insert on the heading bar and then select Chart.
- You will see a blank chart show up with a sidebar. The sidebar has various options for you to customize your chart. First, select your Data range here. Then you can change the type of your chart using the Chart type option.
- Finally, you can head over to the Customize tab in the chart editor, where you can adjust different perimeters to customize your chart visually.
Charts can often take up more space than they need to. They have a massive disadvantage in overlapping with the sheet, often covering up cells containing crucial data. A sparkline chart is a great way to fix this problem as it adds a chart into a single cell.
To add a sparkline chart, use the following formula:
A sparkline chart can create line charts, column charts, bar charts, and a win-loss chart.
How to Create a Great Google Sheets Dashboard
Here are a few rules to follow to create a visually pleasing and easy-to-understand dashboard.
1. Keep It Simple
Make sure not to add too many distracting visuals. Unnecessary effects can be overbearing to a viewer’s eyes, so always try and make a note of that. It’s always better to use simple 2Dcharts instead of 3D ones with many effects.
2. Use a Good Color Scheme
Your choice of colors can make or break your visualization. Too many colors can make your dashboard look messy, so always use a color palette that follows a theme and is light on the eyes. You can use a color palette generator to generate colors for you that go well with each other.
3. Tell a Story
Connect all the elements with each other organically to have relevance to the overall presentation. Although better than raw data, charts and graphs can still be a lot to take in, so make sure they are clearly linked and titled. An organized spreadsheet is always better than a jumbled mess.
4. Filter the Data
A good dashboard will always filter your data based on variables you wish to view. It isn’t a raw spreadsheet. Bombarding your viewers with information will always result in a disaster, so make sure your data is summarised and easy to understand.
Google Sheets Dashboard Template
Even though using a dashboard is extremely helpful, there are ways we can make it even more valuable for us. Templates are pre-made models that can give you a strong starting point for the type of job you’re looking to do. Using templates can save you a lot of time and effort.
Google offers a few handy templates, which you can see on the main screen of Google Sheets. These vary from finance trackers to the analytics dashboard.
To use it, navigate to File>Make a copy.
How a Google Sheets Dashboard Can Make Your Life Easier
Creating a dashboard in Google Sheets is a great way to organize and visualize your data. Building a dashboard in Sheets isn’t too hard to do, and the benefits far outweigh the time needed to learn how to make a dashboard.
Frequently Asked Questions
What Is a Dynamic Dashboard in Google Sheets?
Dynamic dashboards update in real-time, meaning if there is a change in raw data, then the elements of your dashboard will update automatically. This is very beneficial as it improves efficiency, and the interactive features help make the user experience more enjoyable.
Can I Link My Google Form to My Dashboard?
Google Forms have a setting that allows you to save form responses to Google Sheets. You can choose whether you wish to use an existing spreadsheet or create a new one.
Does Google Have a Dashboard App?
There is no app specifically but they have plenty of dashboard templates available by navigating from the Sheets home screen.
The Bottom Line
Building a custom Google Sheets Dashboard may be ideal for your business. But, there are plenty of templates available on the web, including on this page. You can start from there and add your own touches to build the perfect dashboard that fulfills all of your needs.
If you had some trouble understanding the concepts in this article, don’t forget to download our template to experiment with and make changes to with your desired data points.