Income and Expense Tracking Excel Worksheet

Monitoring your expenses and income is one of the initial steps to managing your finances. I created this Excel worksheet to use as a print-friendly handout for an introduction to personal finance class, however it can also be used as a spreadsheet that can be edited. While I do have other finance as well as budgeting spreadsheets which accomplish much more that this, want a simple method to track your financials and budget, this worksheet could aid you in getting started.

Using the Income and Expense Worksheet

I attempted to make the spreadsheet as simple to use as it could be. If you’re using Excel on a smartphone you’ll notice that it has a number of lists that are drop down. It is possible to edit these lists within the Settings worksheet.

The worksheet isn’t able to provide a summary of your the categories of income and expenses in your behalf (like an accounts Register for instance). However, if you’re confident working with Excel you can utilize the pivot Table to look at your earnings and expenses.

Step 1: Clean up your sample’s data. However, don’t delete the formulas in the Balance column.

The data sample is provided to provide an example of how to enter the beginning balances, transfer funds between accounts, and record the basic expenses.

It’s not necessary to utilize the worksheet for all your accounts within one account. You can create a duplicate worksheet to utilize the worksheets for all account If you want to.

Second step: enter the beginning balances of the accounts you would like to monitor.

It’s not necessary but it’s the thing you’d do if you would like to keep track of the balance of each account. There’s an invisible column you can remove which will display your current balance.

Step 3. Make changes to your Accounts as well as the Categories lists on the Settings worksheet, if needed.

These lists govern what’s listed in the drop-down list of each column. It’s not necessary to make use of the drop-down lists. They are only for convenience.

If your personal finances scare you it might be better to begin with only a few categories of expense. When you’re ready for something more sophisticated then you may consider using the Financial Management template.

Step 4: Review the worksheet on expenses and income each day.

If you’re working with the worksheet on your smartphone, you are able to edit it from anywhere. If you’re using the printed worksheet, you are able to keep it in your purse or in your wallet and make changes whenever you buy something.

Another option is to write notes on a 3×5-sized card or on your smartphone regarding your expenditure and then revise your workbook at home.

Step 5: Create a Pivot Table to Analyze Income and Expenses

The picture below illustrates an example of report that you can make quite easily with the Pivot Table within Excel. For detailed instructions, read the blog post written by guest writer as well as Microsoft MVP John MacDougall: Using Pivot Tables to analyse expenses and income.

Tip If you are using pivot Table within Excel 2013, or the newer version, then I’d suggest exploring the updated Timeline feature. It makes it simple to select the desired range of dates you wish to show within your table. Enter the table pivot, and then select the Analyze tab, and select Insert Timeline.