How To Write A Checkmark Symbol Formula in Excel
Incorporating Checkmark Symbols Into Your Spreadsheets
Symbols are great to incorporate into your Excel spreadsheets as they often can add fantastic visuals that help explain your data. One of the most popular symbols to use on spreadsheets is the checkmark symbol. There are quite a few ways to introduce checkmark symbols into your spreadsheet, but in this article, we’ll cover how to utilize Excel formulas to make your checkmark symbols appear and disappear based on values in your spreadsheet data.
For our article example, we’ll stick with a monthly task list that allows the user to check-off items as they are completed. The final result will look something like this:
Types of Checkmark & X Symbols You Can Insert
Using Unicode References
Symbols are most often referenced in spreadsheets using Unicode (a numerical code that represents a character of text). There are a handful of checkmark and X symbols that are available. The Symbol and their Unicode are shown in the below table:
The Excel function UNICHAR() can be used to plug any Unicode in and output the corresponding symbol. We will be relying on this function heavily to control which symbols are displayed in our Excel spreadsheet.
The Unicodes I personally prefer to use in my spreadsheets are:
- =UNICHAR(10004) for Checkmark symbols
- =UNICHAR(10006) for X symbols
Using Insert Symbol Dialog Box
If you prefer not to utilize an Excel function to retrieve your checkmark symbol, you can alternatively grab the symbol character in its text format.
You can do this by navigating to Excel’s Insert tab and clicking the Symbol button on the very far right of the ribbon menu.
The Symbols dialog box will open and allow you to browse through thousands of characters. I recommend changing the font dropdown to Segoe UI Symbol as this font has a great variety of symbols. Most of the checkmark symbols are going to reside within the Dingbats subset.
After you have selected the icon you want, click the Insert button and it will drop that icon into the cell that you currently have selected and you can close out of the Symbol dialog box.
If you would like to learn more about inserting symbols or utilizing icon fonts in Excel, you should check out some other articles I’ve written on the topic:
- The Amazing Hidden Icon/Symbol Font Inside Excel (Not Wingdings!)
- Wingdings & Webdings Font Icon Character Map (Printable Cheat Sheet)
Write Formulas With Checkmark And X Icons
Using An IF Function
Probably, the most common way you will logically toggle symbols on and off will be using an IF() function. Sticking with our To Do list example, we will want to display a checkmark if a completion date is entered and display an “X” if a date has not been entered.
Said another way, IF the date cell is not blank, output a checkmark, ELSE, output an X.
EXAMPLE #1:
As you can see from the above example, we can implement this logic by creating a simple IF function that toggles between our two preferred Unicode characters.
=IF(D5<>””,UNICHAR(10004), UNICHAR(10006))
EXAMPLE #2:
If you prefer to embed the symbol directly into the function, you can insert the symbols or copy them from a cell where they already exist and paste them directly into the Formula Bar.
=IF(D5<>"","✔","✖")Using A COUNTIFS Function
You can also tally or count your checkmark symbols using a COUNTIFS function. In my Task List example, I created a legend that (1) lets the user know what the checkmark and X icons represent and (2) reports out how many tasks have been completed and how many are outstanding.
You can either make your COUNTIFS function reference a cell with the checkmark symbol or insert the checkmark icon directly into the spreadsheet formula.
=COUNTIFS(B:B,"✔") & " Completed"Add Color With Conditional Formatting
Quite often with checkmark/x formulas, I like to have corresponding colors of green and red. You can easily get this effect by setting up conditional formatting rules that alternate spending on the symbol present in the cell.
If the layout allows for it, I will typically set the entire column’s font color (in this case Column B:B) to Red and use a single Conditional Formatting rule to override the color with green if a checkmark icon is present.
My preferred color codes are:
- RED – RGB (224, 76, 65) or #E04C41
- GREEN – RGB(61,177,130) or #3DB182
Similar to the other Excel formulas we covered in this tutorial, you can either use the UNICHAR function or paste the icon character directly into the formula for the Conditional Formatting rule.
Download Example Excel File
If you would like to get a copy of the Excel file I used throughout this article (including the graphic below), feel free to directly download the file by clicking the download button below.
I Hope This Helped!
Hopefully, I was able to explain how you can incorporate checkmark and X symbols into your Excel formulas. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.