Why the Excel Dashboard Toolbox is What You Need

Why the Excel Dashboard Toolbox is What You Need

​Do we really need anything? Sure we do...but why the Excel Dashboard Toolbox.

​Here's the thing.

​Learning dashboard techniques by scouring the web can be a time consuming feat. Buying Excel Dashboard courses is usually a great way to learn the theory and steps in creating a dashboard but often times you're left with having to extract your favorite lessons and concepts on your own.

That's where the Excel Dashboard Toolbox fits in. The Toolbox is all about being able to use the dashboard, review the calculations used and then learn exactly how to implement that technique all bundled into one neat workbook. In my first and most recent Excel Dashboard Toolbox my goal is to train you with 5 Data Transformation Techniques that you can apply immediately in your own dashboards.

The concept is simple. You've got a workbook with the best Excel dashboard creation techniques at the click of a workbook (stored nicely on your local hard drive).

In this article I go over 6 different reasons why the Excel Dashboard Toolbox is what you need.

Continue Reading
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 Sales Trend Dashboard

The Excel Sales Trend Dashboard

For decades, managers and executives around the world have been monitoring sales trends to capture and understand sales cycles in their industries.  In this Excel Sales Trend Dashboard, I’ve created a dashboard that automatically pulls your raw data from a table and displays the monthly sales trends for the data that you’ve added. Checkout the video to see this dashboard in action.

TIP # 1: How to Create Mini Pie Chart Overlays on Your Dashboards

Adding mini chart overlays to your dashboard allows you to associate the numbers in your dashboard to a visual display. Here is a birds eye view of the steps required to do this.  For more details be sure to checkout the video above.

STEP #1: In this dashboard, you want to display the total percentage of sales for the top or bottom customer against the total sales for the month selected by the user.  To do this, automate your data calculations based on what month is selected by the user. Calculate the highest sales and lowest sales by using the max and min values respectively and then by dividing each by the total sales for the month selected.  Once you’ve got that calculation, in another cell subtract the percentage calculated by 1 and this will give you the remaining percentage for each the highest/lowest sales.

STEP #2: Now to create the graph, select the cell with the percentage of total sales for the highest value sold and select the cell that contains the value of 1 – that percentage.  Go to insert and then insert a pie chart graph.

STEP # 3: Select the chart background and remove the outline and background by selecting no fill or no color.  Next you can alter and adjust the pie chart.  The value remaining percentage should be set to also have a no fill background but you’ll include an outline.  For the percentage of total sales displayed, you’ll choose a background fill and make the outline the same color as the other percentage.

TIP # 2: How to Add Data Callouts to Your Excel Dashboard Charts for High/Low Values

Adding data callouts to your dashboard chart to show the highest and lowest values is a great way to quickly point out in what months of the year you’ve performed well or poorly.  Let’s look at the step by step process for doing this.

STEP #1: You’ll need 2 additional columns or series that will be included on your dashboard chart.  These will be added to your calculations tab of the worksheet.

STEP #2: Use the IF formula to display #N/A (using the NA() formula) if the associated value for the sales for the given month is not the highest (under the highest column data set).  The IF formula will use the rank formula to right of your data set and will use the highest rank value to find this value (see video).  Apply this same logic to the lowest value sold column. Note that the NA() formula is what is going to ignore those data points on your chart instead of putting a value of zero in.

STEP # 3: Add the lowest and highest value sold data series’ to your dashboard chart.  We will use a line chart again for the type but we will only display the point.  Select the data points and go to add data callout.  You can the adjust and change the mark fill and the callout fill and color to be green and red depending on what value you are display (i.e. lowest or highest).

TIP # 3: How to Add and Use Excel Data Validation in Your Dashboards

Using data validation in your dashboards is a great way to identify whether a certain condition is met or not.  In this dashboard, I want to know whether or not my raw data set is including more than 12 months of data.  If it is, I want to display a red X and if it isn’t, I want to display a green check mark.  Here’s how to do it.

STEP #1: In a cell on your calculations tab, go to insert and then symbol.  Find the check mark and the X under the wingdings 2 font.  Once you’ve added them, you can the change the font in the cell to get the standard font characters that will need to be used on the front end dashboard.

STEP #2: Use the IF formula to determine if our list of months name range contains more than 12 months of data.  If the count is greater than 12 then display Yes, if not display No.

STEP # 3: Create a separate IF formula that uses the no or yes value in the last cell to display the wingdings X character if the value is Yes and the check mark if the value is No.

STEP # 4: Pull the formatting over to your dashboard cells where you want this displayed using name ranges for the cells on the calculations tab.  Now you can change the color of the Wingding 2 font display by using conditional formatting on the cell.  If the value in the cell refers to a check mark, display the value in green and if it’s an X, display the character in red.

Buy Now

The Excel Sales Trend Dashboard is a great way to capture your company performance in a minimalist way.  It helps you understand where you’ve performed well and poorly and at the same time allows you to identify your best and worst customers quickly and efficiently.  Downloading the dashboard is a great way to help you also learn some of the techniques used in the dashboard to get yourself acquainted with some useful Excel techniques.  Whether you’re looking to learn or looking to cut time off your current processes, this dashboard is for you.

Excel Tutorial: 3 Tips on How to Create an Automated Budget & Expense Tracker

Excel Tutorial: 3 Tips on How to Create an Automated Budget & Expense Tracker

It always seems that no matter how hard you try, there is always an easier way to do something.  My wife emailed me one day this week asking if I’d be able to build her something in Excel that could handle the tracking of all of her departments expenses and then summarize and subtract the transactions from the budget total so that she could tell just exactly how much money she had left in each area.

Unfortunately in past years, she had been handling this through a word document where everything had to be done manually, but I assured her that those days were over. I built this custom budget and expense tracker so that my wife had all of the information that she needed including when and how close she was to eating up her budget.  The tracker allows you to input detail records for each type of expense incurred and summarizes and subtracts the detailed money spent within each expense type on the main budget and expense tracker dashboard.  Have a peak below at how you can enter your detailed expense records and have your total spend and budget remaining update accordingly.

TIP#1

 

TIP # 1: Keep Your Dashboard Header Visible Using Freeze Panes

Step # 1: Selecting the Row Below Where You Want to Freeze


The first step in freezing your header to make it visible is selecting the row just below the area you would like to freeze. Select the row number on the left of the worksheet and this will highlight your entire row.


Step # 2: Accessing Freeze Panes from the Quick Access Ribbon


Once your row is highlighted, you can head over to the view tab on the quick access ribbon and select freeze panes. This will bring up 3 different options but you’ll want to select freeze panes again.


Step # 3: Using the Scroll Bar to Watch the Magic – Freeze Panes!


Use the scroll bar in your worksheet to move up and down. Notice that anything below the rows that you have frozen will move but your dashboard/tracker header will remain visible. Freeze panes has to be one of my favorite options for this reason.


TIP#2

 

TIP # 2: Create Buttons in Your Header to Navigate Between Worksheets

Step # 1: Create the Shape


The first step in creating buttons in Excel is to create, select and add a shape to your worksheet.  To add a shape to your worksheet, in the Quick Access Ribbon go to the insert tab and then select shapes under the illustrations tab. This will bring up a drop down menu with multiple shapes.  From here you’ll select the shape you’d like to use as your Excel button.  This button will be used to navigate between sheets.


Step # 2: Edit the Text


Now that the shape is created, you’ll want to select the shape by right clicking and then you’ll want to select edit text. This will of course allow you to change the text.  Change the text to display the tab name that you’d like to link to in your workbook.


Step # 3: Create the Hyperlink to Link the Button to Another Tab


Now that you’ve changed the button text, you can create a hyperlink with the button so that when the user clicks on it they are brought to another worksheet (such as “Expense Entries”).  To do this, if your shape is still selected type CTRL + K or go to the insert tab and select hyperlink.  Once you are in the hyperlink window, go to place in this document.  Under the cell reference section, select the tab/worksheet within your workbook that you want to link to. NOTE: You can also link to name references, which means if you wanted to you could link to a specific cell anywhere within your workbook.


TIP#3

 

TIP # 3: Use a Nested SUM and IF Formula to Summarize Your Detailed Expenses

Step # 1: Select the Cell On Your Budget & Expense Tracker Where Total Spend Will Be Calculated


The first step is definitely an easy one.  The only thing you need to do in this step is select the top cell on your budget and Expense tracker and get prepared to create the nested SUM & IF formula.  For the purpose of teaching you this exercise, I will suggest that the expense entries and data that is being referred to is put into an Excel Table so that you can use structured references in the array formula that you will be creating.


Step # 2: Creating and Writing the Nested Array SUM & IF Formula


Now that you’ve selected the cell and setup your expense entries table, you can create your nested array SUM & IF Formula (click here for more on array formulas).  The SUM & IF Formula has one simple job, it is to return the sum of all expense entries that match the associated expense type from column B.  This means that if we are looking at cell D5 as shown below, we want to SUM the value of all expense entries when/(IF) our expense type on our expense entries table equals the expense type in D5.

As I have mentioned in step #1,  you can use structured references to build an array formula that will handle this situation. Here’s what our formula will look like:

 

SUM & IF Formula

 

The Formula Explained:

 

  • SUM is used to find to calculate the total sum of all expense entries

 =SUM(

  • IF will be used to SUM only the values in our value of purchase column (on the expense entries table) where the associated expense type in our Budget & Expense Tracker equals the same expense type (Select Purchase Type Column) on our detailed expense entries table/sheet. So if B5 equals the purchase type on our expense entries table, our true statement in the IF formula will add all of our entries when true or will add 0 for the lines that are false.
  • Once you’ve entered your formula and closed the last bracket, type CTRL + SHIFT + ENTER.  This will make your formula an array.  The reason why you want to do this is because you want the formula to not only find the first record where the expense type equals the select purchase type on your expense entry table but you want to sum an array of numbers (multiple entries) where this statement/formula is true.

 

{=SUM(IF(B5=ExpenseEntries[Select Purchase Type),ExpenseEntries[Value of Purchase ($)],0))}


Step # 3: Watch Your Budget & Expense Tracker Update as you Add Expense Entries


Now that you’ve created the formula, you can copy that formula down across all expense types on your budget and expense tracker.  After that’s completed, you can head over to your expense entries table and add records.  You’ll notice that your budget and expense tracker automatically updates to show total spend to date as you add new detailed expense records to your table.


The Budget & Expense Tracker Review

I love simplifying and making processes and tasks easier.  Excel is the perfect tool for doing this.  In this week’s post I was able to go over 3 different tips that you can use when building your own dashboard or tracker.  If you feel that this tracker would be useful for your own purposes, feel free to purchase a copy of the tracker below.

 

If you were to build your own budget and expense tracker, what would you make sure to include and how would you do it?

Excel Dynamic Conditional Formatting: Create a User Controlled KPI Dashboard

Excel Dynamic Conditional Formatting: Create a User Controlled KPI Dashboard

One thing that I love to save is my time.  Wouldn’t things just be that much better if you had a little more time?  I want to show you a quick and fairly painless method of allowing your spreadsheet/dashboard user to update target numbers to monitor salesperson performance.  What is going to save you time is showing you how you can use conditional formatting in Excel and combine it with an input range to build a dynamic key performance indicator table.  Instead of having to tediously go in and update target numbers every month from the manage rules options of Excel conditional formatting, you can painlessly update your targets by simply updating your input range.  Sounds easy, right? It is.  So let’s have a look.

(more…)

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…)