Tom’s Tutorials For Excel: Checkmarking a Cell With Standard and Conditional Formatting

Tom’s Tutorials For Excel: Checkmarking a Cell With Standard and Conditional Formatting

Here’s how you can automatically show a checkmark in a cell, using standard formatting for a font type, and Conditional Formatting to identify when the checkmark exists. There is no programming code involved; it’s all native Excel formatting.

In this example based on the following picture, a company keeps an ongoing list of projects, with a set of tasks that each project might need to complete along the way in order to claim the entire project as being complete. A completed project will have a checkmark placed in the table’s Completed column, and the entire row of the table will be shaded green.



Here’s how it’s done:

Step 1
Select the cells in the table’s Completed column.
Type in the formula =IF(COUNTA(B5:E5)=4,"P","") and press Ctrl+Enter.



Step 2
Press Alt+OE and format the selection for Font Type of Windings2 and Bold.
The upper case letter P looks like a checkmark when it is formatted as Wingdings2.



Step 3
• Select the data range of the table, press Alt+OD for Conditional Formatting, and click New Rule.
• The formula rule is =LEN($F5)>0. Note the absolute reference to column F, which is the Completed column in the table where the checkmark would be.
• Click the Format button.



Step 4
On the Fill tab, select the color you want to shade the rows where a checkmark exists in the completed column. Green was selected for this example, and then click OK.



One last note, it’s a good idea to format for Locked and Hidden the formula cells in the completed column, and to protect the worksheet.