Table of Contents
- 1 What is a dashboard? How to reach our business goals?
- 2 Key Questions before building your dashboard in Excel
- 3 Guidelines for effective dashboard design
- 4 Plan the structure and prepare the input
- 5 Make the Dashboard interactive
- 6 How to Create Dashboard in Excel? – Step by step Tutorial
- 7 Ultimated Dashboard Tools for Excel
- 8 Dashboard do’s and most common mistakes
- 9 Various Types of Dashboards – Free Downloads
- 10 Conclusion and Final Words
Dashboard is a powerful tool to visualize information. A great looking dashboard can radically improve the way you use and get sense of your information. You should optimize every aspect of it. Everything is in one place, important details, project status and key performance indicators. Would this be the dream of all managers?
Dashboards are great for a lot of various tasks not only to display the achievements of a company. If we have enough data with that we can take more efficient steps. We can interfere procedures, we can reorganize, and we can voice our expectancy towards co-workers for future plans.
What will we learn today?
In this article we will show you the best technics step by step. Starting from the ground up, we will go forward with you! Generally, we’ll use the best techniques and visualization guidelines when creating a dashboard in Excel. By the end of the course you will know exactly how to create your own presentation.
Our free training might be just what you need! We will show you, how to build from a clean white spreadsheet to a totally efficient dashboard.
Above all we’ll share all tips, tricks and secrets and will answer the most frequently asked questions. We not only going to learn about dashboards but will detail every relevant subject that connect with the procedure.
Not everyone is born an Excel genius. We weren’t born like that either, we are also not geniuses, but we have learned a lot through the years, and this is a good possibility for everyone! We’ve created the category so that everyone can easily learn the basics of dashboards.
Of course, we can feel fortunate because finally we do the things we really like. Just learn and play with these excel dashboards and you will see: You have a lot of magic eye-catching solutions to build something new, something special. That’s the point.
What is a dashboard? How to reach our business goals?
Actually, what are dashboards? For sure that they are not only represent visual data! When we look at a dashboard it is obvious that we not only interested in momentary achievements. But dashboards are rather information that pose us questions. How are our things going? Is there any factor we should be alarmed about? How will I explain my boss the increase of profit? A good dashboard will give answers to these questions, and much more!
Virtually our goal is to organize, manage and present the huge data accumulated by the company. Further standpoint is to present the organized data in an easily understandable format. Our great help in this is visualization. Under the hood there are complex data and we can dig further down in them by drill-down method.. And most importantly that this happens in mare seconds.
Probably we can call the dashboard the “central brain” or “engine” of the company or department. Informations, key performance indicators in real time and always up to date. We don’t have time to look for reasons of weaker of exceptional results for long days.
Key Questions before building your dashboard in Excel
Before we start creating the dashboard we have to contemplate on a few things. Like, who will use it? Where will data come from? What will we need exactly? Let’s investigate these basic premises in detail!
What is the reason we creating the dashboard? Maybe one of the business procedures have a weakness? Or maybe we would like to prove or disprove a hypothesis? Is it going to serve short term or long-term projects? A well-made dashboard will provide the answer to all the questions!
Who is the dashboard for? A co-worker, a branch manager, an IT manager or a stakeholder has totally different need for information. The result has to be useful for all levels. We have to think this through before we start the work.
What kind of data will we work with? The most commonly used types are: integer, real, categorical, time-series, Geo-spatial. Let it be any of these we must clean it.
What would we like to communicate with our dashboard? Would we like to search the effectiveness between two procedures and portray that? Would we like to compare the costs of two projects? Or would we like to know what the breakdown cost is between two branches?
How many KPIs represent comprehensively the company’s efficiency? Focus on the business goals! Only show KPIs that represent values. With a preparatory survey we have to ascertain these. We simply don’t have resources of money, HR capacity and time to deal with indicators that are not relevant.
What kind of data connectors shall we use? We have to know already in the planning face what kind of tool we’ll use to import data into the dashboard. If we work in Excel the best solution is the Power Bl. The tool is able to handle millions of rows in a blink of an eye. But we can use the classic ODBC connector and the SQL DB.
Are there compatibility issues within the company? IT professionals have to assure that everyone involved uses the same version of Excel. If we build this into the planning phase we can avoid a lot of frustrating problems later.
How often does your dashboard need to be updated? De we make decisions based on real time information? Or is it enough in the habitual daily, weekly, monthly, quarterly or yearly breakdown? We can create the initial structure of the dashboard based on the answer.
What format do we publish the dashboard? Do we send Excel tables to the users or maybe we publish the result on SharePoint? Or maybe we need to embed some charts into a Power Point presentation? We have to consider access issues also. Accessibility levels are different for a manager and for the owner.
Guidelines for effective dashboard design
Dashboard design is one of the most important tasks and it’s not as easy as we might think. We’d like to help you in the planning phase also. Finally, here are the rules we most definitely have to keep in mind!
Start with users, not the data
The best way to start is to understand the objectives and motivations of end-users. If we can realize this for sure we’ll create a dashboard that is most useful for them.
What is this all mean in practice? Go and talk to the user! We have to sit down to his / her computer and understand exactly the information they have to send to their boss and do this with all end-users. We can dramatically cut development costs if we create a dashboard that is not in constant need of updates.
Don’t flood the user with unwanted information
Do not flood the user with unnecessary data! We should seek that the dashboard be truly useful for them. We can even create such custom views that enables to display relevant information only for that particular user.
Provide an overview and allow users to drill-down into the details
A well thought out and well-planned Excel dashboard should be like a quality newspaper. On the front page gives a very good assessment of the key information and leading news there are within. A very good perspicuity or transparency is most important.
If one wants to look at an information in detail must know where to navigate. Allow users to see the most important information and to drill-down into the details if they want to find out more.
Use visualizations and create a clean, uncluttered dashboard
Data visualization is a great and Excel possibilities are practically endless. Use the classical chart types but based on our experiences most useful to use gauge, bullet and variance charts also.
Think about what is going to be most impressive at telling our story. Charts are usually a better idea than pie charts, and certainly avoid over the top visualizations and graphics, such as exploding pie charts and stacked bar charts.
We have to remove unnecessary components from the dashboard because they distract the attention from the essence. We have no need for overcrowded and jammed with useless charts screen.
Create Clean Structure and easy navigation
We have to create a detailed and well thought out menu structure. Our goal is that the users be able to orientate easily about the information most relevant to them.
There are several Excel tricks exist for making professional menus and organize the indicators into the appropriate hierarchy. So the users will always know which level they are on in the structure.
Provide help and guidance where necessary
Most ideal if the dashboard self-explanatory and don’t need any additional explanation. But what if a new colleague arrives to the company and right away has to overtake the tasks of an experienced business analyst. What if we seldom use an indicator? We will see that in these and like cases contextual help is helpful. We’ll show the connection of dashboard and contextual help in our article today.
Plan the structure and prepare the input
We should never start creating a dashboard without planning. If we make mistakes in the planning phase we can only correct them with disproportional effort later. The following rules we must consider:
Plan the layout and create proper draft
We have many possibilities. Some like to plan simply in Word or Power Point. Some prefer the classical methods, takes out a sheet of paper and sketch the ideas. Here’s an example of a typical layout:
You will find ready-to-use sample vector elements (a combination of shapes and images) that you can use to create the basic layout of your Excel Dashboard or Report.)
The shapes and forms help you design a basic layout in Excel. Using this method, you get to see how your draft version could potentially look in an Excel spreadsheet. It helps you organize each section and decide on what types of form controls you’ll need.
The good thing about vector elements is that they are flexible. You can change the color of the treemap chart for example, adjust the text in the titles and change the background color of the boxes. This helps you fine-tune the color and titles to your requirements, so you can come up with a proper draft before your start with implementation.
We prepared chart and form control mockups with the help of basic shapes.
Create Workbook and Worksheet structure
How can we create a consistent workbook structure? What is this mean exactly? Open a workbook and create three sheets.
The first one is the dashboard. This contains the final data visualization this is where date is assessed. The name of the second one is “data”. And the third on is called “calculation.” This partition seems completely logical. From the unorganized raw data throughout Excel formulas and functions we reach the final result, our dashboard.
Data Sources – How to Bring Data into Excel
The most common case is that we don’t store to be processed data in Excel, but we import it from an outside data source. How can we transport it?
Let’s see an example of data import in Excel. On the “Data” tab clicking on the “Get Data” icon we can see a lot of data sources. Let’s see in order what kind of data can we work with. The most important ones we can see on the list below:
• File: Workbook, text, csv, xml, json
• Database: SQL Server, Access, Analysis Services, Oracle DB
• Online Services: Microsoft Exchange, SharePoint, Salesforce
• Azure: Azure SQL Database, Azure SQL DW
• Other sources: Table, Range, Web, Microsoft Query
Last but not least the ODBC connect also present and is been widely used.
The above introduced Excel Live Connectors import and update data in real time. Because of this on the dashboard the display current information. Using data connectors seems to be a rational decision when raw data is not stored in Excel.
Create a Table with Raw Data
In this phase we don’t have cleaned input, but we already imported it with the help of data connectors. What will be the next step? We convert our data into Excel table. The use of Excel tables provides us with many advantages.
First, we must check if our information is in tabular format. Tabular format means that each and every data, data point lives in one cell, for example the name of city, address or phone number.
To convert tabular data into an Excel table, select the data and go to Insert –> Tables –> Table.
What kind of benefits do we get by using Excel table when creating a dashboard?
If we work with Excel table, we can avoid a lot of trouble. For example, if the scenario changes further down the line and we need more data than we can simply expand the table and will not lose the formulas. A very useful tool indeed to use.
Instead of the cell references for example A1:B340, we can use the names! It is a lot more simple and transparent to refer to a range like “Products” instead of writing in the long cell reference.
Prepare, Consolidate, Clean and standardize raw data
After the data is in Excel we can start the data cleaning.
There are many awesome cleansing technics, although we often forget about this obvious method. Some effective ways to clean raw data in Excel:
• Sorting data to see extremes and peaks
• Remove duplicates
• Use the FIND() and REPLACE() functions
• Change Text to Lower – Upper – Proper Case
• Remove unnecessary characters in Excel
• Check the Type of Data in a Cell
• Convert Numbers Stored as Text into Numbers
• Remove extra spaces and eliminate blank cells
• Split text into columns
• Use the CONCATENATE() function
We would like to recommend you a self-developed add-in for particularly data cleaning and sorting. Our add-in goes further than the Excel default tool. We have developed it so that text cleaning and consolidation time consuming procedure be faster and easier. If you want to combine multiple workbooks before start, we can help!
If the data is on more than one Excel workbook or worksheet than we perform consolidation. The merging can be done manually or using VBA macro.
We might meet the opposite case when we only need 1-1 data from a workbook or worksheet. In this case we split and fortunately we have an automated solution for this also.
Effective ways to Analyze the Data
We took you through the procedure that converts unprocessed data into data structure capable of building a dashboard. Ask yourself the following questions: Do we have to display all the data at once? Is it necessary to remove some data?
From now on we can use Excel formulas and various methods that help us move forward.
This list at first seems scary and long. But if you look through the additional articles of the blog related to Excel dashboards everything will be much easier. To make a good dashboard you rather need creativity than the knowledge of all the formulas…
Core Dashboard Functions
• INDEX and MATCH combination for complex lookups
• INDEX for dynamic list selection
• SUMIFS, COUNTIFS, AVERAGEIFS to handle multiple
• LARGE and SMALL for sorting
• ROW(S) and COLUMN(S) for indexing
• ROW as unique identifier for lookups
• CHOOSE for flexibility
• TEXT function for formatting
• N function for tracking
• GETPIVOTDATA: extract information efficiently from pivot tables
• INDIRECT for flexible references
• CHOOSE and NAME MANAGER for flexible ranges
Make the Dashboard interactive
If you wish to make a dynamic and user-friendly Excel dashboard there are a lot of spectacular solution at your disposal. In this chapter we’ll show you the tools we very often use.
Use Worksheet Form Controls for a specific task
Excel provide us with many possibilities to make user interface. Use the Developer tab on the ribbon! There are many elements of the drop-down menu we can choose from. With the use of Form Controls we make our dashboards interactive.
Here are the nine Form Controls that are available in Excel:
Button: with the Button control we can easily run the macros we have recorded earlier. In the first step we assign the macro to the button. By clicking on the Form Control the given command will be executed.
Combo Box: The Combo Box control allows users to select the appropriate ones from a drop-down list. From the pre-defined list, the chosen ones will be implemented.
Check Boxes: These types of Form Controls are based on a True / False premise switch. If the check box is clicked its value is True, if not than its value is False.
For Example, if we use four views (CEO, Territory managers, salespersons, partners), then with the help of the check boxes different information will be seen to the given end-user on the dashboard.
Spin Button: With the help of the Spin Button we can increase or decrease the value of a cell in an interactive way. It could be an excellent solution if we have to display the results of a longer period in a space-effective way.
List Box: The use of this control allows users to choose from a list containing pre-defined elements. When the element is chosen the appropriate action is taken.
Option Button: If we have to choose between more options and we prefer the button style control, than this is the perfect solution. Important to know that by choosing one option automatically turns the others off.
Scroll Bar: Best to use when we have to display a large datasheet, but we have limited space to do it in. makes perfect combination with the OFFSET () function.
Label: With the help of it we can create a Text Button relating to one cell.
Group Box: We usually use this when we would like to combine more controls. For example, if we are using more check boxes, it is better to display these in one group box. This way we get a transparent, visually appropriate layout.
Dashboard Toolbox for interactivity
Make your Dashboard more user-friendly and interactive!
Drop-down list: with the use of the drop-down list we can chose elements from a predefined list. It is very useful when for example we would like to portray the efficiency of different departments within one company. It is worth using because when choosing its elements, the dashboard automatically refreshes and likewise the connecting charts. We cannot press it enough times that we save a considerable amount of space that is great value when we create one-page dashboards in Excel.
Data Entry using userform and VBA: Manually input data always carries the possibility of error. Instead use userform and write a short macro for it! With some VBA support we can create a user-friendly panel that is easy to customize.
Pivot tables: are the strongest “weapon” in Excel when we are working with large data sets. It is easy to use with only a few clicks we can summarize data and we can view the report in any chosen structure.
Data Validation: We might want to restrict what users can write in 1-1 cell. Why is this good for us? Just imagine that 10 users in 10 different workbooks write phone numbers. If we do not restrict the format of the phone numbers with the help of data validation when summarizing the workbooks there might be mistakes. For example, we found not allowed characters that we have to correct manually. But if we set a rule than users must use that restricted / fixed format otherwise they get an error message.
Named ranges: We like this a lot for in a matter of seconds we can name a range with any given name. Just highlight a data range and in the name box we write the chosen name: for example: “sales”. Form this point the formulas can be managed easily.
Conditional formatting: If we would like to underline cells based on any mutual condition or rule than the best choice is conditional formatting. Of course, we can use other methods besides colors. We can achieve spectacular results using icons. But we have an array of tools at our disposal: bars, shapes, color scales, indicators and ratings.
Dynamic Charts: These days to use dynamic charts is almost essential. If we would like the chart to automatically refresh upon the user’s choice use this solution.
Shapes: With the use of shapes we can create really unique buttons and menus. Most often applied when navigating between worksheets. We must not forget that functionality is first when creating a dashboard!
Conditional Formatted Shapes: Usually only the experts know this technique. We can format shapes based on a value of a cell. One possible solution is the linked pictures technique. The newer version of conditional formatted shapes requires more serious VBA knowledge but in return the end-result is more spectacular.
Dashboard Widgets: We can call the Excel widgets also special charts but more exact the chart combination term. The creation of them is time consuming but in return we can make the dashboards unique. Here we have the gauge, the thermometer, the traffic light and the score meter charts.
Rollover Hyperlink: To create a hyperlink rollover effect we need a simple macro to change the series based on where user pointed the mouse. The hyperlink cells have a formula which uses hyperlink and the rollover action built in macro. Hovering over the chart area, changes the value of “Actual range” (it’s a named range) and the chart. Impressive!
Maps: Use our free excel maps to visualize geographical information.
Choose the Right Chart for your presentation
Let’s see some useful tip regarding the charts. We will examine what kind of charts we should use in the function of a given project.
Compare Values: Their mutual characteristic is that they show prominent values (high or low) in a simple way. Recommended chart types are: column, mekko, bar, line and bullet charts.
Composition: How can we portray the different sales achievements in different regions? The most appropriate charts for these kinds of tasks are: Pie, Stacked Bar, Mekko, Stacked Column, Area and Waterfall charts.
Analyzing Trends: If we would like to portray the achievement of an examined data set (for example product) in a given time interval / period than use the following charts: Line, Dual-Axis Lind and Column.
Relationships between variables: With the use of Plot, Bubble and Line charts we can show how one variable act / relate to the others. With the above mentioned charts, we can easily portray positive and negative relations also.
Sales Process Tracking: If you need to track the sales process or the conversion rate across a series of pages or steps we strongly recommend using the sales funnel chart.
Show the differences between budget and actual values: the best choice using the variance micro charts.
Performance measurement: Use gauge charts if you want to see how far you are from reaching a goal. It displays a single value.
Generic Charting Tips – Design Best Practices
Column Chart: Use consistent colors and horizontal labels. So, you can to improve readability. Start the y-axis at 0 to properly reflect the values in your graph.
Bar Graph: Start the y-axis at 0 to properly show the values in your graph.
Line Graph: Use solid lines only. Don’t create more than four lines. Let us keep away from visual interferences.
Dual Axis / Combination Charts: It’s very important to select contrasting colors for the two different data sets.
Area Chart: Remove the chart background and use maximum four categories to keep the chart readable.
Stacked Bar Chart: Use contrasting colors for better clarity.
Pie Chart: Don’t draw too many categories! We want to show the differences between slices. Make sure your percentages add up to 100%.
Waterfall chart: We should use contrasting colors to highlight variances in our data sets. It’s nice idea choosing warm colors to show rises and cool colors to show cuts.
How to Create Dashboard in Excel? – Step by step Tutorial
In this comprehensive tutorial we’ll show you how to create a user-friendly dashboard in Excel.
Build a one-page excel dashboard for tracking the actual status of key performance indicators and design a user friendly contextual help system using a VBA macro.
Before we start, we’ll introduce you the most used indicators in our dashboard example.
Using these metrics, we can track and trace the overall service performance.
• Time to answer: this performance dimension usually expressed in second this is the time from when a call is started until it is responded by a customer service agent.
• Abandon Rate of incoming calls: we measure this key performance indicator as a % of the number of callers who are cut off, before they touch an agent who answers their call.
• FCR: Use this formula to calculate First Call Resolution = [issue solved by first call] / [total issues]
Let us see a quick dashboard overview
We’ll put the weekly results by agents (Time to answer, Abandon rate, FCR) into the top left area. Bottom-left section contain the KPI setup.
Part 3 will show the selected period (average of the branch) from the actual week to actual week + 3 months. Finally, the individual variance will be displayed on the bottom-right section.
Detailed instructions to create interactive dashboard in Excel
1. We are divided this model into three separate worksheets for the excel dashboard, the main dashboard sheet, the input section and the calculation area. It is one of the easiest ways to build a clear and structured model.
The implementation of the unprocessed data and calculated value is very easy: The main dashboard worksheet with the all figures is linked directly to the data table.
2. In this example we’ll use weekly basis. Let us see the data worksheet. With fixed duration the calculated KPI is based on a single duration. You must specify the start and end dates – for example from 1 January 2017. to 31 January.
If you specify only a start date the performance indicator will be calculated from the specified date to the current date. If you specify only the end date the KPI will be calculated from the introduction of the process to the specified end date.
3. On the calculation sheet we’re going to use VLOOKUP formula to find values from data sheet by values based on selected week.
4. Check the bottom-left corner! Let us see the metrics. Creating targets that help you determine progress toward your goals. Each cell is linked separately to the calculation sheet.
5. Okay, let us see how we build the top right section of dashboard! This part is a weekly performance scorecard. We get the values from the linked worksheet ‘calc’.
With conditional formatting, we can visually determine when something is breaking a business rule. We’ll use shapes to highlight the variances.
6. Apply conditional formatting for the selected cells.
Execute the following steps:
• Select the given range. (In this case the time to answer row)
• Jump to Home tab and in the Styles group, choose Conditional Formatting.
• Click Icon Sets, Shapes, Traffic lights
• Enter the values using formulas and click OK
7. Repeat the last step and apply conditional formatting for the abandon rate and FCR.
8. A brand new and useful feature available in Office 2016 are Icons. To insert a new icon to improve dashboard’s visual quality find Excel Icons in the Insert tab of the ribbon in the Illustrations group.
This feature is available in subscription versions of Office 365.
9. Choose your favorite picture and put it into the dashboard sheet. There’s a gallery of icons available and they’re grouped into categories to make them easily searchable.
10. To display the results week by week we’ll use spin button form control. Go to Developer tab and click Insert. Click to spin button form control icon.
11. Right click on the Spin Button. The format control pop-up box will appear. Click on ‘Format Control’ tab.
This will open a Format Control dialogue box. Go to ‘Control’ tab, and make the following changes to create dynamic list: Minimum Value: 1, Maximum Value: 48, Incremental Change: 1, Page Change: 10
12. On the cell link box link the cell which contain the actual week’s value. In this example select cell $C$4 on the data sheet. Now validate it using the arrow button.
13. Let’s see the chart section! We’ll track the weekly performance using easy readable charts. To create charts select the $G$5:$G$16 range on the calculation sheet.
Go to Insert Tab on the ribbon and select the Recommended Chart section.
What are recommended charts in Excel? Select the source range in your worksheet and click this button to get a customized set of charts that Excel think will fit best with your data.
14. Go to All Charts menu and select the combo chart. Choose the chart type axis for your data series. Choose the clustered column type for series1 and Line chart type for series2.
Click OK to insert a combination chart.
15. Copy the chart to main dashboard area and clean it up! You should to remove the unnecessary elements. Right Click on the chart and select Format Chart Area.
Use this method on the Plot Area too.
16. First format the bar chart! Right click on the bar chart and choose the Format Data Series option.
On the Series option use these values: 32% for gap width and 0% for Series Overlap. Choose the right color too.
17. As a next step format the line chart! Right click on the line chart and choose the Format Data Series option.
On the Series option use solid line and red color. Finally set the compound type to dash. The average line is done.
18. Repeat Step 13 to Step 17 to create the combo charts for abandon rate and FCR.
19. Here are the formatted charts below:
20. Optional steps to visualize the variance (+/-) by agents on the selected (actual) week. Go to the bottom right corner.
On the Home tab, click the arrow next to Conditional Formatting, and then click Data Bar.
Select a formatting style. In this example we’ll use light red fill for negative variance and light green fill for positive variance.
After you have applied a quick style, select cells or range, click Conditional Formatting on the ribbon, and then click Manage Rules to update the selected rules manually.
Create contextual help using VBA
Now let us see how to add a useful contextual menu to our prepared dashboard. How to boost user experience using interactive excel VBA help system? How to enhance dashboard visualization using Excel tricks?
You will find the answers for these questions after reading the following tutorial. We are looking for a solution for another business issue that can interest many Excel users, dashboard designers or managers.
Just a few words before we start.
Let’s start with that unfortunate case when we work on an Excel dashboard for days, maybe weeks, we believe to rescue the world and at the end there is something just not right.
It’s nice and good, we might calmly relax, but there is such a great volume of data on it that the outside observer (who is our boss in most cases!) will not find his way on it. We can all agree that this is a very problematic situation. How can we help this?
As a solution we will use a little VBA programming, but don’t get discouraged from this you will need only minimal knowledge.
Steps to build a switchable help button in Excel
Step 1: From the Insert menu chose the ‘Shapes’ option and then from the figures of ‘callouts’ insert one that is appropriate for us.
Step 2: To change the default style, select the shape and navigate to Drawing Tools, Format tab on the ribbon. Add your preferred color scheme to shape. It’s important to remove the background.
Step 3: And what about the explanations in the bubbles? We must assign the explanations with the bubbles to enhance dashboard user interface! Insert a TextBox and enter your text.
Repeat this for four times, with this we created the bubbles in which we will place the correct information and explanations.
Step 4: We need to merge the inserted shapes into a single object. Hold the Ctrl button and select the objects. Right click and select Group.
Step 5: On more very important thing is to name the objects. We do this by marking the group containing the four elements and then click in the ‘Name box’ field we name the group, in this case this will be ‘HelpBox’.
Pro Tip for handling the explanation: Select a chosen object then after pressing the ‘=’ key select the cell containing the explanation wanted to be displayed. After pressing Enter the content of the selected cell will appear in the bubble. The assigning is done!
Step 6. Insert a help icon / shape.
Step 7. Write a simple macro, see below:
As you can see on the picture for this reason we have made a very simple little macro, named ‘HelpSwither’. This operates the previously created ‘HelpBox’ object such way, that if you click on the question mark icon calls show / hides the instructions.
Step 8. Finally assign the macro to the inserted icon.
The last step of the procedure is to assign the in / out macro switch to the question mark icon. The program is only a few lines, but it carries out its task perfectly.
This is as follows: with the use of the right click display the properties of the icon (which is a picture) than chose the ‘Assign Macro’ option.
In the appearing window chose the ‘HelpSwitcher’ macro and with a click assign it to the icon. Save the workbook as xlsm format and the procedure is done.
Ultimated Dashboard Tools for Excel
Move your dashboards to the next level using our dashboard and chart add-in. The UDT add-in based on a clean code, you can use stunning charts and widgets to build your own live dashboard. UDT enable managers and users to highlight the most relevant metrics. It’s no visual basic skills or deep Excel knowledge necessary.
Find out how UDT dashboards will help your company make right decisions, ASAP.
Dashboard do’s and most common mistakes
Let’s look at those rules one by one that are a must keep! After this we’ll show you some typical mistakes and technics that are best to be avoided.
Best Practices for dramatically better dashboard visualization
Simplicity is the ultimate sophistication! Remember the goal of the dashboard is to help identify a problem or help to take right decisions.
- Strive for clarity and simplicity: Maximize impact, minimize noise. If it does not add value or serve a purpose, get rid of it. That’s all.
- Focus on creating a narrative: Don’t just show the data, tell a story. Communicate key insights clearly, quickly and powerfully. Make it simple for your readers to choose what to focus on.
- Strike a balance between design and function: Selecting the right chart type is critical. Beautiful is good, functional is better, BOTH is ideal.
- Make it fit on one page! We all know that sometimes less is often more. To replicate business procedures in a clear manner we always have to think in a one-page dashboard! Making larger tables often create problems. When scrolling the names of the lines and columns cannot be seen. In this case use freeze panes that makes navigation easier.
- Display alerts to grab user attention! If we would like to emphasize the changes of an indicator by any viewpoint we can make an attention-grabbing sign for the user. This can be the classical red / yellow / green cell coloring, in other words conditional formatting. First, we have to set value system or pattern. After this is done when the value changes, colors will automatically also change.
- Use well-structured workbook! We have already mentioned the most important rule: split the data onto different sheets (preparation layer, namely dashboard, calculation and unprocessed data). So, we definitely will not get last in the sea of numbers.
- Apply grid layouts and group items using layout containers. If we do this, we can handle views and objects together. When the user initiates an interaction, the dashboards wouldn’t show useless empty spaces.
- Create easily printable spreadsheets! We have to define Print Area beforehand, so we don’t get a surprise when wanting to print. With the proper set of layout and orientation we can be sure that the end-result will be easily transparent in an offline form.
- Create well-documented dashboard. We don’t use the cell comments usually used in Excel, but an interactive help system. This is a lot more flexible as you will see at the end phase of today’s tutorial.
What should not to do with Dashboards
• Avoid using three dimensional charts! We hope this is evident to everyone. These are hard to interpret and distort data. Just forget about them!
• Incorrectly using red & green colors. When we display the efficiency of a product with red color. For example, on a sales column chart we automatically think this was the least efficient product. Why? We identify the red color with the poorest achievements. But it might be the best based on the numbers. Displaying red and green on one chart is also not the best choice in case of colorblind users. If we prefer this combination we should use chart types that separates the colors indefinitely. These types are like the traffic light widget or gauge.
• Don’t clutter the dashboard view with too many metrics. There is nothing worst when the essence virtually can’t be seen. Information often gets lost between too many charts or widgets. Instead use the drill-down technique if the number of indicators call for this.
• Clean up and secure the final dashboard! It doesn’t matter if we use shared drive to present the dashboard or we send the presentation in e-mail, we have to pursue the smallest file size. Make a backup and remove all unnecessary worksheets and raw data. If necessary, set a password to the workbook.
Various Types of Dashboards – Free Downloads
In this chapter we introduce the frequently used dashboard types and their fields of application.
First of all we have to say a few words about this unusual article for it is a little bit different from the usual tutorial. We don’t only talk about dashboards theoretically but we also create them. And they are free for you to download. Let’s see a little selection of the best ones!
Sales Dashboards and Sales Tracking Templates: When we talk about sales than we can approach the task from a lot of different directions. The main thing is that we have to monitor / track all the activities and procedures that can influence the sales. We can make simple forecasts with the help of sales-forecast charts. But we rather use more dynamic dashboards that demonstrate the development of KPIs.
- Create Interactive Sales Dashboard – Sales activity template
- Use drop-down list and OFFSET – How to create sales template?
- There are two things in focus, net sales and profit – Sales Tracker
- Advanced use of form controls – Dynamic sales template
- Simple and easy forecasting solution – Sales Forecast Chart
HR Dashboard: Our objective is to keep an eye on the operation of the HR department. We can discover varied trends and we can gain advantage by early recognizing surfacing problems. So we can keep their impacts on the business on a healthy level. We also have to pay attention to risks, for example risk lays in the leave of an experienced professional. But this is only one of many of them. Highly emphasized are the following: hiring and on-boarding, employee programs and expenditure.
- HR analytics Dashboard – Keep an eye on the human resources of the company
- HR Scorecard – HR template based on gauge charts
Project Management Dashboard: The most important target is project tracking. There are many diverse tools at our disposal to support this main goal. In Excel we have modeled a software development project and complemented it with some decision supporting tools. The most important ones are: monitoring risk, shaping a work breakdown structure, issue tracking and creation of a gantt chart. We already have the framework. Below you can find some ready-to-use project dashboards!
- Risk Template – How to track and analyze project risks?
- Project Activity Dashboard – Plan and schedule of the resources using Excel
- PM Dashboard – Track a software development project
- Project Status Dashboard – Create a WBS using Excel
- Issue Tracker Dashboard – How to track and analyze the project related issues?
- Project Timeline Dashboard – Create milestones to support decision making!
Interactive solutions and VBA Dashboards: What is VBA good for? How can we construct user-friendly interface with it that increase user experience? Dynamic charts and dashboards make users life easier and simpler. If you would like to know more about the word of Visual Basic for Application and Form Controls don’t hesitate, download the training materials at the end of the articles!
- Scrolling Dashboard Tutorial – Use a slider to create space saving solution
- Rolling 6 months Dashboard – Complex template using form controls and OFFSET
- Rollover Hyperlink Dashboard – Create a nice rollovers using hyperlinks
Conclusion and Final Words
Last but not least, let us see our answer from the general question.
I’m an Excel newbie, how to create a dashboard?
First and foremost for you is to learn. If you already feel comfortable on the fields of basic formulas and form controls than have a go at it. If you are sure of the basics, try to learn the more complex formulas and VBA programming. With the help of VBA you can make your dashboard dynamic! In sum we recommend that you go step by step and success will follow.
Additional resources and step-by-step tutorials:
- All about key performance indicators
- How to create Gauge chart?