Creating Practical Excel Reports - Part 1 : The Basics

Getting the basics right!

Soooo true.

At work, you will use excel for something.  You'll have an excel spreadsheet that you use to keep track of something and probably you'll feed that data into a report, or even produce a report from it.  It doesn't matter if your spreadsheet is tracking project delivery actions, environmental results, safety, training or financial performance.

At some point, you've had to change this spreadsheet.  Someone has asked for an extra column, a new status, a different format etc........and your heart sank.  It sank because you just KNEW that change was going to be a LOT of work.

Wouldn't it be great if you could be confident that changes to your spreadsheet weren't going to be hugely time consuming?  That you could be confident in making changes and know that the new report was easy to check and reliable?




So, what are the practical things that you can do to make this happen?  In the first of this multipart series, we'll look at the things we want to achieve when creating an excel report (or spreadsheet) and some key principles to help achieve them.

What do we want to achieve?

1. Reliable results :  the spreadsheet has to produce accurate reports every time we update it.
2. Simple refresh : When we add new data, it should be simple to update in the spreadsheet.
3. Transparent : Adjustments to the data we bring in should be easily visible, so if we get questions it doesn't take forever to work out where the number came from.
4. Fast changes :  to format of report (and fast changes generally)
5. User Agnostic : In other words, other people can update your spreadsheet (presumably while you are holidaying in the Maldives) without cursing your name...(too much)

Poorly built excel models are the ONLY reason you are not here.
(Not really, but probably one reason?)

In a perfect world, every excel spreadsheet would meet all these criteria, plus you can probably think of a few more.  In this world, when you find an excel spreadsheet that achieved half of these objectives, then you count yourself lucky.

The problems!

The primary issues with most excel spreadsheets lie in the fact that the report sheet is where a lot of the adjustments to data are done.  And, because we need to have pretty colours and spacings, that tends to lead to long formulas and hard coded numbers.

Another problem area is the linking of the report to the underlying data.  Often these are "direct" links (eg "=Data!G14") and changes to the data format throw out the excel report.

In my experience, poorly built Excel spreadsheets which exhibit one (or both!!) of the above spreadsheets can take HOURS to troubleshoot when the numbers don't come out right.  

In fact, it is often faster to rebuild the spreadsheet from scratch (using the principles below) than it is to troubleshoot an existing spreadsheet.

Basic Principles

There are only 3 basic principles:

Maldives, here we come!

Document:  

Always, always document your spreadsheet.  Have a separate page called "Instructions" and actually write how to do stuff.  On every sheet, use text boxes, comments or spare cells to write notes about what is happening and WHY.  It literally takes minutes to do when you are putting a spreadsheet together and it REALLY helps.

Separate:

Every report has at least 3 process parts:

1. Import Data
2. Adjust Data (eg by summarising/grouping, eliminating unwanted data etc)
3. Report Data

If you aren't doing all three steps, you are doing something wrong.  If you are doing these steps, then SEPARATE each step into different sheets (at least one, but more than one is really common).

So you'll have a DATA sheet, a CALCULATION sheet and a REPORT sheet (in addition to the Instruction sheet we talked about earlier (you haven't forgotten already?!). 

Clarify:

Remember, you are going to have to answer questions about where the numbers in the report came from, what was included and why there was a variance/change/exclusion.  So, make it easy on yourself.  

• Separate complex calculations into different cells (or different sheets!).  If you have nested if formulas, separate them out!  In an Excel 2013 spreadsheet you have more rows and columns that you should ever think about using.  Use space on your calculation sheet so you can SEE stages in your calculations.
• DON'T LINK to other spreadsheets.  I put that in capitals so it would stand out.  Sure, it CAN be helpful to link to another sheet, but most often it ends in disaster.  This is because small, insignificant changes to the other sheet can make huge errors appear in yours.  If you need data from another sheet then copy and paste it in as part of your data import.

*  *  *  *  *

The title of this series is creating PRACTICAL excel reports.  Not perfect ones, best practice or benchmark ones, but practical ones.  

In this series, PRACTICAL will not necessarily equal simple...some of the formulas we'll rely on are not simple and offer many opportunities to stuff things up.  (If you haven't checked out the post on "Excel Formulas you NEED to know", go do that now - here's a Link. ) .  There will be a big focus on simplicity though - that tends to lead to more clarity in what is happening in an excel workbook.

In the next instalment, we'll start by looking at the three processes (Import, Adjust and Report) in a little more detail.

Please feel free to comment or ask questions - I'm always keen to hear your experiences!