3 Creative Ways to Use Special Fonts to Enhance Your Excel Spreadsheets

3 Creative Ways to Use Special Fonts to Enhance Your Excel Spreadsheets

How could you use a font to enhance your Excel spreadsheets?

I know that it’s tough to believe but special fonts actually offer a ridiculous amount of possibilities when it comes to building visual displays of any nature in Excel.

The more you begin to understand Excel and how objects interact, the more use you will find of these silly fonts.

When it comes to the office, you’ll be able to take some of these tactics and apply them to your spreadsheets and I’ll guarantee you end up turning some heads (hopefully the people that truly matter).

# 1 – Build a Stoplight Feature Using the Wingding Font to Monitor a Project Status

drop down option

You can pretty much use any special Wingding character for this one but I thought it would be fitting to mimic the icon set (stoplight) features that are currently already available in Excel conditional formatting.

This is a great way to visually manage and enhance your spreadsheets for projects statuses, order statuses or pretty much any type of status you can think of in the business world.  You can follow along through the video or simply follow the basic steps below to implement this tactic:

# 1 – Use data validation to create a drop down list of available statuses.

# 2 – In the cell next to your drop down list, insert the stoplight character from the wingding insert menu.

# 3 – Use conditional formatting formula for each status to set the color of the Wingding character to match the corresponding status.

That’s it! 3 steps and you’re done.  Make sure to take a peak at the video for more details.

 

# 2 – Use “Insert Symbol” with Custom Number Formatting to Visually Enhance Your Data

heat chart

I’ve covered this topic before but it’s well worth running through it again.  Custom number formatting is one of the best ways to visually enhance your spreadsheets.

Using Unicode symbols allows you to copy and paste the symbols into your custom number formatting area so that you can have positive values be displayed with an up arrow and negative values be displayed with a down arrow (of course you can use any other Unicode symbol you’d like).  In this case I use the characters available with the Yu Gothic font.

# 1 – Select any cell on your spreadsheet outside your data set.  Go to the insert symbols in the quick access ribbon.  Find the increase/decrease arrows as show in the screen shot to the left / above into the cell (both the up and down arrow).

# 2 – Copy the symbols that were inserted into the selected cell using CTRL + C. Now select the data set that you’d like to apply the custom number formatting to (see highlighted).

# 3 – Once we’ve selected the data set (type CTRL + 1), this will open the format cells window.  Head down to custom number formatting.  Paste the symbols where necessary in your custom number formatting input box.  Adjust and follow the syntax that I’ve used in the screen shot to the left / above.

If you haven’t done a whole lot of work with custom number formatting, I’ve included a few amazing resources below that you can use to get acquainted with this feature of Excel.

 

link   MORE ON CUSTOM NUMBER FORMATTING IN EXCEL

I use custom number formatting all of the time and it’s important to truly understand its functionality to really enhance your Excel Spreadsheets.

Here is one of my favorite guides for beginners on Excel custom number formatting.  It’s great especially when you’re just starting out and it’s also something that you can continue to use as a reference as you become a seasoned user.

The Definitive Guide to Custom Number Formats in Excel by Excel Tactics 

I’ve also compiled a short list of other links that are interesting in their own way and will also show you how you can apply custom number formatting in Excel:

5 Incredible Number Formatting Tricks that Will Impress Your Boss

20 Custom Number Format Disguises from My Online Training Hub

A Technique to Quickly Develop Custom Number Formats from Chandoo

# 3 – Create a Product Review Data Chart in Excel Using the Wingding Font

star icon

Creating a product review in Excel is something that’s fun but in building one, you’ll also realize that you can use some of the same techniques in other aspects of your work or business.

You can use the REPT formula to repeat a specific character a specified numbers of times (in this case stars) based on the review provided for the associated product.  Let’s see how you can do this.

# 1 – Use data validation to create a drop down for each product with a value of 1 through 5.

# 2 – Insert and find the special Wingding star character in a cell using the insert symbol and copy the text value from the formula bar.

# 3 – Use the REPT formula to generate and create the star rating in the last column of your data chart.  Use the screenshot I’ve provided to the left / above.

link   MORE ON WINGDINGS & THE REPT FORMULA

Clearly the Wingdings font is a useful tool especially when it comes to being able to visually enhance your spreadsheets.  As your skills in Excel mature you’ll be able to think of other ways to use these crazy characters. On the other side, we also have the REPT formula.  This is a great tool for using those Wingding characters.

Let’s have a look at some slick resources that you can use to learn what you can do with this font and this formula:

Wingdings and Webdings Font Icon Sheet from the Spreadsheet Guru

Replace Radar Charts with Tables to Make Comparison Easy from Chandoo 

Dashboarding Fun: Display Smiley Faces in Your Excel Dashboard from Chandoo

Use the Playbill Font to Make Your In Cell Charts More Realistic from Chandoo

Excel Dot Plot Charts for Stock Price from Contextures

Already a subscriber to the ultimate Excel newsletter?
Use the password that was sent to you via email to access the content.
If you are having trouble finding it, shoot me an email.

check

TODAY’S CONTENT UPGRADE: 

Learn how to apply data validation to your Excel forms – See below.

Data Validation

How are you Going to Use these Special Fonts to Enhance Your Spreadsheets?

 

Challenge yourself right now to come up with some creative ways to implement some of these visual Excel tactics to your spreadsheets and in your work.

I honestly use these characters often in my Excel dashboards but you can also think about implementing them into timelines or even custom / self made Gantt charts (i.e. clue it has to do with using the REPT formula).

Be sure to leave a comment letting me know how and where you may have used some of these special fonts to enhance your spreadsheets.  I’m seriously looking forward to hearing from you.

The Excel HR Dashboard: How to Create a Salary Summary Dashboard in Excel

The Excel HR Dashboard: How to Create a Salary Summary Dashboard in Excel

Alright so this is the first week that I’ve attempted to build and create a dashboard all the while creating full blown tutorial videos showing you how to create the dashboard from stage 1.  I haven’t received any overly positive responses from my YouTube channel as of yet but I’ll see how valuable this is to you, the readers of my blog.  If you’re loving the idea, please let me know in the comments or support the cause by purchasing a copy of the dashboard for $5. Depending on the response, I may not continue as it is an insane amount of work to put everything together in such a short period of time on a weekly basis.  Should I continue, I’ll be teaching you more and more Excel dashboard techniques that under normal circumstances you would only learn in paid Excel dashboard courses.

That all being said, let’s learn how to build an Excel HR dashboard.  I’ll go through the creation process and simply outline the key points that are really beneficial and that are covered in each video tutorial.  Let’s do this!

 

(more…)

4 Easy Steps to Using the Excel Option button for Dynamic Dashboards

4 Easy Steps to Using the Excel Option button for Dynamic Dashboards

In business, I often see disputes between the finance and operations departments on which metric is a more important indicator of performance.  Operational leaders (more often than not) like to see their numbers in terms of units/quantities and the finance executives/leaders want to see everything in dollars or currency.  This is where the Excel option button form control comes in handy (note that if you are part of a finance team, you can also use this logic to switch between local currency versus your company’s global standard currency).  I’m going to show you how you can use the option button to switch between quantity/units and currency all in one graph.

(more…)

Excel Customer Aged Receivables Dashboard: Summarizing Your Aged Receivable Data To Help You Make Quicker Decisions

Excel Customer Aged Receivables Dashboard: Summarizing Your Aged Receivable Data To Help You Make Quicker Decisions

Building an Excel customer receivables dashboard came to me when I realized that most companies seem to really make use of their invoice information to look at their customer credit performance.  Reviewing how your customers are doing in terms of making their payments plays a significant role in having a successful company because this is your lifeline for actually getting the bills payed.  This cash flow plays an integral part in the business and also allows your company to grow.

So, What Does This Dashboard Do Exactly?

The customer receivables dashboard allows you to load up your customer contact information (from your customer table) as well as load up your invoice details and due dates (from your invoice detail or invoice table) and provides you with an easy to use interface that allows you to select any customer (using a drop down validation list based on the customer table information that you loaded into the table) and will immediately display your customer’s main contact information as well as the breakdown of their past due invoices.  This will provide you with information like number of past due invoices, total past due invoice amount, oldest invoice and matching customer PO number to that invoice and finally the breakdown and distribution of late invoices based on age group (1-30 days, 31-60 days, 61-90 days and greater than 90 days).  I’ve also included a neat graph that will show you the distribution of past due invoices based on aging category.  Finally within the centre portion of the dashboard you’ll notice that the invoice aging category with the highest value of past due invoices will display the percentage of the total past due invoices.

I’ve included a quick video of the dashboard being used to give you an idea of how the dashboard works and whether or not this is something  that may be useful to you.

 Excel Customer Aged Receivables Dashboard – Video Demonstration

 


As always, thanks for all of your support! If you have any ideas or questions please leave a comment.

 

Aged Receivables Excel Dashboard_Thumbnail

Buy This Dashboard Limited Time OfferUse Coupon Code 3daysale to get 5 dollars off - offer expires Friday, August 22, 2014 @ 12am

 

 

 

11 Reasons Why You Should Check Out The VLOOKUP Book From Chandoo.Org

11 Reasons Why You Should Check Out The VLOOKUP Book From Chandoo.Org

I recently purchased a copy of Chandoo’s The VLOOKUP Book and realized that it was the first piece of material that I had read that covered pretty much all of the bases when  it came to the Vlookup formula and other lookup formulas in general.  Because I was able to learn something from this book I figured you may be able to benefit from purchasing the book.  Since I believe in Chandoo’s products and what he is doing, I decided to become an affiliate (the links in this post are affiliate links) and to provide you with some insight as to whether or not it may be something that you are interested in.  I will say that although the book is not perfectly written, it does a great job at showing all of the available options that you can use to run lookup formulas in Excel.

My hope in this post is to provide you with an overview on what this book has to offer.  Let’s get started with our 11 reasons and if you have any questions about the book or about the Vlookup formula in general, please feel free to leave any comments or to email me at brad@bradedgar.com.

By the way please be patient with the page slider below, it does take a little bit of time to load.

 

CHECK OUT THE VLOOKUP BOOK

1 of 11  

REASON # 1 

You’ll Learn How to use Vlookup to Lookup a Value and Find Corresponding Information About that Value

Vlookup_Name

REASON # 2 

You’ll Learn What the True vs. False Portion of the Vlookup Statement Truly Means and How To Use it

 

true_vs_false

REASON # 3

You’ll Learn How to Use the IFERROR and IFNA Formulas with Vlookup to Display Any Value you Please If Your Formula Doesn’t Not Find a Match

 

IFERROR_IFNA

REASON # 4

You’ll Learn How to Find the Second Item in a Lookup List When there Are Multiple Items

2_record_lookup_Find_Second_One

REASON # 5

You’ll Learn How to Use LOOKUP and HLOOKUP Formulas

HLOOKUP_LOOKUP

REASON # 6

You’ll Learn the Benefits and How to Use Excel Tables with the VLOOKUP Formula – This Will Save You Time and Effort

 

tables_in_vlookup

REASON # 7

You’ll Learn How to Use Wildcards (abbreviated queries) with the Vlookup Formula Using *

 

Wildcard Search

 

REASON # 8

You’ll Learn How to Use Vlookup with Data Validation Lists, Name Ranges, Tables To Build Slick Dashboards

 

GIF_VLOOKUP_RECORDING

REASON # 9

You’ll Learn How to Preform Lookup Formulas When the Data That you Are Looking for is to the Left of Your Lookup Column

Lookup_to_the_Left

REASON # 10

You’ll Learn How to do Lookups Based on Multiple Conditions By using Formulas like SUMIF, SUMPRODUCT, INDEX MATCH, AND SUM and you Will Also Learn the Best Method.

sumproduct_array

REASON # 11

You’ll Learn How to Use Helper Columns with Vlookup to Find the Results You Are Looking For.

 

Helper_Columns

1 of 11  

Excel Shortcut: Using Alt + Shift + Right Arrow To Group Your Pivot Table Fields

Excel Shortcut: Using Alt + Shift + Right Arrow To Group Your Pivot Table Fields

This is just another quick little image/post that shows how powerful Excel shortcuts can be.  If you have the time, please leave some of your favorite keyboard shortcuts in the comments – I’d love to hear them.  If you have any specific to pivot tables even better.  Thanks for checking the website out as always!

Cheers to the weekend.

Pivot Table Grouping