×

How To Write A Checkmark Symbol Formula in Excel

By Chris Newman •  Updated: 08/01/22 •  6 min read
How to add check symbols to Excel Spreadsheet cells.

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:

Excel example file of a Monthly Task List that includes Checkmarks and X's.

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:

Unicode table for using Checkmarks and X's within Excel.

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 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.

Inserting a symbol into Excel spreadsheet.

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:


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 by 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:

Excel IF function example file that uses checkmarks

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:

Excel IF function example with checkmarks.

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 how many tasks have been completed and how many are outstanding.

Excel Countifs Formula with symbols example.

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
Excel conditional formatting rule to use checkmarks.

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.

Excel sample file with checkmark symbols and a task list.

I Hope This Helped!

Hopefully, I was able to explain how you can incorporate checkmarks 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.

Keep Learning

Chris Newman

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.