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.
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.
Common Questions
Do formulas update automatically? Yes. Change the data, the result updates instantly.
Do these work in Excel? Most do. GOOGLEFINANCE and QUERY are Google Sheets-specific.
Most useful formula? SUMIF. Simple to learn, powerful for categorizing expenses.