Graphed LogoGraphed
Login

How to Create a Financial Dashboard in Google Sheets

Cody Schneider•

Creating a financial dashboard can seem daunting, but Google Sheets makes it surprisingly simple and incredibly powerful. This article will walk you through, step-by-step, how to build a clear, effective financial dashboard for your business using nothing but a spreadsheet.

Why Use Google Sheets for a Financial Dashboard?

Before jumping into complex BI tools or expensive accounting software, Google Sheets offers a perfect starting point. It’s free, cloud-based for easy access and collaboration, and flexible enough to grow with your business needs. You can get a clear visual snapshot of your company's financial health without the steep learning curve, making it ideal for founders, small business owners, and marketing teams trying to track their budget and performance.

The best part? You have complete control over what you track and how you see it, allowing you to tailor your dashboard to the specific KPIs that matter most to your business.

First, Gather and Organize Your Financial Data

A dashboard is only as reliable as the data that powers it. The very first step is to create a clean, organized "source of truth" for all your financial transactions. The goal is to get all your income and expenses into one place in a standardized format.

What Financial Data Do You Need?

For most businesses starting out, you won't need to import complex financial statements. You just need a simple log of every transaction. You'll want to list every dollar that comes in and every dollar that goes out.

How to Structure Your Data

The key to a successful Google Sheets dashboard is using a separate tab for your raw data. This keeps your dashboard clean and your formulas simple. Create a new tab and name it something like "Transactions" or "Raw Data."

In this tab, set up a simple table with the following columns:

  • Date: The date the transaction occurred. Make sure to format this as a date (Format > Number > Date).

  • Description: A brief note about the transaction (e.g., "Monthly SaaS Subscription," "Client Project Payment").

  • Category: Assign a category to each transaction. This is crucial for analysis. Examples for expenses might include "Software," "Marketing," "Payroll," and "Office Supplies." Income categories could be "Product Sales," "Service Fees," or "Subscriptions."

  • Type: A simple column to label each transaction as either "Income" or "Expense." This will make your formulas much easier later.

  • Amount: The value of the transaction. Keep all numbers positive here, the "Type" column will handle distinguishing between income and expenses.

Your "Transactions" tab should look something like this:

Diligently logging every transaction here is the most important part of the entire process. The more consistent you are with categorization, the more insightful your dashboard will be.

Step-by-Step Guide to Building Your Financial Dashboard

With your data organized, you’re ready for the fun part: building the dashboard itself. We'll use a three-tab structure to keep everything neat:

  1. Transactions: Your raw data log (which you just created).

  2. Calculations: A hidden tab where you'll summarize your data using formulas.

  3. Dashboard: The main, visual tab with all your charts and key metrics.

Step 1: Create a "Calculations" Tab for Summary Data

Creating summary tables on a separate sheet is a best practice. It separates your complex formulas from your final visuals, making it much easier to troubleshoot issues and update your dashboard later. Create another new tab and name it "Calculations".

On this tab, you’ll build small tables that crunch the numbers from your "Transactions" tab. Here are a few essential summary tables to start with:

1. Monthly Overview: Income, Expenses, and Profit

This table gives you a high-level view of your profitability over time. Set it up with columns for the Month, Total Income, Total Expenses, and Net Profit.

To calculate the income for January, you’ll use the SUMIFS formula. This function adds up numbers in a range that meet multiple criteria. In this case, you'll sum all amounts that are (1) categorized as "Income" and (2) fall within a specific month.

For January's Income (assuming your data is in the "Transactions" tab):

=SUMIFS(Transactions!E:E, Transactions!D:D, "Income", Transactions!A:A, ">="&DATE(2024,1,1), Transactions!A:A, "<="&DATE(2024,1,31))

For January's Expenses:

=SUMIFS(Transactions!E:E, Transactions!D:D, "Expense", Transactions!A:A, ">="&DATE(2024,1,1), Transactions!A:A, "<="&DATE(2024,1,31))

Finally, your Net Profit column is a simple subtraction: Total Income - Total Expenses. Repeat these formulas for each month you want to track.

2. Expense Breakdown by Category

This table helps you understand where your money is going. List your expense categories in one column and use SUMIFS again to calculate the total spent in each.

For the "Software" category:

=SUMIFS(Transactions!E:E, Transactions!D:D, "Expense", Transactions!C:C, "Software")

Create a similar formula for each of your expense categories.

Step 2: Design Your Dashboard and Visualize the Data

Now, create your final tab and name it "Dashboard." This is where you'll turn the summary tables from your "Calculations" tab into easy-to-understand charts and graphs.

Grid lines are distracting on a dashboard, so start by hiding them: Go to View > Show > Gridlines and uncheck the box.

1. Income vs. Expense Bar Chart

A bar chart is perfect for comparing your monthly income and expenses.

  • On your Dashboard tab, go to Insert > Chart.

  • In the Chart editor that appears on the right, select "Column chart" under Chart type.

  • For the "Data range," click the grid icon and select the Month, Total Income, and Total Expenses columns from your "Calculations" tab.

  • Customize the titles and colors to make it clear and on-brand.

2. Expense Breakdown Pie Chart

A pie chart or donut chart is a great way to see the proportion of your spending across different categories.

  • Go to Insert > Chart.

  • Choose "Pie chart" (or "Donut chart" for a modern look).

  • For the data range, select your expense categories and their corresponding totals from the "Calculations" tab.

3. Showcase Key Performance Indicators (KPIs) with Scorecard Charts

Dashboards should give you an instant snapshot of your most important numbers. "Scorecard" charts are perfect for this.

  • Select a cell in your "Calculations" tab that holds a key number, like "Total Revenue for the Year" or "YTD Net Profit."

  • Go to Insert > Chart and select "Scorecard chart" from near the bottom of the chart types list.

  • Style the chart to make it pop. You can even use the "Previous period" comparison to automatically show trends.

Step 3: Make Your Dashboard Interactive

A static dashboard is good, but an interactive one is even better. Adding date controls lets you filter your dashboard to find insights for specific time periods.

  • On your Dashboard tab, select a cell where you'd like a month-selection dropdown.

  • Go to Insert > Dropdown.

  • In the criteria options, select "Dropdown (from a range)."

  • For the range, select the list of months from your "Calculations" tab.

Now, you can update your SUMIFS formulas in the "Calculations" page to reference the dropdown cell on your dashboard. This means when you select "February" from the dropdown, all your charts will automatically update to show data just for that month.

Tips for Maintaining and Improving Your Dashboard

  • Keep Your Data Clean: Garbage in, garbage out. The biggest threat to your dashboard's accuracy is inconsistent data entry. Set clear rules for yourself and your team on how to categorize transactions.

  • Use Conditional Formatting: Make your data tables easier to read by using conditional formatting (Format > Conditional formatting) to automatically color positive numbers green and negative ones red.

  • Automate Your Data Entry: If you find manually logging every transaction too tedious, you can use automation tools like Zapier or Make.com. You can build simple workflows that automatically add a new row to your "Transactions" sheet whenever you get a payment in Stripe, an invoice in QuickBooks, or even based on keywords in your Gmail.

Final Thoughts

Building a custom financial dashboard in Google Sheets is a fantastic way to transform static business data into actionable insights. Following these steps gives you a powerful tool for monitoring financial health, tracking progress towards goals, and making more informed business decisions without needing to hire a data expert.

Once you’ve experienced the power of a live dashboard, the manual work of exporting CSVs and updating your spreadsheet every week will start to feel tedious. That’s why we built Graphed. We connect directly to your financial platforms like Shopify, Stripe, QuickBooks, and your advertising accounts. Instead of writing formulas, you can just ask questions in plain English like, "show me my monthly revenue versus ad spend" and Graphed builds a real-time, shareable dashboard for you instantly.