Quick Summary
Google Sheets formulas for budgeting - syntax, examples, and real-world applications for tracking income, expenses, and savings.
Formulas automate budget calculations - totals update instantly when you change data. Master these and your spreadsheet does the math.
Don’t want to build from scratch? The Monthly Budget Template has these formulas pre-built.
Essential Formulas
These are the basics you’ll use constantly. SUM adds numbers in a range: =SUM(B2:B15). Use it for total expenses, total income, category subtotals. It’s the workhorse of any budget spreadsheet.
AVERAGE finds the mean: =AVERAGE(C2:C13). Useful for average monthly spending or calculating typical utility bills from historical data. Subtraction is simple: =B5-B20 calculates net income (income minus expenses) or budget remaining. Multiplication and division convert between time periods: =B2*12 converts monthly to annual, =C5/4 converts quarterly to monthly.
From our experience: After years of building financial templates, SUMIF and IF are the two formulas we rely on most. SUMIF handles category-based totals across hundreds of rows without breaking a sweat, and IF drives the conditional alerts that flag overspending. Everything else is useful, but those two do about 80% of the work in our own tracking sheets. - Stefan
Conditional Formulas
Conditional formulas add intelligence to your spreadsheet. IF shows different results based on conditions: =IF(C5>B5, "Over Budget", "On Track"). This flags overspending instantly.
SUMIF adds numbers that match a criterion: =SUMIF(A2:A50, "Groceries", B2:B50). This totals spending by category from a transaction list. SUMIFS handles multiple conditions: =SUMIFS(C2:C100, A2:A100, "Dining", B2:B100, "January"). This shows category spending for a specific month. COUNTIF counts matching entries: =COUNTIF(A2:A50, "Restaurant"). Useful for tracking transaction frequency.
Date Formulas
Date formulas automate time-based calculations. TODAY returns the current date: =TODAY(). It updates automatically each day, useful for date stamps and calculating days remaining.
MONTH extracts the month number from a date: =MONTH(A2). This helps group transactions by month. EOMONTH finds month boundaries: =EOMONTH(TODAY(), 0) returns the last day of the current month. Use it for bill due dates and budget period endings.
Advanced Formulas
These require more setup but add significant power. VLOOKUP finds values in a table: =VLOOKUP("Rent", A2:C20, 3, FALSE). It looks up “Rent” in column A and returns the value from column C.
QUERY is Google Sheets’ most powerful function: =QUERY(A1:D100, "SELECT A, SUM(D) WHERE B='January' GROUP BY A"). It filters and aggregates data using database-style syntax. GOOGLEFINANCE pulls live financial data: =GOOGLEFINANCE("AAPL", "price") for stock prices, =GOOGLEFINANCE("CURRENCY:EURUSD") for exchange rates. Useful for investment tracking.
Practical Combinations
Real budgets combine formulas for useful calculations. Net income subtracts expense total from income: =SUM(B2:B10)-SUM(C2:C30). Savings rate shows what percentage of income you’re saving: =(B2-SUM(C2:C30))/B2*100.
Over/under status with amount shows both the status and how much: =IF(C5>B5, "OVER by "&TEXT(C5-B5,"$#,##0"), "Under by "&TEXT(B5-C5,"$#,##0")). Category as percentage of total helps understand spending distribution: =SUMIF(A:A,"Groceries",B:B)/SUM(B:B)*100.
Common Errors
Errors happen, and understanding them helps fix problems quickly. #REF! means a reference to a deleted cell - check that all referenced cells still exist. #VALUE! indicates wrong data type - ensure cells contain numbers where the formula expects numbers.
#DIV/0! means you’re dividing by zero. Use IF to check first: =IF(B2=0, 0, A2/B2). #N/A usually means VLOOKUP can’t find a value. Check spelling, or use IFERROR to handle missing values gracefully: =IFERROR(VLOOKUP(A2,D:E,2,FALSE), "Not Found").
Tips
A few practices make formulas easier to work with. Lock references using $ to prevent cells from shifting when copying. $A$1 locks both column and row. Name ranges for readability - instead of =SUM(B2:B50), name the range “Income” and use =SUM(Income).
Format as currency by selecting cells, then Format, then Number, then Currency. This makes numbers readable without adding manual dollar signs. Start simple - a working =SUM() is better than a broken complex formula. Build complexity gradually.
More Google Sheets Tutorials
- Conditional Formatting for Budget Tracking - Visual alerts for overspending
- Create Dropdown Lists for Budgeting - Standardize category entry
- Budget Dashboard in Google Sheets - SPARKLINE charts and visual summaries
- How to Lock Cells in a Budget Spreadsheet - Protect formulas from accidental edits
- Import Bank Statements to Google Sheets - CSV import workflow
- Google Sheets Formulas - Full formula reference library