Document changes in a graph excel

Question

I keep track of my financial activities on an excel document, which I update once a week or so. I simply replace old data with fresh data when I update.

So that I may track the evolution of certain values over time, I want to record the change in the cell's value in a graph. Is it possible without having to manually save earlier data elsewhere? What would be the least labor-intensive method for accomplishing this?

 This is the doc I'm using. I primarily want to track net worth change over time, and maybe certain assets.

Answer 1

To be able to trace changes, the file must contain the previous data somewhere.

If you want the most recent values in one place, I'd advise having one worksheet each for assets, debt, net worth, pensions, etc. You may use lookup functions like VLOOKUP or INDEX and MATCH to get the most recent values from the assets, debt, etc. worksheets. It could rapidly become disorganized or confusing if you attempt to capture weekly statistics for everything on one sheet.

The spreadsheet for assets could be formatted in one of the following ways. Each asset has a separate column, so all you have to do to add new information is put it in the appropriate column. For Debt, you could follow a similar procedure, calculate the figure for each date using a formula on the Net Worth page, and then add whatever charts you wish to automatically update.

A slightly more complex format might be to use what you have but insert a Date column and add additional rows when you add new data. This might be a bit more work to update and there might be some difficulty creating charts for specific assets with this, but you get to keep more of the info in one place.