Create Custom Symbols in Excel based on Numbers

Excel has several in-built features to create custom formatting to your numbers. But if none of them meets your requirement, you will have to create your own. The key benefit of adding custom formatting is that it only controls how the number is displayed without changing the underlying value of that number. A cool feature within Excel is the ability to format a cell’s value by pressing CTRL + 1 on any cell. This brings up the Format Cells dialogue box and under the Custom category, you can customize the Type to whatever you like.

You can even create custom symbols in Excel using this feature!

But before you understand how to add a symbol to a number in Excel, you need to first know how to write a number format code.

Key Takeaways:

• Use Conditional Formatting for Symbols – You can assign custom symbols (✓, ✗, ▲, ▼) based on numerical values using Conditional Formatting with Icon Sets or custom rules.
• CHAR Function for Special Symbols – The CHAR function helps insert Unicode characters, such as =CHAR(9650) for an upward triangle (▲).
• IF Function for Symbol Assignment – The IF function can display symbols based on conditions, e.g., =IF(A1>0, "▲", "▼") to show an up/down indicator.
• Custom Number Formatting for Symbols – You can modify number formatting to display symbols without affecting the actual values, e.g., 0 "★" to append a star.
• Wingdings and Webdings Fonts for Symbols – Changing the font to Wingdings or Webdings allows for unique symbols based on numeric values, useful for dashboards and reports.

Understanding the Number Format Code

You can change the format of a cell’s value by either using various formats available in Excel or creating a custom format using number format code.

Number format code is created using symbols that tells Excel how you want to display the cell’s value. When adding a custom format in Excel, there are four formatting sections that you have to follow:

Positive format; Negative format; Zero format; Text format. 

Each of these sections is separated by a semicolon(;) and only the first section is required to create a custom format.

You can view the Custom Number Formats blog post that explains this in more detail here

Create Custom Symbols in Excel

Now that you have understood the structure of how to use number format code, let’s use that knowledge and learn how to insert symbol in excel formula based on the cell’s value.

Working with an example will make this concept more clear. So, let’s get started.

Follow the step-by-step tutorial below to know how to add a symbol in excel formula and make sure to download the exercise workbook to follow along:

DOWNLOAD EXCEL WORKBOOK

Example #1:

In the table below, you have daily temperature recorded and you want to add symbol °C to it.

You want to add “°C” symbol to the temperature column and the edited table like this:

The following steps should be incorporated to create custom symbols in Excel:

STEP 1: Select the “Temperature” column

STEP 2: Go to Home > Under Format Dropdown, Select More Number Formats

STEP 3: In the Format Cell dialog box, select Custom

STEP 4: In the type section, type 0.00 °C and Click OK

This is how the edited table will look like.

Example #2: 

In Example #1, you have learned how to add symbols in Excel irrespective of the cell’s value. Now let’s move forward and understand how to add symbols based on the number stored in the cell.

So, the symbols added would be based on the value stored in the cell.

In the table below, you have the status for different projects listed below with “0″ indicating Completed and “-1” indicating Pending.

Now you want to create custom symbols in Excel i.e. you want to add custom symbol :

• ✓ Completed; when status is 0
• ✕ Pending; when status is -1

The table with custom symbols should look like this:

STEP 1: Select the Status Column

STEP 2: Press Ctrl +1 to open the Format Cells dialog box

STEP 3: Select the Custom category and select a number format type – ✓” Completed”;✕” Pending”. This will change the format to ✓ Completed when cell value is 0 and ✕ Pending when cell value is -1.

You can also add color to make the formatting more distinct. Use the format type –  ✓” Completed”[Green];[Red]✕” Pending” to add green color to the completed project and red to pending projects.

This is how the table will look like this.

Example #3: 

Here, you have monthly sales, estimates benchmark sales and variance calculated.

You want the % Variance column in our data to have symbols ▲▼ to show a negative and positive variance. So, you have the % variance value customized as below:

• Green in color with ▲ symbol; when variance % is positive
• Red in color with ▼ symbol; when variance % is negative

The table should look something like this:

STEP 1: Enter a Variance calculation in a column, select the column’s variance numbers and press CTRL + 1 to bring up the Format Cells dialogue box

STEP 2: Select the Custom category and select a number format type – “#,##0;[Red]-#,##0

The first section of this code #,##0 is for a positive number, and second code [Red]-#,##0 is for a negative number.

To show the positive number in green color and add a % sign, follow Step 3.

STEP 3: Under the Type: area you will need to enter the text [green] at the start of the positive value string and enter the % sign at the end of the positive and negative value strings

STEP 4: Now select a blank cell and go to Insert > Symbol > Font: Arial > Subset: Geometric Shapes and then Insert the Up-Pointing Triangle and Insert the Down-Pointing Triangle and press Cancel

STEP 5: You will need to copy the triangles, select the variance numbers, press CTRL + 1 and paste the triangles before each positive and negative value string, then press OK

You now have your custom number formats with an upwards triangle for any positive %s and a downwards triangle for any negative %s

Frequently Asked Questions

How can I add symbols based on numbers without changing values?

Use Custom Number Formatting. For example, applying 0 "★" will display a star next to numbers without altering them.

Can I use Conditional Formatting to insert symbols automatically?

Yes, Conditional Formatting can apply symbols like arrows or checkmarks based on value thresholds using Icon Sets or custom rules.

How do I use the CHAR function to insert symbols?

The CHAR function inserts Unicode symbols, e.g., =CHAR(10003) for a checkmark (✓) or =CHAR(10060) for a cross (✗).

What font should I use for special characters in Excel?

Wingdings, Webdings, and Symbol fonts allow you to display unique characters, such as arrows and icons, when paired with numeric values.

Can I create a formula to show symbols dynamically?

Yes, using the IF function: =IF(A1>0, "▲", "▼") will display an up (▲) or down (▼) symbol depending on the number in A1.