Szilvia Juhasz guest hosts today's Learn Excel podcast - how to use fun fonts in your Excel dashboards. Thanks to Szilvia for guest hosting.
Transcript of the video:
Hello Excel fans.
Sylvia You’haas AKA XSzil here.
Coming at you all the way from Venice Beach, California.
Delighted to be guest podcasting for MrExcel.
Today I want to talk about some unsung font heroes like webdings and wingdings.
These fonts have great applications when it comes to dashboards or management reports.
You can build your own custom icon sets a lot more variety than you have with excels built-in conditional formatting icon sets.
So, let's take a look at some of these fonts capital J, K, L formatted as wingdings.
Who knew that was so cute.
Now these are just three out of many more examples of special characters you can create using those fonts.
So, a good way to get familiar with all the little special characters that are there for the taking to create yourself a nice little character map.
So, let's do that.
The first thing you're going to need for a character map is a sequential list of numbers.
Start with the number 33.
Don't worry about why and then just increment by 1 you can hold your control key and drag the fill handle all the way down to 255 give you lots of options and then you're going to need something called the char function =char(A2) the char function in Excel takes a single argument a number and it returns a character based on your computer's internal character sets.
So, code number 33 is an exclamation point.
I can double-click that fills handle to get all of the characters that correspond to those particular code numbers.
So, as I'm scrolling through here keep in mind this is still formatted in Calibri, right?
What we really want to see here is how these characters are going to look when formatted with those special fonts.
So, I have done that in advance like on the cooking shows here and we've got 4 different fonts the same formula across the board just different fonts.
So, again, I can browse through those to check out all of the little characters that correspond to those code numbers and now I can use these in my next dashboard report to build my custom icon set.
So, let's take a look at how we can apply this technique in a report.
What I've done here is I've just kind of recreated a little mini character gallery appear so we happen for reference as we build these formulas.
So for example, let's say did I pass or fail?
Anything 65% or higher is a passing grade we get a thumbs up and everything else thumbs down.
So, we can construct an if statement that says if the grade is greater than or equal to 65% get a thumbs up.
Otherwise, thumbs down boohoo.
Now keep in mind this is still formatted in the default font calibri so we need to make it match the font up here, which is wingdings so we can make this wingdings here and now we get the results we expect right thumbs down for non-passing grades and thumbs up for passing grade.
Excellent!
So, one final example here little data validation list action weather forecast sunny cloudy or rainy.
Now, I've already built the formula here so we just need to change the font this is just another, if statement that says if the weather forecast is sunny return that icon if it's cloudy we'll take that one otherwise it's rainy.
So, the only thing left to do here is change the font which in this case we want webdings.
So, let's just make that webdings and it's always sunny here.
So hopefully this gives you some inspiration for building your next dashboard with your custom icon sets.
Remember, you can still apply color using conditional formatting on top of this formula technique.
So, it's been a pleasure sharing this technique with all of you.
Hope to see you again soon peace out.
Sylvia You’haas AKA XSzil here.
Coming at you all the way from Venice Beach, California.
Delighted to be guest podcasting for MrExcel.
Today I want to talk about some unsung font heroes like webdings and wingdings.
These fonts have great applications when it comes to dashboards or management reports.
You can build your own custom icon sets a lot more variety than you have with excels built-in conditional formatting icon sets.
So, let's take a look at some of these fonts capital J, K, L formatted as wingdings.
Who knew that was so cute.
Now these are just three out of many more examples of special characters you can create using those fonts.
So, a good way to get familiar with all the little special characters that are there for the taking to create yourself a nice little character map.
So, let's do that.
The first thing you're going to need for a character map is a sequential list of numbers.
Start with the number 33.
Don't worry about why and then just increment by 1 you can hold your control key and drag the fill handle all the way down to 255 give you lots of options and then you're going to need something called the char function =char(A2) the char function in Excel takes a single argument a number and it returns a character based on your computer's internal character sets.
So, code number 33 is an exclamation point.
I can double-click that fills handle to get all of the characters that correspond to those particular code numbers.
So, as I'm scrolling through here keep in mind this is still formatted in Calibri, right?
What we really want to see here is how these characters are going to look when formatted with those special fonts.
So, I have done that in advance like on the cooking shows here and we've got 4 different fonts the same formula across the board just different fonts.
So, again, I can browse through those to check out all of the little characters that correspond to those code numbers and now I can use these in my next dashboard report to build my custom icon set.
So, let's take a look at how we can apply this technique in a report.
What I've done here is I've just kind of recreated a little mini character gallery appear so we happen for reference as we build these formulas.
So for example, let's say did I pass or fail?
Anything 65% or higher is a passing grade we get a thumbs up and everything else thumbs down.
So, we can construct an if statement that says if the grade is greater than or equal to 65% get a thumbs up.
Otherwise, thumbs down boohoo.
Now keep in mind this is still formatted in the default font calibri so we need to make it match the font up here, which is wingdings so we can make this wingdings here and now we get the results we expect right thumbs down for non-passing grades and thumbs up for passing grade.
Excellent!
So, one final example here little data validation list action weather forecast sunny cloudy or rainy.
Now, I've already built the formula here so we just need to change the font this is just another, if statement that says if the weather forecast is sunny return that icon if it's cloudy we'll take that one otherwise it's rainy.
So, the only thing left to do here is change the font which in this case we want webdings.
So, let's just make that webdings and it's always sunny here.
So hopefully this gives you some inspiration for building your next dashboard with your custom icon sets.
Remember, you can still apply color using conditional formatting on top of this formula technique.
So, it's been a pleasure sharing this technique with all of you.
Hope to see you again soon peace out.
