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
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.
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
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
Watch the tutorial below: