User Problem: How do I used Cell Styles in Excel?
Solution: Gurus of Microsoft Word have known about using styles for a decade. In Excel 2010, Microsoft promotes styles in Excel, adding a drop-down right on the Home tab offering 42 built-in styles as shown in Figure 1 (below).
You can choose which styles you think are appropriate and which are not. In Figure 2, cell A1 has a Title style. Row 3 and column A use the Heading 4 style. Column H and Row 12 use the Calculation style.
Adding New Styles
Most of my worksheets deal with positive numbers. Thus, it is preferable to use a number format of “#,##0” instead of the accounting format. When you use the Comma style, Excel uses the accounting format. This format allows for the possibility of negative numbers in parentheses, and therefore all the positive numbers are not quite right-aligned with the cell as shown in Figure 3. You can add your own new style to a workbook.
First, format a cell with the correct formatting. It might be easiest to go to a blank cell that had not previously been formatted. Type a number and format that cell. To reach the full Format Cells dialog, use the Ctrl+1 shortcut key. On the Number tab, choose the Number category. Specify 0 decimal places and a thousands separator, as shown in Figure 4. On the Alignment tab, specify right-aligned and topaligned.
To create a new style, select the cell with the formatting for that style. Open the Cell Styles drop-down and choose New Style at the bottom of the menu. Give the style a name such as CommaGood. Because this a numeric style, you want to apply the settings from the Number and Alignment tab, but you do not want to change the existing font, color, fill, or borders. Uncheck the boxes for Font, Border, Fill, and Protection (Figure 5). Click OK to create the style.
Your custom styles now appear at the top of the Cell Styles menu. When you apply the CommaGood style, you get the thousands separator without the extra space after the number (Figure 6).
Sharing Styles with Other Workbooks
Styles that you create are available only in the current workbook. It would be better if you could globally make the style available to all workbooks.
There are two approaches that you can use. In the first approach, you create a sample workbook that contains all your favorite custom styles. You can copy those styles to any workbook by following these steps:
1). Open the new workbook and the sample styles workbook.
2). Make the new workbook the active workbook.
3). Open the Cell Styles menu and choose Merge.
4). In the Merge Styles dialog, choose to merge from the sample styles workbook.
Excel will copy the styles to the new workbook.
This approach is admittedly a hassle because you would have to apply the styles to every workbook you ever create. The second approach is more difficult at first, but then will be easier as you create new workbooks. This approach uses a new book template.
Follow these steps to define a book template:
1). Open a blank workbook with a single worksheet.
2). Create new styles as described earlier.
3). Clear the cells that you used to create the styles.
4). Optionally, if you have favorite Page Setup settings, apply any custom margins,
headers, footers, or scaling settings.
5). In Windows Explorer, create a new folder. You might call this folder c:\XLDefault.
6). In Excel, go to File, Options. In the left navigation, choose Advanced. Scroll to the bottom and look for a section called General. There is a setting for At Startup, Open All Files In. Enter the name of the folder from step 5 in that box. Click OK.
7). Use File, Save As. In the Save as Type drop-down, choose Excel Template. Annoyingly, Excel navigates to a different path. Browse back to C:\XLDefault.
8). Save the file with a name of Book.
9). Perform a second Save As to save as a template with the name of Sheet.
Whenever you want to create a new workbook, use the Ctrl+N shortcut. This will load the Book.xltx file as the new blank workbook. All your favorite cell styles will be available. When you click the new worksheet icon, Excel will insert Sheet.xltx as the new sheet. This will ensure that your page setup settings apply to the new worksheet.
This article first appeared in the October 13, 2014, edition of the CFO LearningPro, Excel Edition, newsletter.
CFO contributor Bill Jelen. a.k.a. Mr. Excel, is an Excel MVP and the author of 35 books about Microsoft Excel.