how to build an income and expense tracker in google sheets
IN SUMMARY
Building an income and expense tracker in Google Sheets involves creating separate sections for income sources, expected amounts, actual transactions, expense categories, budgets, and a summary table. Utilize data validation for drop-down lists, array formulas for dynamic calculations, and conditional formatting for visual cues.
Setting Up the Income Section
List your income sources (e.g., paycheck, side hustle) in one column and enter the expected amounts in another column. Use data validation to create a drop-down list for income sources in the transactions table.
Create columns for date, income source, and amount. Use an array formula to automatically populate the full date based on the day entered. The amount column should sum up transactions for each income source using another array formula.
Add a SUM formula to calculate the total actual income. Use an array formula to find the difference between expected and actual income. Conditional formatting can highlight positive or negative differences in different colors.
Creating the Expense Section
Similar to the income section, create a column for expense categories and another for budgeted amounts. Use data validation for a drop-down list of expense categories in the transactions table.
Create columns for date, expense category, and amount. Use the same array formula approach as the income section to populate dates and sum up transactions for each category.
Add a SUM formula for total actual expenses. Use an array formula to find the difference between budgeted and actual expenses. Conditional formatting can highlight overspending or underspending in different colors.
Building the Summary Section
Add a table with rows for income, expenses, and amount left. Include columns for expected, actual, and difference amounts. Pull data from the income and expense sections using cell references.
Use conditional formatting rules to highlight positive and negative differences in the summary table. For example, green for positive income difference, red for overspending on expenses.
Create a chart to visualize expected vs. actual income and expenses. Customize the chart's appearance, colors, and labels for better readability.