Hi and welcome to my second hub on how best to use Conditional formatting in Excel. Conditional formatting allows you to give your spreadsheets more visual impact or appeal. You can highlight data that a user is interested in, or make parts of your spreadsheet stand out.
In today’s hub, I will be looking at enhancing an Excel to do list created using Check boxes and Combo boxes with Conditional Formatting. We will be using both to create to do lists and using colours and icons to indicate whether each item on our list is complete or not.
To create Check boxes and Combo boxes for your own list, you can find detailed instructions on my hubs below:
- To learn more about using, creating and configuring Check boxes, my hub can be found here:
- To find out more about Combo boxes, I have a hub going into much more detail here:
In my first hub on Conditional Formatting, I discussed using Icon Sets and also using formulas to determine which cells should be formatted. I also discussed how to ensure that the rules you create are not overwritten. Than hub can be found here:
Using Conditional Formatting with Check boxes in Excel 2007 and Excel 2010 to give them extra visual impact
Check boxes allow you to create lists such as mine below and to indicate if individual items are completed by checking their associated boxes.
Before we go onto adding the additional formatting, a few points about Check boxes:
- Each check box is linked to a cell in Excel, in my example each Check box is linked to the cell in column I next to it (I 10 to I 13 in my example)
- The linked cell will show whether the box is ticked (True) or not (False) as shown below.
We will use the True / False data in column I for our formatting.
Using Icon Sets with Check Boxes in Excel 2007 and Excel 2010
The first formatting we will use is to add icon sets to our spreadsheet. In this example, we will use the tick and cross icon set to indicate whether a task is complete or not. To begin:
- For A10, enter in =-H10 to link it to the linked cell for the check box in that row and then do the same for each subsequent cell in the column (A11 =-H11 and so on)
- Select the cells to the left of your list (in my example column A)
- Click on the Conditional Formatting button in the Styles group on the Home tab
- Choose New Rule
- Next, select Format all cells based on their values
- Under Format Style pick Icon Sets
- Configure the Display each icon according to these rules and configure it to be the same as the figure below
- Finally, choose the Icon Style that you prefer (I like 3 Symbols (Uncircled))
- Click OK
Note: the ! is not used, but Excel provided us with 3 and 4 icon sets and no 2 icon sets so we had to improvise!
You will be back at the Conditional Formatting Rules Manager which will look like the below.
Your list will now look like mine below
Using Conditional Formatting with formulas to give Check boxes visual impact in Excel 2007 and Excel 2010
As well as Icon Sets, you can use formulas with Conditional Formatting to further illustrate the status of the items in your checklist. As we saw above, a Check box returns a True if the box is ticked and a False if it is not. We can use a formula to enable us to use this output.
We begin as we began above by:
- selecting the cells we want to format and then clicking on the Conditional Formatting button and selecting New Rule
- This time, we select Use a formula to determine which cells to format
- The formula I will use is =H10=FALSE
- Next, click the Format button and select the Fill tab
- I then choose to fill the cell with red
- I repeat the above for the formula =H10=TRUE
- Now you can see that our list now clearly illustrates at a glance which items in our list are complete (green) and which are not (red).
Conditional Formatting for Combo boxes in Excel 2007 and Excel 2010
The main difference between Combo and Check boxes when using their output is that a Check Box returns True or False, but a Combo box returns a number.
The number a Combo box returns corresponds to the position of the item you selected on the list. In our example, Yes is 1 and No is 2. I have illustrated the difference below (the Combo boxes are linked to cells in column G and the Check boxes to cells in column H)
In this example, if Yes is selected, the list item will be crossed out and green, for No, it will be crossed out and red. To achieve this:
- Select the cell containing your first list item and click the Conditional Formatting button and select Create a new rule.
- Choose Use a formula to determine which cells to format
- In the Format values where this formula is true field I enter =G3=1
- Click Format and then Font
- Select Strikethrough and green as the text Color
- Create another new rule for No and do exactly the same as above except using the formula =G3=2 in your list in the formula above and select red for the text colour
- Once you have created these rules you should end up with something similar to the rules below.
Conditional formatting allows you to make your spreadsheets more visually appealing and also to highlight values or trends. In this hub, we used it to highlight items on to do lists. These lists were created with either Check boxes or Combo boxes and the highlighting changes automatically depending on whether an item is marked complete or not.
We used Icon Sets as well as changing both the font and the cell fill colour using Conditional Formatting in this hub.
I hope that you found this hub useful and informative. Please feel free to leave any comments you may have below and thanks for reading!