Visually Effective Excel Dashboards

I provide a brief summary of the structure of this online Excel report and dashboard course. Depending on your needs right now and how you'd like to learn, you could either jump in when I create the Excel KPI dashboard or the Regional (pivot slicer) dashboard from scratch or you can take the course from beginning to end. 

You can download the main course files here - you will find a demo workbook where you can follow along with me when I cover the formulas required for dashboards, Excel Form Controls and advanced Excel charting techniques. You can also download the files from the source data section when I demonstrate different raw data sources and also the final KPI dashboard and Regional (Slicer/ Pivot table) dashboards.

What exactly is a dashboard? Find out in this lecture.

Based on my experience as project manager, I take you through the checklist of questions you need answered, when you start a new Excel report or Excel dashboard project. Don't forget to download the PDF document for this lecture.

Once you've gone through the checklist, these are the first steps you need to take to start with your new Excel report or Excel dashboard project. Downloadable PDF is included.

Use the Vector Elements (graphics) included in this sample Excel file, to create mock-ups (layouts) for your Excel dashboard projects.

In this lecture I share with you my favorite Excel keyboard shortcuts when I design reports and dashboards in Excel.

Use these 4 time-saving Excel tips to start quicker and work faster. It makes creating reports and dashboards in Excel much easier.

How should you setup your Excel spreadsheet report? What's the optimal method of organizing your report and calculations? How should the data flow? In this lecture I share with you the optimal method for creating Excel dashboards and reports and how to organize your tabs for best results.

What makes a good Excel report? How you design and present the information makes a big difference in how readable your reports are. Find out what makes a good report layout in this lecture.

Color plays an important role when creating Excel reports. How do you pick your colors? which color combinations work well together? Watch this lecture to find out.

How do you come up with a color scheme for your Excel reports? How can you find the RGB code in Excel? All covered here as well as a sample color palette you can download for own use.

These concrete design tips help you create better Excel reports - faster.

In this video, I show you how to set your own default theme in Excel so that every time you create a new workbook, the theme colors that are used are the ones set by you and NOT the Microsoft Office Default theme. Unfortunately setting your own default theme in Excel is not as easy as in Microsoft Word or PowerPoint where you can just tick mark - "set as default theme". In Excel you have to follow a few steps which I show in the video.

Test your knowledge on the topics learnt in the design section.

The source data for your dashboard could come from different systems. This lecture provides you with a quick overview of what to expect in this section and how you can prepare for different source systems.

One possible source is to have the data in an Excel spreadsheet. The data could be in separate tabs - it could be in an Excel table or it could be in an Excel Pivot table. It could be in a tabular format or a non-tabular format. What do I mean by tabular and how do you handle cases where data is non-tabular? Find out in this tutorial.

One possible source for the raw data of your Excel dashboard is Microsoft Access. In this lecture I show you how to import data from Ms. Access to Excel - either as an Excel Table or as a Pivot Table. I also show you how to optimize the connection properties so that when the data is changed in Access, the updated information is available in Excel.

CSV files (data file or text files) are easily extracted from most systems. It's tempting to open the files directly in Excel and then copy and paste the data in your Excel dashboard report. But doing this, is a manual process which you have to repeat every single time. A better way would be to set up an open connection to the CSV file so that every time the file is replaced or appended, you just have to refresh your data set in Excel and have the latest data available. No copy and pasting necessary. Find out how in this lecture.

Another common source of data is third party BI tools. Most come with an Excel-addin which enable you to connect and retrieve data from them. In a lot of cases the data sets retrieved don't come in a tabular format.

This lecture provides an overview of the formulas covered in this section. These are Excel formulas that help you handle any situation (whether tabular or not) to transform your data and prepare it the way you need for your reports. You can download the Demo Excel workbook and follow along with me.

INDEX MATCH is my most used formula when I create Excel reports. This lecture takes you through the basics of the INDEX and then the MATCH formula and how these two formulas work well together. This formula combination is not just for dashboards but for any type of analysis you need to do where you have large data sets.

Make sure you understand the basics of INDEX MATCH before moving to more advanced (actually real life) cases. Real business cases are often more complex and that's when you need to use this version of Index Match.

the INDEX formula is so versatile. This video shows INDEX from another angle to give you a better idea of all that Index is capable of doing.

SUMIFS is a great formula, not to just to sum by different criteria but also to grab data out of data tables by criteria. COUNTIFS, does a count based on different criteria and AVERAGEIFS calculates the average based on single or multiple criteria. Watch this lecture to find out more about the potential of these great formulas.

LARGE and SMALL Excel formulas are great for dashboards and any reports that require ranking.

Sometimes you might need indexes in Excel, especially when you're creating ranking reports. Watch this lecture to see how the ROW and COLUMN formulas would be of use here.

When you create ranking reports in Excel, you need to be sure that you have unique ranks - even if two categories get the same rank and have an identical number, you still need to create a unique rank so you can organize them one after the other. Watch this lecture to find out how the ROW function could help you with that.

CHOOSE function can really simplify long IF formulas. It's a great function to know and have on hand, especially if you use form controls in Excel.

Excel's TEXT function is great to use when creating Excel reports and you'd like to combine numbers with text and still keep the number formatting. Find out how it works and what you need to watch out for when using this function in your Excel reports.

N function is a very simple function and can be use for "other" purposes. Find out for what, in this video.

GetPivotData is a great Excel function when you want to extract your data from your pivot table in a dynamic way. It is independent of the cell position. The only requirement is that the value you want to extract is "visible" in your pivot table. Why would you want to extract data from a pivot table in a dynamic way? Find out more in this lecture.

INDIRECT function can be a bit confusing at first but it's a great way to get dynamic ranges. In this lecture I show you how this works and later when we create the Slicer based regional dashboard we use this to a lookup on images. 

INDIRECT function is volatile - CHOOSE and name managers are alternative ways to get dynamic ranges and they are not volatile. Find out how they can work together in this lecture.

Learn how to use Excel's Combo Box Form control here.

Check boxes are great for allowing the user to select different options. Find out how check boxes work in Excel in this lecture.

The spin button, changes the value in the cell by clicking the up and down arrows. Find out how you can Excel's spin button form control in this lecture.

When you want to restrict the options available to the user to just one option, you can use Excel's Option Button form control. Find out what you need to do if you are using more than one option button groups in your reports and how you need to configure the option button form control in Excel.

List Box is another interactive form control that allows the user to pick an item from a list. Find out to configure the form control list box in this lecture.

Use Excel's scroll bar from control to scroll through a graph or an Excel table. Find out which function works best here and how you can configure the Scroll bar form control.

This lecture shows you an important technique I often use, when improving the readability of Excel charts.

This is another technique often used to design better Excel graphs. These techniques will equip you to create non-standard Excel charts which not just impressive but also quick to read.

The error bar technique is one I often use when I can't get the effect I want in a chart using the standard chart options. It's meant for statistical analysis, but error bars can do so much more for business reports. Find out how it works here and later we use this technique to create a non-standard chart for the KPI dashboard. 

This video shows you how to create dynamic chart ranges - i.e. "From - To" Charts. This means the user selects from which category or date they want to view the data, and to which category / date, and the chart only visualizes the values for the categories in between. This means the chart needs to work with dynamic ranges which change in size.

In this lecture you can see an overview of the finished KPI dashboard in Excel - this will give you a good idea of what we're setting out to achieve in the next few sections. 

In this lecture I share with you my version of the dashboard mock-up / wireframe and how I start with drawing it out on a piece of paper and then how I bring it in Excel (using Excel shapes to draw out each section of the dashboard).

In this lecture we start with a new blank Excel spreadsheet and start to setup our Excel file to build the dashboard on. We setup our QAT (Quick Acces Toolbar) and also define the color palette for the dashboard.

Here we take a look at the raw data and their format. The raw data is non-tabular which means we need to dig through our formula toolbox to find just the right ones to be able to prepare the data for the dashboard. This makes it all the more fun!

Now we start to setup the calculation sheet - here we will prepare the data for the dashboard. Organization is key for this tab - we need to break down the sheet to different sections where we do the calculations for each part of the dashboard. We can limit formatting here to some color coding but we don't need to worry about any extensive formatting here since the purpose of this tab is for data preparation only.

Here you can download the draft Excel KPI dashboard which includes only the part we have completed in this section. Download this if you haven't followed this section but want to jump in and work together on the next section.

In this lecture we setup a combo box to be able to select the division. 

Here we setup the calculations required to grab the respective apps that belong to each of the divisions which are selected from the combo box. We need to dig inside our formula toolbox and use a version of the INDEX MATCH formula. 

In this lecture we add another level of complexity to our dashboard by adding a scroll bar so we can scroll through our table on the dashboard page. This scroll bar, will depend on the calculations we did in the previous lecture - we just need to write smart formulas on the dashboard page to get the scrolling effect.

Now that we have the scrolling in place, we add conditionally formatted data bars (that looks like a bar chart actually) to be able to see the deviation of actual values to previous year values.

Here we cover another conditional formatting method to get up and down arrows that show the deviations of actual values to budget. We don't use Excel's default conditional formatting symbols, but we bring in our own set of symbols here. 

Now we just need to format the scrolling table so it fits well on our Excel dashboard page.

In this lecture we setup the calculations required to create conditional comments for the header of our Excel KPI report.

Using shapes and text boxes in Excel, we bring over the result of the calculations to the header of our Excel  dashboard and arrange them for optimal presentation.

In this lecture we start with the calculations we need to be able to setup a graph that shows the top 6 / bottom 6 categories that are over / under budget respectively. This part has two form controls that control the graph so we have to start slow and simple to make sure we don't over complicate things.

Once we have setup the basic calculations, we add a list box so the user can pick between different KPI's for the graph.

Now we add the option button so the user can pick between top or bottom 6 categories. 

We finally have all the necessary calculations and now we setup the non-standard Excel charts that compares actual values to budget values per KPI - it is also a sorted graph based on the top or bottom 6 selection. The error bar technique works great here.

Now that everything is setup and working, we bring it all over to the dashboard page. A few things you need to watch out for when you do this - all covered here.

To get the calculations for our monthly profit line chart we need a more complex index and match formula to extract the data in a dynamic way from our raw data table. 

Once we have the calculations setup, we create our scroll-able line chart so we can scroll through each category. We notice some problems (such as a crashing line for actual values) which we will amend in the next lectures.

This trick is great for any line charts you create in Excel. If you want to avoid data points from being plotted (i.e. avoid the crash of the line to zero), you need to apply this technique.

In this lecture we add check boxes so we can  toggle each of the line series on and off - i.e. visible and invisible. Find out how we do this in this lecture.

When you create line series charts in Excel, it's best to show the label for each of series at the end of the line. Find out how to do this - especially for a line that gets longer as new data is added - In this lecture I show you a trick for just that.