Graphed LogoGraphed
Login

How to Create a Revenue Dashboard in Google Sheets

Cody Schneider•

Trying to understand your business revenue can feel like you're piecing together a puzzle with pieces from a dozen different boxes. This guide cuts through that complexity by showing you exactly how to build a clear, powerful revenue dashboard right inside Google Sheets. We'll walk through everything from structuring your data to creating interactive charts that actually tell you what’s working and what isn’t.

First, Why Even Use Google Sheets for This?

You’ve got built-in analytics inside Stripe, Shopify, and every other tool you use. So why go through the trouble of building something in a spreadsheet? The answer is simple: to see the complete picture. Your business performance isn't siloed in one app, and your reporting shouldn't be either. Google Sheets provides a free, flexible, and centralized place to track everything side-by-side.

  • It’s free and accessible: You don't need expensive business intelligence subscriptions. If you have a Google account, you have everything you need to start.

  • It's a single source of truth: Instead of bouncing between three different platforms to understand revenue, you can consolidate everything for a bird's-eye view.

  • It teaches you the fundamentals: Building a dashboard manually helps you truly understand your data - how it’s structured, what metrics matter, and how they relate to each other.

Step 1: Lay the Groundwork with a Raw Data Tab

Every successful dashboard starts with a clean, well-organized data source. This is the most important step because messy source data leads to a confusing and unreliable dashboard. No exceptions.

Create a new Google Sheet and dedicate the first tab entirely to your raw data. Name it something simple, like "Raw Data" or "_RevenueData." Resisting the urge to format or analyze on this sheet is critical. Think of it as a pristine logbook, you only add information, you never change it.

Your goal is to have a simple, database-like table. Each row should represent a single transaction or revenue event. Here’s a good starting structure for your columns:

  • Transaction ID: A unique identifier for the purchase (e.g., Stripe charge ID).

  • Date: The date the transaction occurred. Make sure this is a consistently formatted date.

  • Source: Where the revenue came from (e.g., Stripe, Shopify, Manual Invoice).

  • Product/Service: The name of the item or service sold.

  • Category: A higher-level grouping for your products/services (e.g., 'Software Subscription,' 'Consulting,' 'Digital Products').

  • Revenue: The total amount of the transaction.

  • Customer Name/ID: To track repeat business or LTV later on.

Consistency is your best friend here. Decide on naming conventions and stick to them. For example, always use "Stripe" or "Shopify" — not "stripe," "Shopify Store," or "shopify." Inconsistencies will fragment your data and make it incredibly difficult to analyze accurately.

Step 2: Get Your Revenue Data into the Sheet

Now that your sheet is structured, you need to populate it. You have two main options: manual or automated.

The Manual Approach

If you're just starting, manual entry is perfectly fine. At the end of each day or week, export a CSV from your payment processor(s) and copy-paste the relevant data into your "Raw Data" tab. It’s tedious but effective for low transaction volumes and gives you an intimate feel for your numbers. Just be careful with copy-paste errors.

The Automated Approach

As your business grows, manual data entry will quickly become a major drain on your time. Automating this process is the next logical step. Tools like Zapier or Make.com can connect your apps (Shopify, Stripe, PayPal, etc.) to your Google Sheet. You can create simple "recipes" or "scenarios," such as "when a new sale happens in Shopify, add a new row to my Google Sheet with the details."

This approach saves an incredible amount of time, eliminates human error, and ensures your dashboard’s data is always close to real-time. It takes a little bit of setup, but the payoff is huge.

Step 3: Build the “Brain” - Your Analysis Tab

Here’s a pro tip: never build your charts directly from your raw data tab. Over time, that raw data sheet will become huge, and pointing charts directly at thousands of rows can become slow and unwieldy. Instead, we’ll create an intermediate tab to perform calculations and create summary tables. It’s this tab that will feed your final dashboard.

Create a new tab and name it "Analysis." This is where we’ll summarize the raw numbers into meaningful insights.

Method 1: The Trusty Pivot Table

Pivot tables are your fastest path to summarizing data inside a spreadsheet. They are incredibly powerful and surprisingly easy to get started with.

Let’s create a summary of Revenue by Month:

  1. Click anywhere inside your data on the "Raw Data" tab.

  2. Go to the menu and select Insert > Pivot table.

  3. Make sure the "Data range" covers all your columns and set it to insert into your "Analysis" sheet.

  4. The Pivot Table editor will appear. Configure it as follows:

    • In the Rows section, click "Add" and select your "Date" column. A date grouping will automatically appear — right-click it and select "Create pivot date group," then choose "Year-Month."

    • In the Values section, click "Add" and select your "Revenue" column. It should default to "Summarize by: SUM."

Just like that, you have a beautiful summary table showing your total revenue for each month. You can follow the same process to create other essential summaries, like Revenue by Source (use "Source" for Rows) and Revenue by Product (use "Product/Service" for Rows).

Method 2: The Flexible Formula (Using QUERY)

For even more control, the QUERY function is like having SQL inside Google Sheets. It's one of the most powerful and versatile formulas you can learn. It looks a little intimidating at first, but the logic is straightforward.

Let's recreate the Revenue by Source summary with a QUERY formula:

In a cell in your "Analysis" tab, paste this formula:

=QUERY('Raw Data'!A:G, "SELECT C, SUM(F) WHERE C IS NOT NULL GROUP BY C LABEL SUM(F) 'Total Revenue'", 1)

Breaking it down:

  • 'Raw Data'!A:G is the range of all your data.

  • "SELECT C, SUM(F)..." is your instruction.

    • SELECT C, SUM(F) tells it to grab the "Source" column (Column C) and sum the "Revenue" column (Column F).

    • WHERE C IS NOT NULL ensures it doesn't include blank rows.

    • GROUP BY C groups all the similar sources together before summing their revenue.

    • LABEL SUM(F) 'Total Revenue' renames the sum column to something more human-readable.

  • 1 at the end tells the function that your data has one header row.

The beauty of QUERY is that once you understand the basic structure, you can adapt it to answer almost any question about your data without wrestling with a user interface.

Step 4: Now, Create Your Visual Dashboard Tab

With your analysis engine doing the heavy lifting, it's time for the fun part. Create one more new tab and call it "Dashboard." Keep this sheet a clean, gridless canvas that’s easy to read at a glance. You can do this from the menu by clicking View > Show and unticking "Gridlines."

Your dashboard will be a collection of charts that pull their information directly from your "Analysis" tab, not your "Raw Data" tab.

Creating Essential Charts

1. Revenue Over Time (Line Chart)

A line chart is the best way to visualize trends. Let’s chart the Revenue by Month table we created in the last step.

  1. Highlight the two columns of your "Revenue by Month" summary table on the "Analysis" sheet.

  2. Go to Insert > Chart.

  3. Google Sheets will likely default to a line chart, but if not, select it from the "Chart type" dropdown in the editor.

  4. Cut and paste (Ctrl+X and Ctrl+V) this chart onto your "Dashboard" tab.

2. Revenue Broken Down by Source (Pie or Bar Chart)

To see which channels are your biggest moneymakers, use a pie chart or a bar chart.

  1. Highlight the data in your "Revenue by Source" summary table.

  2. Click Insert > Chart.

  3. Choose either a Pie Chart (for showing proportions of a whole) or a Bar Chart (for easier comparison between sources).

  4. Move the finished chart to your dashboard.

3. Key Performance Indicators (Scorecard Charts)

Scorecards are perfect for displaying single, important numbers like total revenue.

  1. First, calculate this number in your "Analysis" tab. In an empty cell, use a simple formula: =SUM('Raw Data'!F:F).

  2. Now, back in the "Dashboard" tab, select the cell containing your total revenue figure.

  3. Go to Insert > Chart.

  4. In the chart editor, scroll down and select the Scorecard chart.

  5. Move it into place on your dashboard.

Arrange your charts on the "Dashboard" tab logically. Give them meaningful titles. Soon, you’ll have a professional-looking dashboard that clearly communicates the health of your business.

Step 5 (Bonus): Make It Interactive with Slicers

A static dashboard is good, but an interactive one is even better. Slicers allow you to filter your entire dashboard without touching the underlying formulas.

If you used Pivot Tables in your Analysis tab, this is incredibly simple to set up:

  1. Go to your "Analysis" tab and click inside one of your pivot tables.

  2. From the menu, choose Data > Add a slicer.

  3. A filter box will appear. In the options, choose the column you want to filter by, like "Category" or "Product/Service."

  4. Cut and paste the slicer onto your main "Dashboard" tab.

Now, you can use the dropdown slicer to filter your dashboard. For example, if you choose the 'Software Subscription' category, all the pivot-table-driven charts on your dashboard will instantly update to show data for only that category. It’s an incredibly powerful way to drill down and uncover insights.

Final Thoughts

Building a revenue dashboard in Google Sheets moves you from just collecting data to actually understanding it. By organizing your revenue streams into a single, clean view, you’ve created a powerful tool that transforms raw numbers into the clarity needed to grow your business.

While Google Sheets dashboards are a huge step up from manual checking, keeping them synced across Shopify, Stripe, Google Ads, and all your other tools can still involve a lot of spreadsheet wrangling. At Graphed, we created our tool specifically to eliminate this part of the process. We let you connect your data sources with one click and then build real-time dashboards simply by describing what you want to see. Your reports stay updated automatically, freeing you to focus on strategy instead of spending hours manually piecing data together.