Step by Step to Create Monthly Income & Expense Report
Here is the second part of Track Your Spending with Spreadsheet In Use. You can find the first part at How to Track Your Spending with Spreadsheet (1).In this post, we will learn how to create the report check and track your income and expense in each month. The report will looks something like the capture below:
 |
| Spreadsheet In Use: Monthly Income & Expense Report |
The tracking recorded sample data Income & Expense Data look something like below image.
 |
| Income & Expense Data |
So we will have all together 8 columns as the detail below:
- The Date (Column A)
- Description (Column B)
- Type (income or expense) (Column C)
- The Amount (Column D)
- Actual Amount to track the sign of the amount (Column E)
- The balance (Column F)
- Optional Memo (just incase you would like to note something for reference later) (Column G)
- Month (Column H)
Generate Month Column
To generate the month information we will use the formula in H2 column:=text(A2,"yyyy-mm")
or
=year(A2)&"-"&text(month(A2),"00")
This formula will generate the month in format yyyy-mm, where yyyy is 4 digits of year and mm is 2 digits of the month. The reason why year comes first in this format is when we do the report, the month and year will be in the correct order. You will see this when there are data more than one year.
Quick Formula Generating
One little trick to do a quick formula insert in every cell in H column from 2nd row to the last row of the information, follow these following steps:- Copy (CTRL+C) H2 cell.
- Go to column F then press and hold CTRL key and at the same time press the down arrow key. This will take you to the last row.
- Then, go to column H at that last row and hold down the SHIFT and CTRL key and press up arrow key at the same time. This will select H column from last row to the first row. While still holding down the Shift and CTRL key click on H2 to select only the second row to the last row.
- Paste the value (CTRL+V) you have copied from H2, you will have the data in H column ready there from the 2nd row to the last one.
Note: CTRL key is COMMAND key in Mac OS
Now you are having your data ready to create monthly expense report.
Creating Monthly Income & Expense Report
- Now go to A1 cell hold down SHIFT and CTRL key and press right arrow key follows by down arrow key. With this step, you will select the whole information (data set).
- Go to menu Data and click at "Pivot Table" as the capture below.
Spreadsheet in Use: Create Pivot Report - Click at the report area to enable Report Editor at the right of the report.
Spreadsheet in Use: Pivot Report Area - Select Description for Rows, Month for Columns and Actual Amount for Values with Summarize by as Sum. And you will have monthly Income & Expense Pivot report as below.
Spreadsheet In Use: Monthly Income & Expense Pivot Report - To view the report differently, you can switch Rows and Column value and you will have the report as below.
Spreadsheet In Use: Monthly Income & Expense Pivot Report 2
The data and example reports could be found at:
- Income & Expense Data
- Monthly Income & Expense Report - Row View
- Monthly Income & Expense Report - Column View
Please leave questions or feedback and please stay tune for my next tips and trick in Spreadsheet in Use. Thank you.
