Forum Discussion
olsxn
Dec 05, 2023Copper Contributor
Can I add a new sheet to my monthly financial tracker and simultaneously populate a cell?
Hi there, I'm relatively new to Excel so I'm not sure if this is possible in the way I'm trying to go about it. I have created an xls for monthly financial tracking. Right now it has a sheet for ...
mathetes
Dec 05, 2023Silver Contributor
Hi there, I'm relatively new to Excel so I'm not sure if this is possible in the way I'm trying to go about it.
Welcome to the wonderful world of Excel. And commendation for wondering if the way you're going about it is (a) possible, and, hopefully (b) the best way to do it even if possible.
I added that last--you didn't ask if it is the best way, but I'm suggesting you should ask that as well.
It's quite common that beginners with Excel will do what you're doing--create a separate sheet for each month.
What you're doing is clear to the human eye and mind, but fails to take advantage of Excel's abilities to slice and dice info. You're doing some of that "manually" by separating data into months and separate sections of the sheet.
In general, as you get more experienced with Excel, I think you'll find that Excel works really well with such things as tracking finances, if you have at the heart a SINGLE database with all of your transactions, be they income or expense, cash flow in, cash flow out, with sub-categories as needed under each of those. Keep all your raw data or "input" in one place. The let Excel do the work of separating out the data, summarizing it -- the output -- extracting and reporting on data, slicing and dicing in whatever subset--month, quarter, category (savings, debts, etc)--you wish.
I've attached a very simple example of what I mean. You'll find here a single tab--"RawData"-- that includes records of transactions, categorized under "Type" as income or expense, with a separate column for "Category". Other than those two columns--which are again simply examples--there is a column for date of transactions, and then a column for amount and another for "adjusted amount," the latter being used to ensure that expenses are always displayed as negative amounts in summaries.
Then there's a "Summary" tab that makes use of Excel's Pivot Table to create a cross-tabulated monthly summary.