Create Excel Dashboard

Our free excel dashboard guide might be just what you need! We will show you, how to build from a clean white spreadsheet to a totally efficient report from the ground up.

No, it’s not a usual tutorial. We’ll give you a complete dashboard framework using Microsoft Excel. We divided the training into 4 parts:

1. As first you’ll learn all what you need to know about excel dashboard.
2. Next, check the dashboard training (comprehensive guide).
3. After that use our free tools to create better dashboard.
4. Finally, download top 5 best hand-picked free excel dashboards.

Easy and simply learning curve, isn’t?

Above all we will share all tips, tricks and secrets regarding excel dashboard for basic and advanced users too. Furthermore, we will answer the most frequently asked questions. We not only going to learn about dashboards but will detail every relevant step that connect with the main workflow. This helps you to quickly understand the anatomy of excel dashboard.

Don’t forget: Simplicity is the ultimate sophistication.

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 this step-by-step tutorial. Everyone can easily learn the basics of dashboards. Of course, we can feel fortunate because finally we do the things we really like.

Learn and play with these downloadable excel dashboards! You will get eye-catching solutions to build something new, something special. That’s the point.

What is a dashboard?

create-excel-dashboard-how-to

What are dashboards actually? For sure that they are not only represent visual data! When you look at a dashboard it is obvious that you not only interested in real-time performances.

But dashboards are rather information that pose us questions. How are our things going? Is there any factor you should be alarmed about? How will you explain your boss the causes of increased profit? A well-structured excel dashboard will give suitable answers to these key questions, and much more!

Practically our goal is to clean, organize, manage and show the huge data accumulated by the company. Further standpoint is to present the organized data in an easily understandable format. Under the hood there are complex data. You can dig further down in them by drill-down method. And most importantly that this happens in mere seconds.

Probably we can call the dashboard the central brain or engine of the company or department. It’s necessary to show 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.

General rules and guidelines for effective dashboard design

excel dashboard effective design

Before you start to create excel dashboard the one of most important tasks is dashboard design. It’s not as easy as we might think. We’d like to help you in the planning phase also. Finally, here are the ground rules. Keep in your mind these dashboard fundamentals!

Start with users, not the data: The best way to start is to understand the objectives and motivations of end-users. If you can realize this for sure you will create a dashboard in Excel that is most useful for them.

What is this all mean in practice? Go and talk to the users! Keep calm, sit down and try to understand exactly the business requirements. Create a dashboard that is not in constant need of updates! Because of this you can dramatically cut development costs.

Don’t flood the user with unwanted information: Do not flood the user with unnecessary data! You should seek that the dashboard be truly useful for them. You can even create such custom views. Custom views and filtering data enable to display relevant information only for that 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 provides a clear overview of the key information and leading news there are within. If one wants to look at an information in detail must know where to navigate. Allow users to see the most important information. Drill-down into the details if they want to find out more!

Use visualizations and create a clean, uncluttered dashboard: Charting possibilities in Excel are practically endless. That’s where data visualization comes into play. Use the classical chart types if you want. 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: Dynamic charts are usually a better idea than pie charts. Avoid over the top visualizations and graphics, such as exploding pie charts and stacked bar charts. Remove unnecessary components from the dashboard because they distract the attention from the essence. You have no need for overcrowded and jammed with useless charts screen.

Create clean Structure and easy navigation: Create a detailed and well thought out menu structure. Your 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. Try to 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 you seldom use an indicator?

You will see that in these and like cases contextual help is helpful. We’ll show you the logical link between contextual help and dashboard in our step-by-step tutorial.

How to create a dashboard in Excel? – Step by step Tutorial

Our primary goal is to show a comparative view of the performance using interactive visual controls in Excel. In this tutorial, I will show you how to create a dynamic excel dashboard step by step using form controls, advanced formulas and conditional formatting. Let us see how the result looks like:

create-excel-dashboard-initial-view-01

Explanation: The final dashboard screen is separated into three parts. On the left side we’ll display a sorted list with switchable radio buttons between quarters. The middle section is responsible for the real time results (display the top 5 / bottom 5 states by sales).

The right area of dashboard is important too. Set up your targets first! You will prepare the source of the conditional formatting’s rules using this table.

This dynamic spreadsheet let us check the performance by states during a given period. Using conditional formatting you can analyze the high and low differences easily. You can rank the highest and poorest performing states.

Plan the dashboard layout and create proper draft

You should never start creating a dashboard without planning. If you make mistakes in the planning phase you can only correct them with disproportional effort later. Your dashboard and main indicators are useless if it doesn’t get communicated properly.

You have many possibilities to create proper layout. 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.

We are using Excel to create ready-to-use chart and form control elements with shapes. It’s easy to create the initial layout of your Excel Dashboard or report. Hereby you get to see how your draft version could potentially look in an Excel spreadsheet. It helps you organize each section. You can decide on what types of dashboard elements (charts, form controls) you’ll need in the planning phase.

Here is the layout of our dashboard:

create excel dashboard proper layout

The good thing about vector elements is that they are flexible and reusable. You can change the color of the tree map chart for example. Furthermore, you can freely customize the text in the titles and change the background color of the boxes.

This helps you customize and 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 the layout using chart and form control mockups. Our dashboard layout is ready we’ll step forward.

Connect to a CSV file using Power Query – External Data Sources

The most common case is that you don’t store to be processed data in Excel. You can import it from an external data source. Let’s see an example of data import in Excel. Use Excel’s Get & Transform function (with help of Power Query) to connect to a Comma Separated Values (.csv) file. Using Power Query editor, we can get and transform our data easily. Our data is stored in a csv file. To import data from a file, do the following steps.

  1. Click on the Data tab on the ribbon, choose ‘Get Data’ > ‘From Text / CSV’ > ‘From File’ > ‘From Text/CSV’

create excel dashboard import csv 01

2. In the Import data dialog box, locate the file that you want to query. Click Import.

create excel dashboard import csv 02

3. If you are working with a CSV file, Power Query will automatically detect column delimiters. One more important thing! Power Query automatically uses the first row as the column names and changes each column data type.

create excel dashboard import csv 03

4. If you want to edit your data ‘on the fly’ click to edit. A Power Query Editor will be appeared.

 

create excel dashboard power query editor import

5. Finally click Close and Load. Excel puts the external data range in the location that you specify. Our data transformed into a table. Why it’s so important? We’ll answer this question on the next chapter.

Explanation of Get and Transform function: If you use this function in Excel, you create a query in your workbook. This query enables you to manage data using various external data sources. If you want to refresh your data and keep your excel dashboard always on use the built-in Data Model in Excel. Using data connectors (for example ODBC) seems to be a rational decision when raw data is not stored in Excel.

Convert tabular format into excel table – Internal Data Sources

In this phase you don’t have cleaned input, but you already have data on a worksheet. What will be the next step?

First, you must check if the necessary information is in tabular format. Tabular format means that every data, data point lives in one cell. For example, the name of city, address or phone number. If the data is in tabular format you should convert it into Excel table. Using Excel tables provides us with many advantages.

If you work with Excel table, you can avoid a huge amount of trouble. For example, if the scenario changes further down the line and you need more data than you can simply expand the table and will not lose the formulas. A very useful tool indeed to use!

To convert tabular data into an Excel table, follow these simple steps: Select the data then on the Insert Tab choose Table or press Ctrl + T.

If our data table has headers we need to check the box ‘My Table has headers’. In case your data doesn’t have headers, Excel will automatically insert headers into the first row.

how to create excel dashboard table

Explanation: What kind of benefits do you get by using Excel table when creating a dashboard? Instead of the cell references for example ‘A1:B340’, use the names, in this case. It is a simpler and more transparent to refer to a range like ‘Products’ instead of writing in the long cell reference. When your tabular data is converted into Excel Table, your data would look as shown below. Data filters are enabled on the header.

Prepare, Consolidate and Clean Raw Data

After the data is in Excel you can start the data cleansing process. There are many awesome cleansing tricks, although we often forget about this obvious method. Some effective ways to clean 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 our free excel add-in for cleaning and sorting raw data. We have developed it so that text cleaning and workbook 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.

Create the ideal Workbook and Worksheet structure

How can you create a consistent workbook structure? What is this mean exactly? Open a workbook and create three sheets. Let us see the parts of the workbook structure:

create-excel-dashboard-ideal-worksheet-structure

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 you reach the result, your dashboard.

After a few preliminary remarks, we turn back to the detailed tutorial.

Analyze data and build the dashboard

We took you through the procedure that converts unprocessed data into data structure capable of building a dashboard.

Ask yourself the following questions: Do you have to display all the data at once? Is it necessary to remove some data? From now on you 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…

We’ll use these core Excel Dashboard Functions:

  • MATCH
  • OFFSET
  • LARGE and SMALL for sorting
  • CHOOSE for flexibility
  • CHOOSE and NAME MANAGER for flexible ranges

Now let us see the how to build the dashboard in Excel!

Step 1: We have the raw data for US states and for 4 quarters. The initial data set looks as shown below.

create-excel-dashboard-data-tables-02

Step 2: Use the name manager to create named ranges. Select the cell or range! In this example we will use both.

Select the =data!$E$10:$E$109 range on the data worksheet.

Go to Formulas tab!

In the Defined Names group, click Define Name. Enter a Name (main_menu) for the range.
Now select cell E6 and add enter a name. (product_sort_order) Let us see the results on the picture below!

create-excel-dashboard-named-range-03

Step 3: Use the OFFSET function to build a dynamic named range for further calculations. The OFFSET function helps to determine the ranking and grouping as soon as possible.

On the calculations sheet type:=OFFSET(data!$E10,0,product_sort_order).

Explanation: How to get the exact name from the unsorted list? OFFSET(name of the states from data table; 0 = same row; 1st position)

create-excel-dashboard-offset-04

Step 4: To create unique list we’ll apply a small formula trick. Use the =E10+$C10 / 1000000000 formula to create unique list!

create-excel-dashboard-unique-sort-05

Step 5: Now we will show you how to get the proper value in a range. The solution is simple: you can use the LARGE and SMALL function together with the CHOOSE function.

=CHOOSE($E$7;LARGE($F$10:$F$109,$C10);SMALL($F$10:$F$109,$C10))

The CHOOSE formula returns a value from a list using a given position or index. For example, CHOOSE(2,”data1″,”data2″,”data3″) returns “data2”, since data2 is the 2nd value listed after the index number.

The LARGE and SMALL functions get the smallest and largest numbers from a range. They can help you find the third smallest, second largest, and so on. Both retrieves numeric values based on their position in a list when sorted by value.

create-excel-dashboard-sorted-list-06

Step 6: You should apply the MATCH function to get the position of an item in an array. Here is the formula below: =MATCH(G10,$F$10:$F$109,0)

create-excel-dashboard-using-match-07

Step 7: The next step to calculate the first position for Q1. Use the following formula: OFFSET(data!F$9,$H10,0)

create-excel-dashboard-offset-final-08

How to 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.

We’ll use Worksheet Form Controls for a specific task. Excel provide us with many possibilities to make user interface. Add and use the Developer tab on the ribbon! There are many elements of the drop-down menu you can choose from. With the use of Form Controls you make your own excel dashboards interactive.

dashboard-excel-windows-form-controls

In this example we’ll use Option buttons and Scroll Bars. Just a few works about them:

Option Button: If you 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.

Tip: Here are the complete list of Form Controls that are available in Excel: Button, Combo Box, Check Boxes, Spin Button, List Box, Label and Group Box.

Okay, back to the dashboard!

Step 8: Jump to the dashboard worksheet! Interactive controls offer flexibility. You can insert option buttons to make the navigation between periods clutter free.

Because we have just one choice (from the Q1 – Q4 list) Option buttons are perfect.
To add option button in Excel, click the Developer tab first, then click Insert. Finally, click option button icon under Form Controls.

excel dashboard insert radio buttons

Step 9: To activate radio buttons, it is necessary to create a connection between the form control and the cell in the worksheet. Right-click the option button, and then click Format Control to create a linked cell.

Go to the Control tab. In the Cell link box, enter a cell reference that contains the current state of the option button. Use the same linked cell (E6 on the calculation sheet) for Quarter 1 to Quarter 4.

create-excel-dashboard-link-to-button-10

Step 10: You can improve the dashboard’s visual quality using icons. Choose the Insert tab on the ribbon. You can find the Icons section in the Illustrations group. A brand new and useful feature available in available in subscription versions of Office 365.

excel dashboard icon interface ribbon

Step 11: Choose your preferred visual element and put it into the worksheet. There’s a sorted gallery of icons available. Furthermore, they’re grouped into types to make them easily searchable.

create excel dashboard insert business icons

Step 12: Right-click on the picture then choose the Assign Macro command from the drop-down list. Add a macro from the list.

create-excel-dashboard-assign-macro-13

Step 13: The Macro is very simple. It’s not necessarily further explanation. To insert the macro, you need to create a module. VBA is a great framework to create interactions between form controls and the excel dashboard.

Press ALT + F11. This shortcut opens the Visual Basic Editor. Right click on the name of your workbook and insert a new module.

create-excel-dashboard-vba-editor-14

Insert your small code then exit from the editor.

create-excel-dashboard-insert-code-15

Step 14: Within the next step we’ll explain how to combine the conditional formatting tool with the target values.

create-excel-dashboard-conditional-logic-16

Step 15: You can use the scroll bar form control and OFFSET combination in such situations where you only want to display parts of a large and complex data table. Try to place the separate elements to fit them on one worksheet.

To add a scroll bar, do the following: On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Scroll bar.

excel dashboard insert scroll bars asap

Step 16: Open the Format Control dialogue box: right click on the Scroll bar and Click on the Format Control. Go to Control tab and set up the parameters to create a dynamic list for your dashboard.

  • Current Value: 1
  • Minimum Value: 1
  • Maximum Value: 40
  • Incremental Change: 1
  • Page Change: 10
  • Cell Link: $E$5 on the calculation sheet.

create-excel-dashboard-format-control-20

Step 17: How to apply conditional formatting for the selected cells? Select the range first and highlight patterns and trends with conditional formatting.

On the Home tab, click Conditional Formatting, and then click New rule. Select the rule type: ‘Format all cells based on their values’. Choose your preferred format style, in this case Icon set.

Here is the default window. You should modify the given parameters.

create-excel-dashboard-cf-icon-set-17

Step 18: Check the Value and Type setup on the bottom-right corner. Add the values and select the Formula under the Type section. Click OK to apply the rules.

To manage all rules in this worksheet, use the Conditional Formatting Rules Manager. The dashboard is live now! If you change your target values the dashboard will be changed in real time.

create-excel-dashboard-cf-add-formula-18

Finally, the core dashboard is ready, but we must now move forward.

Create contextual help for your excel dashboard

First, we will explain how to add a useful contextual menu to your prepared dashboard. How to boost user experience using interactive help system? You will find the answers for this question after reading the following tutorial. You 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 you work on an excel dashboard for days, maybe weeks. You believe to rescue the world and at the end there is something just not right.

It’s nice and good, you might calmly relax, but there is such a great volume of data on it that the outside user (who is your boss in most cases!) will not find his way on it. You can all agree that this is a very problematic situation.

How can you help this? As a solution you will use a little programming. Don’t get discouraged from this! You will need only minimal knowledge.

Steps to build a switchable help button in Excel:

  1. From the Insert menu chose the ‘Shapes’ option and then from the figures of callouts insert one that is appropriate for us.

excel dashboard insert shapes easily

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.

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.

create-excel-dashboard-vba-insert-textbox-2

Repeat this for four times, with this we created the bubbles in which we will place the correct information and explanations.

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.

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’.

create-excel-dashboard-vba-helpbox-3

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!

6. Insert a help icon / shape.

7. . Write and use this simple macro below:

Sub HelpSwitcher()
‘Macro to Show & Hide Dashboard Help
ActiveSheet.Shapes.Range(Array(“HelpBox”)).Visible = Not ActiveSheet.Shapes.Range(Array(“HelpBox”)).Visible
[valHelpStatus] = ActiveSheet.Shapes.Range(Array(“HelpBox”)).Visible
End Sub

As you can see 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.

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. Our excel dashboard is ready to use!

Download

Frequently Asked Questions regarding dashboards

Before you start creating the dashboard using Excel, you have to contemplate on a few things. Like, who will use it? Where will data come from? What will we need exactly? Let’s examine these core premises in detail!

What is the reason you creating the dashboard? Maybe one of the business procedures have a weakness? Or maybe you 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 must be useful for all levels. Let us think this through carefully.

What kind of data will you 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 you like to communicate with your 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 you have to ascertain these. In the most cases you 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? You have to know already in the planning phase what kind of tool we’ll use to import data into the dashboard. If you work in Excel, the best solution is the Power Query and Power Bl. These tools are perfect to handle millions of rows in a blink of an eye. But you 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 you build this into the planning phase you can avoid a lot of frustrating problems later.

How often does your dashboard need to be updated? Should you make decisions based on real time information? Or is it enough in the habitual daily, weekly, monthly, quarterly or yearly breakdown? You can create the initial structure of the dashboard based on the answer.

What format do you publish the dashboard? Do you send flat Excel tables to the users or maybe you publish the result on SharePoint? Or maybe you need to embed some charts into a Power Point presentation? You have to consider access issues also. Accessibility levels are different for a manager and for the owner.

Excel dashboard toolbox for interactivity

Let us see how to make your dashboard more user-friendly and interactive!

Drop-down list: with the use of the drop-down list we can choose elements from a predefined list. It is very useful when for example we would like to show 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: Would you like to underline cells based on any mutual condition or rule? The best choice is conditional formatting. Of course, you can use other methods besides colors. You can achieve spectacular results using icons. But you 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 unique buttons and menus. Most often applied when navigating between worksheets. We must not forget that functionality is first when creating a dashboard in Excel!

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 programming knowledge but in return the end-result is more spectacular.

Dashboard Widgets: We can call them 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.

excel dashboard widget pack

Download

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 and TreeMaps: Use our free excel maps to visualize geographical information. If you need a risk map or a product comparison tool, we recommend using treemap chart.

How to choose the right chart type for your dashboard?

Let’s see some useful tip regarding the charts. In the last years excel charting has been radically changed. For example the column chart is not able to visualize complex information. 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 types for excel charting are: column, marimekko, 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.

Excel dashboard tools – Free Gauge Chart Add-in

We have a great news for dashboard lovers! Install and check our add-in to create the popular chart in seconds.

Download

If you are in hurry please check our professional excel dashboard solution:

Various types of excel dashboards – Free Downloads

In this chapter we introduce the frequently used excel dashboard types and their fields of application.

First, 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! Scroll through the Excel dashboard examples listed below and choose a template to download for free. All excel dashboards are compatible with Microsoft Excel 2010, Excel 2013 and Excel 2016 / Office 365.

Call Center Dashboard

Our objective is to reduce the time to answer and abandon rate and increase the first call resolution. Using this dashboard, you can analyze your weekly call center metrics – including branch performance, time to answer and first call resolution – with a call center dashboard. A dashboard allows you to compile complex data at various levels, from overall call center performance to departments and individual agents.

Are you ready for the next challenge? The spin button (Excel form control) play a key role in creating dynamic dashboards. In this lesson you will learn how to create and configure spin button in Excel. You can analyze, track and trace your results easily, weekly data by using the spin button to change periods.

Download

HR Analytics Dashboard

Keep an eye on the operation of the HR department to gain advantage by early recognizing surfacing problems. You can keep their impacts on the business on a healthy level. We also 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.

So, we can keep their impacts on the business on a healthy status. We keep ourselves to the main dashboard design rule: create one-page-report. Let us see the main measures: Salary, Bonus, Overtime and Slick Days. Using a drop-down list, it is simple to choose the given department.

You can display easily the size of the department using the Excel formula REPT and the Webdings font. Webdings are special fonts. Using a simple vector object and text boxes we can build stunning dashboard in Excel.

Download

Dynamic VBA Dashboard

What is VBA good for? How can we construct user-friendly interface with it that increase user experience? Dynamic charts make users life easier and simpler. We will show you the essential things!

Would you like to understand how to create dynamic chart? It is a core and vital component of dashboard. Insert two command buttons to create the start and stop controls of the animation. Use a simple macro and assign them to two Command buttons. The static chart will be dynamic!

Our Excel Dashboard uses a public variable to keep track of the animation status. DoEvents statements is the key to chart animation. DoEvents passes control to the operating system. Control is returned after the operating system has completed processing the events.

Download

SEO Analytics Dashboard

Every website owner feels that it is important to get an overview of the operation of the site based on different indicators. It is possible to display the main indicators and trends on a single dashboard? Absolutely.

Clap your eyes on the SEO analytics! You can track the number of unique visitors, new visits, bounce rate and more with an SEO analytics excel dashboard.

Let us say a few words about the dashboard structure! The first column shows the actual daily information and the positive / negative variance in %. The second column shows yesterday’s performance. The third one shows historical information from 30 days earlier. To visualize trends, we will use sparklines (mini charts).

Download

 

1 COMMENT

Comments are closed.