Forum Discussion
Rules across Sheets
mathetes I just want to make sure I understand your suggestion clearly. You are proposing I use a separate database entirely, and export it to Excel? That would make the experience more streamlined?
You are proposing I use a separate database entirely, and export it to Excel? That would make the experience more streamlined?
Not at all. I'm proposing not having monthly spreadsheets that essentially contain information all laid out in the same manner. Combine those into a single database IN Excel (adding a column, if needed, that contains the date, to serve as the basis for differentiating by month), IN the same workbook where you could use the Pivot Table (or other methods) to create summary reports on that transactional data.
When you have, for example, all your utility payments in separate sheets, depending on which month(s) they got paid, it's tricky to put together a summary. You have to draw from multiple sheets just to see trends, say, in electricity expenses. But if all of your payments, of every type, are in a single spreadsheet (organized as an Excel table), it is simplicity itself to produce reports that analyze trends by month by payee, by budget category.
I've attached an example that is not meant to do any more than show how a single database (AKA a "table") can serve as the source for a Pivot Table that yields summary amounts by month, by category. This is a very simple example. The Pivot Table is an incredibly powerful tool for taking huge amounts of data of this type and breaking it apart in cross-tabulated manner.