How to make prettier, more readable Excel spreadsheets

Excel provides a powerful tool for working with financial information, but it can sometimes take a lot of work to find the important data in the sea of numbers on an Excel spreadsheet. This article looks at ways to make data in Excel more attractive and easier to read.

Let’s start with the illustration below. How easy is it to find the key data or see which cells should be changed to facilitate updated information? Have you ever noticed that spreadsheets built by colleagues do not look similar to your own?

Two key qualities of good spreadsheet design are consistency and transparency.

Examples of consistency:

• Formulae are copied without amendment across rows.
• Cells with a common purpose (eg, inputs that are assumptions, such as inflation rates) are formatted similarly.
• Titles are positioned in the same cells in different worksheets.
• Assumption cells (cells containing data that can be changed by the user to affect model outputs) are unlocked, while all other cells are locked so that only these cells can be changed.

Examples of transparency:

• Assumptions are formatted to be instantly recognisable.
• Key outputs (eg, totals) can be identified immediately, with their derivation made obvious.

Excel’s Styles features can assist with both transparency and consistency.

Formats and Styles

The terms Formats and Styles are often used interchangeably, but they are not the same thing. To see this, select any cell in Excel and apply the shortcut keystroke Ctrl+1. This shortcut brings up the Format Cells dialog box:

Excel has six format properties: Number, Alignment, Font, Border, Patterns, and Protection. A style is simply a pre-defined set of these various formats. With a little forethought, these styles can be set up and applied to a worksheet cell or range very easily.

Creating your own styles is straightforward with Excel’s Style dialog box: Go to the Home tab, click Cell Styles in the Styles group and then select New Cell Style… (the keyboard shortcut for this is Alt+H+J+N):

Example

Let’s create an assumption format for entering data in dollars. First, select a cell or range of cells. Then call up the above dialog box. The dropdown box (highlighted above) can be edited. Change the name to “Dollar Assumptions” and click the Format button, which makes the Format Cells dialog box reappear. Next, do the following:

• Number: Select the Currency category, with zero decimal places, and apply the “$” symbol.
• Alignment: Set Horizontal to Right (Indent) with zero indent.
• Fill: Change Pattern Colour to an “easy-on-the-eye” colour such as pale green.
• Protection: Uncheck the box next to Locked (this allows the cell to be changed in a protected worksheet).

Click OK to return to the Style dialog box.

Note that no formats have been ascribed for Font or Border in this example. We don’t want the style to control (ie, overwrite) these properties, so the Style Includes boxes for these two format properties should be unchecked, as shown here:

By clicking OK or Add, the cell or range has now been formatted with the “Dollar Assumptions” style. Now that this style has been added, simply select the range and then click on the style in the Styles gallery on the Home tab.

Updating and retaining styles

The difference between Formats and Styles becomes obvious when you realise you want to change (update) a style. Just select one of the cells that the style is attached to and call up the Style dialog box in the usual way, modifying the style as required. Click OK when finished. Note that every cell in the open workbook that uses this style has automatically updated. Once you start using styles, you will never look back!

You will only want to set up styles once. When you’re finished, simply save the file as a template using File-->Save As (you may wish to delete or remove formatted cells first so that you have a blank workbook). Using File-->New will call up your saved styles.

Styles: Before and after

Which do you prefer?

— Liam Bastick is director of SumProduct, a global consultancy specialising in Excel training. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.