I dont know where to share this so I thought here would be a good place as i’ve seen some questions asking it.
I like to keep tabs on my income/outgoing so i needed a spreadsheet to easily see where i can trim some fat. So i came up with this two sheet strategy that allows me to see at a glance where the money comes and goes.
The first table is a monthly breakdown, 1 row per month.
The second table is a daily/weekly breakdown with 1 row per day/week its up to you the formulas are setup to for daily rows but will work without edit for weekly rows.
Nothing to it but to do it!
Sheet1 (headings on row 1, date column A, formula column B)
- Column A: format to date. Its important that the date is automatically converted not typed in manually.
- Copy the formula into cell B2 then drag it down to fill all 12 rows, the row working row for sheet1 will adjust properly.
- =SUMPRODUCT(MONTH(‘sheet2’.$B$2:$B$367)=MONTH(‘sheet1’.$B2),‘sheet2’.$C$2:$C$367)
Sheet2(headings on row 1, date column A, formula column B)
- Column A: format to date.
- Copy formula into Cell B2 drag the formula down the column to as many rows as you want to use.
- =sum(C2:Z2)
You can style the spreadsheets as you wish, just keep the date columns in each table as date format.
The $ in the Sheet1 formula are required as they lock the formula to the specified cells so when moving the formula to another cell it prevents automatically adjusting those parts.
To work with multiple sheets:
- Duplicate sheet 2
- Add a new column to sheet1 ( C ), give it a header to match the name of your duplicate sheet.
- Drag the formula from B2 to C2
- Adjust the formula replace sheet2 with the new sheet name eg: expenses
- Drag the new formula from C2 down the full column to your last row.