Excel with Interactive Excel Dashboards

Learn how to create amazing interactive Excel Dashboards that will wow your boss & take your career to the next level. Lifetime access with no subscription on Udemy.
4.4 (85 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
2,475 students enrolled
$200
Take This Course
  • Lectures 112
  • Contents Video: 12.5 hours
    Other: 5 mins
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 7/2013 English

Course Description

Excel Dashboards are a powerful tool to communicate and summarize complex Excel data.

The price of this course reflects its value and I'm confident you'll get results. Just read some of the unsolicited testimonials I've received:

  • "Financial Analyst - $52,500 (before taking your course)
  • Sr. Financial Analyst - ($60,000) After finalizing my dashboard
  • Sr. Business Analyst - ($70,000) Start that this Monday

Taking your course has honed my skills and led to two promotions and a reputation. I am also energized to learn more. James C"

"I originally signed up for this as continuing professional education for my CPA license but I have learned so much, easily more than any other technical class I've taken in years. Thank you for a wonderful class, you are a great teacher. Dielle B"

Dashboards can be created to summarize important information and are excellent for sharing a “snapshot" view among teams.

Master the Creation of Interactive Excel Dashboards and Learn to Build and Distribute Dashboard Reports From the Ground up.

  • Advanced Features
  • Design and Display Principles
  • Interactive Controls
  • Analyzing Data
  • Automating your Dashboard so it updates in seconds

Impressive Content that Will Provide Insight, Analysis and Alerts Dashboards are fully interactive and dynamic and can help with project management, customer service, retail management, financial forecasting and much more.

In order to learn Excel Dashboard students should have access to Excel 2007 or later. Students should also be familiar with putting together any type of report in Excel to be able to apply the time saving and visualization techniques taught here. Students should be able to build and apply basic formulas in Excel as well. Pivot Table and Pivot Chart knowledge is helpful, but expertise is not necessary to take this course.

Contents and Overview This course includes 107 lectures and over 11 hours of content + 5 sample Excel Dashboards in order to give students a working knowledge of utilizing Excel Dashboard visuals to communicate their data.

3 hours of the tutorials are optional supplementary Excel lessons and only for students who need to fill any knowledge gaps.I don't waffle on, my tutorials get to the point so you're up to speed fast.

Included are all the workbooks from the course which contain extensive notes, so you don't have to worry about taking notes while watching the tutorials. You can use the workbooks to practice what you learn and as a quick reference guide later on.

The course covers advanced features of Excel Dashboards, such as display and visualization principles for clear dashboard creation, using interactive controls to keep information informative and relevant, analyzing and automating information for a fully functional and complete dashboard. It also includes key considerations for distributing dashboards like password protections, locking and unlocking cells and protecting workbooks.

Upon completion, students will have a variety of techniques and examples that can be applied to their own datasets. Students will be able to choose the right chart for their data, write easy to interpret reports, know which areas of the dashboard will get the most attention, animate charts using VBA, create interactive reports (so users can change views and filter information), among a host of other abilities taught in this course.

What are the requirements?

  • Ideally you already know how to use Excel and are familiar with basic formulas and how to build them. You’ve created basic charts using the standard ones available in Excel, and you’re familiar with Pivot Tables and Pivot Charts but not an expert.
  • You need Excel 2007 or later so you can open the workbooks and practice what you learn.
  • Mac users - this course is recorded in Excel for the PC. If you're a savvy Mac user then you will still benefit from this course and be able to map the menus you see in the tutorials to those on your Mac. I have had many Mac users successfully take this course.

What am I going to get from this course?

  • I teach you the 5 key questions to ask in the planning stage of your report so you get it right first time.
  • Learn how to choose the right chart for your data; I teach you over 20 charts and show you which type of chart will best display your data.
  • Learn how to write reports that are easy to read and interpret; plus I share with you a heat map of your page that shows you where your most important data should go and where will get the least attention.
  • Be able to build Dashboards that are quick and easy to update, in fact they can update themselves (imagine being able to confidently say that in an interview), by pulling in data direct from your external database, Microsoft Access, the web etc. at the click of a button if you set them up right (I show you how).
  • Animate your charts with some simple VBA code that I teach you how to edit to suit your charts. If you want 'Wow factor', this is it.
  • Learn how to create reports that are fully interactive so the reader can change the view themselves; they can filter by product, time period or any parameter you choose, they can show and hide data using check boxes, drop down lists, and option buttons to name a few.
  • Get your skills up to date with our supplementary Excel tutorials on PivotTables, Lookup and conditional logic formulas, macros and more.

What is the target audience?

  • This course is not for complete beginners because building Dashboards, and I mean proper interactive dashboards in Excel, requires some intermediate to advanced skills.
  • Any course that says it’s for ‘All Levels’ is either going to cover a load of basics you probably already know, or it won’t get to the advanced topics that are going to make your dashboards truly dynamic and interactive.
  • I know everyone is at different levels of Excel ability, which is why I’ve included a load of optional supplementary Excel lessons so you can fill in any knowledge gaps, like PivotTables, lookup formulas, Conditional Formatting etc.
  • I recommend this course for people who have put together any type of report in Excel before, as you will definitely benefit from the time saving tips and data visualisation techniques I share with you.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction
10:13

In the first video we take a quick overview of the course and what you should expect, then we dive right into the 5 key questions you must ask before building your dashboard so you get it right first time. Note: All supporting files are available for download with this lesson in the 'Materials' section.

Section 2: Getting Your Data Ready
06:37

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.

Section 3: Design Principles
05:57
Design can be scary if you're not artistically inclined. In this lesson I share with you a heat map for your report, so you know where to put your most important information. Plus I teach you 9 design principles that will ensure your reports are easy on the eye and don't end up a mish mash of formatting junk.
Section 4: Display Principles
13:53

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.

02:48

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.

04:11
When data fluctuates significantly from month to month, whether it's due to seasonality or just the nature of your business cycle, it's difficult to establish whether the overall trend is up or down. I’ll show you how you can use built in smoothing tools like Trendlines and Moving Averages, plus how to normalize your data using the AVERAGE Function.
15:18

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.

02:37
In this lesson I show you how to use Excel’s Shapes to emphasise and annotate anomalies in the data.
04:50
In this lesson I show you how you can use a combination of in-cell charts, conditional formatting and symbols to emphasise key metrics visually.
08:38
In this lesson I show you how to create charts that highlight the top or bottom positions in different colours automatically by laying your data out in a particular way. I'll also show you how to calculate the ranking using the LARGE and SMALL functions.
05:20
Learn how to create a histogram to group your data into bins using the FREQUENCY function and then plot it in a chart including a cumulative indicator that aids in identifying Pareto’s 80/20 point.
04:07

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.

04:05

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.

12:40

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.

14:09

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.

04:40

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.

17:47

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.

01:43
Imagine you have a dashboard that allows the user to select different views. It might be changing the year, region, or product etc. It would be helpful if the labels dynamically updated in line with the different views. In this lesson I show you how you can achieve this with text boxes linked to cells.
09:06

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.

04:33
Incorporating unicode characters in your dashboards allows you to use the universal symbols for increase and decrease (up arrow/down arrow) in your charts and dashboards. The benefit of this is that they take up very little space and can even be included in your chart axis labels. I show you how in this lesson.
03:55

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

04:06
Earlier I showed you how you can use Shapes to annotate key points in your charts, but if your chart is likely to be updated each month then you'll find yourself manually moving the shapes to match the movement of the data in the chart. Here I'll show you how you can plot the Shape as part of the chart so that it automatically moves and updates in line with the chart.
05:12

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.

07:47
I touched briefly on in-cell charts in an earlier lesson but here I'm going to go in depth and show you how you can create column and win/loss in-cell charts as well as some other clever formatting. This is great if you don't have Excel 2010+ and the luxury of Sparklines.
06:51

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.

05:34

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.

08:10

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.

08:37

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.

Supplementary Excel Lecture: Excel 2013 Chart Labels
07:03
02:00

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

Section 5: Interactive Controls
01:55

In this section we're going to look at how we can use form controls to add interactive elements to our dashboards.

Form controls are things like List boxes, check boxes, and radio buttons to name a few and they allow the report recipient to choose what information they want displayed in the report.

03:53
Check box form controls allow you to easily toggle on and off data displayed in your report. In this lesson I show you how you can link a formula to the output of the check box to control whether the median line is displayed or not.
16:20
In this lesson I show you how to use a list box to control more than one chart. The example in this lesson allows the user to choose to display data for a specific salesperson or all salespeople.
09:09

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.

02:28

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.

03:43
Scroll bars allow the user to create a moving image of their data. For example; scrolling through the months of the year.
02:28
Form control buttons are fairly simple things. They allow you to assign macros to them, but in this lesson I also show you a more stylish option that allows more formatting changes than the boring form control buttons.
06:04

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.

24:35

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)

17:02
Link your charts to a macro that makes them dance before your eyes. Your boss and colleagues will be blown away by this trick.
11:15
This lesson shows you a range of practical applications for the INDIRECT function, one of which is a clever way you can choose which chart to display from a drop down list.
07:10
Never update a cell range in a formula or chart again. The OFFSET function allows you to create references to cells or ranges of cells on the fly. It can be based on selections you make from data validation lists or simply as your data grows.
09:46
The INDEX function allows you to create references to cells or ranges of cells on the fly in a similar way to the OFFSET function, however the advantage of INDEX is that it isn't a volitile function like OFFSET.
</b>
08:13
In this tutorial I show you how to link a dynamic named range to data validation lists and then use those ranges in your chart so that when a new date range is selected the chart automatically updates. I also show you a twist on the dynamic named range using INDEX!
13:14

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

15:35

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.

05:49

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.

05:49
This is a supplementary lecture from our Excel course </b>on Inserting Drop Down Lists (Data Validation Lists). This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to work with Drop Down Lists.
Section 6: Analysing the Data
13:10

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.

08:36

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.

11:31

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 :-)

03:34

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

04:06

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.

02:48
This is a supplementary lecture from our Excel course on inserting PivotTables. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to insert a PivotTable.
04:07
This is a supplementary lecture from our Excel course on choosing fields in PivotTables. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to insert a PivotTable.
03:29
This is a supplementary lecture from our Excel course on modifying a PivotTable layout. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to insert a PivotTable.
02:04
This is a supplementary lecture from our Excel course on filtering PivotTables. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to insert a PivotTable.
02:10
This is a supplementary lecture from our Excel course on modifying PivotTable data. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to insert a PivotTable.
03:26
This is a supplementary lecture from our Excel course on inserting Excel Tables. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to insert an Excel Table.
02:39
This is a supplementary lecture from our Excel course on adding rows and columns to Excel Tables. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to insert an Excel Table.
04:18
This is a supplementary lecture from our Excel course on working with formulas in Excel Tables. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to insert an Excel Table.
02:58
This is a supplementary lecture from our Excel course on creating a Pivot table from an Excel Table. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to insert PivotTables from an Excel Table.
06:20
This is a supplementary lecture from our Excel course on VLOOKUP Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know VLOOKUP.
09:14
This is a supplementary lecture from our Excel course on VLOOKUP Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know VLOOKUP.
03:53
This is a supplementary lecture from our Excel course on HLOOKUP Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know HLOOKUP.
01:36
This is a supplementary lecture from our Excel course on HLOOKUP Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know HLOOKUP.
06:10
This is a supplementary lecture from our Excel course on the CHOOSE Function. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know CHOOSE.
07:02
This is a supplementary lecture from our Excel course on INDEX and MATCH Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know INDEX and MATCH.
07:23
This is a supplementary lecture from our Excel course on IF Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know the IF function.
03:31
This is a supplementary lecture from our Excel course on nested IF Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to nest IF functions.
03:01
This is a supplementary lecture from our Excel course on IF AND Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know IF AND.
Supplementary Excel Lecture: IF OR Formula
02:43
01:52
This is a supplementary lecture from our Excel course on IF NOT Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know IF NOT.
02:19
This is a supplementary lecture from our Excel course on IFERROR Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know IFERROR.
04:15
This is a supplementary lecture from our Excel course on SUMIF and SUMIFS Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know SUMIF and SUMIFS.
04:41
This is a supplementary lecture from our Excel course on AVERAGEIF and AVERAGEIFS Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know AVERAGEIF and AVERAGEIFS.
09:48
This is a supplementary lecture from our Excel course on COUNTIF and COUNTIFS Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know COUNTIF and COUNTIFS.
10:18
This is a supplementary lecture from our Excel course on SUMPRODUCT Formulas. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know SUMPRODUCT.
Section 7: Automating Your Dashboard
08:07
In this lesson I show you how you can pull data directly from Access into an Excel PivotTable without having to import it to Excel first. This also allows you to maintain a connection to your Access database which means you can simply refresh the connection to update the PivotTable.
09:39
Very few reports you do will be for a fixed time period. It's more likely that each month you'll be adding new data. In this lesson I show you a few ways you can automate incorporating new data into your reports so that you're not spending days updating cell references in your charts, formulas and the like.
02:39
When you have more than one PivotTable in a file it can become time consuming to update them all each time you update the source data. In this lesson I share a simple macro that will allow you to update all PivotTables in your file automatically.
03:40
This is a supplementary lecture from our Excel course on Macros. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to record Macros.
04:17
This is a supplementary lecture from our Excel course on Recording Macros. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to record Macros.
03:30
This is a supplementary lecture from our Excel course on assigning Macros to a button. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to work with Macros.
02:55
This is a supplementary lecture from our Excel course on inserting a Macro that runs when a workbook is opened. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to set up macros that run on opening.
03:39
This is a supplementary lecture from our Excel course on how to inspect and modify Macros. This is included in the Dashboard course to fill in any gaps in your Excel knowledge if required. You do not need to watch this lecture if you already know how to modify Macros.
Section 8: Putting It All Together
01:37
We've covered a lot of material, but perhaps some of the most important points were shared at the beginning. Here's a quick re-cap to keep you on track.
03:47
In this lesson I want to give you a tour of the dashboard we're going to build together in this section of lessons. I also show you how I've structured the workbook and how the different sheets interact.
08:06
Pyramid or Waterfall charts are a great way to show data that naturally takes on the shape of a pyramid. In this lesson I share with you the tricks required to get Excel to create these charts since they're not available in the standard list of charts.
09:50
In this lesson I show you an array formula alternative to the SUMIFS function which is great if you or your report recipient only has Excel 2003.
06:23
In this lesson I show you how to calculate the highest and lowest values for a particular year using the LARGE and SMALL functions in array formulas, plus we'll use the database function DAVERAGE to calculate the average.
03:50
In this lesson I show you the handy ranking option built right into PivotTables in Excel 2010. I also show you a workaround for earlier versions of Excel.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Mynda Treacy, Microsoft Excel MVP, Teaching 25,000+ Students

Mynda Treacy is a qualified accountant and has been working with Excel since 1995, and sharing her knowledge on her Excel blog since 2010.

In 2014 she was awarded Microsoft Excel MVP for her contribution to the Excel community.

She also teaches several highly acclaimed Excel courses at her website, My Online Training Hub, including an Excel Dashboard Course and Excel Expert course .

Her courses incorporate real business scenarios drawn from her accounting background and work experience in multi-national companies in London.

Her teaching style is to the point with practical examples and files you can download for reference later on.

She doesn't waffle on just to make the course long. She'd rather get you up to speed quickly because she knows that what you really want is to know how to do this stuff, not spend hours watching videos.

Ready to start learning?
Take This Course