Any Symbol of your choice for Deviations
Rather than insert a symbol as we did previously, we will use a font here.
One such font with symbols is Wingdings.
With this font, though you type in a number, a symbol will appear.
Say we want a negative deviation to appear as the symbol for a lot of reports – which appears when you type in the number 4.
And we want a positive deviation to appear as simply the report symbol – which appears when you type in the number 2.
We also want to conditionally format them so that the many reports version is red, and the single report version is green.
Before I can use custom formatting, I need values in my cell.
As before, we create the values by using Actual – PY:
Cell D23= B23-C23These currently appear in the Wingdings font as it’s selected. Press Enter.
It will be easier to follow if we change to a standard font, and then change back to Wingdings once we’ve done the formatting.
Highlight cells D23-D34. Press F2 and then Ctrl-Enter.
Press Ctrl-1 to go to Custom Formatting.
Edit the arguments to say:
[color43]2;[red]4;This 2 and 4 are the character codes for the single report and multiple report symbols mentioned above.
Press Enter.
The result is that any number that was negative became 4, and any number that was positive became 2.
Change the font back to Wingdings, and the positive deviations appear as green single reports, and the negative deviations appear as red multiple reports.
Alternative method using standard font
I did a video on conditional formatting symbols some time ago and a friend of mine, Michael, commented that a lot of these symbols are available in the standard font with UNICHAR codes.
=unichar(10000)This gives me the pen symbol.
If I copy and paste that into the formula bar, then copy again, then I have a pen symbol that I can use without always entering the UNICHAR code.
This is useful for formulas, statements, etc, and gives us access to a huge variety of symbols in the standard font.
How do we use this?
For fun, let’s use the sad face and happy face – UNICHAR codes 9785 and 9786.
=unichar(9785)=unichar(9786)Copy and right-click, paste as values.
Copy again, and paste into the formula bar, and then copy the result.
Paste this into an empty cell for later.
Create values in cell E23 by doing Actual – PY:
Cell D23 =B23-C23
This time, stay in the standard font.
Highlight cell E23-E34, press F2 and then Ctrl-Enter.
This fills the cells with the same values as before. When they’re selected, press Ctrl-1 to access custom formatting.
Edit the statements to read:
[color43]"☺";[red]"☹";Press OK.
Now we have the happy face and sad face as deviations.
Featured Course
Visually Effective Excel Dashboards
Up/Down Arrows for Higher Variances
As a last step, we’re going to take a look at how we can add thresholds to this.
Add some values in there, by calculating the percentage:
Cell L23 =J23/K23-1 Drag L23 down to L34.
Highlight, F2 and Ctrl-Enter.
Format them as percentages.
Add conditions with custom formatting: Ctrl-1.
Add conditions
Up until now, we have followed the simple rule with custom formatting:
First comes positive, then negative, then zero, then text.
BUT:
Custom formatting also allows for conditions.
You can define an upper, lower, other condition.
A high, low, medium condition.
To define conditions, they also go in square brackets [ ], similar to colors.
The condition we want to add is:
Any value that is above 5%, I want to have a green arrow.
Any value that is below -5%, to appear as a red arrow.
Any other value to appear as white.
To do this, edit the arguments to read:
[color10][>0.05]“▲”;[red][<-0.05]“▼”;[white]Press Enter.
Left align to be closer to the values.
That’s how we add thresholds to this.
Cases where both thresholds are positive
The previous example works well with positive and negative thresholds, but what if both are positive?
For example, if we had the following two conditions:
The green arrow to appear for values above or equal to 5%.
The red arrow to appear for values below 5% (not below -5% as in the previous example).
To the rescue comes the if formula.
Cell M23 =IF(J23/K23-1>=0.05,1,-1)This means, if the precentage is greater than or equal to 5%, then show 1, if not, show -1.
The 1 and -1 will be replaced next with custom formatting.
Press Enter.
Drag cell M23 down to M34.
While highlighted, press Ctrl-1 to access custom formatting.
Edit the statements to read:
[color10]“▲”;[red]“▼”Press OK.
Now we have the positive numbers below 5% showing with the red down arrow.
Feel free to Download the Workbook HERE.
Custom formatting is a really powerful tool, and once you get the hang of it, you can get really fast. It does take some practice though.
Note: VBA code from Excel MVP Charles Williams to time your formulas is included here.
Excel Custom Formatting with Thresholds & Icons: Quick & Easy
‘Can you use cell reference instead of using [>0.005] OR [<0.005]?’
-a question from Michelle on YouTube
Connecting threshold values to cells in custom formatting
Taking an example of a data set below, indicate the thresholds to be used.
For now, place the 5% on cell E5.
In an empty cell, bring in the symbols to be used from Insert > Symbol.
Custom formatting is a way to disguise values in a cell to return a symbol.
To do so, determine the value equivalents or conditions of each symbol.
In this case:
- ▲= above the positive threshold (5%)
- ▼= less than the negative threshold (e.g. -5%)
- No symbol = anything in between
Determine the threshold and place it in a cell.
For this example, the threshold of 5% is placed as a header in the last column.
Conditional formatting allows you to define 4 types of values.
In this case, the types that will be used are:
- Positive number
- Negative number
- Zero
Fill in the last column with values to indicate if the entry is above, below, or at the threshold value.
Then replace these values with symbols.
To do this, use the IF() function on cell E6:
IF(logical_text, [value_if_true], [value_if_false])
- logical_text = D6>$E$5
- If the value is above 5%
- [value_if_true] = D6
- If the value is above 5%, display the actual value
- [value_if_false] = if(D6<-$E$5, D6, 0)
- If the value is below -5%, display the actual value
- The 0 is used because that’s the third option under custom formatting.
The final formula now becomes:
Cell E6 = IF(D6>$E$5, D6, IF(D6<-$E$5, D6, 0))
Pull the formula down to the last row to populate the rest of the rows.
Copy the symbols to be used.
Highlight the contents of the last column where custom formatting will be applied.
Press CTRL + 1 to bring up the Custom Formatting window.
In the Custom Formatting window, select the Custom category.
Define the custom formatting to be used under the Type section.
Use this link to get equivalent color codes from the Microsoft website.
Type: [formatting of positive values]; [formatting of negative values]; [formatting of zeroes]
In this case, we will use: [color10]▲; [red]▼;
Leaving the third value blank hides the zero values.
Notice that this formatting is connected based on the threshold in cell E5, which is 5%.
Changing this value automatically changes the thresholds where the custom formatting is applied.
Setting a dynamic threshold value for custom formatting
Is there a way to set the threshold based on an average? – Sukhomoy
There are two ways to incorporate a calculated value as a threshold:
- Instead of placing a static value as the threshold value, use a formula
- Build the formula inside the IF() formulas.
Setting a range for custom formatting
Joe: Can I set conditions such as if it’s between 1-3 red, 4-6 green and 7-10 yellow?
Brandy: Is it possible to have multiple conditions?
Yes, since Custom Formatting enables you to format 4 types of values (positive, negative, zero, and text), it is possible to have up to 4 conditions.
This means that so long as your conditions are setup to be one of these types, custom formatting can be applied.
In an example, the conditions are:
- 5-10%: light green ■
- 3-5%: yellow
- >10%: dark green
- <3%: red
Notice that under these conditions, it is quite possible to not have any negative values.
However, none of them should actually be a 0 or text, since formulas will be used to translate the values for each condition into one of the 4 types.
- 5-10%: light green ■ = Set as positive
- 3-5%: yellow = Set as negative
- >10%: dark green = Set as 0
- <3%: red = Set as text
This is done through the nested IF() formulas incorporating the following logical_text below for each condition.
Starting at cell O6:
- IF(AND(N6>=5%, N6<=10%), 1 = this assigns values between 5-10% to a positive number 1
- IF(AND(N6>=3%, N6<=5%), -1 = this assigns values between 3-5% to a negative number 1
- IF(N6 > 10%, 0 = this assigns values above 10% to a 0
- “t” = this assigns everything else to a text
The final formula becomes:
Cell O6 = IF(AND(N6>=5%, N6<=10%), 1, IF(AND(N6>=3%, N6<=5%), -1,
IF(N6 > 10%, 0, “t”)))
Note that these values: 1, -1, 0, and t are placeholders that fall under each type.
This can be changed to any other value of the same type.
Drag the formula down to the last row of the table.
Copy the symbol to be used.
In this case, ■.
Highlight the area where custom formatting will be applied.
Press CTRL + 1 to bring up the custom formatting window.
Select the Custom category.
Define the custom formatting to be used under the Type section.
Use (this link) to get equivalent color codes from the MSDN website.
Type: [formatting of positive values]; [formatting of negative values]; [formatting of zeroes]; [formatting of text]
In this case, we will use: [color43] ■; [yellow] ■; [color10]■; [red] ■
Once applied, the values of the cells will change into the custom formatting.
It is also possible to set the thresholds as dynamic by doing cell references.
The 4 value types are at your disposal, so you can get creative in using them.
Video and Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
