When you use a formula to apply conditional formatting, it can be tricky to set the formula up correctly. The dialog used to create and edit the formula doesn’t provide all the nice features that the formula bar does. You can’t easy check references, you don’t get function screen tips, and the window is too small. An easy workaround is to test the formula on the worksheet directly. For example, if we want to highlight odd numbers in this set of random values, we can just add the formula in an empty area, and point back at the values, starting with the first value. If you want, you can copy the formula across to cover the same area as the data. This gives you a result for each cell in the data, and helps you visualize what you’ll get with conditional formatting. By working with the formulas on the worksheet, you have full access to the formula bar, screen tips, reference highlighting, and a larger work space. Once you have things working, just copy the formula that will correspond to the active cell, and use it for the rule. It should work straight-away. Now let’s try it with a more complicated example. Here we have some random dates. We want to be able to highlight dates in any given month. We have an input cell at the top, designed to hold month name, abbreviated to three letters. For the formula, we can use the =TEXT function and “mmm” for the date format. That gives us the month. Now we just need to compare each result against the input cell. The result is a set of boolean values, perfect for CF. Finally, we need to create the rule. I’ll copy formula in I4, then create the new rule. Notice that I copied the formula that refers to the active cell in the selection, which is B4. When I save the rule, we have a working conditional forma I can enter any month and see dates in that month highlighted. You can use this approach whenever you need to check a formula before you create a new conditional format.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
