Smart Uses of Custom Formatting – Part 2

Explore the possibilities of Custom Formatting with Part 2 of our guide.

If you missed it, check out Part 1.

CHAPTER 4:

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 report 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-C23

These 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 {LINK} 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.

 
TWEET THIS EXCEL FORMATTING TECHNIQUE

CHAPTER 5:

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.

Video and Workbook

This video – Part 2 – shows the third and fourth of the four techniques described above.

Feel free to Download the Workbook HERE.

Free Excel Download

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.

TWEET THIS EXCEL FORMATTING TECHNIQUE

Try it yourself

I hope you liked my guide to Custom Formatting. What did you think of it?

Do you have any questions?

In any case, give me an idea of how this worked out by leaving a comment below.

The new Excel Dashboards course is here!

Now available on Udemy

Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.

GET 75% OFF THE COURSE
Free Ebook

Leave A Comment

Share This