How to Create a Simple Dashboard in Google Sheets

Tired of scrolling through endless rows just to find a simple metric? A well-built dashboard in Google Sheets can turn that cluttered spreadsheet into a clear, visual summary of your most important data. It's a fantastic way to track progress, spot trends, and share insights without needing a complex business intelligence tool. This guide will show you exactly how to build one, step by step.

Graphed

Still Building Reports Manually?

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

Watch the Demo

First Things First: Prepare Your Data

Before you create a single chart, the most important step is getting your data in order. A great dashboard is built on a foundation of clean, well-structured data. Think of it like cooking: you need good ingredients before you can make a great meal.

1. Create a "Raw Data" Tab

Your Google Sheet should have at least two tabs: one for your raw data and one for the dashboard itself. This separation is critical. Your dashboard will pull information from your raw data, but you’ll never format or change the raw data tab itself.

Your raw data tab should be organized like a simple database:

• Each row is a single record. For example, a single day's ad spend, a single sale, or a single website visit.
• Each column is a specific attribute or metric. For example, "Date," "Campaign Name," "Clicks," "Spend," "Conversions."
• Keep headers simple and clear. Use one row for headers. Do not merge cells.
• Ensure consistent formatting. Dates should be in a date format, numbers as numbers, etc.

Graphed

Still Building Reports Manually?

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

Watch the Demo

2. Create Your "Dashboard" Tab

Next, create a new, blank tab and name it "Dashboard" or something similar. This is your canvas. All your charts, key metrics, and controls will live here. Keeping it separate ensures that if you add more raw data later, you won't break your visual layout.

Building Your Dashboard Components

With your data prepped, it's time for the fun part: building the visual elements that will make up your dashboard. We'll start by summarizing a large amount of data into smaller, useful tables and then turn those into charts.

1. Summarize Data with Pivot Tables

Trying to chart your raw data directly is usually a mess. A pivot table is your best friend for summarizing thousands of rows of data into a neat, aggregated view that’s perfect for creating charts.

Let's create a pivot table to summarize ad spend and conversions by campaign.

1. Go to your Raw Data tab and select all of your data (a quick shortcut is to click cell A1 and press Ctrl+A or Cmd+A).
2. In the menu, go to Insert > Pivot Table.
3. In the dialog box that appears, choose "New sheet." It’s best practice to keep your pivot tables on their own separate tab to avoid clutter. Let's call this new sheet "Pivot Data".
4. The Pivot table editor side panel will appear. Now, we'll tell it how to summarize our data:

Just like that, you have a tidy table that sums up your key metrics for each campaign. This table is what we'll use to build our first chart.

Graphed

Still Building Reports Manually?

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

Watch the Demo

2. Calculate Key Performance Indicators (KPIs)

Sometimes, you just need a big, bold number at the top of your dashboard for a quick status check. These are your KPIs. We'll add these directly to our dashboard tab using simple formulas that reference our raw data.

On your Dashboard tab, set up a few labels:

• In cell A2, type "Total Spend"
• In cell A3, type "Total Conversions"
• In cell A4, type "Avg. Cost Per Conversion"

Now, let's add the formulas in the cells next to your labels:

For Total Spend (in B2):

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

For Total Conversions (in B3):

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

For Avg. Cost Per Conversion (in B4):

=B2/B3

You can now format these cells to make the numbers stand out (e.g., large font size, bold text, currency formatting) to create clear KPI cards for your dashboard.

3. Visualize Data with Charts

Now let's turn that pivot table we made into a chart. Visuals are much easier to interpret at a glance than tables of numbers.

1. Navigate back to your Pivot Data tab.
2. Select the data in your pivot table (in our example, the campaign names and their summed Spend and Conversions).
3. Go to the menu and click Insert > Chart.
4. Google Sheets will automatically suggest a chart type. You can easily change this using the Chart editor panel that appears on the right. For this data, a Column chart or Bar chart is a great choice to compare campaign performance.
5. Use the "Customize" tab in the Chart editor to change things like the chart title, colors, and axis labels to make it clearer.
6. Finally, to move your chart to the dashboard, click the three little dots in the top-right corner of the chart and select "Move to own sheet," or choose "Copy Chart" and then paste it directly onto your Dashboard tab. Arrange it however you like.

Make Your Dashboard Interactive with Slicers

A static dashboard is useful, but an interactive one is far more powerful. Slicers are user-friendly filter buttons that allow you or anyone you share the dashboard with to dynamically filter the data displayed in your charts and pivot tables.

Let's add a slicer to filter our dashboard by marketing channel.

1. Go to your Dashboard tab and click on the chart you want to filter.
2. In the menu, go to Data > Add a Slicer.
3. A slicer element will appear. On the right-hand panel, under a "Data" tab that pops open, select the column you want to filter with - let's choose "Channel" from our raw data.
4. The slicer is now live! Simply click the filter icon on the slicer element and select a channel (e.g., "Facebook Ads"). You'll see your chart and pivot table automatically update to only show data for campaigns running that specific channel. Leave the "Apply to" dropdown at its default of "Pivot Tables," and "All." This will make sure that any charts dependent on that particular pivot table for their data series will update accordingly. It's super handy when your audience might one day ask questions like, "Okay, this is great, but now just show me the Instagram performance." Simply use your slicer instead of rebuilding the chart. You've got an interactive dashboard right here inside of Sheets.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Tips for Better Dashboard Design

Building the components is one thing, arranging them effectively is another. Follow these simple principles to create a dashboard that's easy to read and understand.

• Put the Most Important 'Need to Know' KPIs Up Top: Place your most critical, high-level numbers (like total revenue or total conversions) up at the top left, as this is where most people's eyes start first when they look. Supporting charts and more granular details can sit below that row.
• Choose the Right Chart for the Job: Don't just pick a chart type at random. Bar and column charts are great for comparing categories. Line charts or area charts can display trends over time. Pie charts are useful for showing composition, like 'which channels contributed to our total'.
• Group Related Information Together: Arrange all charts and related metrics regarding sales together. Set up separate sections for marketing efforts and customer support topics. This logical flow makes your overall picture more intuitive and easier to read.
• Use Purposeful Coloration: Resist going color-crazy. Stick with a simple palette, perhaps using a single accent color to highlight key results or performance outcomes. This is also a great place for brand colors to enhance the visual appeal!

Final Thoughts

Creating a dashboard inside Google Sheets is a powerful way to make your data more approachable and help make decisions faster and with confidence. By separating your raw data from its visual display, using pivot tables, and adding slicers, you can turn a simple spreadsheet into a real interactive analytical tool.

The only downside of Google Sheets dashboards is that they're still relatively manual. You often have to constantly download CSVs and paste them into your raw data sheet to keep things updated. At my company, we actually built a tool Graphed just for solving this problem. It connects to all your sources like Google Analytics, Facebook Ads, and more directly, keeping data live automatically. We wanted to create a way you could just talk to your data and see what you want in plain English, and have an AI-driven analysis build the dashboard in seconds.

However, for getting started, Google Sheets is an excellent place to build your data visualization skills. Happy building!