How to Create a KPI Report in Google Sheets? Step-by-Step Guide

Author's avatar Reporting UPDATED May 29, 2024 PUBLISHED Mar 23, 2022 22 minutes read

Table of contents

    Peter Caputa

    Create interactive data visualizations, unlock data-driven insights, and visualize real-time performance effortlessly with Databox. To discover how, visit this page to learn more.

    Marketing, sales, HR, accounting, finance, research and development. All of these departments in your company have specific goals to achieve and use KPIs to track their progress towards those goals.

    Every so often, team leaders will forward these KPIs to you so that you can track the overall performance of your business. According to our report on the state of business reporting, almost half of the companies set their goals for 1 to 5 metrics.

    Analyzing the data separately for each department is immensely time-consuming, which is why we use Google Sheets to create KPI reports.

    Google Sheets dashboards and KPI reports provide you with a comprehensive overview of the most important metrics from each department, all in one place. With these reports, you will have an insight into which parts of your business are doing great and which may be problematic.

    Also, by using visualization tools in Google Sheets, you will be able to present the most valuable data to your key shareholders in an understandable way.

    In this guide, we are going to teach you how to build a great KPI report in Google Sheets that you can use to impress the highest-ranking members in your company.

    setup-free-dashboard-databox-dsss

    What Is a KPI Report in Google Sheets?

    KPIs are metrics that provide you with insight into how your company is performing. They are commonly used for determining whether business goals are progressing in the right way.

    In most cases, your department leaders will share the most important KPIs in their area to show you how their team is performing.

    However, keeping track of all the significant KPIs from each department is extremely hard without the help of certain tools.

    This is why many executives create KPI reports in Google Sheets.

    Google Sheets provides a relatively easy way for you to visualize technical data. It includes the common spreadsheet calculations and formulas that you can use for reporting and analysis.

    A good KPI report can help you and your teams acquire actionable insights into the performance of each part of the company. You will have a clearer overview of your business performances and you will gain an insight into which areas seem to be causing issues.

    By tracking the metrics in your KPI report, you can assess the success of your strategies and the overall health of your business. 

    How to Create a KPI Report in Google Sheets

    Using KPI reports is one of the best ways to gain perspective over the various departments in your business.

    By highlighting all the key metrics in a single project dashboard, you will have access to all the significant data that you will need to make informed decisions.

    The best thing is – KPI reports can be extremely flexible.

    You can either choose to create a KPI report that covers all aspects of your business (marketing, sales, accounting, HR, etc.) or a specific report that only tracks the performance of a particular department.

    However, no matter which KPI report you need, you will have to go through the grueling creation process first.

    Creating a KPI report can be extremely frustrating, even for those that have years of experience.

    But don’t worry, we have prepared a step-by-step guide that will help you go through it with ease.

    Follow these few steps to create a comprehensive KPI report:

    Step 1: Create a Database

    The first thing you need to do is set a data source for your Google sheet database.

    This can either be done through a Google Form that you already use for gathering data or a CSV file from your analytics tool.

    However, we recommend that you create a database manually since it’s the best way to later automate data generation in your Google Sheet.

    As an example, we will create a ‘monthly marketing expenses’ database. Next, we will add a ‘Budget’ column that we will use to track spending trends.

    Step 1: Create a Database

    In case you are using a complex tool like Google Analytics to gather data in your campaign performance, you will need to integrate certain GSheet functions to make the analysis process easier.

    These are some of the functions you will need:

    • Conditional formatting – Use “IF” conditions to format the cells in your Google Sheet
    • Vlookup – Vertical data lookup
    • Hlookup – Horizontal data lookup
    • Pivot table – Useful for isolating and classifying data

    Step 2: Develop a Chart

    After creating a database, your next step will be to develop a chart.

    Do this by selecting all the data in your spreadsheet and clicking ‘Insert’. Next, select ‘Chart’.

    Step 2 – Develop a Chart

    The ‘Chart’ editor option allows you to create a pie chart, bar graph, or any other type of line graph that you want to integrate.

    Additionally, you can edit graph/chart details such as axis title, chart height, color theme, and many more.

    Related: How to Create A Bar Graph (and more) in Google Sheets

    Step 3: Format the Chart

    Time for the most important step – formatting your chart into an understandable full-overview dashboard.

    Once you are finished with the ‘Chart’ editor, select the chart area and move it away from the data table.

    Next, click the ‘three dots’ symbol located in the right corner of the chart area.

    You will see two options: ‘Publish it’ and ‘Move to own sheet’.

    By clicking on ‘Publish’, the Google Sheet will provide you with a link that you can send to other users. In the ‘Permissions’ option, you can also restrict certain users from entering the link.

    Step 3: Format the Chart

    In case the published chart is running a bit slow, you can edit the data to see how long it will take for the chart area to be updated.

    With the ‘Move to own sheet’ option, you can refresh the data much quicker.

    Although, you will have to switch between tabs to edit the data and adapt it to the chart.

    Format a Google Sheets Chart

    By following these steps above, you will be able to create a few different charts and then place them in a separate tab.

    With this kind of data visualization, you can compare different sets of metrics in one place.

    create a few different charts and then place them in a separate tab

    Limitations of Using Google Sheets for Dashboards

    While Google Sheets have long been the epitome of project management tools, we can’t say that’s the case nowadays.

    Technology is constantly evolving and with new management tools being released every few months, Google Sheets simply isn’t the best and most practical alternative anymore.

    Here are just some of the limitations of using Google Sheets for dashboards.

    Limited Automation Ability

    If you have gone through the creation process, then you have already experienced having to manually input every single piece of information.

    This is because Google Sheets has no automation features.

    Sure, you can use a Google Apps Script or modify Google Sheets to fasten up the process, but this isn’t really the easier alternative. Simply learning how to do it will take ages.

    Once your projects are up and running, updating a Google Sheet dashboard manually will become incredibly tiresome.

    Limited Integration

    While there are project management tools that support Google Sheet integration or Google spreadsheet implementation, it’s an extremely rare occurrence.

    Most of the time, Google Sheets will only be compactable with the Google Workspace.

    This may not seem like that big of a deal, but once you need certain data integrated into your project tool, you will see how big of a hassle it actually is.

    Lacks of Comprehensiveness

    The third limitation of Google Sheets is that it simply lacks some typical day-to-day functions.

    Here are some of the things that GSheets can’t cover:

    • Time management
    • Time tracking
    • Communication
    • Internal database categorization

    All of these functions are important for growing businesses and if you aren’t using powerful project management software, you won’t get very far with Google Sheets.

    How to Create a Google Sheets Dashboard in Databox

    As we said, with project management tools constantly evolving, Google Sheets simply can’t keep up with the pace with the new features.

    With limited automation, integration, and comprehensiveness, creating Google Sheet dashboards will inevitably take up too much of your valuable time.

    This is why advanced project management tools such as Databox are considered to be a much better alternative.

    With Databox automation features, you will be able to quickly pull out any useful data into your dashboard and then visualize it at a click of a button. This provides you with a comprehensive and understandable overview of your key metrics.

    Not only that, with visualized data, you will have an easier time sharing your performances with the rest of the team.

    Want to give it a try? Here’s a quick video tutorial of how it works.

    For a more in-depth walk-through, as well as specific use cases for getting started with the integration, continue reading below.

    Step 1: Connect Google Sheets

    In the Databox web app, navigate to Databoards and create a new Databoard. In the left-hand panel, switch to the ‘Google Sheets’ source.

    Connect Google Sheets to Databox

    Step 2: Create a custom metric

    Once you connect your spreadsheet, you can create your first custom metric.

    The Google Sheets integration is powered by Databox’s Metric Builder tool. Metric Builder provides a user interface (UI) where non-technical users can visualize their data just by making selections in the interface. With it, there is no need to learn a new query language or even write a line of code.

    So, anyone on your team can use Metric Builder to extract custom metrics from your spreadsheets in just a few clicks.

    Create a custom Google Sheets metric

    Here is a full explanation of each of the form fields that will help you build a custom metric:

    Custom Metric Name

    You can enter a name for your metric. Alternatively, leave it blank and skip to the next fields. Once you choose a Value from your spreadsheet, we will automatically populate that Value’s row or column header as the custom metric name.

    Value (Metric)

    The first step is to select a column, row, or cell range that holds the numerical values you want to visualize. Put your cursor in the Value form field, then make a selection directly on the spreadsheet below by highlighting the cells in the spreadsheet with your mouse.

    You can also enter a cell range manually by using the ‘A1 notation.’ (If you are unfamiliar with that concept, you can read more about ‘A!’ notation here).

    Values must be numerical values, not strings. You can use currencies or other units with numeric values and we will recognize them. In fact, Google Sheets does a good job of automatically formatting the cells, so you shouldn’t need to worry about formatting them inside Databox.

    Date (Timestamp)

     The next step is to select a column, row, or cell range with dates (timestamps). Again, when this input is selected in the Databox Metric Builder interface, you can make a selection directly on the spreadsheet below it by highlighting the cells you want to visualize. Values and Dates must have corresponding values, so make sure you select a similar range of cells. Databox accepts any ‘Date’ or ‘Date time’ that is formatted inside Google Sheets.

    To ensure that all cells are formatted correctly, you can select them and go under “Format > Number > Date” within your Google Sheet. If you have hours added please select “Date time” format. If your Date cells are formatted as Strings in Google Sheets (not recommended), we will assume your data is the US date if in the format (Month/Day/Year) and if no day is specified (only Month/Year), we will store values on the first of each month.

    Dimension (optional)

    If you have additional information in your spreadsheet, for example, Country of purchase, Referer information, Browser, Product name, etc., you can reference cells or ranges for Dimensions by using ‘A1′ notation or by selecting it directly on the spreadsheet preview below the Metric Builder form.

    Each metric can support up to one Dimension range. To visualize multiple Dimensions, you must create another custom metric.

    Number format and Scale 

    Read more about Format and Scale settings in Databox in this article.

    Data type

    This is the last setting that is important for the correct visualization of your data.

    The data type describes the data you’re pulling into Databox and depends on the use-case and kind of values. There are three data types that are described right in the interface. You can learn more about Data types in our help documentation. If you are not sure whether you’ve selected the right data type, you can use the live preview as a way to determine whether your data is being summarized correctly.

    On the right side of the Metric Builder form, there is a live preview of your data, which also gives you an option to switch between visualization types like line graphs, pie charts, and numbers. We show only the appropriate visualization for the data you’ve selected.

    Step 3: Drag and drop your custom metric onto a dashboard

    Once the query is saved, your new metric will appear in the left-hand side panel (under this Google Sheets connection) and you can easily drag and drop it right into your dashboard.

    Drag and drop your custom metric onto a Databoard

    Voila, that’s it!

    In addition to visualizing data in a Databoard, you can also use your Google Sheets custom metrics in AlertsScorecards, and in Data Calculations.

    Best Practices for Creating a Google Sheets Dashboard

    There is no “right way” to build a Google Sheet dashboard.

    However, there are certain best practices that can take you a few steps closer to developing your perfect Google Sheet dashboard.

    Here are some that you might find useful.

    Keep it simple

    While adding visuals to your dashboard can be fun, you shouldn’t overdo it. Too much visualized data may backfire on you and distract your key shareholders from what’s important.

    Pick formats that can represent your data accordingly and use them to highlight the numbers in the dashboard that you think are most important.

    Use an Appropriate Color Scheme

    Don’t use flashy color schemes. Instead, pick some neutral colors that aren’t too distracting but still do a good job of highlighting your KPIs.

    Tie All Your Visualizations into One Story

    You should figure out how to connect your charts/graphs in a way that they tell a specific story.

    Simply displaying numbers can be confusing and your shareholders will often be short on time to analyze the meaning behind each KPI. If you are able to tell a story with your visualizations, you and your team will have more time to discuss future strategies.

    Enable data filtering

    Once you create your dashboard, you should be able to filter your data upon KPI variables.

    By using filters, you can analyze data from specific sources and filter the dashboard metrics that fit the criterion.

    Read Our Help Article Related to Google Sheets

    In case you need additional help understanding Google Sheets, you can read our help articles.

    We describe all the Metric Builder fields and provide additional examples.

    Troubleshoot by Checking Out the Query Log

    Sometimes, you may run into certain issues such as missing some pieces of data.

    The best way to figure out the problem is by checking out the Query log.

    Follow these few steps:

    • Go to the Metric Builder page (under ‘Data Manager’)
    • Search for your custom (query) metric
    • Check the ‘Log’

    It happens that users accidentally change the spreadsheet by moving rows and columns. By checking the ‘Log’, you will have a clear picture of what happened.

    Select a Whole Column or Whole Row, Rather Than Fixed Ranges

    When referring to Values, Dates, or Dimensions, you should select a whole column or whole row, rather than fixed ranges.

    That way, once you add new rows and columns, the data will automatically be recognized and your custom queries won’t need to be changed.

    Avoid Discrepancies for Long Time Periods

    Discrepancies typically occur if you have previously selected the wrong ‘Data Type’ in the Metric Builder main interface.

    Read this article to learn more.

    Correctly Format Your Dates in Your Spreadsheet

    Always make sure you use the ‘Date’ or ‘Date Time’ to correctly format the dates in your spreadsheet.

    Similarly, you can use the ‘Numbers’ or ‘Currency’ options to ensure the correctness of your values.

    The best way to define the correct format is by selecting the cells in your Google Sheet and opening ‘Format’ > ‘Number’ > ‘…’.

    Check Your Time Zone Settings

    If you notice data from December is being stored for November, that means there is a problem in the timezone settings.

    Before connecting your spreadsheet, you should check the timezone settings in your Google Sheets (under ‘File > Spreadsheet settings… > Time Zone’).

    If you are using Databox, then the GSheet time zone should match the one in your Databox account (you can check it under ‘My Profile’).

    Google Sheets Report Examples and Common Use Cases

    Do you need a place where you can track the most important metrics from your KPI reports?

    Databox customizable dashboards can help.

    By using our pre-built dashboards, you will be able to integrate all of your most important data from Google Sheets into one comprehensive report. Plus, with only a few clicks, you can visualize the data and make it more understandable to other high-ranking members of your company.

    Here are some of our best examples.

    Financial Health Report Example

    Making informed financial decisions comes from understanding your numbers. The Financial Health Report Example helps you with this.

    Some of the things you will learn are:

    • How well you are doing with customer acquisition
    • What is your revenue growth
    • What is the cost of customer acquisition
    • What are the general and administrative costs

    With all of these questions answered, you will have a better insight into your company’s profitability.

    All of your key financial data can be compiled into a single Google Sheet where you can also integrate information from the other business systems that your company uses.

    Once the Sheet is set up, you can connect it to this comprehensive dashboard and view all of the key metrics in one place.

    Financial Health Report Example

    Sector Report Example

    No matter how big or small your business is, keeping track of all the different departments (sales, marketing, accounting, etc.) can be overwhelming.

    That is why we use the Sector Report Example.

    Although you probably have team leaders whose responsibility is to meet their team goals, as an executive, you want to be kept in the loop of how things are progressing.

    However, creating a specific report for each department takes up too much of your valuable time and just isn’t worth it.

    By using a comprehensive dashboard such as the Sector Report, you will be able to integrate the most important KPIs from each of your departments in one place.

    You will also be able to visualize all of these metrics and make the performance indicators understandable to the high-ranking members in the company.

    Sector Report Example

    HR Report Example

    The most valuable asset of any business is the people working there.

    And even if you have the most reliable employees, you will still want to track their progress in achieving business goals.

    You can do this by using the HR Report Example.

    This customizable dashboard allows you to collect employee engagement data through which you will gain an insight into what’s going on among the ranks.

    The best thing is, you can use it to create KPI dashboards that monitor employee sentiment and high-priority issues.

    When you have this information, you can discuss it with your team leaders and create better employee engagement plans for the future.

    HR Report Example

    Sales Report Example

    Now let’s see a few examples of how you can build a sales report in Databox from your Google Sheets data.

    Let’s take a look at this spreadsheet.

    sales data spreadsheet

    It’s a log of successful purchases for an imaginary online book shop. It includes the date and time (timestamp) of each purchase (column A), product name (B), revenue of each sale in US Dollars currency (C), referer (D), customer name (E), country of residence (F) and email (G).

    We can make many custom metrics out of this spreadsheet.

    To get the Revenue metric, select column C (the whole column “C:C”) for Values and for timestamps, select the whole column “A:A” for Dates.

    For this kind of example, the correct Data Type is the ‘Unprocessed data (event value)’. This Data Type supports cells populated with event values for the specified Date/Timestamp and for longer Date Ranges (i.e. This Month, This Quarter, etc.), with multiple events, data will be aggregated and the total will be displayed for the time period.

    The example spreadsheet includes more data than just revenue, of course.

    So, let’s also create a new metric with ‘Country’ dimension (column F), to see how my revenue is distributed across different countries. To create the ‘Revenue by Country’ metric, the query would look like the screen grab below, where I chose column “F:F”  as the Dimension value:

    query builder example for google sheets

    In this case, we switched to the ‘Pie’ visualization because I’m interested in the total distribution in 2018 by country.

    One custom metric can use only one dimension. But, if you want to view revenue by different dimensions (besides country), create a new query with a different dimension selected and leave the Values as column C and Dates as column A.

    Monthly Overview Report Example

    Another common use-case for Google Sheets is when a company manually enters key performance indicators (KPIs) into a spreadsheet each month. As an example, take a look at this spreadsheet.

    monthly KPIs data spreadsheet

    It’s a spreadsheet template with seven KPIs for an imaginary company.

    It includes the months (row 1), sessions (row 2), new customers for each month (row 3), a calculated conversion rate (row 4), monthly revenue in US Dollars currency (row 5), average revenue per customer (row 6), expenses in US Dollars (row 7) and profit (row 8).

    (As an aside, the conversion rate is calculated inside this Google Sheet. I could have also set up this calculation inside Databox with the use of the Data Calculations feature.)

    query builder example for google sheets

    Let’s make a Databoard from this worksheet…

    To get the Expenses metric, let’s select the row 7 (the whole row “7:7”) for Values and months (the first row “1:1”) for Dates.

    For Dates, we have entered Months in the spreadsheet, but these are actually the first day of each Month.

    For this example, the correct Data Type is the ‘Daily values (processed data)’. This Data Type supports cells populated with daily (or in this case monthly) values for the specified Date/Timestamp.

    In Databox, the latest updated value will be displayed (in case you had multiple columns with the same date, but different times.). For longer Date Ranges (i.e. This Year, This Quarter, etc.), data from each day will be aggregated.

    In our example, monthly data will be stored in the first of each month. To have more granular data, you could store daily values in your spreadsheet, too.

    setup-free-dashboard-databox-dsss

    Build a Google Sheets Dashboard in Databox for Free

    Google Sheets are a useful tool for editing, organizing, and later analyzing the most important KPIs in your business.

    Similar to other spreadsheets, GSheets also include built-in formulas, pivot tables, and conditional formatting.

    But, even with the built-in charts that you can use for visualization, combining the charts into a comprehensive dashboard is pretty much mission impossible.

    Having to manually update your GSheet each time you gather enough data is simply not worth the time or the nerves anymore.

    Luckily, Databox has your back.

    You can integrate your Google Sheets with our custom dashboard software to create a comprehensive report that includes insightful visualizations of your key metrics.

    Combining multiple data sources in one place is one of the main advantages of Databox. By connecting data from your Google Sheets, you can create custom dashboards that showcase the most important KPIs from each of your departments.

    Telling the story behind your data has never been easier.

    Want to create a KPI report but you are short on time? Sign up here for our free setup service.

    Author's avatar
    Article by
    Filip Stojanovic

    Filip Stojanovic is a content writer who studies Business and Political Sciences. Also, I am a huge tennis enthusiast. Although my dream is to win a Grand Slam, working as a content writer is also interesting.

    More from this author