Finance Spreadsheet in Google Sheets: Setup and Formulas

A finance spreadsheet in Google Sheets can help you track income, spending, budgets, savings, and debt in one place. Start with a dated transaction log, then use category totals and a budget tab to compare what you planned with what you actually spent; the formulas below show how to build that setup in a browser.

What a finance spreadsheet can do

A spreadsheet is a flexible way to organize financial information, but it is not a bank account or an accounting system. You enter or import records, decide how to categorize them, and use formulas to summarize the data. That makes a Google Sheets workbook useful for household budgeting, shared expenses, savings goals, or a small project with straightforward income and costs.

The most useful starting point is often a monthly spending plan. A transaction log records what happened, while a budget table shows what you intended to spend. Comparing the two can reveal, for example, that groceries are running above plan while transport is below it. The spreadsheet helps you see the pattern; deciding what to do about it is a separate judgment.

Plan the workbook before entering data

Create a blank spreadsheet in Google Sheets and give it a clear name. Use separate tabs for information with different purposes, rather than building one enormous table. A practical beginner workbook might include:

Transactions: the dated record of income and spending.
Budget: planned amounts and actual totals by category.
Savings or net worth: optional snapshots of goals, assets, and debts.

Keep each tabular list consistent: one header row, one record per row, and one kind of information per column. Avoid blank rows inside the transaction list, merged cells in the data area, and subtotal lines mixed in with records. Those choices make sorting, filtering, and formula ranges more dependable.

Build a transaction log

On the Transactions tab, use columns such as Date, Category, Description, Amount, and Account. Enter expenses as positive numbers and use a separate Type column for Income or Expense if you want to distinguish cash flow clearly. Alternatively, use negative amounts for expenses and positive amounts for income, but choose one convention and document it. Mixing conventions makes totals misleading.

For example, a few rows might record January 4 groceries of $85, January 6 transit of $30, and January 15 groceries of $42.50. Use actual spreadsheet dates rather than text that merely looks like a date. Format the date and amount columns with Format > Number, choosing an appropriate date and currency format. Currency formatting changes how a value appears, not the underlying value.

To make category labels consistent, select the cells where categories will be entered and choose Data > Data validation to create a dropdown. A dropdown can reduce near-duplicates such as “Transport,” “transport,” and “Transit” that would otherwise split a category total. Add categories that fit your records, and review them occasionally rather than creating a new label for every individual purchase.

Summarize spending with formulas

Google Sheets formulas begin with an equals sign. For a category total, put the category to summarize in a cell such as E2. With categories in Transactions!B2:B1000 and positive expense amounts in Transactions!D2:D1000, and with E2 containing Groceries, enter this in F2:

=SUMIF(Transactions!B2:B1000,E2,Transactions!D2:D1000)

For the sample records above, the result for Groceries is $127.50. SUMIF adds amounts whose category matches the selected label. Keeping the category criterion in a cell makes it easy to copy the formula down for other categories.

You can also total a date range. With dates in Transactions!A2:A1000 and amounts in Transactions!D2:D1000, this formula totals all entries dated in January 2026:

=SUMIFS(Transactions!D2:D1000,Transactions!A2:A1000,">="&DATE(2026,1,1),Transactions!A2:A1000,"<"&DATE(2026,2,1))

For the three sample transactions, the result is $157.50. The inclusive start and exclusive next-month boundary include dates throughout January, including any time values attached to dates. If a result is unexpectedly zero, check that the date cells are real dates, not text, and that the amount range contains numbers.

Compare budgeted and actual amounts

On the Budget tab, put categories in column A, planned monthly amounts in column B, actual spending in column C, and remaining budget in column D. For instance, if A2 is Groceries and B2 is $150, the actual amount can be calculated from the transaction log.

With the category in Budget!A2, categories in Transactions!B2:B1000, and amounts in Transactions!D2:D1000, enter this in Budget!C2:

=SUMIF(Transactions!B2:B1000,A2,Transactions!D2:D1000)

The sample groceries transactions return $127.50. With the planned budget in B2 and the calculated actual amount in C2, enter this in D2 to show the remaining amount:

=B2-C2

With a $150 plan and $127.50 spent, the result is $22.50 remaining. A negative result means spending exceeded the planned amount; it does not mean the formula is wrong. Copy formulas down for additional categories, and keep planned amounts separate from actual transactions so that the comparison remains meaningful.

Conditional formatting can make the comparison easier to scan. Select the relevant cells and use Format > Conditional formatting to highlight negative remaining amounts. Check that the rule applies to the intended range and that its condition matches the cell values; a color is a visual cue, not a substitute for checking the numbers.

Track savings, balances, and loans carefully

A separate savings tab can list a goal, a target amount, the current balance, and a target date. You can update the balance periodically or record each deposit as a transaction and summarize it. State whether a balance is current or as of a particular date. Without an “as of” date, an old snapshot can look more current than it is.

For a simple fixed-payment loan estimate, Google Sheets has the PMT function. Suppose a hypothetical loan has a $10,000 principal, a 6% annual interest rate, and a 36-month term, with monthly payments and no additional fees. Enter this formula to show the payment as a positive number:

=-PMT(6%/12,36,10000)

The approximate monthly payment is $304.22. Multiplying the unrounded monthly payment by 36 gives total payments of about $10,951.88, or roughly $951.88 above the principal. This illustration assumes the rate and payment schedule remain constant; real loans may include fees, different compounding rules, variable rates, or other terms. Check the actual agreement with the lender or a qualified professional. This calculation is not financial advice.

Make the sheet easier to use and review

Good formatting helps people spot mistakes without changing the underlying arithmetic. Freeze the header row with View > Freeze, use filters to view a date range or category, and keep number formats consistent. A chart can make a summary more readable: select a small summary table and choose Insert > Chart. A chart based on carefully chosen category totals is usually clearer than one built directly from hundreds of transaction rows.

Use descriptive tab and column names, and add a short note explaining whether expenses are entered as positive or negative values.
Check totals against a bank or card statement periodically; imported or manually entered records can be missing, duplicated, or miscategorized.
Keep a dated backup or make a copy before making major structural changes. Formulas can be damaged when columns are moved or ranges are overwritten.
Use filters and sorting on the full table, including its headers, so records stay together.

If you import a CSV file, inspect the date and amount columns after import. Decimal marks, date formats, and currency symbols can be interpreted differently depending on the file and spreadsheet locale. A value that looks like a number may have been imported as text, in which case some totals will not include it as expected.

Privacy, sharing, and limitations

Google Sheets supports online collaboration, which can be convenient for people maintaining a shared household budget. Before sharing, use the Share controls to choose who can view or edit the file, and give each person only the access needed. Avoid placing account passwords, full payment-card numbers, or other credentials in spreadsheet cells. A finance workbook can contain sensitive information even when it does not store login details.

Google Sheets is available at no charge for many personal uses, while Google Workspace subscriptions and organizational arrangements can have different features and costs. Availability and terms can change, so check Google’s current product information for the account you use. Sheets is convenient for flexible tracking, but it does not automatically guarantee that records are complete, correctly categorized, or suitable for tax, business accounting, or regulatory reporting.

Google Sheets compared with other options

For a simple household budget, Sheets can be easier to share than a file stored on one computer, and its browser-based editing is useful when collaborators need to see the same workbook. Microsoft Excel offers a different interface and its own capabilities; Excel users work with the ribbon, while the menu paths in this guide—such as Format > Conditional formatting—are Google Sheets paths. Formula names often overlap, but features, behavior, and compatibility are not identical, so verify formulas if moving a workbook between products.

A dedicated budgeting app may connect to financial accounts or provide built-in reports, depending on the service and region. That convenience comes with its own data-sharing, subscription, and account-access considerations. A spreadsheet offers more control over layout and categories, but usually requires more manual upkeep. Choose based on whether you value customization and transparent formulas or automated features more, and review the privacy terms of any service that handles financial data.

Frequently Asked Questions

Does Google Sheets have a finance or budget template?
Google Sheets may offer templates through its template gallery, though the selection can vary. You can also create a workbook from a blank sheet using a transaction tab and a budget tab. Review any template’s categories and formulas before relying on its totals.

How do I make a monthly budget in Google Sheets?
List categories and planned amounts on a Budget tab, log each transaction with a date and category, and use SUMIF to total each category. Compare actual totals with plans, then review the log for missing or duplicated transactions.

Can Google Sheets automatically connect to my bank?
A spreadsheet does not necessarily connect to a bank account by itself. Some third-party services or extensions may offer connections, but availability and access vary. Check the provider’s current terms and privacy practices; manual entry or a bank-exported CSV avoids granting a separate service ongoing account access.

Why is my SUMIF result zero or incorrect?
Check that the category text matches, the amount cells contain numbers, and the formula ranges cover the rows you expect. Extra spaces, inconsistent category names, text-formatted amounts, and entries outside the selected range are common causes.

Is a Google Sheets budget private?
It is private only to the extent allowed by the file’s sharing settings and the security of the account and device. Review who has access, avoid storing credentials or full card numbers, and use appropriate account security. Do not assume a shared link is restricted unless its access setting says so.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.