How to Create a Live Dashboard in Google Sheets

Building a dashboard directly in Google Sheets is a fantastic way to transform scattered rows of data into a clear, actionable overview of your business performance. You don't need expensive software or a data science degree - just the tool you probably already have open in another tab. This guide will walk you through setting up a live, interactive dashboard in Google Sheets, step by step.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Start With Your Data Strategy

Before you create a single chart, you need a plan for your data. A dashboard is only as useful as the information it pulls from. Your first step is to get all of your raw data into a single, organized place.

1. Centralize Your Raw Data Source

The best practice is to have two main tabs in your Google Sheet:

• Data Tab: This sheet is purely for your raw, unedited data. All of your automated imports and raw numbers should feed directly into this tab. Don't add any formatting, charts, or formulas here - it's meant to be a clean, machine-readable source.
• Dashboard Tab: This is where you'll build your visualizations. All charts, summaries, and key performance indicators (KPIs) on this tab will pull their information from the "Data" tab.

Separating your data from your dashboard keeps things clean, prevents you from accidentally deleting a critical cell, and makes your formulas much easier to manage.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Automate Your Data Imports

A "live" dashboard can't rely on you manually copying and pasting CSV files every morning. The goal is to set up a system that feeds data into your "Data" tab automatically. Here are a few ways to do that.

Use the =IMPORTRANGE Function

If your data already lives in another Google Sheet, =IMPORTRANGE is your best friend. It pulls data from one spreadsheet into another in real time.

How to use it:

=IMPORTRANGE("SPREADSHEET_URL", "Sheet1!A1:D100")

• Replace SPREADSHEET_URL with the full URL of the source spreadsheet.
• Replace "Sheet1!A1:D100" with the tab name and cell range you want to import.

The first time you use it, you'll need to click the cell and grant permission for the sheets to connect. After that, any change in the source sheet will automatically update in your dashboard's data tab.

Use Third-Party Connectors and Add-ons

For pulling data from outside of Google Sheets (like Google Analytics, Facebook Ads, Shopify, or Salesforce), you’ll need a connector. These are Google Workspace Add-ons that handle the API connections for you.

• Popular Connectors: Search the Google Workspace Marketplace for tools like Supermetrics, Coefficient, or API Connector by Mixed Analytics.
• How they work: Once installed, you can configure them to pull specific reports (e.g., campaign performance from Facebook Ads) and schedule them to refresh automatically - hourly or daily. This data is piped directly into your "Data" tab, keeping it consistently up-to-date.

Use Google Apps Script for Custom Connections

For more technical users, Google Apps Script allows you to write JavaScript-based functions to pull data from almost any API. You can then set up "time-based triggers" to run your script automatically every hour or every day, providing a powerful way to import custom data sets.

Build Your Dashboard Visualizations

With your data automatically feeding into the "Data" tab, it's time to build the fun part in your "Dashboard" tab. This is where you summarize and visualize your information to get glanceable insights.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Create Scorecard KPIs

Scorecards are single, important numbers that you want to see at a glance, like Total Revenue, New Users, or Conversion Rate. These are easy to create with basic formulas.

For example, to display Total Revenue, find an empty cell in your dashboard tab and enter a simple SUM formula that references your data tab:

=SUM(Data!B2:B)

This formula will sum every value in column B (your revenue column, for example) in the "Data" tab. Pair this with a clear label like "Total Revenue" and give it a large font size to make it stand out.

Step 2: Add Charts and Graphs

Visual charts are the core of any dashboard. They help you spot trends and compare performance instantly. Here's how to create one:

1. On your "Data" tab, highlight the data you want to visualize (e.g., date and website sessions).
2. Click on the menu Insert > Chart.
3. Google Sheets will suggest a chart type, but you can customize it completely in the Chart Editor panel.
4. Once your chart looks right, click the three dots in the corner of the chart and select Copy chart.
5. Go to your "Dashboard" tab and paste it. Now you have a chart on your dashboard that is directly linked to your raw data. When the data refreshes, your chart will, too.

Pro Tip: Don't create charts directly on the Dashboard tab with confusing data references. Build them from the source data, then move them to the dashboard for organization and clarity.

• Good Chart Choices:

Step 3: Make Your Dashboard Interactive with Filters and Slicers

A static dashboard is good, but an interactive one is even better. You can add dropdown menus or "Slicers" to filter all the charts and data on your dashboard at once.

How to Add a Slicer:

1. Select a data range or a chart on your "Dashboard" tab.
2. Go to Data > Add a Slicer.
3. A slicer (which looks like a filter button) will appear on your sheet. In the Slicer panel that opens on the right, choose which column it should filter by (e.g., 'Date', 'Campaign Name', or 'Country').
4. Now, when you use the slicer, it will filter all the charts and pivot tables based on that data set. You can add multiple slicers to allow for complex filtering, like showing data for a specific campaign in a particular country.

Step 4: Use Pivot Tables to Summarize Data

If you have a massive dataset, you first need to summarize it before you can chart it. Pivot tables are perfect for this. For instance, you could use a pivot table to quickly group your sales data by month and by region without writing a single formula.

1. Click anywhere in your dataset on the "Data" tab.
2. Go to Insert > Pivot table.
3. Choose to create it on a new sheet (or an existing one).
4. Use the Pivot Table Editor to drag and drop your data fields. For "Rows," you might add 'Product Category'. For "Values," you might add 'Revenue' (summarized by SUM).

You can then create charts based on your pivot table summary, giving you an aggregated view that's always up-to-date.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 5: Control Your Refresh Rate

The "live" aspect of a Google Sheets dashboard depends on how frequently your data functions and connected add-ons refresh. Here's what to expect:

• Functions like IMPORTRANGE update in real-time.
• Functions like IMPORTDATA or IMPORTHTML automatically refresh about every hour.
• Third-party connectors usually allow you to set a refresh schedule within their settings, typically from once per day to once every hour. Check your specific add-on’s documentation.

Final Thoughts

Building a dashboard in Google Sheets is a powerful, low-cost way to get a unified view of your key metrics, especially when you automate the data flow with connectors and internal functions. It gives you a centralized hub for performance tracking that you can easily share with your entire team, putting valuable information at everyone's fingertips.

While Google Sheets is an incredible tool, the daily process of managing data connectors, dealing with slow-loading sheets, and manually stitching together information from sources like Shopify, Salesforce, and Google Ads can become a full-time job. To solve this, we built Graphed to connect to all your data sources with a single click and deliver truly live, always-on dashboards. Instead of writing formulas, you can just ask questions in plain English - like "show me my ad spend vs. revenue last month" - and we instantly build the charts and reports for you, saving you hours of tedious manual work.