I have the visual sense of a blind goat, but over the years I have picked up a number of Excel design tips which I apply to any spreadsheet that someone else has to look at.
This post sets out ten areas which you can look at to make your spreadsheets cleaner and more professional.
I’ve written this post on the assumption that you are reading spreadsheets online, but most of these Excel design tips also work for printed documents.
1. Turn off Gridlines
The first tip is the easiest – turn off gridlines. Gridlines are the lines marking the rows and columns.
You find this option in the View ribbon, and it’s a checkbox so you can toggle them on and off, which is helpful if you need them to design your spreadsheet. I tend to work without them almost from the start, because it helps me see how the document will look if printed or sent to PDF.
2. Change your Font
The second tip is also simple – change the font from the default. Calibri is nice and readable but the second you change to something else, it makes a big design impact, just because you’re not using the default.
You’ll want to see how your chosen font behaves at different sizes, and alignments, bordered and bolded, before you settle on it. (You can change everything on a sheet by pressing CTRL+A to select everything before making a font change). A plain san-serif will work best with numbers. There’s a reason why Excel used to default to Arial and now defaults to Calibri. But there are lots of other options out there. I use Mada, for example.
Less is more – I never use more than one font in a workbook. I’ve heard that some daredevils have been known to use two, in carefully controlled conditions.
Bold and italic are good font modifiers for excel; underline isn’t, as it can get lost with gridlines and cell borders.
3. Number format
The default number format for Excel (at least in the UK/US editions) is not the best for displaying numbers. Nor is the default accounting, or currency, although they are slightly better.
This table shows a number in “General” format, then the default “Number” format. The three custom columns have all been created using “Custom format”.
Custom formatting can be a little fiddly, so I usually start with one of the more extended list of options and then edit it. I also have a library of formats that I use again and again. Custom 1 is my current favourite, and the syntax is for this one is #,##0_);[Red](#,##0);-?
(On a side note – there are loads of cool things you can do with custom formatting, which I previously thought you would have to use conditional formatting for. For example, you can make zeros appear as blank cells. I learned this and many other great tips from Rene Martin and he blogs about number formats (and other topics) here.)
As a bare minimum;
- use the 1,000 separator
- use the largest degree of rounding possible. That is, if your numbers are in the millions, you probably only need to display to the nearest thousand.
By default, Excel aligns text to the left and numbers to the right, which means loads of spreadsheets just don’t line up right.
As well as using align text to correct this, you can also use a change in alignment as a subtle way to show that the last column represents something different to the previous columns.
It’s even better if you combine it with…
Numbers are hard to take in en masse. Using spacing effectively can help distinguish some columns from others:
I also always include a blank row at the top and a blank column to the left.
People often use borders, for example with tables of numbers. However, lines on their own do not necessarily make tables easier to read. Less is definitely more from a design perspective, too.
To be honest, I prefer the version without borders at all but with spacing instead to separate out the calculation column. (See Excel Design Tip 5, above)
7. Non data ink / charts
The last point is a practical example of what the great statistician Edward Tufte called the “data-ink ratio”. Whenever you are thinking about the visual presentation of numbers, graphs etc you should minimise “non-data ink”. (See also; turning off gridlines, the first and best Excel design tip.)
When you draw a chart in Excel, Microsoft’s defaults often contain non-data ink – axis lines and markers, for example.
Even removing a couple of objects makes the chart stand out more.
Depending on what point you are trying to illustrate, you might add a few data labels for the key bits of data, which will give your audience a sense of scale without cluttering the page.
8. Colour use and colour palette
Use colour sparingly. Remember not everyone sees colour the same way so try not to use colour to distinguish between different types of data. I tend to use blocks of colour as headings, and that’s it, other than graphs.
However, speaking of graphs, if you are trying to produce a consistent look and feel to your spreadsheets, or just want them to look different from the default, it is worth taking the time to set a theme. Within your custom theme, you can set the colours you want to use.
This is useful because it carries across into charts, tables and other visual features within Excel. It also gives you a series of monochromatic shades based on the colours you select, which may be a better option for your chart anyway.
9. Using Excel Tables
If you’re a casual user of Excel, you might think that Excel Tables were about formatting. After all, the function (on the “Home” tab of the ribbon) is labelled “Format asTable”.
Excel tables do way more than just assign a choice of formats to your table, and at some point I’ll write a post on these because they are still woefully underused.
However, if you’ve got to the point where you are using them, but are wondering if you can make them more consistent with the principles of non-data ink, why yes, you can. For a start, you can pick a completely blank table style:
If you want to have a bit of colour but otherwise keep it minimal, within “table design” there are options to remove banding (the alternate colours that lots of tables have) and to get rid of the filter buttons.
10. Taking this to the next level
Some of these Excel design tips can be saved into an Excel template, so once you’ve got a combination you like, you don’t have to spend time customising each spreadsheet. You can also set Cell Styles, which is a good way to ensure consistency within your workbooks or within an organisation. You can also save custom chart templates.
Ultimately, to make your spreadsheets look really good, you should consult with a proper designer, or at least apply consistent brand guidelines.
I dream of a world where company branding exercises remember that Excel exists, and that as much time is given to setting up cell styles and determining rules for row heights as designing a PowerPoint template.
In the mean time, I hope my tips will be of use.