How to make your spreadsheets easier to read and more attractive using formats and styles. By Liam Bastick, associate director with BPM Analytical Empowerment.
Query
I’d like to make the data in my Excel spreadsheets more attractive and easier to read when I present it to other people. Do you have any tips?
Advice
Take the illustration below. How easy is it to find the key data, or see which cells should be changed to facilitate updated information? How easy are these things in your own spreadsheets? Have you ever noticed that spreadsheets built by colleagues do not look similar to your own?
Two key qualities of a good spreadsheet are consistency and transparency.
Examples of consistency:
- formulae are copied without amendment across rows
- cells with a common purpose (for example, 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, where 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 (for example, totals) can be identified immediately, with their derivation made obvious.
Excel’s ‘Styles’ features can assist with both transparency and consistency.
Formats and styles
These terms 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’s 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.
In Excel 2003 (and earlier) go to Format–> Style (Alt + O + S).
In Excel 2007, go to the Home tab, click the arrow in the bottom right corner and then select ‘New Cell Style…’ (Alt + N):
Either way, the dialog box appears:
Excel’s Style Dialog Box (Excel 2003 illustration)
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. We’ll change the name to ‘Dollar Assumptions” and click the ‘Modify button’ (Excel 2003 and earlier) or the ‘Format’ button (Excel 2007).
The Format Cells dialog box reappears:
- Number: select the Currency category, with zero decimal places and apply the ‘$’ symbol
- Alignment: Horizontal – Right (Indent) with zero indent
- Patterns: select an ‘easy-on-the-eye’ colour such as pale green
- Protection: uncheck the Locked check box (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 (that is, overwrite) these properties, so the ‘Style includes’ check boxes for these two format properties should be unchecked:
Dollar Assumptions:
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, in Excel 2007 you simply select the range and then click on the style in the Styles gallery on the Home tab.
Applying styles in Excel 2003 (and earlier) is just as simple once you have made a customisation.
For pre Excel 2007, you can call up the Style dialog box and then choose the appropriate style and click ‘OK’. This is a little cumbersome. It is easy to incorporate this Style dropdown box onto an existing toolbar and dispense with several of these steps.
By right clicking on any toolbar or using the shortcut (Alt + T + C), call up the Customize dialog box and select the Commands tab, then the Format category.
Customize Dialog Box:
Choose the Style dropdown using the left click on the mouse and drag it onto an existing toolbar of your choice (then close the dialog box).
Style Dropdown Attached to Formatting Toolbar (Example)
Easy!
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 (or Office Menu–>New–>My Templates in Excel 2007) will call up your saved styles.
Styles – before and after
Returning to our original query, which do you prefer?
Before Styles:
After Styles:
Source: CIMA Global Website
while you are presenting the excel sheet, first put your self in the shoes of others and then think how best can you make your presentation good at the same time clear.The currency marks have to be taken proper care.
Hi, interest post. I’ll write you later about few questions!
Good article – plenty of food for thought.
This is a great article. I’m new to blogging but still learning. Thanks for the great resource.
A splendid article! i benefit a lot from it. Thanks a lot for your beautiful and convincing words. i am deeply impressed with this blog. i will recommend it to my friends.