Content Summary
Question
Is there an easy way to achieve consistent formats throughout a file?
Answer
The easiest way to achieve consistent formatting is to use Styles. Styles are shown in the middle of the Home ribbon, see Figure 1.
Using Styles to apply formatting to cells and ranges ensures they are consistent throughout the file. One of the biggest advantages is that if you need to change the format of a Style (for example, your boss wants to use a yellow fill colour for input cells instead of orange), you simply amend the Style and all the cells formatted with that Style will automatically change throughout the whole file.
As Excel is part of the Microsoft Office suite, Styles can also be used in Word and PowerPoint to create consistent formatting.
There are many built-in Styles available in Excel. Click the drop-down at the bottom right of the list in Figure 1 to see the listing as shown in Figure 2
To preview a Style, select a range and then click the icon at the bottom right corner to see all the Styles. Then hover the mouse icon over a Style and Excel will preview it. Click it to apply the Style.
All of these Styles can be amended. Unfortunately the amendment only applies to the current file. If you want to create a standard set of Styles that apply to all the new files, then you will need to create a template that has the new Styles and then use that template to create your new files.
To amend a Style, right-click the Style name and choose Modify, see Figure 3.
The Style dialogue box is displayed, which has a listing of the current formats for that Style, see Figure 4.
You can click the Format button to amend the formats for that Style. You can use most of the standard formats with the exception of some border formats.
You can create a new Style from scratch by using the New Cell Style option seen at the bottom of the image in Figure 2.
Input cells
It is best practice in Excel to use a consistent cell fill colour throughout your file for all input cells. This makes it easier for the user to determine which cells require input.
The Input Style can be used for your input cells. Notice that it has protection turned off: see the last option in Figure 4, which means cells can be updated if the sheet is protected.
Typically you will need to create separate Input Styles for your various input cells because the formats for the input cells usually vary between numbers, dollars, dollars-and-cents, percentages and text. You would create a separate Input Style for each of these types of input.
You can see in Figure 3 that there is a Duplicate option when you right-click the Style. This allows you to create a copy of that Style. You can then modify part of the Style to create a new one. This is ideal when creating multiple Input Styles.
You could duplicate and name Styles such as Input_DollarsCents, Input_Percent and Input_No_Decimals to handle different types of input cells for your spreadsheet.
The Merge Styles option seen at the bottom of Figure 2 allows you to import Styles from one open workbook to another. You need to be in the destination workbook (the one you want to import the Styles to) to use the Merge Styles option.
WARNING: I have found the Merge Styles feature to be unstable. Make sure you save the file before using Merge Styles, and then save the file with a new version number after using Merge Styles. This means you can return to the older version if there are issues. (The F12 function key is a shortcut for Save As.)
Another Styles Issue
If you copy a sheet from one file to another you can bring extra custom Styles with the sheet. This can expand the number of custom Styles listed until you get multiple custom Styles like those shown in Figure 5.
Having so many custom Styles can increase the file loading time. If this happens one solution is to delete all the custom Styles.
Unfortunately this is a fairly tedious and repetitive task, but it is ideal for a macro to perform.
I have created a macro that removes all the custom Styles after confirming the deletion with the user. The companion Excel file contains this macro. The companion video will demonstrate its use.
NOTE: Cells formatted with a custom Style will revert to the default format if the custom Style is removed.
WARNING: Macros cannot be undone and you should save the file before running the macro.
Make it easier to see where data belongs – or is missing – by setting up and using consistent Styles throughout your Excel files.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to various organisations. Questions can be sent to [email protected]
