KPI Dashboard in Google Sheets (The Ultimate Guide)

KPIs or Key Performance Indicators are an integral part of any thriving business. While KPIs help businesses to track their progress, they also make it easy for businesses to make informed decisions based on data-driven insight.

A KPI dashboard is a robust tool that allows businesses of all sizes to visualize their performance metrics in one place and monitor them over time.

Now, the million-dollar question is, how can you create a KPI dashboard in Google Sheets? Well, in today’s guide, we will show you a comprehensive step-by-step guide to creating a KPI dashboard in Google Sheets.

From setting up your spreadsheet to designing effective KPIs and visualizations, today’s Google Sheets tutorial has everything you need to know about creating a KPI dashboard in Google Sheets.

But before we jump into the practical aspects of this guide, let’s start with the basics- Understanding KPI reports in Google Sheets.

What is a KPI dashboard in Google Sheets?

If you’re just hearing about a KPI dashboard in Google Sheets for the first time, it’s easy to get confused. But not to worry, it isn’t as complex as you imagine it to be.

In simple terms, a Google Sheets KPI dashboard is a data visualization-centric spreadsheet that shows a department, company, or individual’s key metrics in a chart or graph.

The primary purpose of a KPI dashboard is to convey information as quickly as possible, making it easy for top-level management to make decisions and strategize the next move.

Why KPI reports in Google Sheets are important

As we mentioned right from the get-go, KPIs are important metrics that provide better insight into your business’s performance. KPIs are important for determining whether business goals are progressing in the right direction.

If you work in a company with several departments, there is every likelihood that your department leaders will sometimes share the most important KPIs related to your department. This is intended to show how the various teams are performing.

Unfortunately, keeping track of these KPIs can be daunting, especially if you don’t have the right tool. This is why many executives now rely on Google Sheets to create KPI reports.

With Google Sheets, you have an excellent tool that makes it easy to visualize technical data. From common spreadsheet calculations to more comprehensive formulas, Google Sheets provides access to exciting tools you can use for reporting and analysis.

With a good KPI report, you and your team will gain valuable insight into the performance of each section of the company. And yes, you’ll also have a clearer view of how your business is performing, including problematic areas that must be addressed.

Creating a KPI dashboard in Google Sheets

Having gone over the basics, we are sure you now have a better understanding of KPI dashboard in Google Sheets. Now, we want to jump into the exciting part, which involves creating a KPI dashboard in Google Sheets database.

You might find it a bit challenging if you haven’t done this before. But with us guiding you every step of the way, you’ll find the entire process straightforward.

The first thing we need to do is set up a data source for our Google Sheets database. You can do this using a Google Form you previously used for gathering data or a CSV file from your analytics tool, whichever works for you.

But since we don’t have any of those, we will be creating our database manually and inputting it into a spreadsheet.

For this guide, we will look at two specific KPIs:

Monthly marketing expenses database
Leads and conversion metrics.

What this means is that we will be working with two specific spreadsheets.

We will kick off with the monthly marketing expenses database.

Here is the sample data we will be working with:

Step 1: Highlight the data in the spreadsheet

The first step we need to take to create a KPI dashboard in Google Sheets is to select the range of data in our spreadsheet. So go ahead and select all the data in your spreadsheet.

Step 2: Insert > Chart

With our dataset now highlighted, we need to create a chart, which is super easy. Simply head to the Insert tab and select Chart. Google Sheets will automatically create a chart in your spreadsheet.

Here is a short video showing you how to execute this step.

Using Google Sheets, you can customize your chart using the Chart editor. You should typically find it on the extreme right of your spreadsheet. Using the Chart editor, you can:

Choose between different chart types, including line charts, bar charts, column charts, and much more.

Make personalized changes to your chart, including adding titles.

Finally, you can customize your charts by changing fonts, adding colors, and tweaking other little changes.

Step 3: Convert chart to KPI dashboard

After you’re done creating a chart in your spreadsheet? Well, the next thing you need to do is convert the chart into a dashboard, which is super easy.

What we are basically doing here is bringing our raw data to life. And guess what, there are two ways you can convert your chart to a KPI dashboard in Google Sheets. Read on as we show you both ways.

Publish it

When you opt for this method, Google Sheets generates a shareable link and publishes your chart on the web. The cool thing about this method is that you can share the link with your team members. Not just that, this method also allows you how to seamlessly manage permissions, especially when it comes to who can edit and view the dashboard.

Here is how to execute this method.

Start off by clicking anywhere on your chart. You should notice a three-dot icon on the top right of the chart. Click on it and select the option for “Publish chart.”

The video below shows you exactly how to execute this step:

After selecting the option for publish chart, Google Sheets will automatically launch a pop-up window. Now, click on publish for Google to publish the chart online.

With your chart now published, you should get a link to the published chart. Here is what ours looks like.

Try to visit the link to view the KPI dashboard created by Google Sheets. After clicking our link, here is what ours looks like.

The drawback of the publishing method

While the publishing method gets the job done, it does have its shortcomings. Whenever you edit the raw data, it takes forever for the changes to be effected on the dashboard, which can be annoying.

To resolve this issue, we highly recommend opting for the second method, which we will show you shortly.

Move to own sheet

Although this method will have you toggling between tabs now and then, we prefer it because it reflects changes made in real-time.

Here is how to execute this step in three easy clicks.

Start by clicking anywhere on the chart. This will reveal a three-dot icon on the top right of your chart. Click on it and select the “Move to own sheet option.” After selecting this option, Google Sheets will automatically create a new tab in the same sheet and move your chart over there.

Here is what our chart looks like in the new tab created by Google sheets:

As we mentioned earlier, the benefit of using the “Move to own sheet” method is that any changes made to the original data reflect instantly in the dashboard.

Leads and conversion metrics KPI

Everything we have covered so far for creating a KPI dashboard in Google Sheets is for the first metrics, which relate to the monthly marketing expenses. Now, we will proceed to create a chart for the leads and conversion metrics.

All we need to do is create a new sheet in our original spreadsheet. And here is how to go about it.

Step 1: Create a new spreadsheet

Beneath your current spreadsheet, you’ll find a plus icon. Simply click the plus icon, and Google sheets will automatically create a new spreadsheet titled Sheet 2.

After executing the step above, you should have a new spreadsheet like the one below.

Step 2: Create a database

After creating a new spreadsheet, we need to create a new database for lead and conversion. For this guide, we will use the following sample data.

Step 3: Select Dataset

With our data now entered into our spreadsheet, we need to highlight the dataset we would use to generate a chart in Google Sheets. Since we are using the entire data in our spreadsheet, we will go ahead and highlight all of them.

Step 4: Insert > Chart

Done selecting the dataset you want to use to create a chart in Google Sheets? You can now proceed to the next step, which is pretty straightforward. Simply head to the Insert tab and select Chart.

The video below gives a clearer picture of how to execute this step:

Step 5: Change the line chart to a column chart

Since we already created a line chart for the first metric, we want the second metric to be a different type of chart. Here is how to change it.

Launch the Chart editor. To do this, all you need to do is click anywhere in your chart. You should notice a three-dot icon in the top right corner. Click on it and select the option for “Edit chart.”

This action will launch the Chart editor to the extreme right of your spreadsheet.

Now, head to the Chart editor and select the Setup menu. Underneath the option for chart type, change line chart to pie chart.

After the changes have been effected in your spreadsheet, you should have a chart that looks like this.

Convert chart to dashboard

Just like we did for the previous step, you’ll need to repeat the procedure we detailed earlier to create and make a dashboard.

We know it can be tiring, but there is no way around it. After creating a dashboard for lead and conversion, you can now copy both charts into a new spreadsheet.

Copy both charts to a new spreadsheet called dashboard

With both dashboards now created, we need to create a new spreadsheet and rename it as dashboard. This is where we will combine both charts.

Here is how to do it.

Navigate to the bottom of your spreadsheet and click the plus icon. This action will create a new spreadsheet called Sheet 4.

Rename new sheet to Dashboard

After creating the new sheet, we need to rename it to Dashboard. Here is how to execute that:

Right click on Sheet 4 and select the option for Rename, then type in Dashboard. It’s that easy.

Copy charts to Dashboard

Now, copy the first chart we created in Chart 1, and paste it to cell A1 in the newly created sheet for Dashboard.

After completing this step, here is what our dashboard looks like:

Now, repeat the same process by copying the chart in Chart 2 and pasting it into Cell A17 in the spreadsheet for Dashboard.

If you did everything right, you should have a dashboard that looks like this:

From the video above, you’ll agree that our KPI dashboard in Google Sheets allows you to compare data and different metrics from one place, which is exactly the objective of creating a dashboard in Google Sheets.

Conclusion

Creating a KPI dashboard in Google Sheets is a brilliant way to monitor your business performance. With a KPI dashboard, it’s easy to make informed business decisions, as most of your decisions will be taken based on available data.

By following the step-by-step guide outlined in today’s guide, you’ll be able to define your KPIs, create charts and visualization and build a KPI dashboard in Google Sheets. We also included picture and video illustrations to guide you every step of the way.

In case you need more clarifications or have a question you’d like to ask, please leave a comment below, and we will be happy to respond.