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:
Select the cells in the table’s Completed column.
Type in the formula
=IF(COUNTA(B5:E5)=4,"P","") and 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.
• Select the data range of the table, press
Alt+OD for Conditional Formatting, and click
• 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.
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.