Excel: Display a specific symbol based on cell value
In daily Excel operations, presenting information visually often improves communication and data analysis efficiency. While conditional formatting allows you to highlight cells with color based on certain criteria, there are many situations where you may want specific symbols—such as check marks, thumbs up/down, or custom icons—to appear in cells according to their values. For instance, marking pass/fail conditions, progress status, or performance indicators with distinctive symbols can make spreadsheets clearer and more actionable, as demonstrated in the screenshot below:
Note: The procedures outlined in this tutorial are tested with Excel 2021. Some features, dialog layouts, or symbol compatibility may differ slightly depending on your Excel version. For older versions, interface wording or symbol support may vary, so refer to your version's help documentation if needed.
Table of Contents
- Display symbol based on cell value
- Excel Formula - Use Unicode symbols directly (check mark / cross mark compatibility)
- Custom Number Formatting - Symbol display without formulas
Display symbol based on cell value
Before adding symbols based on cell values, it's important to understand which font style and character code your chosen symbol uses. This detail ensures that characters display correctly rather than showing as unrecognizable glyphs. Take, for example, the thumb up and thumb down symbols: both are included in the Wingdings font style, with character codes 67 for thumbs up and 68 for thumbs down, respectively.
If you wish to use icons or symbols other than standard ones, you should first identify the font and character code using these steps:
1. Go to Insert > Symbol in the Excel ribbon. Within the Symbol dialog that appears, pick your desired font from the Font drop-down list. Then scroll through the list to choose the symbol you want, and note its Character code at the bottom right corner. This code is essential for referencing the symbol via formulas.
Tip: For non-English characters or special Unicode symbols, select fonts like Arial Unicode MS or Segoe UI Symbol, which offer broader compatibility.
 |  |
2. After you have determined your target symbol's font and character code, select the cell or column where you'd like symbols to display. Change the font style of that cell or column to the identified font by clicking Home > Font on the ribbon. This ensures correct symbol rendering, and maintains uniform formatting for subsequent formulas and data entries.
3. Enter the formula that determines which symbol to display based on the cell's value. For example, to display a thumb up when the value in cell B2 exceeds 75, and thumb down otherwise, use the formula below in your target cell (e.g., C2):
=IF(B2>75,CHAR(67),CHAR(68))
Your selected symbols should now appear in the cells according to your defined logic. If you prefer not to use font-specific symbols (for improved device compatibility or online usage), you can also use Unicode-based checkmarks, crosses, or other icons. See the alternative solutions below in this tutorial for details.
To further enhance visibility or signal meaning, you might wish to use color or bold formatting for the displayed symbols. Excel lets you apply these features through Conditional Formatting:
4. Highlight the formula cells you want to format. Navigate to Home > Conditional Formatting > New Rule....
5. In the New Formatting Rule dialog, select Use a formula to determine which cells to format. Enter a formula matching your main symbol logic—such as =B2>75—in the textbox, then click Format... under the Font tab to select a color or style. Click OK twice to apply the formatting.
6. Repeat the previous conditional formatting steps for alternate symbols or logic—for example, use =B2<=75 for the thumbs down symbol. Modify each format rule's color or style as required.
Adjust the font size and use bold typeface to make symbols more visually prominent, especially if you intend your spreadsheet to serve as a dashboard or review sheet.
Common troubleshooting tips: If your symbols are not displaying properly, confirm that the correct font is applied to the target cells. Some fonts only support a limited character set, so mismatched font selection may result in blank squares or unusual glyphs. Also, check for formula errors or incorrect references when copying formulas to new ranges. If symbols disappear after saving and reopening your file, double-check for font compatibility on your device or with recipients.
When working with shared workbooks or cross-platform Excel usage (such as on mobile or web), using common Unicode symbols may offer better compatibility and visual consistency. See the additional approaches below to address different scenarios and preferences.
Excel Formula - Use Unicode symbols directly (check mark / cross mark compatibility)
Sometimes, you may want to display symbols like a check mark (✓) or cross mark (✗) directly in your worksheet, without needing to change font styles or look up character codes. Unicode symbols are natively supported in modern Excel versions and are widely recognized across devices and platforms, making them useful for shared files, web apps, and mobile Excel.
Applicable scenarios: Use Unicode symbols for pass/fail indicators, task completion status, or any situation where universal compatibility across platforms is needed.
Pros: No need to adjust fonts. Symbols appear the same on different devices. Easy to type and copy.
Cons: Some older Excel versions or rare fonts may not fully support certain Unicode symbols. Always test on your target device.
1. Enter the following formula in the target cell (for example, C2):
=IF(B2>75,"✓","✗")2. Press Enter to confirm. To apply the results to multiple rows, use the fill handle to drag the formula down or copy and paste as needed. Each row will update dynamically according to the corresponding value in column B.
Tip: You can replace "✓" and "✗" with any Unicode symbol you prefer. Most common symbols (arrows, dots, stars) are supported.
Custom Number Formatting - Symbol display without formulas
To display symbols in cells based strictly on their own values, you can also use Excel's custom number formatting feature. This method visually assigns a symbol to a cell depending on whether it meets a specific criteria (such as positive, negative, zero), without a formula. For example, you can format a cell to show a check mark for positive numbers and a cross mark for negatives.
Applicable scenarios: Use this when you want quick symbol representation without modifying cell values or using formulas. Good for dashboards and visually tracking performance at a glance.
Pros: Doesn't require extra columns or formulas. The actual value stays unchanged, only the display is modified. Quick to apply to a range.
Cons: Applies only for numeric values (or basic logical conditions). Formatting options are limited by Excel format syntax.
Steps:
1. Select the cells you wish to format (e.g., C2:C10).
2. Right-click the selection and choose Format Cells, then switch to the Number tab and choose Custom.
3. In the Type field, enter a custom format. For example:
[Green]"✓";[Red]"✗";"—"This format displays a green check mark for positive values, a red cross mark for negative values, and an em dash for zero. Adjust the symbol and colors according to your needs.
4. Click OK to confirm. The chosen symbol will be displayed based on each cell's value, directly replacing the usual number display.
Note: Custom number formatting doesn't change the underlying cell value—it simply alters the visual presentation in the workbook.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Display Cell Value In A Graph Object (Shape, Textbox, Picture, Chart) in Excel
This tutorial tells you how to add the contents of a cell or a range to a graph object such as adding cell contents to a shape, a picture, or a title on a chart in Excel.
How To Reduce Excel File Size?
Sometimes, it will take minutes to open or save if the Excel file is too large. For solving this problem, here in this tutorial, it tells you how to reduce the Excel file size by removing the contents or formats which are unnecessary or never used.
Excel: How To Create Or Insert A Bookmark
Have you ever imagined creating or inserting a bookmark for quickly jumping to a specific data range while there is a lot of data in a worksheet or a workbook?
How to apply shading to odd or even (alternative) rows/columns in Excel?
While designing a worksheet, many people tend to apply shading to odd or even (alternative) rows or columns in order to make the worksheet more visual. This article will show you two methods to apply shading to odd or even rows/columns in Excel.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
