Get the Most Out of Excel’s Conditional Formatting
As a continuation of our post last week, we decided to expand on some great uses from conditional formatting. Highlighting negative values in red and using several built-in conditions is great when you are starting out, but you are not getting nearly enough bang for your buck! Here are three ways to get more out of Excel’s useful conditional formatting tools. Images in this article were taken using Excel 2013 on the Windows 7 OS. The steps apply to Excel 2010-2016.
To follow using our example, download FPS_Apply Conditional Formatting.xlsx
Use Icons Instead of Formatting
When you want a little more visual interest than colored text and backgrounds, you can use conditional formatting to display icon sets.
How to do it:
- Select the column or data range.
- Open the New Formatting Rule dialog box by clicking the Conditional Formatting dropdown button and selecting New Rule. (You can use the Icon Sets option in the dropdown menu if default settings are acceptable.)
- In the Format Style dropdown menu, select Icon Sets.
- Choose the type of icons you want in the Icon Style dropdown.
- In the lower half of the dialog, you can set specific criteria for how each icon is displayed. In this example, we want:
- Donors who gave 5000 and up to show a full yellow star
- Donors who gave 500-5000 to show a half yellow star
- Donors who gave 500 and less to show an empty star
You can display icons based on percentages and custom formulas by changing the Value and Type criteria.
Highlight an entire row or column
When you are looking at data that has many columns, highlighting just one cell might not be enough for you to see the whole picture. Instead, highlight the whole row!
How to do it:
- Select the entire data range (excluding column headings).
- Open the New Formatting Rule dialog box by clicking the Conditional Formatting dropdown button and selecting New Rule.
- Select Use a formula to determine which cells to format.
- Enter the formula that describes your conditions. In this example, we want to highlight all rows of customers from Illinois so our formula will be: =$D2=”Illinois”D2 is the first cell in our data that has the text we are looking for. Because the conditional logic works like copy/paste in Excel as it is applied to each cell in the range, you need to put the $ symbol in front of the column letter to make it absolute. Do not put it in front of the row number as you want the formula to evaluate each new row.
- Hit OK to close the dialog box and apply the formatting.
Click Manage Rules to view and edit the conditional formatting formulas if needed. Notice that the Applies to field includes the range that we selected at the beginning.
Calculate and Highlight Date Data
A frequent task for any business that manages personal information is to calculate ages from birthdates. Using the technique above to highlight an entire row, we can use the following formula in the Format values where the formula is true field to help us see which members on the list are 65 or older:
=INT(YEARFRAC($G2,TODAY()))>65
Learn more about date functions here.
There’s much more that conditional formatting can do for your data than highlight red text. These ideas should get you started and give you some new areas of Excel to explore!
