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 and even insert symbols!

There are four formatting sections that you have to follow:

Positive format; Negative format; Zero format; Text format

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

In our example below we want to enter a % Variance column in our data and insert the symbols ▲▼ to show a negative and positive variance.

DOWNLOAD EXCEL WORKBOOK

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

excel format cells dialogue box

STEP 2: Select the Custom category and select a number format type

Excel custom number format

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

excel type format cells dialogue box

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

excel symbol characters

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

excel triangles in custom format

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

excel variance numbers

Watch the tutorial below:

Custom Symbols with Numbers

HELPFUL RESOURCE:

Me_Podcast_Banner_EXCEL

 

 

 

 

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Fix Column Width in a Pivot Table Each time you Refresh a Pivot Table you will most likely get annoyed at the fact that the column widths that you worked so hard to align - will return back to normal 🙁 Do not fear, Pivot Table Options is here! All you need to do is Right Click in the Pivot Table and choose...
Remove Duplicates in an Excel Table When you have duplicates values within your Excel Table there is a quick and easy way to remove those values. The duplicate values could be all over your Excel Table and sometimes it takes valuable time trying to locate those duplicates and then deleting them. Not to worry,...
Show The Percent of Column Total With Excel Pivot ... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF COLUMN TOTAL calculation. This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, ...
Show The Percent of Parent Total With Excel Pivot ...   Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT TOTAL calculation. In the example below I show you how to get the Percent of a Product Total i.e The % Sales of each Product ...