Get actionable insights with a data dashboard
Think of the last time you needed to check a KPI or another important metric for a report. Searching through a sea of raw data in multiple tabs and multiple spreadsheets is incredibly frustrating. Especially when you need to find accurate information quickly. You can resolve this problem if you create a dashboard in Google Sheets, providing you and your colleagues with real-time data on demand.
Dashboards present your data in a visual format, giving you an at-a-glance overview of your key metrics. Charts, tables and graphs are engaging and easy for everyone to read, enabling users, coworkers and clients to better understand information extracted from spreadsheets. Plus they can look pretty cool and impressive if you need to present data to others. Many teams now display their performance dashboard for everyone to see, on a TV screen in the office. This can help motivate the team and keep everyone on the same page.
Dashboards can be used to track every business process, from monitoring warehouse stock to analyzing the results of a survey.
Data visualization has become a powerful tool in business and data analysis. For teams working in the cloud, dashboards allow different departments instant access to dynamic data, in real time. These actionable insights make it easier to spot trends and problems more quickly, and make fast decisions based on accurate information.
There’s plenty of specialist data visualization software out there, but it’s also possible to create good quality dashboards with Google Sheets and Google Data Studio. Here we’ll give you a step-by-step guide on how to create a dashboard with Google Sheets and Google Data Studio.
How to create a dashboard in Google Sheets
When designing any dashboard, you first need to ask yourself:
- What data do I want to see?
- What format do I want it in? (E.g. a piechart, a scorecard, a scatter chart, a graph).
In this example, let’s imagine you want to analyze the largest 2000 companies in the world. You want to compare their profits with their ranking in the Forbes 2000 list and you want to see this data report in the form of a column chart. Here’s how to do it.
Step 1: Insert chart
- Open the spreadsheet containing your data. Here we’ve used the dataset from Forbes 2000, including the assets, revenues, market value and other non-numerical KPIs of the largest 2000 companies worldwide.
- Select the column and the rows that you want to include.
- Click Insert Chart from the menu bar.
- Google Sheets automatically suggests a chart for you, indicated as Suggested in the chart editor (that pops out on the right-hand side). In this case, it’s a scatter chart:
Step 2: Change style
- Now adjust the style and data range in the chart editor (on the right-hand side).
- Click on Setup. In this example we will change it to a Column chart.
Step 3: Move dashboard
- Click on the 3 dots in the top right-hand corner of the chart.
- Select move to own sheet. This puts the dashboard in its own tab (sheet) separated from your other data.
- Under Customize, select Maximize for a full-screen view.
- The chart automatically updates once you filter the spreadsheet.
How to add a sparkline
A sparkline is a mini chart that can be added to a single cell to display a trend over a particular time period. This can be useful to spot increases or decreases, indicating where you need to take a closer look at your data. Data often comes as a time series, as is the case with many financial and stock metrics. Trend analysis is also important in web and marketing analysis. You might want to look at trends for certain keywords or page visits, for example.
Here’s a short hack to create a sparkline:
- Make sure trend data or a time series is included in the dataset.
- Use this formula: e.g. =SPARKLINE(N2:Y2)
- The cell can be expanded, just like a normal cell.
Google Sheets is constantly releasing other new features to create more engaging spreadsheets with interactive reporting features. Check out how to use Scorecard Charts, Slicers and Themes here.
Dashboard templates in Google Sheets
If you’re overwhelmed by the prospect of creating your own customized dashboard from scratch, or simply too busy, there’s a range of templates out there to help you. Check out Google Sheets templates gallery or, if you’re working in online marketing, try a specialist reporting solution like Supermetrics.
If you want a ready-to-use automated dashboard that pulls data from multiple different spreadsheets, check out Sheetgo’s templates. From cash flow and inventory management to sales leads and student attendance tracking, there is a workflow template for every business process.
How to make a dashboard in Google Data Studio
For more advanced dashboards with really smart graphics, try Google Data Studio. It allows you to create professional-looking data visualizations without any programming knowledge. It combines various diagram features and the type of data manipulation tools you find in statistical software.
You can:
- Add data from Google Sheets, Google Analytics, Google Ads, BigQuery and social media platforms straight to your dashboard using pre-built connectors.
- Create engaging visualizations with simple drag-and-drop tools.
- Make interactive reports with viewer filters and date range controls.
- Use a pre-built Data Studio marketing template.
- Use the share function to collaborate on a dashboard with other editors, in real time.
Here’s how to do it.
Start Google Data Studio.
First of all, check out the toolbar at the top of the screen and the editing menu that pops up on the right, once you have added a chart.
Step 1: Connect your data source.
Here we’ll start with a blank report.
In the pop-up on the right-hand side, click Add a data source > Create new data source. Here you can select a file from Google Drive or connect to other apps like Google Ads or Google Analytics.
How to connect Excel files to Google Data Studio
Google Data Studio connects directly to Google Sheets, but if you want to pull your dataset from Excel files, you’ll need to transfer the data to Google Sheets first. You could do this manually, but a dashboard only functions if its being constantly fed live data. To solve this problem, you can set up an automated data workflow using Sheetgo to connect your files seamlessly.
Excel ⇒ Google Sheets ⇒ Google Data Studio.
Find out here how to set up an automated chain of connections with Sheetgo.
Step 2: Insert a diagram
In the following examples, we’ll use our Forbes 2000 dataset again. In the menu bar, go to Insert and then choose what kind of visualization you want.
Here are some examples of visualization you can create:
Create a map in your dashboard
Let’s say we want to create a map from our dataset. We want to view the density of Forbes 2000 companies per country. Countries with more companies will appear a darker shade of blue on the map.
- Click Insert and choose Geo Map (alternatively, click on the Add a chart button).
- Go to the pop-up menu on the right.
- Under DATA, change the metric to Country and the aggregation to Count.
Add a table
Here we want a table to show the best ranking company for each country.
- Go to Insert and choose Table.
- In the menu on the right, choose Country as the dimension.
- Drag and drop rank to Metric.
- Change the aggregation of rank to Min.
- Put position under Sort.
Create a donut chart
We’ll now create a donut chart displaying the different industries represented by the data.
- Click on Add a chart, select Pie > Donut.
- Under Add dimension, add Industry.
- Under Metric, select Industry.
- Sort by Industry > Descending.
In the right-hand menu click on STYLE, select Color by > Single Color.
You can add another dashboard to your report on a separate page by clicking Add a page in the menu bar.
Here we’ll create a new table displaying the market value of the largest companies in the dataset.
- Insert > Table.
- In the right-hand menu, under DATA, set Dimension to Name.
- Under Metric, drag and drop marketvalue.
- Under Sort, select marketvalue.
- Under STYLE > Metrics, select Heat map.
We’ll now create a second table containing more KPIs, providing an overview of the differences between the largest companies in this list.
- Insert a Table and add the metrics: marketvalue, revenue, assets.
- Under Dimension, select Name
- Sort by > marketvalue
- Under STYLE > Metrics, choose Bar. Choose different colors for each column.
How to create a filterered (Pivot) table in your dashboard
Here we want a table that displays all assets per country, per industry.
- Insert > Pivot table.
- Under Row Dimension, add Industry.
- Under Column Dimension, select Country.
- Under Metric, add Assets (note that the Aggregation should be set to Sum by default).
We want to add a filter for US, UK Germany and China.
- Go to the DATA menu on the right
- Under Filter > Add a filter, click on Create a filter
A menu opens at the bottom of the screen. Here you select your filter option
In this example we will use the following steps:
- Include > Country > Equal to (=). As a value we will enter, for example, Germany.
- Click on OR
- Repeat for the other countries.
Google Data Studio crashing? Prepare your data first.
Google Data Studio is a powerful app for creating impressive dashboards but it can struggle to process large amounts of data in complex reports. You might notice that your dashboard in Data Studio slows down, freezes or crashes, especially when you’re using lots of filters and Pivot tables.
It’s a good idea to prepare your data beforehand, so that Data Studio can import consolidated and summarized data directly from simpler Google Sheets files. If you want your dashboard to combine data from multiple sources, you can use a tool like Sheetgo to streamline and process your data in a workflow, before connecting to Data Studio. With Sheetgo you can:
- Automate data transfer between Google Sheets files, as well as from Excel and CSV.
- Schedule automatic updates when you want them.
- Consolidate and merge data from multiple files and folder into one sheet.
- Create your own customized workflows.
Read how to do it here.
Good luck creating your dashboard. Looking for other reporting solutions in Google Sheets? Check out our top 10 recommended Google Sheets add-ons for reporting.
