Excel with Interactive Excel Dashboards

In this lesson we'll learn the best practices for setting up your data models that feed your dashboard reports. We'll look at how to structure your workbook and what key functions and tools you need to know.

In this lesson we’ll cover charts that are good for displaying data that follows a trend, for example, over time. We’ll also look at tricks we can use to display data that spans different years, axes labelling tricks, charting two data sets, logarithmic scaling and more.

When charting different units of measure it makes sense to use a secondary axis, especially if the units being displayed have vastly different scales. For example sales in thousands vs percentages. Using a secondary axis allows both units of measure to be clearly displayed in one chart.

Sparklines are a great addition to Excel 2010's charting capabilities. They allow you to show bite sized charts in a single cell. In this lesson I cover inserting Sparklines, formatting, grouping, and scaling and axis tricks. Plus I’ll show you how you can set up the Sparkline to dynamically incorporate new data.

One of the most common reports is Actual vs Target and the variance. Plotting the variance can be challenging as it often gets lost due to the scale of the actual and target figures that are much larger. In this lesson I show you a few ways you can plot variances.

Sometimes we don't just have one value that represents our target. You might have an upper and lower limit, for example a complaints department might have an acceptable target of complaints received that’s between 50 and 100 per month. In this lesson I teach you how you can represent the target range in a chart with the actual complaints plotted on top.

Bullet graphs were developed by visualisation expert Stephen Few and are a great way to display multiple layers of information in a small amount of space, which makes them ideal for dashboards. Excel doesn't have any built in Bullet Charts but in this lesson I show you how you can wrangle it into creating them.

Excel 2010's new Sparklines are a great addition to the charting capability of Excel, and while they have a Win/Loss chart it fails to plot draw results. In this lesson I show you how you can use Conditional Formatting to include the draw results which is important in a lot of sports dashboards.

Custom number formats are useful for hiding data you don't wan't visible, formatting values in millions, thousands etc. without changing the underlying value and much more.

An extension of a dynamic label is to create a text formula that updates based on the results displayed in your chart or table. In this lesson I show you how you can join text and values that result from formulas in the one cell and have them display in your dashboard automatically.

We often need to add extra series to charts, in this video I show you the easy way to achieve this.

When you have a lot of data to plot in one chart often the best solution is to use Small Multiple charts so that each series is easy to see, but comparisons among the series are also possible.

Panel  Charts are a great way to show two or more sets of data side by side. They appear to be separate charts but they are actually one chart with a clever use of formatting and overlayed scatter charts to create dividing lines between each data set.

Panel Charts are a great way to show two or more sets of data side by side. They appear to be separate charts but they are actually one chart with a clever use of formatting and overlayed scatter charts to create dividing lines between each data set.

Step charts are useful for displaying how the levels in your data increase, remain constant or decrease over time. Particularly if your data isn't evenly dispersed.

If you're new to Excel 2013 and you're wondering where the formatting tools went in Excel 2013 then take a few minutes to watch this video and familiarize yourself.

Plotting multiple types of charts in the one chart in Excel 2013 is easy with the new Combo Chart feature.

Option Buttons or Radio Buttons work in a similar way to List Boxes, except since they are added to a worksheet one-by-one they are better suited to small lists. Here I'll show you how you can use multiple option buttons to control one chart.

In this lesson I show you how you can use Group Boxes to group form controls together. This is important when building dashboards because you may have more than one group of form controls performing different tasks in the one report.

Combo Box controls work similarly to List Boxes in that they display a list of values for you to choose from and return a value for the item you've chosen.

In this example we've taken it a step further and used the output of the combo box in a macro to control two PivotTables.

One of the most common interactive dashboard features you're likely to want to employ is filtering date ranges.

In this lesson I show you 3 different ways we can do this in Excel without VBA. Two use Slicers which are only available in Excel 2010 onwards, and if you only have Excel 2007 I'll show you two ways to filter using formulas (you can use this in all versions of Excel)

Make your charts dynamically change when you hover your mouse over a cell.

In this tutorial I show you how to incorporate interactive controls like radio buttons and scroll bars to create a dynamic table that enables the user to choose what category to sort on and then scroll through the list of data. Download the workbook from the Supplementary Materials area.

If you use data validation lists or combo boxes in your reports then it's likely you'll want to extract a list of unique items from your source data for use in these lists, and have them easily update. In this video I show you how to do that using a PivotTable, and have the list automatically sorted in alphabetical order.

PivotTables and PivotCharts are a great tool to use with dashboards since they allow you to slice and dice large amounts of data in seconds. Giving the user of your dashboards fexibility and interactivity that they are renowned for.

Slicers are a new feature in Excel 2010 and they are an interactive control that allows you to filter your data just like you can in a PivotTable, but the beauty of a slicer is that it can sit anywhere in the workbook since it floats above the worksheet like an object.

You've probably seen the GETPIVOTDATA function before when linking a formula to a PivotTable, and if you were like me, when I first saw it I wanted to turn it off. But there are very good reasons for using the GETPIVOTDATA function, especially when linking reports to a PivotTable.

In this lesson I'm going to help you learn to love the GETPIVOTDATA function :-)

In this video I show you a clever trick for capturing the Slicer selection in your formulas so you can use them in your dashboards as an alternative to data validation lists and combo boxes. Note: the Excel file for this video is included in the file for the previous tutorial 'Slicers'.

This video gives a quick tour of a dashboard built using Slicers, PivotTables adn PivotCharts almost exclusively. Download the Dashboard seen in this video from the 'Materials' section for this lecture.

Scatter charts are commonly used for displaying and comparing numeric values, such as scientific, statistical, and engineering data. They effectively display data that includes pairs or grouped sets of values like we have in this dashboard with our Male and Female life expectancy.