Recently, it was stated that Excel formulas are the most widely used programming language in the world, but it is also the case that Excel is [very probably] the mode widely used database in the world, and for certain is the most widely used reporting solution in the world. No matter how big or small a company is, it is highly likely it has several Excel spreadsheets that contain lists of things (i.e. databases), there will be one or more spreadsheets with VLookup and business logic built in formulae, and many people will be sending sales reports or accounting reports or customer services fault reports to their managers or clients that are made up of Excel tables and pivots and charts.
The Good
It is very easy to quickly do simple things, to get started from a blank worksheet to some pivots and charts and potentially some quite extensive logic and calculations. It is totally flexible, there aren’t many things it can’t be used for, to some extent or other. It is ubiquitous, so there is no issue with opening or sharing files, or it being an obscure program that your latest new starter hasn’t used before. It is the multi-tool of the office, the Swiss Army Knife for desktop PC users.
The Bad
As with many things, its strength is also its Achilles heel. Great flexibility, no rules, type whatever your want wherever you want. Add numbers and text, no type checking. There’s nothing that forces good practice, forces or encourages you to follow good practice or avoid pitfalls. You build your spreadsheet today and it works, tomorrow someone inserts a new column, overwrites a formula with a hard coded number, and on come Monday your replacement can’t figure out why the numbers in the monthly report don’t look right.
So what about The Ugly?
Well, you’ve all seen an ugly spreadsheet full of endless formulae that you can’t decipher. The Ugly is what most people create with Excel. Something that started out as a quick and simple temporary solution, and over time became a business critical birds nest of a spreadsheet with 5 different versions of the same business rule implemented on different sheets.
But just because it can be bad and ugly, doesn’t mean it has to be. All you need to do is follow some good practice guidelines, avoid dangerous practices, and you will make the life of that person who inherits your spreadsheet when you have gone, a whole lot easier. It doesn’t have to be a police state either. It is a futile exercise going around criticising other peoples ugly spreadsheets, and trying to achieve Excel perfection everywhere. But the more people in your company that at least have a basic familiarisation of the good practices and know the major pitfalls, the better. That’s progress.
What should we remember to do, and to not do?
(this isn’t an exhaustive list remember, and in not particular order)
Comments and Documentation
This is an easy one, but the one most rarely followed. If you’ve got a complex spreadsheet with lots of formulae, put some notes in the spreadsheet itself that explain what is going on. Adding Notes to a cell to explain the reason for the logic, is much more helpful than someone just staring the formulae to try and understand why it is doing what it is. You can also put warnings and guidance in the Notes. e.g. “Remember, if you change this value, you need to check cell X10 still adds up”. Documentation cab also be just putting names against pivots and sets of data, that make it easy to find the data for a chart rather than having to open the chart and work backwards. Often we come across a hidden worksheet that has loads of pivot tables in that are used for charts in the main sheets, but the hidden “pivots” is just a mass of unnamed pivot tables. Putting a label and comment in the cell above the pivot table, that’s easy and helpful.
Structure & Naming
Organise your workbook into sensibly named individual worksheets, there’s really not much to be gained by having one super large worksheet. Even something as simple as organising the worksheets into a logical order in the workbook, left to right or right to left, if there is a flow to how the data or parts of the workbook build up. Whilst you may give thought to the layout the visual parts of a worksheet, the data items that a business user needs to read, you should also think about the layout of formulae, pivots, and other supporting data on a worksheet. Again, group items together that are logically related, and organise them in a sensible flow either from top to bottom or left to right in a worksheet, and as mentioned earlier, don’t forget to put some names and comments and documentation with them. Allow plenty of space between formulae and pivots in the worksheet, they have a habit of getting bigger over time and it is always safer to not have to move thing around to make space.
Label Rows & Columns
It really shouldn’t need saying that you should put names against columns of data, and rows of data if appropriate. If you have a list of sales regions, or list of product categories, put a heading in the cell/row above the list to make it clear what it is. If you use the data in a pivot then obviously the header row will be used for the field names in the pivot, but putting labels on data is good practice to always get into, even if it isn’t being used for pivots.
Keep formulae simple, and readable
The worst formulae are the really big ones, all in one cell, lots of brackets, impossible to read. If they are very hard to understand, its more likely there will be an error when someone tries to modify it, or even someone misunderstands the formula and corrects it thinking that it was wrong, when in fact it was correct. The easy solution to large unreadable formulae is to break them out into several smaller formula in their own cells, and use them like building blocks to get to the final answer. And don’t forget to put some comments in the cells, or labels above them to help understand what is going on in the formulae.
Avoid hardcoded constants in formulae
If you embed constants within formulae, all to often you find out that they aren’t really constants, they are variables. That is, you thought the amount of wastage to be applied to stock was always 5%, but actually it needs to be changed, or need to use different wastage for different product lines. Put the “constant” in a cell on its own, with a nice label, and reference the cell in any formulae. This also helps avoid having multiple copies of that constant used in numerous formulae in the spreadsheet, and when you need to change that wastage % figure to 4%, you know that changing it in one place will be applied everywhere it is used.
Add validation and automated checks
A technique that is rarely used but can be incredibly helpful in large and complex spreadsheets, and doesn’t have to be complicated to do, is to add some checks and balances. This is the idea of having separate calculations that use different logic to the main formulae in your spreadsheet, to validate that your data and main calculations are correct. A very simple example, if you have worked out all regional sales totals by regional manager, and then you are referencing those in another worksheet, have a check calculation on the final worksheet where the values are shown to compare all the regional sales totals with the total sales value from the source data. They should be the same, if not, you’re missing a region somewhere, or your data table hasn’t been extended correctly. Put some conditional formatting in a cell on the main report page, that makes it bright red with yellow text saying “ERROR” when a validation calculation finds a problem.
But most of all, it is good
Most users of Excel only use a tiny fraction of its functionality. Even users that create charts and nice looking management reports, only use a small amount of functionality that they could use to make their reports more readable and more powerful. Whilst Excel is obviously good for the quick and dirty ad-hoc lists and simple charts, it is surprisingly really good at doing quite sophisticated reports, interactive charts, forecasting future trends. See some great examples here of charts in Excel that you might think you would only get in a BI system.
You can do a lot in terms of reporting and data presentations and visualisation in Excel before you need to start to looking at more serious BI and data visualisation tools such as Power BI or Tableau, and even in businesses that do have enterprise BI tools, there is still a place for Excel.