How best to manage my growing data in Excel?
- by Mike
This isn't a question about formulas or features in Excel. I'm debating the correct/best way to manage the growing amount of data 'I have to' manage in Excel (I produce PIVOT tables/reports for my management).
DATA: I record the number of publications we order: cost, date ordered, start and end of subscription, who requested it, when they ordered it, when I ordered it, will it be cancelled next year, etc, etc, etc.
DILEMMA: Obviously we re-order a lot of the same publications, so depending on how I manage the data I could be duplicating all over the place.
OPTION 1: So, do I use ROWs = publication name in Row 1 and all the related columns for each financial year are copied and pasted after each financial year ready for the new FY information? This will lead to me going to column ZZ.
OPTION 2: Or, do I use COLUMNs = each row has only one FY information for each publication and if we re-order or cancel a publication I re-type the publication name in a row below and fill in appropriate columns? This will lead to a long list of publications down to row 10000, and potential for misspelling of repeat ordered publication names.
IDEAS: What's the best way - thinking in terms of pivot table best practice, being able to sum or count easy, report formatting, etc.
Any best practices much appreciated.
