Creating an expense and income worksheet can help you manage your personal finances. This can be a simple spreadsheet that provides an overview of your accounts and tracks your main expenses. Here’s how in Microsoft Excel.
Create a simple list
In this example, we simply want to store key information about each expense and each income. It does not need to be overly elaborate. Here is an example of a simple list with some sample data.
Enter the column headers for the information you want to store on each expense and form of income as well as multiple rows of data as shown above. Consider how you want to track this data and how to refer to it.
This sample data is a guide. Enter the information in a meaningful way for you.
Format the list as a table
Formatting the range as a table will make it easier to calculate and control formatting.
Click anywhere in your data list, then select Insert> Table.
Highlight the data range of your list that you want to use. Make sure that the range is correct in the “Create a table” window and that the “My table has headers” box is checked. Click the “OK” button to create your table.
The list is now formatted as a table. The default blue formatting style will also be applied.
When more rows are added to the list, the table expands and automatically applies formatting to the new rows.
If you want to change the table layout style, select your table, click the “Table Design” button, and then click the “More” button in the corner of the table styles gallery.
This will expand the gallery with a list of styles to choose from.
You can also create your own style or delete the current style by clicking on the “Delete” button.
Name the table
We will give a name to the table to make it easier to consult in formulas and other Excel features.
To do this, click in the table and then select the “Table design” button. From there, enter a meaningful name such as “Accounts2020” in the Name field of the table.
Add totals for income and expenses
Formatting your data in tabular form simplifies adding total rows for your income and expenses.
Click in the table, select “Table design”, then check the “Total row” box.
A total row is added at the bottom of the table. By default, it will perform a calculation on the last column.
In my table, the last column is the expenditure column, so these values are totaled.
Click on the cell you want to use to calculate your total in the income column, select the list arrow, then choose the calculation of the sum.
There are now totals for income and expenses.
When you have a new income or expense to add, click and drag the blue resize handle in the lower right corner of the table.
Drag it down the number of rows you want to add.
Enter the new data in the empty lines above the total line. The totals will be automatically updated.
Summarize income and expenses by month
It is important to keep totals of how much money has entered your account and how much you are spending. However, it is more useful to see these totals grouped by month and to see how much you spend on different expense categories or on different types of expenses.
To find these answers, you can create a PivotTable.
Click in the table, select the “Table design” tab, then choose “Summarize with pivot table”.
The Create PivotTable window will display the table as data to use and place the PivotTable on a new worksheet. Click on the “OK” button.
The PivotTable appears on the left and a list of fields appears on the right.
This is a quick demonstration to easily summarize your expenses and income with a pivot table. If you’re new to PivotTables, check out this detailed article.
To display a breakdown of your expenses and income by month, drag the “Date” column in the “Rows” area and the “Input” and “Exit” columns in the “Values” area.
Be aware that your columns may be named differently.
The “Date” field is automatically grouped into months. The “In” and “Out” fields are added together.
In a second PivotTable, you can view a summary of your expenses by category.
Click and drag the “Category” field in “Rows” and the “Output” field in “Values”.
The following PivotTable is created summarizing the expenses by category.
Update PivotTables of Income and Expenses
When new rows are added to the income and expenses table, select the “Data” tab, click on the “Refresh all” arrow, then choose “Refresh all” to update the two pivot tables.