I add my own answer, so I will find it here, and won't have to reinvent it next time. While conditions in number formats can be used to visually flag numbers, you can’t sort or filter based on colors introduced in number format codes.There are many things to get wrong about freezing panes. When trying this test with the conditional number formatting techniques, they don’t cause Excel to ask for a save, hence they aren’t volatile. If Excel asks you to save, then you know the worksheet is volatile. Reopen the workbook and close it again without making changes. There’s a simple process to test if a worksheet is volatile: Save and close the workbook.
Figure 6 shows the formatting codes used for the various columns in Figure 1. Then copy the character from the formula bar in order to paste in the custom number format box. To access these last two characters, enter =CHAR(161) or =CHAR(240), respectively, in a blank cell. Column F uses an upside down exclamation point. Column E in Figure 1 uses “Low! ”0 in the format to display the word Low! followed by a space before the number. The optional final zone is used to specify a number format for any cells not meeting the first two conditions.įor example, column B in Figure 1 uses [ to fill the cell with greater than (>) signs.Īny quoted text in the number format will be displayed next to the number. Instead of automatically applying the zones to positive, negative, and zero, you can specify a condition in square brackets to be used for zones 1 and 2. For example, the built-in Accounting number format uses the following code: _($* #,#0.00_) _($* (#,#0.00) _($* (See Figure 3.) The zones are used to specify a different format for positive, negative, zero, and text values.
The custom number formatting code is usually composed of four zones that are separated by semicolons. Click OK to close the Format Cells dialog.In the Type box, enter a custom number formatting code (discussed below).Press Ctrl+1 to open the Format Cells dialog.To apply custom number formatting, you follow these steps:
While these methods aren’t as elegant as the red flag used in column A, they allow you to flag certain values without making every cell volatile. Because these flags use conditional formatting, they will be reevaluated during every worksheet calculation.Ĭolumns B through G of Figure 1 show six possible ways (out of hundreds) to flag cells using custom number formatting. The result is a red flag appearing next to any values below 90. Click OK to close the Edit Formatting Rule dialog, and then click OK to close the Conditional Formatting Rules Manager.Change the Value to any number larger than 90 in the first row and to 90 in the second row. Change the Type to Number for the first two rows. In the Edit Formatting Rule dialog, change the Icon to “No Cell Icon” for the first two rows.Select Home Conditional Formatting, Manage Rules, Edit Rule.
Go to Home, Conditional Formatting, Icon Sets, 3 Flags.
To have only the red flag appear on values less than 90, follow these steps: While the flags look great, they are volatile. Any time that a value falls below 90, you want to highlight the cell.Ĭolumn A contains an icon set from the conditional formatting menu. The worksheet speed instantly improved.įigure 1 shows six different ways to flag cells. Through a group effort, we determined that replacing the traditional conditional formatting with an old custom number format created a way to flag the same cells without causing all 600,000 cells to be volatile. I was presenting at the PricewaterhouseCoopers (PwC) offices in Melbourne, Australia, for the Excel Summit South when someone in the audience had such a spreadsheet.