We can use a combination of the IF function and some clever conditional formatting. Let’s jump right in . Here is a sample To Do List.
We can insert a tick icon in the Completed Column to easily see our Completed tasks.
Step 1. Identify Activities That Have The Status Done With The IF Function.
First let’s identify which activities are completed by using the IF Function which is straightforward in it’s syntax as below.
IF(Something is True, then do something, otherwise do something else)
In this example we want to identify the cells in Column C which contain the word “Done”. Where Excel finds “Done” we then display the Value 1. (This value will be used in the second part of this solution where we tackle some conditional formatting).
The formula is easy
We now have identified the cells with Done and flagged them with a 1. Let’s move on!.
Step 2. Create A Tick Symbol Using Conditional Formatting For Completed Tasks.
- Select cells to format -ie. cell in Column B.
- Conditional Formatting – New Rule.
- Format all cells based on their values.
- Format Style – Icon Sets – Tick Show Icon Only
- Select the Green Tick Icon
- Change the setting When Value Is to >=1 and the Type to Number
- Change the remaining two Icons set symbols to No Cell Icon
- Hit Ok.
That’s all that it takes. Your completed tasks are now shown with the Green Tick Icon. You list now will look like this.
Don’t forget to sign up to the Excel at Excel Newsletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.