How to Create a Finance Tracker in Google Sheets

Building a personal finance tracker from scratch sounds like a chore, but it's one of the most effective ways to truly understand your spending habits and take control of your money. This guide will walk you through creating a powerful and totally custom finance tracker in Google Sheets, complete with an automated summary dashboard and charts, without needing to be a spreadsheet guru.

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

Why Use Google Sheets for Your Finance Tracker?

Before we start building, let's appreciate why Google Sheets is such a great tool for this job. It's more flexible than a rigid budgeting app and much more accessible than desktop software like Excel.

• It's Free: All you need is a Google account. There are no subscription fees or hidden costs.
• Accessible Everywhere: Your tracker is saved in the cloud, so you can access and update it from your phone, tablet, or any computer with an internet connection.
• Easily Shareable: If you manage finances with a partner, you can both access and edit the same sheet in real-time. No more sending files back and forth.
• Completely Customizable: You're not stuck with someone else's categories or reports. You can build a system that perfectly matches your financial life.

Step 1: Setting Up the Basic Structure

First, we need to create the foundation of our tracker. This involves creating a new spreadsheet and setting up a few specific sheets (or tabs) to keep things organized.

Create and Name Your Sheets

Start by heading to sheets.google.com and creating a new, blank spreadsheet. At the bottom of the screen, you'll see a tab named "Sheet1". We're going to create three separate sheets for our tracker by clicking the "+" icon to add new sheets:

• Transactions: This will be the heart of our document, where we log every single income and expense transaction. Rename "Sheet1" to "Transactions."
• Dashboard: This sheet will give us a high-level overview of our financial health, with automated summaries and charts. Create a new sheet and name it "Dashboard."
• Categories: This sheet will hold a list of all your income and expense categories. This keeps everything clean and makes it easy to add new categories later. Create a third sheet and name it "Categories."

Define Your Income and Expense Categories

Go to your "Categories" sheet. In column A, list all of your potential sources of income. In column B, list all of your potential expense categories. Being thorough here will pay off later.

Here’s a good starting point:

• Column A (Income): Salary, Freelance Work, Side Hustle, Interest, Gifts, etc.
• Column B (Expenses): Rent/Mortgage, Groceries, Utilities, Transport, Phone Bill, Internet, Restaurants, Entertainment, Shopping, Health, Insurance, etc.

Set Up the "Transactions" Sheet

Now, click over to the "Transactions" sheet. This is where we'll log everything. Set up the following headers in the first row (A1 to F1):

• Date (Column A): The date of the transaction.
• Description (Column B): A brief note about what the transaction was (e.g., "Weekly Groceries," "Electricity Bill").
• Category (Column C): The specific budget category this transaction falls under.
• Income (Column D): Any money you received. If it's an expense, this column will be blank.
• Expense (Column E): Any money you spent. If it's income, this will be blank.
• Balance (Column F): A running total of your money after each transaction.

Step 2: Automating with Formulas and Data Validation

This is where the magic happens. We'll add some formulas and features to do a lot of the work for you.

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

Creating a Dropdown Menu for Categories

Instead of manually typing a category every time, let's create a dropdown menu to prevent typos and keep our data consistent.

1. In the "Transactions" sheet, select cell C2. If you expect to have a lot of transactions, you can click on the column letter "C" to select the entire column (minus the header).
2. Go to the top menu and click Data > Data validation.
3. In the settings box that appears, under "Criteria", select Dropdown (from a range).
4. Click the grid icon next to the "Dropdown (from a range)" text box to select your data range.
5. A small box will pop up. Click on your "Categories" tab. Select the range that contains all of your income and expense categories (e.g., A2:B20). Make sure you select a little deeper than your last category so you can add more later. Click OK.
6. Finally, click Done.

Now, when you click on any cell in column C, a small arrow will appear. Clicking it will show you a dropdown list of all your predefined categories, ensuring consistent entries every time. This is critical for getting accurate summaries later.

The Formula for a Running Balance

We want the "Balance" column to automatically update with each new entry. We'll start with an opening balance and build from there.

1. Let's say you're starting on the first of the month with $1,000 in your bank account. In cell F1, you could write "Opening Balance". Then, in F2, type 1000. This is your manual starting point.
2. In cell F3, we will put our first formula. It will take the balance from the cell above, add any income from the current row, and subtract any expenses. Type this formula into cell F3:

=F2+D3-E3

Now, here’s the trick to apply this to all future rows. Click once on cell F3, and you'll see a small blue square in the bottom-right corner of the cell. Click and drag this square down as far as you anticipate having entries (500 rows should be a good start). This automatically copies the formula, adjusting it for each row. Now your balance will update with every transaction you add.

Step 3: Building Your Summary Dashboard

A list of transactions is useful, but a summary dashboard helps you see the bigger picture at a glance. Let's switch over to the "Dashboard" sheet.

We will create a simple summary table showing our total income, total expenses, and net savings for the period. In the "Dashboard" sheet, create the following labels in cells A1, A2, and A3: "Total Income", "Total Expenses", and "Net Savings".

Calculate Total Income and Expenses with SUM

To calculate the totals, we'll use the straightforward SUM function, which adds up all the numbers in a range.

• In cell B1 (next to "Total Income"), enter this formula:

=SUM(Transactions!D:D)

• In cell B2 (next to "Total Expenses"), enter this formula:

=SUM(Transactions!E:E)

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

Calculate Net Savings

For your net savings (what's left over), we'll do a simple subtraction of the two cells we just created.

• In cell B3 (next to "Net Savings"), enter this formula:

=B1-B2

Now you have a live summary. Every time you log a new transaction, these totals will update instantly.

Summarizing Spending by Category with SUMIF

This is where you'll find the most powerful insights: seeing exactly where your money is going. We'll create another table on the dashboard that lists each expense category and automatically calculates the total amount spent. For this, we will be using the SUMIF function. SUMIF is a very useful formula that sums (adds up) values IF they meet a specific certain criteria.

The structure for this is: SUMIF([What Column You Want to Look At To See If The Category Match], [Which Category Am I specifically Searching for To Validate if We Should add To Sum The Number To The Total], [What Column Of Numbers We Want Added Up/summed Together If The Criteria in Column 1 Is A Match])

• On your "Dashboard" sheet, list your expense categories starting in cell A5 (e.g., Groceries, Transport, Entertainment, etc.). You can just copy and paste them from your "Categories" sheet.
• In cell B5, next to your first expense category, enter the following formula. Be sure to replace "Groceries" with whatever is written in cell A5:

=SUMIF(Transactions!C:C, "Groceries", Transactions!E:E)

Let's break this down:

• Transactions!C:C is the range where we look for the category names (the entire column C in our Transactions sheet).
• "Groceries" is the specific text we are searching for a match.
• Transactions!E:E is the range containing the values to add up if the criteria is a match (the amounts in our Expense column).

Essentially, this formula looks through all transaction costs and only adds up if you classified it as groceries. The great thing about SUMIF is that we'll make a minor change and use a cell reference instead of a static reference. In cell B5 replace "Groceries" with A5:

=SUMIF(Transactions!C:C, A5, Transactions!E:E)

Now, all you need to do is click on cell B5, grab the small blue square in the corner, and drag the formula down to the rest of all your expense categories. Each category will now show you its running total of your transactions you enter in step 1 on the "Transactions" tab.

Step 4: Visualizing Your Data with Charts

Finally, let's bring the dashboard data into beautiful charts. This makes insights much quicker. We'll create two powerful charts now that our dashboard is set up: a pie chart and a bar chart comparing your financial health.

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

Creating an Expense Breakdown Pie Chart

To see where money is mostly flowing in expenses, there's nothing more easily and insightful than a pie chart to visualize exactly that proportion.

1. In your "Dashboard" sheet, highlight all of your categories and their totals from the category breakdown (e.g., A5:B20).
2. Go to the menu and select Insert. Then, click on Chart. Google Sheets is surprisingly smart and will most likely automatically create a pie "donut" chart. However, if not, it's easy to change just by choosing "Pie Chart" from the dropdown under "Chart type".

Creating a Line Chart to Compare Income vs. Expenses

A monthly line chart is a great way to observe spikes in your income compared to what you're spending. To build this, we'll need to slightly rearrange our data so it's in a format the chart can understand.

In the "Dashboard" sheet, create a new table with a list of months (e.g., January, February, etc.) in one column (e.g., column D) and headers "Income" and "Expenses" next (e.g., columns E and F). Now, use the SUMIFS function to pull the totals for each month in income and expenses.

In column E next to the "January" row, enter this formula (assume Transactions!A:A contains dates):

=SUMIFS(Transactions!D:D, Transactions!A:A, ">=1/1/2022", Transactions!A:A, "<1/2/2022")

Repeat similarly for other months and for expenses in column F. Once done, create a chart to visualize trends over time.

Final Thoughts

Building your own finance tracker in Google Sheets puts you in the driver's seat of your financial life. By setting up a transaction log, automating calculations, and visualizing the results on a dashboard, you transform raw data into clear, actionable information that helps you budget better and achieve your goals.

While Google Sheets is fantastic for logging manual inputs, growing your business often means your financial data isn't in just one place - it's scattered across Shopify, Stripe, QuickBooks, and your advertising platforms. This is where we designed Graphed to help. We connect all those sources for you, and allow you to build real-time dashboards and ask questions about your business performance in plain English. Instead of spending hours wrangling CSV files, you can get insights in seconds and make faster, smarter decisions for your business.