[zwarag]

While I understand the power of Excel for specific calculations like your loan payment analysis, I struggle with modeling "continuous" data like monthly budgets and yearly reviews. Coming from a database background, I know how to model recurring financial data with tables and relationships, but I'm less clear on Excel best practices for this.

How do you structure your spreadsheet to handle: 1. Monthly recurring budgets/allowances 2. Year-over-year analysis 3. Category management 4. Historical tracking

Do you maintain separate sheets/tabs for each time period? Use pivot tables?

[orev]

I suspect that your database background is causing you to think too formally about how people structure Excel data. For the vast majority, they’re not normalizing data, running queries on sheets, etc. I can easily see this as one tab per account, then pulling in specific cells on other tabs for summaries and other analysis.

[kenjackson]

Isn’t this exactly the sort of thing spreadsheets are good for? I feel like I must be misunderstanding what you’re doing.

[pixelatedindex]

Maybe if you have millions of rows you might have trouble with excel. Other than that, from a data storage perspective an Excel is just a database that’s human readable.

[danielmarkbruce]

Not really. It generally won't have a schema. You generally can't write queries against it.

[max51]

> You generally can't write queries against it.

Yes you can, there are a lot of different ways of doing it depending on what type of data you have and how you structured it.

[danielmarkbruce]

It is nice to have a schema. Once you use schemas, it's hard to think otherwise. It feels gross.

[danielmarkbruce]

Once you go down the relational path, it's really hard to get out of it (and I don't even think you should, I think you are right).

[goodlinks]

There are many ways to skin a cat, but my advice how to try it an excel way (assuming a db backgound) would be..

try dumber things, sounds stupid but you dont need rules and structure, just data :)

denormalise a more often to break down the problem, the data and problem are your goal not structure (as much as db).

Yes period per tab type of thing is quite common, as at some point you want to close the period and never change it.

Lean into the non-standardisation to handle the real world. E.g for most of your budget its one line per item per month but this one are flexes with headcount so that has its own page, and tax is balnced in month x so ill just over type all the formulae there when the real numbers come in.

Also if the model is complex try naming fields and showing the formula in a cell next to it to remind you how its calculated (if not ready using it check out "format as table" to do this for tabular data)

And yes pivot the crap out of everything.

There is also "add to model" which gives you powerbi type modelling in excel which can also be handy and fast.

Not extensive list, and for lots of things db is better when you know how to use it.. but those are some of the "i get it" scenarios for me