Developing Good Reports in Excel – Factors to Consider – Part 1

Crayola

I think it is pretty clear in everything one does to revisit the basics every now and then. You may consider this as a ‘golden rule’ of life… or not… but anyway this point is clarified in the case of the 10 commandment, the constitution of a country, et cetera. Excel is no exception to this ‘golden rule’-ish principle.

Lots of people see spreadsheets as a tool to add, subtract or even as a colour palette. These are not close to the truth. In fact if you work in the field of finance or accounting or data analysis, I am sure you would have come across a few reports that are very painful to follow. Some examples are cases where the report is too colourful a child would use Crayola to extend the boarders of the report; too complex to simply follow the formula precedents or dependents or too ‘raw’ that every character looks the same.

There are obviously some ground rules to follow when reporting in excel. The reports considered in this report excludes simple tabular reports such as often seen in the ‘student and scores’  example but rather looks at reports that require some level of aggregation of data columns using some level of constraints. Let’s look at some of the factors to consider.

  • Make the report about the users

It is very easy to feel the need to impress your users by including irrelevant information. It is important to consider that you are not reporting to yourself. Usually, if you are an analyst you are more likely to be developing your spreadsheet for someone else. You will need to focus of the outcome the user requires and make the development as specific as possible. This is not to say that you should not include suggestive tabs or report important deviations. You will often know when you are being too elaborate.

  • Data→Processing→Result

This is the most basic rule in Excel reporting. You need to keep the three elements apart from each other. If you are not using external data or a pivot, the ‘processing’ element may be important to separate. It is important to avoid putting all your source data, calculations and results in the same place. Clearly define an area for your data. This sends the user to the more important stuff thereby saving the possibility of incorrect information. Data integrity is key thus by separating these elements, you prevent the user from accidentally changing the raw data which changes the outcome intended. You may use edit lock tools with passwords to prevent data integrity issues.

  • Colours are not for fun

In Excel, colours are not just to add an aesthetic feel to the report outcome. Colour codes should be initially defined and then applied. You may use conditional formatting to quickly get this done or apply them manually but either way, it must have some real meaning. You may use it to define common factors, regions, relationships or opposites among others. The next time you use a colour that has no business in your report, be sure to state that ‘This colour has no meaning’. The latter is of course a joke. There is no denying that you can use colours to design tables and just highlight table headings, sections and sub-sections. The key point here remains that colours must have some meaning.

  • Relationships ‘are’ it

The real deal about spreadsheets are relationships. It cannot get any simpler than that. Records are rows across columns and each record is about a specific event. Thus, when dimensions are defined in a report, it is important that the relationships are easily identifiable. This can be achieved through formatting by using colours, text features and applying freezing to rows and columns to allow the user to easily make sense of the report’s story in order to make good decisions. Imagine how annoying it is to have to get to the middle of a report say column CC and scrolling leftward to column A to find the relationship.

We will continue with more factors to consider in Part 2. Enjoy