Project Planning – Bring Attention to Overdue Tasks
In this example, I show you how you can use symbols and traffic light type of color coding to bring attention to tasks that are past your deadline. This way you can visually indicate which tasks are complete and which are still pending. I use a combination of symbols and Excel’s conditional formatting feature to achieve the below:
Setting up the data
The example shows a project time plan with tasks, start date and end date. The end date is calculated by the WORKDAY() function according to the number of working days specified. (The link to how to use the WORKDAY() function is found here ).
Rather than just seeing the end date of each task, using symbols can help you see their individual status visually. Since today’s date will be used as reference for overdue tasks, use the TODAY() function to automatically insert today’s date every time the workbook is opened.
Cell A1 = TODAY()
Add a column that would serve as an indication if you have completed the task. Label that as Done (Y), where you can input “Y” if a task has been completed.
How to use symbols in Excel
While you can use the symbols that are available in Conditional Formatting Icon Sets, you can also use your own personalized icons.
To insert a symbol of your choice:
- Under the Insert tab, go to Symbols.
- Select a Font. There are a lot of symbols available for Arial, but in this example, let’s take symbols from Wingdings 2.
- Click on a symbol you want to use, then click Insert. Notice that it inserts that icon in the selected cell on your spreadsheet.
- Close the Symbol window.
When you click on the cell containing the inserted symbol, for example the check mark, you will see on the formula bar that it actually puts a letter “P” which is formatted in Wingdings 2 font to look like a check mark. If you change the font of this cell back to the standard font, it would show you the letter P. If you are using symbols that are from a non-standard font such as Wingdings 2, you have to ensure that the cells are formatted with that particular font.
Select two other symbols for tasks that are pending (clock) and delayed tasks (phone).
To make sure that the symbols are displayed accordingly, use the character codes of the symbols. Go to the Insert Symbol window to find the character code of each symbol. After clicking on the symbol, you can see its corresponding Character code at the bottom, which is 80 for the check mark. The corresponding character codes for the symbols are shown below:
Note down these character codes since these will be used in the conditional formulas later on. The formula to use to insert the symbols correctly is:
=CHAR(Character Code)Using the nested IF() function
We want Excel to show us these three different symbols based on their status. Since we have three conditions, we can use a nested IF() formula. As a first logical test, a completed task (indicated by a Y under the Done column) should display a check mark. Following the IF() syntax, the formula is written as
Cell F1 =IF(E3="Y",CHAR(80)..The second condition is that if the end date is less than today’s date, we want to get the phone symbol which symbolizes that it is a delayed task. For this we can add a second IF:
= IF(D3<$A$1, CHAR(39)…Where A1 = today’s date
(This cell reference needs to be fixed.
A fast way to do that is by clicking on F4 button)
Lastly, if it doesn’t satisfy all these conditions because the deadline for the task is in the future, it , should display a clock symbol. The final formula becomes:
Cell F1 =IF(E4=”Y”,CHAR(80),IF(D4<$A$1,CHAR(39),CHAR(137)))If the column is still formatted in the standard Arial font, you will see different characters displayed instead of the symbols. To fix this, highlight the column containing the symbols and change the font to the correct one, which is Wingdings 2.
Formatting the symbols
You can further color code the column containing the symbols for better visualization;
This seems to require 3 conditional formatting rules, but you can save on 1 formatting by pre-formatting the cells for one of the conditions. For example, format them to a gray color by default since that’s how we want the cells containing the last condition (clock) to look like, and what’s left is to create rules for the other two conditions.
Highlight the column that contains the symbols. Go to Home > Conditional Formatting > New Rule. Use the option to format only cells that contain a cell value equal to the check mark symbol. For this, type out =CHAR(80) to refer to the corresponding symbol.
Click on Format. Specify the color and font style formatting. For the check mark, format the Color to green and change the Font Style to Bold. Click on OK.
Add another rule to format cells containing the phone symbol to the color red with a bold font style.
If you want to further polish the overall look of the table, merge the column heading Done (Y) at E2 and F2 and resize the column width accordingly. Add a bottom border to it and you’re all set.
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
