At a glance
Clever formatting can make a big difference to how numbers are presented, but it is important to not overuse formats. Using bold formatting to emphasise a figure or totals works well, but bolding all the numbers defeats the purpose.
It is also a good idea to limit the number of colours you use on a sheet. Use colour for emphasis and to direct the eye of the user, and opt for pastels over bright colours.
White (empty) space can be used to separate sections, while lines could be effective in separating and surrounding sections.
Matter of styles
Halfway across the Home Ribbon is the Styles section. A style can apply a combination of formats in one or two clicks.
Using styles can speed up formatting and make it easier to apply consistent formats throughout your file. There are numerous built-in, ready to use styles, and you can also create your own.
Using consistent formats throughout your file makes it easier to use and understand. For example, having a consistent cell colour for input cells makes it easier for users to identify the cells they can change.
One advantage of using styles is that it is easy to manage global formatting changes. Let’s say you create a file using the built-in orange Input Style, but then decide to change it to yellow.
When you modify the built-in Input Style from orange to yellow, all calls using that style throughout the file are updated automatically.
Styles are saved with the file. To share custom styles between files, you have a few options.
Copy a cell with a custom style from one file to another. The custom style will also be copied across to the other file. Create a default bank file template.
This allows you to make a standard set of styles available in all new files you create. Use a custom file template. This allows you to create a specific set of styles for a certain type of file. Templates are discussed at the end of the article.
Some style suggestions
Thousands comma separator – It is common to use the thousands (,) separator number format for large numbers. It is also common not to display decimals for these numbers.
Unfortunately, the comma icon in the Number section of the Home Ribbon applies the comma separator with two decimal places. You can amend this by modifying the Comma Style.
Click the drop-down on the Style Gallery, right-click the Comma Style and choose Modify. Click the Format button. Choose a format without the decimal places and click OK, and then click OK again.
Red brackets for negatives – There used to be a default format that displayed negatives in red brackets. You can create this format as a style using a custom number format. Click the drop-down for the Style Gallery and chose New Cell Style.
Enter a name for the style and click the Format button. In the Number tab, click the Custom option (bottom left) and enter the following in the Type: section:
#,##0.00_);[Red](#,##0.00)
This format includes two decimal places. To remove the decimals, use:
#,##0_);[Red](#,##0)
Then click OK, and OK again.
Month and year – The most popular way to display months going across the page in reports, budgets and financial models is to use the mmm-yy format (for example, Jul-21). There is a standard custom number format for this format.
You can create a style to make the format easier to apply. You could also add a border if you commonly use borders for the month.
Amend the input style
One failing of the Input Style format is that it is locked. This means that if you apply protection to the sheet, you can’t change the cell. This doesn’t make sense if you want another file user to make inputs.
You can modify the Input Style by right-clicking the Input Style and choosing Modify and clicking the Format button. In the Protection tab, untick the Locked option and click OK, and OK again.
The built-in Input Style doesn’t include any number formatting. You may want to create specific Input Styles for numbers, percentages, dollars and dates.
Applying a format to input cells helps the user understand what type of entry is required. You can right-click the Input Style and duplicate it to create specific number formats.
Turn off the gridlines
Excel’s gridlines add visual “noise”. Removing them can clean up the on-screen display, printouts and charts.
There are three main types of gridlines in Excel.
Sheet gridlines – These are light grey and help you see the cells. To remove them, press in sequence Alt W V G (don’t hold the keys down). To turn them back on, use the same shortcut. It is common to remove sheet gridlines for reports and dashboards.
Print gridlines – Print guidelines can be changed on the Page Layout Ribbon and only display when a sheet is printed out.
Chart gridlines – A chart often looks much cleaner with the chart gridlines turned off.
Format painter
Once you have created a format, you can easily apply it to other ranges using the Format Painter icon in the Clipboard section (left-hand side) on the Home Ribbon.
First select the range that has the format you want to use. Then, click the Format Painter icon. You can now “paint” the format onto another range once, using the mouse. If you double-click the Format Painter, you can paint the format as many times as you like.
The mouse cursor will include a paint brush icon while you are painting the format. To stop painting, press the Esc key. This doesn’t change the formulas or values in the range.
Conditional formats
In the Styles section of the Home Ribbon is the Conditional Formatting icon, which provides many automated formatting options. You can automate format changes controlled by conditions you create, based on cell values.
These are perfect for exception reporting, where you may want variance cell formats to change colour based on whether they are favourable or unfavourable.
Templates
To capture all these formats or styles for use in future files, you can create a template. Creating a template file is a straightforward process. Press the F12 function key, change the Save As type to one of the template file types and save the document.
This saves all your templates together, so they can be chosen when you create a new file.
Use this technique for customised file templates for specific tasks, for example timesheets or leave forms. To use a template, click the File Ribbon, then click New, and select the Personal option, where you will find your custom templates listed.
Default blank templates
Creating a template for a new sheet or a new file is a similar technique. You need to save the template to the XLSTART folder. You also need to use specific names for the templates.
The XLSTART folder is a system folder created when Excel is installed. The location of the XLSTART folder varies with the version of Excel. A couple of XLSTART locations on my system are shown in Figure 1. The top one is the current version folder.
Saving a template file called Book in the XLSTART folder defines a new workbook. A template called Sheet defines a new worksheet. See Figure 2.
Note: the PERSONAL file in the XLSTART folder relates to personal macros. Any files (or file shortcuts) in the XLSTART folder will be automatically opened when Excel is opened.
In Excel Options – Advanced. you can specify an alterative folder to use instead of XLSTART.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected]
