Creative Chaos In Excel? I Got A Better Idea

Not me, just a puzzled guy

 There is such thing as "creative chaos" and it may work for writers, artists and inventors. However, I know for sure it does not work for organizing calculations and models in Excel. It is really frustrating to open an Excel file and it is a mess of fonts, borders, formulas, values, sheet names, lines without headers and other "design solutions" that make it look as if some ancient gods had a pagan feast and suddenly left. Aesthetics apart, such files are very difficult to navigate, maintain and even more difficult to update or change. I realize that most of it is not a result of a directed thought but a chaotic thoughts and randomly coming requirements for things to calculate (ASAP, as usual). There are few simple guidelines to follow in order to produce neat and tidy Excel files.They do not take time at all and relate more to discipline than some specific Excel or model-building knowledge. I could not cover everything in great details here but take a look to get some good ideas and have a base for further thoughts.


All the rules could be summarized by simple idea: organize the sheets and calculations flow in a way to make it easily readable, easy to navigate, easy to find the piece you need and easy to change. Here is a list of things that help in this:

- Put header on each sheet with description of what is on it and some additional details to help better and faster understanding. See here for some further comments that are targeted at data sources but are relevant to general sheet description as well.

- Use meaningful sheet names. "Sheet1" and "Sheet2" are good as long as you have few sheets in workbook. Keeping this naming for more than few sheets would make it difficult to identify.

- Use meaningful column names. If this is not a good option, add better description as a comment on the column headers.

- Organize sheets to have easily read calculation flow. Top to bottom is good as it is the natural direction for reading. Left-to-right is also good. In general, think about how good the sheet would be read - adjust column names and formatting to see as much as possible of the calculation block without scrolling.

- Separate source and reference data from calculations. You could do that in sheets or in separate ranges on a sheet that are clearly identifiable. 

- Use some sort of coding for what is what. Color coding is a good idea. For example, I usually use orange for reference data, green for source data, blue for calculations and output and apply this color scheme to tab color and column headers and backgrounds.

- Add a notification for each block of data/calculations for identification together with a comment of what is in it. It could be a short not in simple cases or more elaborated notes in case of more complex operations.

- Use named data ranges. They will make formulas much easier to read and navigation to this range much faster.

- Group together logically close operations/calculations and separate the groups with a marker.  Spreading connected calculations all over the sheet is a recipe for errors and wasting of time navigating up and down when verifying or making changes in the calculations.

- Break long steps of calculations in separate sheets.  That makes navigation and tracing much easier.

- Add an index sheet. Describe all sheets in the workbook - what is on them. Adding a hyperlink to the sheet is also a good idea.

- Add a sheet with explanation of the calculation flow in case it is bit more complicated or often need to be explained to users. It could be simple step-by-step explanation of the flow or a scheme. It would be even nicer to link different points of the calculation with the sheet and block where it actually happens.

- Separate manual inputs and parameters from calculations. Having them all over the place is not a disaster but in this way you create a central point for verifying and adjusting these inputs. It makes creating scenarios much easier as well.

- Use consistent fonts, font sizeс and colors.

- Avoid hiding rows and column. Use grouping instead.

- Add navigation between different sheets and calculation steps. This is very helpful in case of large calculation sheets or workbooks.

These hints are easy to implement. The benefits are numerous and heavily outweigh the downside that comes in as the extra time to implement. Most of the hints could also be applied with relatively little effort to legacy workbooks that usually suffer from the problems we try to avoid. Again, these are general hints and I am sure the reader could think of more that make Excel files nice and tidy.