Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Formatting 'pure' vs derived data (All)

    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 <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20> .)

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,828
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Formatting 'pure' vs derived data (All)

    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

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Formatting 'pure' vs derived data (All)

    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

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting 'pure' vs derived data (All)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,828
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Formatting 'pure' vs derived data (All)

    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

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting 'pure' vs derived data (All)

    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. <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Deb <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •