How to Create a Tracking Dashboard in Google Sheets

A tracking dashboard is one of the best ways to understand the performance of your business, but building one often seems complicated and expensive. The good news is you already have a powerful and free tool for the job: Google Sheets. This article will guide you step-by-step through creating a clean, automated, and insightful tracking dashboard entirely within Google Sheets.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First, Why Use Google Sheets for a Dashboard?

While dedicated business intelligence tools are powerful, they often come with steep learning curves and high costs. Google Sheets provides a fantastic starting point for a few key reasons:

• It’s free. You can’t beat the price tag. All you need is a Google account.
• It’s collaborative. You can easily share your dashboard with team members, clients, or stakeholders, and you can all work on it in real-time.
• It’s in the cloud. Access your dashboard from any device, anywhere, without worrying about saving different versions of a file.
• It’s surprisingly powerful. With features like pivot tables, charts, and simple formulas, you can visualize your data effectively without needing to be a spreadsheet guru.

Step 1: Plan Your Dashboard Before You Build

Before you open a blank spreadsheet, take a few minutes to plan. A dashboard without a clear purpose is just a collection of charts that don’t tell a story. Answering these three questions first will save you hours of frustration later.

1. What are your Key Performance Indicators (KPIs)?

What metrics actually matter to your goals? Don't track everything, track what matters. KPIs are the specific, measurable metrics that show whether you are achieving your objectives.

• For an e-commerce store: You might track Total Revenue, Average Order Value (AOV), Carts Abandoned, Conversion Rate, and Customer Lifetime Value (CLV).
• For a content creator: You might focus on YouTube Subscribers, Pageviews, Video Watch Time, or Newsletter Sign-ups.
• For a sales team: You'd be interested in New Leads, Deals Closed, Sales Pipeline Value, and Conversion Rate by Rep.

Choose 3–5 primary KPIs and a handful of secondary metrics to support them. Write them down.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

2. Where will your data come from?

Identify the sources for your KPIs. Your data will likely come from a few different places:

• Manual Entry: For simple data that doesn't change too often, like weekly follower counts from a social platform you check by hand.
• CSV Exports: The most common method. Almost every platform (from Shopify to Mailchimp) allows you to export your data as a CSV file, which you can then import into Google Sheets.
• Automated Connectors: For popular services like Google Analytics, you can use the official Google Sheets Add-on to pull data directly into your sheet on a schedule.
• Other Google Sheets: If your team already tracks data in other spreadsheets, you can pull it into your dashboard's sheet using the IMPORTRANGE formula.

3. How should the dashboard be laid out?

Finally, sketch a rough layout on paper or in a simple design tool. A classic dashboard layout has a few key components:

• SCORECARDS: Big, bold numbers showing your main KPIs at the very top for an at-a-glance view.
• TRENDS: Line charts showing how your key metrics are performing over time (e.g., weekly revenue, monthly website traffic).
• BREAKDOWNS: Bar or pie charts that break down a metric into categories (e.g., Sales by Product Category, Traffic by Marketing Channel).
• FILTERS: Interactive dropdowns that let you (or your team) filter the entire dashboard by a specific date range, product, or campaign.

Step 2: Set Up Your Google Sheet

The secret to a stress-free dashboard is separating your raw data from your visualizations. Create a new Google Sheet and add two tabs at the bottom:

1. Raw Data: This is where all your imported or manually entered information will live. Think of it as the engine room. It can look messy, and that's okay.
2. Dashboard: This is your presentation layer. It will be clean, well-organized, and only contain charts and summary data. All the information displayed here will pull from the "Raw Data" tab.

Now, get your data into the "Raw Data" tab. For this tutorial, let’s imagine we’re tracking website traffic and have exported a CSV from our analytics tool with columns for Date, Traffic Source, and Sessions.

Simply copy and paste that data into your "Raw Data" tab. Your table should look something like this:

Step 3: Build the Dashboard Components

Now for the fun part: building the visual elements on your "Dashboard" tab. We will build everything here by referencing the data in the "Raw Data" tab.

Create Big-Number Scorecards

First, let's create a scorecard for our most important KPI: Total Sessions.

1. On your "Dashboard" tab, select a cell (like B2). We'll put the number here.
2. In the formula bar, type in a formula to calculate the total sessions. It will reference the 'Sessions' column in your other tab:

=SUM('Raw Data'!C:C)

1. The cell will now show the total number of sessions. To make it a scorecard, style it!

• Increase the font size to something large (e.g., 48pt).
• Center the text and make it bold.
• In the cell underneath it (B3), simply type a label like "Total Sessions".
• Give the scorecard a light background color to make it stand out. Repeat this for 2–3 other key metrics.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Prepare Data for Charts with Pivot Tables

Dashboards are powered by summary tables. You can’t create a simple line chart trend from 10,000 individual rows of data. You first need to summarize that data. The easiest way to create summary tables without complex formulas is by using a Pivot Table.

Let's create a simple pivot table to summarize our sessions by month.

1. Go to your "Raw Data" tab and select all your data (Ctrl+A or Cmd+A).
2. From the top menu, go to Insert > Pivot Table.
3. In the dialog box, choose "Existing sheet" and then click the grid icon to select a spot in your "Dashboard" tab to place it (e.g., cell E2). Keep a little space away from your scorecards.
4. The Pivot Table editor will open on the right. Now, just drag and drop:

• In the Rows section, click "Add" and select "Date".
• Right-click on any of the dates in your new pivot table, hover over "Create pivot date group", and select "Year-Month." This rolls up your daily data into months automatically.
• In the Values section, click "Add" and select "Sessions". The default setting is SUM, which is what we want.

You’ve just created a perfect little table that summarizes total sessions by month. This is the table we'll use to create our trend chart.

Build Your First Chart (A Line Chart)

With our new pivot table ready, creating a chart is easy.

1. Highlight the data range in your new pivot table (from "Year-Month" down to the last session number).
2. Go to Insert > Chart.
3. Google Sheets will probably guess that you want a Line chart, which is perfect for showing trends over time. If not, you can select it from the "Chart type" dropdown in the Chart editor.
4. In the "Customize" tab of the chart editor, you can give your chart a title (e.g., "Monthly Website Sessions"), adjust colors, and label your axes to make it clearer.

Drag and resize your chart to fit neatly on your dashboard. You just visualized your first trend!

Add a Bar Chart for Comparison

What about comparing traffic sources? We can use the same process. Create another pivot table, but this time, set it up differently:

• Rows: Add "Traffic Source".
• Values: Add "Sessions".

Now, highlight this new pivot table and insert another chart. This time, a Bar Chart or Column Chart will work best for comparing the categories. Customize it with a clear title and colors, and place it next to your line chart.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 4: Add Interactivity with Slicers

A static dashboard is useful, but an interactive one is empowering. A Slicer is a filter that sits on your dashboard and lets any user filter the charts without messing with formulas or pivot tables.

1. Click on any of your pivot tables that power a chart.
2. From the top menu, go to Data > Add a slicer.
3. A filter box will appear. In the Slicer options on the right, for the "Column," choose "Traffic Source".
4. Drag this new slicer object to a convenient place on your dashboard.

Now, you (or anyone you share the dashboard with) can use the slicer's dropdown menu to choose only "Organic search" or "Social," and all the charts connected to that pivot table will instantly update. This is incredibly powerful for drilling down into your data on the fly.

Step 5: Design and Polish

The last step is to make your dashboard easy to read. A few quick tweaks can make a huge difference:

• Remove Gridlines: Go to View > Show and uncheck "Gridlines" to give your dashboard a clean, professional look.
• Use Color Thoughtfully: Use a consistent color scheme. You can even use your brand colors in the chart customizer to keep everything on-brand.
• Align Everything: Snap your scorecards, charts, and slicers to the grid so everything is neatly aligned. This small change makes a big impact on readability.
• Provide Context: Use text boxes to add notes or explain what a chart is showing. A little context goes a long way.

Final Thoughts

Building a dashboard in Google Sheets is a fantastic, no-cost way to get on top of your key metrics. By planning your approach, separating raw data from visualizations, and using tools like pivot tables and slicers, you can create a powerful report that helps you make better decisions for your business or project.

While Google Sheets is an amazing tool, you might find that the most time-consuming part of this process is manually exporting and pasting data every week. We've lived that frustration, which is why we built Graphed. We automate the entire data collection process by connecting directly to your tools like Google Analytics, Shopify, Facebook Ads, and Salesforce. Instead of building pivot tables and charts by hand, you just describe the dashboard you want in plain English, and it’s built for you in seconds with live, always-up-to-date data.