Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Can I add a new sheet to my monthly financial tracker and simultaneously populate a cell?

Copper 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.

 

I have created an xls for monthly financial tracking. Right now it has a sheet for January and a sheet for February.

On each sheet I have three 2D line charts (third screenshot) with markers that track my savings, debt and expenditure. The data that drives these charts is hidden at the bottom of the sheets (shown in the first screenshot below), and is calculated for each month by reading from the subtotal cells in the Savings and Debt (second screenshot).

When I duplicated the January sheet it naturally copied over the formulas for the January cells. Is there a solution where I can duplicate a sheet and simultaneously populate the February cells with a formula so that the charts auto update?

 

Thanks in advance and please let me know if you need more information!

 

 

olsxn_0-1701794129307.pngolsxn_1-1701794162427.pngolsxn_2-1701794173845.png

 

1 Reply

@olsxn 

 

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.