how to create a monthly income and expense summary in google sheets
IN SUMMARY
Creating a monthly income and expense summary in Google Sheets involves setting up a template with sections for income sources, expected amounts, actual amounts, expense categories, budgets, and transactions. Use formulas and data validation to automate calculations and create drop-down menus for easy data entry.
Setting up the Income Section
List your income sources (e.g., paycheck, side hustle) in one column and enter the expected amounts for each source in another column. Use data validation to create a drop-down menu for income sources in the transactions table.
Create a table with columns for date, income source, and amount. Use an array formula to automatically populate the date column based on the day entered. The income source column should have a drop-down menu populated from the income sources list. Enter transactions with the corresponding amount.
Use a SUM formula to calculate the total actual amount received for each income source. Subtract the expected amount from the actual amount to get the difference. Apply conditional formatting to highlight positive or negative differences.
Setting up the Expense Section
List your expense categories (e.g., rent, groceries) in one column and enter the budgeted amounts for each category in another column. Use data validation to create a drop-down menu for expense categories in the transactions table.
Create a table with columns for date, expense category, and amount. Use an array formula to automatically populate the date column based on the day entered. The expense category column should have a drop-down menu populated from the expense categories list. Enter transactions with the corresponding amount.
Use a SUM formula to calculate the total actual amount spent for each expense category. Subtract the budgeted amount from the actual amount to get the difference. Apply conditional formatting to highlight positive or negative differences.
Creating a Summary Section
Create a table with rows for income, expenses, and amount left. Include columns for expected, actual, and difference amounts. Use formulas to populate these cells with the corresponding values from the income and expense sections.
Use conditional formatting to highlight positive and negative differences in the summary table. For example, make positive income differences green and negative income differences red. Reverse the logic for expenses (negative differences green, positive differences red).
Insert a chart to visualize the expected and actual amounts for income and expenses. Customize the chart's appearance, such as colors and font sizes, for better readability.