Formatting ‘pure’ vs derived data (All)

Topic

New Reply

Hey,
are there any standards/practices, or has anyone any suggestions regarding the formatting of ‘pure’ (directly entered) and derived data in Excel in order to make large files more transparant & understandable for others? Or is this just something anyone has to find out & experience for him/herself?
I’m working on a fairly complicated Excel file with derived data. Besides documenting it’s structure in an accompagnying document, I did some experimenting on visually indicating directly entered data against data which refers to other cells (just referring, or combining & calculating values from elswhere on the sheet & from other sheets) and how you can limit/avoid ‘conflicts’ between this kind of formattting and the formatting for other purposes (like outprints for users,…).
(Factually this could be a question about data organisation in Excell too but in order to limit the subject, I’ld rather skip that question right now .)

Reply | Quote

Replies

If I understand you correctly you seem to be asking how easy is it to distinguish between cells using formulas and those that don’t.

There is a simple toggle to switch between the ‘displayed sheet’ and the underlying ‘formula sheet’:
Press Ctrl ` (that second key is just under the top left [Esc] key on a UK keyboard – I think it’s there on US kbds too)

zeddy

Reply | Quote

zeddy,
thanks for your reply, with that shortcut key worth knowing about… but it seems that I didn’t formulate my question right.

Assume you have a large and complex Excel file containing both ‘nude’ data and many derived & calculated information. For a stranger who needs to use your file, it can be really hard to find out how everything is organised, e.g. which data are ‘pure’ and which are derived information. Besides the point you offer, that you actually can easily find calculated cells by switching to ‘formula view’, I wondered if there weren’t any common or good practices which allow you to visually layout your data so that the structures & build up become more apparent, understandable, transparant.

For example (based on a large data set which we eventually started migrating to a relational database…): a file contains certain demographic parameters (population, number of births, number of deaths, number of immigrations, number of emigrations) for a number of cities, along the years. Further, it contains a lot of derived tables, statistics and graphs like population growth over a number of years, etc, made & used for different reporting needs, studies,… One person gathered & managed those data, organised them into several excel files. At a certain moment, we noticed that it took more and more time for her to deliver the appropriate tables for reports & studies, a collegue was worried that certain values didn’t logically correspond anymore,… And so we dived into her data… but it took days to figure out how it was organised, which numbers were based on what values, how the links were between the sheets & data presented in them.
Now: assume we could start all over, forget Access, and try to continue managing all this in Excell. Besides proper structuring (well thought planning,…), are there any layout guidelines or thumb rules one could apply so that someone else can be helped to ‘find his/her way’ more easily in the file?

Just a shot in the dark, it could possibly be someting like use as a different foreground colour for
– black for nude data
– two shades of green for cells copying the (exact) content of other cells, in the same or another worksheet
– two shades of orange-red for cells calculating data based on other cells, in the same or another worksheet

Reply | Quote

So now we’re into the spreadsheet design discussion area!

I would advise something like this:

Have a separate sheet (or area) for input.
Format it nicely so it is easy to read on a screen
Ease the process of input using (Data, validation) dropdowns and all other tricks Excel has built in.
Take care to group the inputcells logically: Make sure like data is in the same table.
If you are going to do lookups in this data later on, make sure the data has a simple table structure: one row of headers, one row for each set of data (as a Database table is setup).

Have another sheet (area) for your calculations
Protect this sheet against inadvertant changes (or prying eyes )
Define (descriptive) names that refer to the data used in the calcs (even better: use dynamic names so they adjust to the amount of data automatically)

Create pretty output sheets that have formulas referring to the calc results and maybe use some dropdowns so the user can select the part of the analisys to be printed. A few (!) input cells might live on these sheets, but I would use controls set NOT to print that are tied to cells on the actual inputsheet.
Protect this sheet too.

Create a toolbar with some buttons that enables to user to print, save or whatever needs to be done.

Reply | Quote

Hasse
This is excellent advice from Jan.
In most of my workbooks, I tend to have a main startup sheet (called [Main]) and the last sheet is usually [Parameters].
For serious bits of work, I’d also have a [History] log sheet to record who did what and when to the spreadsheet structure, design changes etc.
For User input, I usually have pale green unprotected cells.
I use colour-coding to indicate external linked data.
I use formatting to indicate named cells.
For critical workbooks, I capture the user’s Excel settings, save them, then completely remove all Excel toolbars, keyboard shortcuts and everything else and only allow my specific features, restoring the user’s Excel and windows environment on exit..

By the way, another useful tool to use when trying to ‘understand’ another person’s workbook is to use the Excel Auditing Toolbar. This allows you to trace precedents and dependants of selected cells e.g all cells that are referred to by this cell or all cells that ‘use’ the current cell. Lines are drawn between ‘linked’ cells and clicking on the lines jumps you to the linked cell. You can use this feature to trace back to the source raw data of formula cells.
The Auditing toolbar is found under Tools->Auditing->Show Auditing Toolbar

zeddy

Reply | Quote

I build a lot of financial models for my group at work (pretty user-interfaces with lots of VBA on top of complex financial analysis so non-MBA types can understand it) and the convention we use is blue for any data cell (your term for raw data not calculated by a formula) that the end user can change, dark gray font are those cells that the end-user can not change (either hard-coded by the tool’s designer/admin or formula-based) and text/labels are black (there are also rules for the navigation buttons, etc. which conform to my company’s web site style).

Additionally if a user changes a blue data cell from its default value, I have code that changes it to green which means it’s “user defined” (they changed the default data to their own value). I also have a button on each sheet that restores the default values as well. Each sheet includes a legend which clearly shows the meaning of these colors.

As the others have said, sheet organization and navigation is very important to how usable a workbook is. Don’t cram everything together just because it fits and don’t use lots of different colors. If someone has to ask yow how to use the workbook, what it all means, where is the data, then it’s not organized well enough.

Deb

Reply | Quote