Welcome to Excel Dashboard School! Check our blog!
Excel Dashboard Training
Excel Dashboard is a powerful tool to visualize information. Most of all we will help you if you want to create powerful reports and analysis. Our Training will show you, how to build from a clean white spreadsheet to a totally efficient report.
A great looking excel dashboard can radically improve the way you use and get sense of your information. Generally, use the best techniques and visualization guidelines when creating your reports.
If you need more details check out our free templates section.
We have started our blog 4 years ago. We are in such fortunate position that we can do in full time what we love to do. Follow closely all the notes of the training and you’ll get acquainted with a lot of unique methods!
Within a few weeks you will be able to use all technics in expert level because our training is very efficient.
What are Excel dashboards? – Fundamentals
First of all tools capable of data visualization! Besides this they show the status of the key performance indicators (KPIs). Furthermore important factor to consolidate and organize data on one single worksheet.
How should we start creating them? It’s seems like easy. The practical rule is that there is no rule! The sky is the limit! What we really need is creativity. There are endless possibilities at our disposal, you will see this when reading our articles. On a more serious note, let’s see some advice worth to keep in mind!
General Dashboard rules, advices and tips
- Focus on the essence! The basis of every dashboard is the one-page-layout. Keep it in mind that the user doesn’t always interested in the details. A CEO is usually always busy, having just enough time to look through some KPIs. While the business analyst has to see even the smallest details.
- Before you effectively start creating a dashboard here are some useful tips and advices:
KISS! Keep it simple, sparse. Very important is that we appropriately apply this rule! Don’t jam the dashboard full of unusable charts. This only distracts attention from the essence!
- User-friendly interface. What we measure is not the only important thing. Representation is also important. When you create a dashboard you have to think about other also have to understand it besides you.
- Add alert for KPIs. Apply varied tools! Some great technics are: gauge charts, traffic light widgets, conditional formatting, custom shapes, bullet charts.
- Ability to pull real-time data from several bases. The most frequent data sources are the following: online and offline database, OLAP cubes, pivot tables, text files, internal and external data sources.
- Logical structure behind the scenes: most of the mistakes can be filtered out if we work with well-structured data bases and data tables. Mostly we use three worksheets for the creation of all dashboards. The first one contains the raw data tables. On the second one we make the calculations. Finally on the third one we display the final excel dashboard
- Displays actual data, forecast or key trends – key performance indicators. The KPI is such a huge subject! Therefore, we devote a complete chapter for the creation of KPI dashboards.
Before create your first Dashboard in Excel
- Do we know the sources of the information? Where is the dashboard “feed” from? Automated updates are necessary or is it enough to send weekly reports? If we know the answer to all these questions than it will be easy for us. If we do not than we have to find out every little detail by a preliminary survey.
- How many key performance indicators are needed? It is very important that you exactly know. If the goal is to track only 5-10 KPIs than it is sensible to use gauge chart. Most of the companies will need a lot more than that. The gauge chart is incredible tool, but it could be more efficient. If the goal is the tracking of more than 10 KPIs than the best choice is the bullet chart.
- What should be the format of the final dashboard? Will we have to share our information through intranet or only share it with a few managers? We have to pay attention to set authorizations. The dashboard should always be a read-only! Just imagine where would it lead if somebody opens the file, modifies it than forward it in e-mail. I don’t even dare to think…
- Compatibility first! Remember that different version of Excel have different function. If the file was created with an Excel 2016 and someone wants to open it with an Excel 2007 will not come anything good out of it.
Frequently asked questions regarding dashboards
Q: How do I chose the appropriate chart?
A: This always depends on the given project. Think about what you want to visualize. In the newer versions of Excel you can use the Recommended charts” function.
Q: Which is the best solution for creating a KPI dashboard?
A: Good question! This depends on the number of KPIs necessary. If you only have to measure a few of them than gauge chart is the best. If there are more of them we recommend the bullet chart.
Q: How to track and visualize budget vs actual performance?
A: By all means use a variance chart but as an alternative the bullet chart is suitable.
Q: I am not a dashboard designer, but would like to create splendid presentations. What should I do?
A: Read on! More of our free tools support beginner users also.
Q: Is it rewarding to use a BI dashboard?
A: If the classical Excel dashboard is not enough than yes. The Excel Power BI was created specifically for cases like this!
How to create a stunning dashboard in Excel?
A lot more data and lesser and lesser time. Doesn’t this sound familiar?
In this chapter we’ll talk about the creation of classical dashboards.
Build Your First Excel Dashboard Now!
The Excel Dashboard Tutorial is one of our oldest article but still timely. Are you working with large data tables but you only have to display a fraction of them?
This is what the OFFSET formula is for. The basic rule for the presentation is for it to be a one-page-layout. With a help of one slider and the formula we can create a scrolling list. Please take a closer look at it!
Excel uses several lookup actions. Most known is the VLOOKUP. Particularly effective the joint use of the INDEX and MATCH formulas. Which formula should we prefer? In a large data table how can we find the necessary information?
One of the most liked technic is conditional formatting. The traffic light report is a versatile tool and the creation of it is not at all difficult. The traffic light has three statuses. The three different colors indicate (red, yellow and green) that the operation of a given indicator is acceptable or not.
We apply a new method besides the VLOOKUP and SUMIF formulas. The REPT is a rarely used function. It is time you get closer acquainted with it. The first parameter marks the character that we will repeat. The second parameter is a number. It will draw the chosen character into one single cell as many times as the value of the second parameter.
This letter one is an important characteristics of the REPT formula. We will create a mini chart with the use of one single cell!
We’ll make specific graphics! HR is a company field that is very seldom talked about (undeservedly). It is an expectation form the HR manager to keep his / her eye on the human resources of the company. The only thing you have to do is to review the operation of the Excel dashboard!
Customer service is a marketing field where we have to make quick decisions so we can intervene on time. Probably the planning time for the first bigger dashboard project was almost longer than the actual creation. Two drop-down lists and we can use a spectacular data visualization.
This is enough to show you each and every little detail! We note here for the sake of interest that the Excel dashboard can process a huge data set. In the report there are data for more than 8000 outgoing calls!
The template contains of two main parts. The first one shows an overview of the marketing results of a one year period in monthly breakdown. In the first round we have to find the minimum and maximum values of the given time-period. Then we mark the two values with red and green color on the dynamic line chart.
Worth to take your time to pay attention to some small things. One of the most important rule of data visualization is that the less is always more! We have created the Excel dashboard so that you can customize it quickly.
Create Sales Reports
When you are in sales, you cannot guarantee that all will be tracked from time to time. Today we will show you step by step how to create an advanced sales tracking template.
Maybe the following is the most frequent subject of sales reports. How can we make such sales report that displays the results of a given time period?
At almost every company this is the main expectation. The most notable characteristics of the template is that we work with cumulated data. If you chose for example the month of June you will see the achievements in the time from January until June. Practically you continuously can track the variation of the plan / actual indicator.
The next business spreadsheet is one of our favorites in the subject! Practically this is the most complex sales dashboard we have ever created. We have several filter options at our disposal. The first is the year, the second is the region and the third one is the sales-personal.
We have examined a three year cycle. Furthermore the data table contains three regions and seven sales-personal. We portray the highlighted indicators on spectacular charts. These are the volume of net marketing and the variation of the profit.
At this resolution the excel dashboard design has a considerable role! We have divided the report into four parts. In addition each and every part provides information form a different angle.
Create Dynamic Excel Dashboards
It’s a crucial criteria in designing an excel dashboard that is should support valuable business decisions. The interactive window on the right hand site is the most valuable part of the product metrics dashboard.
For the chosen product we display the most important indicators on an interactive chart.
We visualize the results for the marketing related to 15 different products. We display trends and graphical elements so the report will be smarter.
Sparklines is one of Excel’s most popular charts. It is excellent for displaying trends! For further details click here. We use the gauge chart here for the first time. We’ll learn a lot about that later!
Are you a website owner? Would you like to take a look under the hood? We can help you because there are work-ready templates waiting for you. With the help of conditional formatting we create dynamic reports.
The SEO analytics dashboard capable to give a real picture about the operation of a website in the form of various indicators. We can make the appropriate decisions based on the indicators listed in the tables. With the help of these we can positively influence the operation and traffic of a website.
How to create a simple dashboard that allows quick analysis? The excel HR dashboard will compare the annual performance of co-workers listed in the first column in quarterly breakdown. We will collate the planned and the actual index.
Excel Risk Template Dashboard – In this article I’ll show you how to build Excel risk template. We used an exclusive ID to categorize the issue in the tracker. The Description of Issue column should be populated with a description of the issue. The priority column should be populated with the priority of the issue.
Select from categories as defined by the risk management plan. Effective choices include the following: High, Medium and Low.
Advanced tricks to create awesome data visualization
We use animation when the task is so unique that we don’t get along with the known methods. Let’s see what kind of data we will use and what will be the concept!
We examine the marketing indicators of three products.
We compare the considerable amount of data with an animated VBA chart. Why do we need this? Most of all because it is impossible to display the daily data of a four year cycle on a static chart.
Why is this Excel dashboard particular? With the help of the dynamic chart we can portray a longer time-period by daily breakdown. And portrayal cannot go to the expense of understanding. This rule is worth to learn well!
Rollover solutions can be useful to present instant info to the customer or user. No longer need to stress, all you have to do is build the custom function yourself. We will help you to sharpen your data visualization techniques. Using a small VBA code we can create a dynamic sales performance chart.
Rolling 6 Months Dashboard with flexible design can be a perfect solution for you if you want to display huge amount of data. Just put in a slider (scroll bar) so it will only show the selected 6 months.
Excel provides for you a built-in control to manage time series. By moving the slider to the right and to the left simultaneously the contents of the list continually changes and contains only the actually chosen 6 months. This is how we make our dashboard dynamic.
Custom chart tools and pro add-ins
Design a better chart in Excel using Gauges! Using speedometers for management reports and BI dashboards is a smart decision for smart Excel users.
The Gauge Marker Pro is an excellent Excel add-in. If the goal is the creation of a KPI dashboard then this practically is essential. Have you met the key performance indicators (KPI) for the first time? No problem!
You can acquire the knowledge in merely moments. You can also have a great use of it as a developer. Time is essential! The add-in makes the handling of KPIs simple. More serious challenge? We have a perfect solution for this also.
As a result of the unique chart types and real time widgets Ultimate Dashboard Tools is one of the most successful product on the market.
Are you interested in professional solutions? Most of all we are strongly recommend you this add-in! Finally here’s some of the really unique charts: waterfall chart, variance chart, Gantt chart, Tree map chart and advanced bullet chart tool.
Dashboard Developing and Chart Design
Boost your Excel Dashboards! All right, the task is given! But how should we start?
When an idea is born in our heads there are two possible ways to go about it. In the first scenario we immediately get to work and neglect the planning part. This never leads to nowhere. In the second case we carefully plan the design and the necessary steps.
What do you think which one is the more productive in the long run? In our opinion the planning phase should take up 70% of the whole project. The remaining 30% is needed for the implementation. Mistakes are a lot less if we advance step by step.
How can we make the planning more user friendly? We have the perfect template for this and it is called Dashboard Developer Tools.
Smart VBA tools and custom shapes
Our first article searches the answer to a very important question. How can we elevate the user experience? We have to do it in such way that will not make the dashboard over crowded.
We need small and smart VBA codes. Even if you see such things for the first time you will understand them. We can save a lot of time if we support our reports with user guides. What is this mean? It is a solution that applies an environment sensitive responsive help system.
We creating Excel dashboards for over 10 years. One of the most time-consuming procedure is the planning of a one-page-layout. Either you are a beginner or a power user this customization pack will be a great help.
Link your Infographic to your data!
Using our customization pack you can immediately add special elements to your excel dashboards and reports. You can apply the standard Excel tools.
Move, resize or delete specific shapes; swap fill color or font color; change shadow settings or apply gradients. Finally you can also link shapes to selected cells.
Excel Dashboard & Infographics – In focus: the content! A good infographic can tell everything a given subject. We recommend you use Excel shape objects and special font types. Think about how you would make the data attractive!
Free Excel Dashboard Widgets
What is dashboard widget? We hear and talk a lot about it in our everyday Excel work. Maybe many don’t know what this phrase exactly means. Widgets are supplements, mini applications that Excel does not include as a default.
Do you need new ideas? From gauge chart it is never enough! The quarter style widget is not too frequent but can be excellently use. Just imagine that you have created the dashboard and you notice at the end that you run out of place on the worksheet.
This type of widget is really space saving this is why it can be applied in every situation. You can download three more gauge widgets from here!
Finally let’s see a little surprise. With the help of a traffic light widget we can make demanding balanced scorecard templates in the matter of a few minutes.
KPI Dashboards and Gauge Charts
The all-time classic! Everything wants KPI dashboards! These days the BI systems have dashboard support. With their help we can create KPI dashboards in which we can place our most important indicators, the KPIs. A KPI can have a goal which is the exact value that it should reach, and ranges for tracing purpose.
The business world for some time now is heading in the direction that we weigh every possible thing. To quote a classic phrase: we weigh it, it gets better. So we got the idea for the key performance indicator from here. And how true is the before quoted sentence!
Build pro excel kpi dashboards from scratch! There can be mapped multiple departments and significant related business process within a company. These days we can apply 50+ indicators at a moderate sized company. We would like to show everybody the world of performance measuring with the help of the articles of the KPI category.
Excel KPI dashboard can be effective tools in reaching business goals. Was this enough reading for you? Excel dashboard templates allow you to see trends and key performance indicators. It’s an awesome tool for creating visual reports. The goal of this chapter is to show you how to build better reports. As is usual KPI can be linked to three different statuses.
If the indicator is red, we know at once that something is not going well with the examined process and we have to intervene right away. The yellow sign alerts us that – although we are not too far from the plan – the reviewed procedure, in our case the marketing, needs increased focus. The green light needs no explanation when we see this on the dashboard than all is in the best order.
Ready to use KPI Dashboard Templates
How to visualize data on a human map? We try a whole new idea with our first Healthcare KPI dashboard! Our goal is tracking and analyzing the most important healthcare quality information of human body. We’ll use an exclusive visual design and style which helps you to identify the key performance indicators and the overall performance.
We divided the male and female shapes into nine shape groups. The first 8 slicer display the health of a body part. The last one visualize the overall health status. Effective tool is the value based color mapping. We have created the dynamic Excel maps with this method! You can find all necessary information on the calc worksheet.
KPI Tracking Dashboard is a metro UI style template. Namely this is an Excel add-in where conditional formatting function finally expands. No experience needed to use the add-in. Fortunately it automatically does all the calculation tasks.
What is the clearly and simply method? Your task is only to define the limits of the key performance indicators. Set the arbitrarily picked color and value zones and your dashboard is done! A VBA code ensures that when the values of the cells change the elements of the dashboard also change dynamically.
How to create Gauge charts?
We show you from the beginning how to create a gauge chart. We can only tell that you will find easy and complicated amongst them.
The Speedometer guarantees a spectacular visualization for performance display of marketing workers. With Excel there are more solutions to create a complex speedometer dashboard.
In this tutorial we introduce a variance built up by the combination of a pie and donut chart. Completed with the conditional formatting function we can use it to measure plan / actual values also.
We would like to show you a brand new type of chart. The Dual gauge chart is one of our most popular Excel dashboard templates, downloadable for free! It brakes ties with traditional visualization. Above this it spectacularly displays the variation of plan / actual values. It is not a secret that Excel gurus prefer the speedometer stile implementations.
We have created a unique never before published dashboard. We hope this will convince everybody that the use of gauge charts are essential.
Additional Resources on Dashboards
The sales KPI dashboard makes it possible for us to examine more product simultaneously. It’s most important function to summarize and display the results. Using a little tricky solution we can simply list the best and the worst effective salesman. With the help of the excel dashboard it is easy to give feedbacks to the colleagues.
With the making of the Customer KPI dashboard we have tested the limits of Excel. It contains everything that we have talked about so far. The Main Objective is building a one-page Excel KPI dashboard for tracking the actual status of key performance indicators.
Human Resources KPI Scorecard using typical HR KPI metrics and excel gauges. We have chosen the following elements so that the template can be well applied in real marketing life.
• Employee turnover,
• Training and Development.
You can find all the information for the basis of creating the dashboard on the Excel dashboard HR scorecard worksheet. In the KPI column appears the four basic indicators already noted. For 1-1 KPI we have assigned 3-4 subcategories. These reflect well the indicators used in the field of HR.
Project Management Dashboards
Visualization is an easy way to present project status to clients or management, without sharing the details of project.
The project activity excel dashboard will show one of the most crucial field of the project management. It approaches the planning and scheduling phase from a brand new viewpoint. What does the phrase project activity dashboard mean?
Relating with a project we use various resources. These can be the following: materials and tools for manufacturing and last but not least, human resources.
The dashboard contains a classical summarizing analysis. On the “data” worksheet you can find the well-known basic tables / charts: name, activity, responsible person and the starting and ending dates. We examine 15 marketing procedures all together. Associated with this are 15 project members. We have to measure all of the procedure and all of the activities bringing it to effect.
It is easy to switch between Projects and Human resources views. For this purpose we have created excellent little switches. Let’s see how the compounded and parallel tasks effect the whole marketing process!
Each and every project can be joined by several sub-tasks. When we summarize these will be clear that how many activity congested in one given time period. Important here that this information serve as a kind of alarm for the management.
The most important question is this. What is the Gantt chart? Why advisable to use it? The Gantt chart carries a double function. For one we can display each and every task of the project with it. So it gives an excellent overview. For second the project manager can trace the current status of the project.
The Gantt chart is the first part of the Issue Tracker Dashboard.
Its second part is the gauge and we use it now to track the overall progress. The human resource heat map shows the development of resources with the help of conditional formatting.
How to manage multiple projects in Excel?
The project management excel dashboard analyzes procedures in the software development. There will be very interesting topics discussed because software development isn’t that we design a product and within two weeks we develop it without any arising issues. There is no such thing! We effectively support the work of the developers with the application of an Excel project dashboard.
We did not have a hard time with the development of the project status template. We’ve built in the traffic lights already used in the KPI topic. We can use also traffic lights to visualize the popularity of products further to evaluate the project status. We’ll show you how to create a great looking project dashboard in Excel from scratch.
A project timeline dashboard helps you visualize complex information using easy-to-understand methods. You can keep an eye on projects. Try to imagine that from now on we don’t make a list-like form of the to-do list of the project.
We use an easy to make chart for this purpose. Milestones indicate critical project goals and objectives. Use our dashboard as a simpler way to show, schedule and report your project status!