Excel Logo

Creating an expense and income spreadsheet can help you manage your personal finances. This can be a simple spreadsheet that provides an insight into your accounts and tracks your main expenses. Here’s how in Microsoft Excel.

Create a Simple List

In this example, we just want to store some key information about each expense and income. It doesn’t need to be too elaborate. Below is an example of a simple list with some sample data.

Sample expense and income spreadsheet data

Enter column headers for the information you want to store about each expense and form of income along with several lines of data as shown above. Think about how you want to track this data and how you would refer to it.

This sample data is a guide. Enter the information in a way that is meaningful to you.

Format the List as a Table

Formatting the range as a table will make it easier to perform calculations and control the formatting.

Click anywhere within your list of data and then select Insert > Table.

Insert a table in Excel

Highlight the range of data in your list that you want to use. Ensure that the range is correct in the “Create Table” window and that the “My Table Has Headers” box is checked. Click the “OK” button to create your table.

Specify the range for your table

The list is now formatted as a table. The default blue formatting style will also be applied.

Range formatted as a table

When more rows are added to the list, the table will automatically expand and apply formatting to the new rows.

If you would like to change the table formatting style, select your table, click the “Table Design” button, and then the “More” button on the corner of the table styles gallery.

The table styles gallery on the Ribbon

This will expand the gallery with a list of styles to choose from.

You can also create your own style or clear the current style by clicking the “Clear” button.

Clear a table style

Name the Table

We will give the table a name to make it easier to refer to 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” into the Table Name box.

Naming an Excel table

Add Totals for the Income and Expenses

Having your data formatted as a table makes it simple to add total rows for your income and expenses.

Click in the table, select “Table Design”, and then check the “Total Row” box.

Total row checkbox on the ribbon

A total row is added to the bottom of the table. By default, it will perform a calculation on the last column.

In my table, the last column is the expense column, so those values are totaled.

Click the cell that you want to use to calculate your total in the income column, select the list arrow, and then choose the Sum calculation.

Adding a total row to the table

There are now totals for the income and the expenses.

When you have a new income or expense to add, click and drag the blue resize handle in the bottom-right corner of the table.

Drag it down the number of rows you want to add.

Expand the table quickly

Enter the new data in the blank rows above the total row. The totals will automatically update.

Row for new expense and income data

Summarize the Income and Expenses by Month

It is important to keep totals of how much money is coming into 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 in 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, and then choose “Summarise With PivotTable”.

Summarise with a PivotTable

The Create PivotTable window will show the table as the data to use and will place the PivotTable on a new worksheet. Click the “OK” button.

Create a PivotTable in Excel

The PivotTable appears on the left, and a Field List appears on the right.

This is a quick demo to summarise your expense and income easily with a PivotTable. If you are new to PivotTables, check out this in-depth article.

To view a breakdown of your expense and income by month, drag the “Date” column into the “Rows” area and the “In” and “Out” columns into the “Values” area.

Be aware that your columns may be named differently.

Dragging fields to create a PivotTable

The “Date” field is automatically grouped into months. The “In” and “Out” fields are summed.

Income and expenses grouped by month

In a second PivotTable, you can view a summary of your expenses by category.

Click and drag the “Category” field into “Rows” and the “Out” field into “Values”.

Total expenses by category

The following PivotTable is created summarising expenses by category.

second PivotTable summarising expenses by category

Update the Income and Expenses PivotTables

When new rows are added to the income and expenses table, select the “Data” tab, click the “Refresh All” arrow, and then choose “Refresh All” to update both PivotTables.

Refresh all PivotTables

Alan Murray Alan Murray
Alan Murray has worked as an Excel trainer and consultant for twenty years. On most days, he can be found teaching Excel in a classroom or seminar. Alan gets a buzz from helping people improve their productivity and working lives with Excel.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.