Basic techniques/tips for data organization

[3G]

So, thanks to this board I'm on the up and up when it comes to nifty formulas, dashboards, and other presentation layers. The one thing that I am wondering is that if there are any basic reccomendations people have as to how to organize your spreadsheets? I've seen some folks say have a "data sheet" with the raw data, a "summary sheet" with your calcs/etc, and, a "presentation sheet" which is basically lipstick on the pig of the summary sheet.


How do some regular board visitors go abouts their organization of data? Do you stack your data "tables" on top of each other? Across? How do you choose which columns to keep one table vs another?


I often end up with a bunch of smaller tables all over a page, often with similar columns or rows (i.e. years, Dates, etc), and, while the data isn't necessarily "related", I often wonder if I should just clean them up and put them in one table.


So...how do you do it?

 

[Luke M]

Depends...am I using the final output, or my pointy haired boss? <grin>


Typically, I like to have 1 sheet for all the raw data that's related. So, it's all in one table, or there some clear correlation. Otherwise, I'll just use another worksheet (they're free!) Tables would go across typically, with row 1 (headers) frozen. This is usually unedited, so that if a problem occurs I can quickly tell if the error is with my workbook/formulas, or if the raw data has a problem.

At this point, I'm usually either combining data from tables (if not able to do in a query/macro) and then running some calculations. Depending on size, this may take another worksheet.

Then, as you mentioned, it's just about creating a nice dashboard/presentation sheet. I think this works out best as it typically limits the amount of recalculation needed if a user does something on dashboard.


Overall, I think the best layout is whatever works best for you, and still keeps things clear. Too often I've seen workbooks built that did lots of cool tricks, but no one except original author could figure out how to work it.

 

[Mike86]

One main worksheet with a master data table. If you have to use more than one table, they go on separate worksheets. Way too easy to delete lines in the wrong table if everything's on one sheet. Then one or more tabs for helper tables / columns or criteria fields. I usually end up regretting it if the helper tables get too messy, so use more tabs if you need. Output goes on a single tab, if possible.


You can do some interesting things with tables stacked across multiple tabs, but it's much easier to deal with if everything's in one place.


I find it's easier to ignore extra data than to put it in at the end, so there are sometimes columns that don't get used for anything. Most people don't break the data down sufficiently or attempt to present it in a more human readable format, making it really difficult to analyze.