How to Create Expense and Income Spreadsheets in Microsoft Excel

Excel logo

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.

Sample Expense and Revenue Worksheet 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.

Insert a table in Excel

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.

Specify the range of 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 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.

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 delete the current style by clicking on the “Delete” button.

Clear table style

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.

Name an Excel 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.

Total Line on Ribbon Check 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.

Adding a total row to the table

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.

Expand the board quickly

Enter the new data in the empty lines above the total line. The totals will be automatically updated.

Line for new expenditure and income data

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”.

Summarize with a PivotTable

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.

Create PivotTable in Excel

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.

Dragging fields to create a PivotTable

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

Revenue 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 in “Rows” and the “Output” field in “Values”.

Total expenses by category

The following PivotTable is created summarizing the expenses by category.

second pivot table summarizing 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.

Refresh all PivotTables

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.