Forum Discussion
identifying changes overtime in an excel spreadsheet
I am struggling to find a way to track changes on an excel spreadsheet. I have a report I can pull from a shared information database I work with that will give me information on clients and their income. It will show a row for each income source update. I'm trying to figure out a formula or method for determining if the client has increased or decreased income overtime. The spreadsheet looks something like this:
| Client ID | Income from Any Source? | Total Monthly Income: | Receiving Income Source? | Source of Income | Monthly Amount | Start Date | End Date |
| 1 | Yes | $ 100 | No | Earned income | $ - | 1/1/2025 | 2/28/2025 |
| 1 | Yes | $ 100 | No | SSI | $ - | 1/1/2025 | |
| 1 | Yes | $ 100 | Yes | Earned income | $ 100 | 3/1/2025 | |
| 2 | No | $ - | Yes | SSDI | $ 200 | 2/20/2025 | 5/15/2025 |
| 2 | No | $ - | No | SSI | $ - | 2/20/2025 | |
| 2 | No | $ - | No | Earned income | $ - | 2/20/2025 | |
| 2 | No | $ - | No | Pension | $ - | 2/20/2025 | |
| 2 | No | $ - | No | SSDI | $ - | 5/16/2025 | |
| 3 | No | $ - | No | Earned income | $ - | 1/23/2025 | |
| 3 | No | $ - | No | SSDI | $ - | 1/23/2025 | |
| 4 | Yes | $ 200 | No | SSI | $ - | 2/4/2025 | 6/14/2025 |
| 4 | Yes | $ 200 | No | SSDI | $ - | 2/4/2025 | |
| 4 | Yes | $ 200 | Yes | SSI | $ 100 | 6/15/2025 | 7/5/2025 |
| 4 | Yes | $ 200 | No | Earned income | $ - | 2/4/2025 | 6/14/2025 |
| 4 | Yes | $ 200 | Yes | Earned income | $ 200 | 6/15/2025 | |
| 4 | Yes | $ 200 | No | SSI | $ - | 7/6/2025 | |
| 5 | Yes | $ 100 | No | Earned income | $ - | 1/14/2025 | 4/4/2025 |
| 5 | Yes | $ 100 | Yes | Earned income | $ 100 | 4/5/2025 | |
| 6 | No | $ 300 | No | SSDI | $ - | 2/26/2025 | 7/1/2025 |
| 6 | No | $ 300 | No | SSI | $ - | 2/26/2025 | 7/1/2025 |
| 6 | Yes | $ 300 | Yes | Pension | $ 50 | 2/26/2025 | |
| 6 | Yes | $ 300 | No | Unemployment | $ - | 2/26/2025 | |
| 6 | Yes | $ 300 | Yes | SSDI | $ 100 | 7/2/2025 | |
| 6 | Yes | $ 300 | Yes | SSI | $ 150 | 7/2/2025 |
I'd like to be able to easily see the client ID, if they have an increase or decrease in overall income, and what source(s) have increased or decreased. I know it will likely take multiple formulas to get this but I can't seem to figure it out.
I am open to any ideas so I don't have to manually go through and examine each client to determine. Thanks!
1 Reply
Not sure I totally get the request, but I think you can pull this off using a pivot table.
- Select the data, format it as a table (Home tab or press control+t).
- Click the "Summarize with Pivot table" button on the Table Design tab, or the Pivot table button on the Insert tab
- Drag both the Client ID and the Start Date fields to the Rows box
- Drag the Monthly Amount field to the Values box
- Right-click any of the numbers in the "Sum of Amount" column in the Pivot table and select "Show Values As", "Difference From".
- In the small box, choose Start Date in the first drop-down and "(previous)" in the second and click OK
