Conditional Formatting is one of the most useful features of Microsoft Excel. And while most people are happy to use it for changing the color of the text in a particular cell, there are endless ways in which it can be extended. Here are a few:
Remember when you had a copy a complicated formula onto 40,000 cells and some of them gave you the dreaded #DIV! error. That did really spoil your worksheet, didn’t it! One way out of this mess is to put an error check – simply add the iserror excel function to the formula, as in “=if(iserror(formula()),””,formula)”. That would take care of the #DIV!, #N/A and other errors. But that also causes the size of worksheet to bloat and slow down the performance. Now if you know that the values from these cells were not going to be used further, you can simply use conditional formatting to hide the errors. How? Well you simple select the desired cell (in our case A1), click Format -> Conditional Formatting on the menu and enter “Formula is” and equal to =iserror(A1). You then press the Format button and on the Fonts tab, select the font color as white (or the one matching your background). Copy the format to all other cells using the Excel Format Painter menu icon and viola !!!! all the errors in the cells disappear (or rather turn white!).
In addition to hiding errors, you can highlight numbers using this trick. Say if you have a series of numbers that represent sales over a period of 10 years and you want to highlight the ones where the sales were higher than say, $100 million, simply click Format -> Conditional Formatting from the menu and enter “Cell Value is” and “greater than” and add “100,000,000”. Now choose the Fonts tab and choose a font size which is slightly larger than the default. Using this, all the cells where the value is greater than 100 million will get highlighted with a font size that is larger than the other cells.
Incell micro charts in Excel
You can use the excel REPT() function to repeat a character. So writing REPT(12,5) in a cell would give you 1212121212. Now if you edit the font style for that cell and change that to say, ‘windings” or something else, you’d see the number 12 being replaced by another symbol. Look around till you find a symbol that you can use to create a bar chart. (Another way is to simple insert a symbol using the ‘Insert’ -> ‘Symbol’ menu option.) I picked up a small rectangle that really stacked up pretty well to create a bar graph. You can pick and choose whichever one you like. Coming back to conditional formatting, you can simply add color or change size by adding a formula. Like in the above examples, you can add “Cell Value is” and “greater than” and “100,000,000” and choose to format the font in red color.
Create a heat map
One of my personal favorites is the heatmap. Conditional formatting allows you to specify upto three different conditions for formatting a cell. If you use the first one for a lower threshold and a ligher color and use successively darker shades for darker color, you can create a heatmap. Simply put “Cell value is” and “greater than” and “100” as the first condition. Format the cell by using a lighter share of grey for this condition. Now add another condition – “Cell Value is” and “greater than” and “1000” and use a slightly darker shade of grey. Finally, enter the third condition as “Cell value is” and “greater than” and say “10000” and use a really dark shade. Copy this format to all other cells using the Excel Format Painter menu icon. And behold, you have a stunning heatmap of unparalleled beauty. This trick gets the numbers to be separated out using colors and allows you to analyze if certain values are concentrated in a particular area and then probably segment them out.
You can download an example of conditional formatting in excel here.