How best to manage my growing data in Excel?

Question

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.

Answer 1

If you have to stay in Excel, you should organize your data in a way that will make it easier to move to a relational database later. Even if you can't see moving it now, you will have the time and resources and management support to do it right someday, so make it easier on yourself.

It will be hard to have normalized data in Excel, but get as close as you can. Make another sheet with the publication names and use Data Validation in the "publication name" field to help prevent misspellings.

If I were doing it in RDBS, I would have three tables: Orders, Publications, People.

People: PersonID, PersonName ...

Publications: PublicationID, PubName, ...

Orders: OrderID, OrderDate, PublicationID, RequestedBy(personid), OrderedBy(personid), etc.

That may not be exactly how you will want it, but you can figure out what will work for your data. Once you know how it will look in an RDBS, set it up in Excel. For instance, each row would be an order and the columns would be

OrderDate, PublicationName, RequestedBy, OrderedBy, etc..

use Data Validation pointing to a separate table for PublicationName and for RequestedBy and OrderedBy. Then three years from now, you can create ID fields for your Publications and People tables. Then use formulas to replace the names in your orders table with the IDs you come up with. Import the whole mess into and RDBS and you're good to go.

Meanwhile, you're quasi-normalized data will be in great form to make pivot tables, IMO.

Answer 2

Actually, Excel is not the right and efficient way to manage large amounts of data. In my company, which is a magazine distribution planning firm, they were used to do similar tasks as yours in Excel, but in time, Excel became inadequate, and IT has decided to move all Excel-related processes/works to an online database. The whole process (leaving Excel and moving to an online database) was handled as a project. Now everything is controlled via web interfaces and Oracle database. People manage the data after logging into their online accounts, and they can generate automatical web based reports. PL/SQL is used to generate special reports as well. Shortly, you should somehow start leaving Excel and move to a database solution. This kind of changes always seems hard, but once you manage them programmaticly with a successful coordination of IT, you pass to a higher and more productive level.

---

EDIT: IMO, it's no matter to what direction (down or right) you fill in data as long as you don't exceed Excel's maximum limits. Excel 2007's maximum limit for worksheet size is 1,048,576 rows and 16,384 columns. So you should just make sure that you organize the formulae well and input the correct data in correct cells. Since maximum column limit is 16,384, it's better to go downwards in your case.