Excel with Interactive Excel Dashboards
3.9 (119 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.
3,242 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel with Interactive Excel Dashboards to your Wishlist.

Add to Wishlist

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.
3.9 (119 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.
3,242 students enrolled
Created by Mynda Treacy
Last updated 10/2015
English
English
Price: $200
30-Day Money-Back Guarantee
Includes:
  • 12.5 hours on-demand video
  • 1 Article
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • 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.
View Curriculum
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.
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.

Who 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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 112 Lectures Collapse All 112 Lectures 12:24:53
+
Introduction
1 Lecture 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.

Preview 10:13
+
Getting Your Data Ready
1 Lecture 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.

Ideal Data Formats
06:37
+
Design Principles
1 Lecture 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.
Getting Your Design Right
05:57
+
Display Principles
27 Lectures 03:09:40

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.

Charts and Tricks for Trending
13:53

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.

Secondary Axis
02:48

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.
Smoothing Data
04:11

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.

Sparklines
15:18

In this lesson I show you how to use Excel’s Shapes to emphasise and annotate anomalies in the data.
Formatting Tricks
02:37

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.
Highlighting Comparisons
04:50

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.
Top and Bottom Ranking
08:38

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.
Frequency Distribution
05:20

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.

Target vs Variance Charts
04:07

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.

Performance Against Target Range
04:05

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.

Bullet Graphs Excel 2007/2010
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.

Bullet Graphs Excel 2013/2016
14:09

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.

Win/Loss/Draw Conditional Format
04:40

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.

Custom Number Formats
17:47

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.
Dynamic Labels
01:43

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.

Preview 09:06

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.
Preview 04:33

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

Adding Series to Charts
03:55

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.
Embedding Graphics in Charts
04:06

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.

Small Multiples Charts
05:12

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.
In Cell Charts
07:47

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 Excel 2007/2010
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.

Panel Charts Excel 2013/2016
05:34

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.

Step Charts
08:10

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 Formatting
08:37

Supplementary Excel Lecture: Excel 2013 Chart Labels
07:03

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

Supplementary Excel Lecture: Excel 2013 Combo Charts
02:00
+
Interactive Controls
18 Lectures 02:44:28

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.

Form Controls Introduction
01:55

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.
Check Boxes
03:53

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.
List Boxes
16:20

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.

Option Buttons
09:09

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.

Group Boxes
02:28

Scroll bars allow the user to create a moving image of their data. For example; scrolling through the months of the year.
Scroll Bar
03:43

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.
Buttons
02:28

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.

Combo Box with Macro
06:04

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)

Dynamic Date Filters 4 Ways
24:35

Link your charts to a macro that makes them dance before your eyes. Your boss and colleagues will be blown away by this trick.
Animating Charts
17:02

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.
INDIRECT Function Tricks
11:15

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.
Dynamic Named Range with OFFSET
07:10

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>
Dynamic Named Range with INDEX
09:46

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!
Dynamic Ranges for Charts
08:13

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

Rollover Technique
13:14

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.

Scroll and Sort Table
15:35

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.

Dynamic Unique List
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.
Supplementary Excel Lecture: Inserting Drop Down Lists
05:49
+
Analysing the Data
30 Lectures 02:33:02

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.

PivotTables and PivotCharts
13:10

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.

Slicers
08:36

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

GETPIVOTDATA Function
11:31

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

Capture Slicer Selection in Formula
03:34

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.

Slicer & PivotChart Olympic Dashboard Overview
04:06

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.
Supplementary Excel Lecture: Creating PivotTables
02:48

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.
Supplementary Excel Lecture: Choosing Fields
04:07

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.
Supplementary Excel Lecture: PivotTable Layout
03:29

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.
Supplementary Excel Lecture: Filtering PivotTables
02:04

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.
Supplementary Excel Lecture: Modifying PivotTable Data
02:10

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.
Supplementary Excel Lecture: Insert a Table and Style Options
03:26

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.
Supplementary Excel Lecture: Add Rows and Columns to a Table
02:39

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.
Supplementary Excel Lecture: Perform a Function in a Table
04:18

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.
Supplementary Excel Lecture: Summarize a Table with a PivotTable
02:58

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.
Supplementary Excel Lecture: VLOOKUP Formula Sorted List
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.
Supplementary Excel Lecture: VLOOKUP Formula Exact Match
09:14

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.
Supplementary Excel Lecture: HLOOKUP Formula Sorted List
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.
Supplementary Excel Lecture: HLOOKUP Formula Exact Match
01:36

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.
Supplementary Excel Lecture: CHOOSE Function
06:10

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.
Supplementary Excel Lecture: INDEX and MATCH Formula
07:02

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.
Supplementary Excel Lecture: IF Function
07:23

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.
Supplementary Excel Lecture: Nested IF Formula
03:31

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 AND Formula
03:01

Supplementary Excel Lecture: IF OR Formula
02:43

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.
Supplementary Excel Lecture: IF NOT Formula
01:52

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.
Supplementary Excel Lecture: IFERROR Function
02:19

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.
Supplementary Excel Lecture: SUMIF and SUMIFS Formulas
04:15

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.
Supplementary Excel Lecture: AVERAGEIF and AVERAGEIFS Formulas
04:41

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.
Supplementary Excel Lecture: COUNTIF and COUNTIFS Formulas
09:48

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.
Supplementary Excel Lecture: SUMPRODUCT Formula
10:18
+
Automating Your Dashboard
8 Lectures 38:26
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.
Linking to Access
08:07

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.
Incorporating New Data Automatically
09: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.
Auto Refresh PivotTables
02:39

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.
Supplementary Excel Lecture: Introduction to Macros
03:40

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.
Supplementary Excel Lecture: Recording a Macro
04:17

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.
Supplementary Excel Lecture: Assign a Macro to a Button or Shape
03:30

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.
Supplementary Excel Lecture: Run a Macro Upon Opening a Workbook
02:55

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.
Supplementary Excel Lecture: How to Inspect and Modify a Macro
03:39
+
Putting It All Together
9 Lectures 53:59
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.
Quick Re-cap
01:37

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.
Example Dashboard Tour
03:47

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.
Pyramid Chart
08:06

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.
Array Formula Alternative to SUMIFS
09:50

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.
LARGE and SMALL Array and DAVERAGE
06:23

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.
Rank Values with PivotTable
03:50

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.

Scatter Chart
08:31

Excel's Database Functions aren't very well known, but they're a simple way to summarize and analyze data that you'd typically need a complex array formula for.
DMAX, DMIN, DAVERAGE
05:43

Once you've done your analysis and created your charts you'll want to make sure they're all aligned nicely in your dashboard report. In this lesson I'm going to share with you a few tips for quickly getting everything lined up and neat and tidy.
Formatting Quickly
06:12
+
Distributing Your Dashboard
8 Lectures 48:34
You've put in the hard work but before you press send make sure you've checked your report isn't littered with typos etc. In this lesson I share with you the key checks you should make and some different options you have for publishing your reports.
Checking and Publishing
03:39

Excel Web App - Introduction
05:35

Preparing Your File for Excel Web App
04:41

Publish with Excel Web App
09:59

Password Protection
13:52

This is a supplementary lecture from our Excel course Workbook Security. 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 secure workbooks.
Supplementary Excel Lecture: Workbook Passwords
03:40

This is a supplementary lecture from our Excel course Protecting Workbooks. 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 protect workbooks.
Supplementary Excel Lecture: Protecting Workbooks
03:08

This is a supplementary lecture from our Excel course unlocking cells. 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 unlock cells.
Supplementary Excel Lecture: Unlocking Cells
04:00
+
Bonus: Tour de France Dashboard
5 Lectures 42:18
Come behind the scenes of my Tour de France dashboard while I show you how I put it together and give you some tips for working with dynamic data sources. Download the Excel file from the Materials section and follow along.
Dashboard Overview
13:07

In this tutorial I show you a clever way you can use the SUMIFS formula instead of a lookup formula.
SUMIFS Formula
07:43

With INDEX, MATCH and OFFSET we can create dynamic lookup formulas that change based on the selection in a data validation list or combo box list.
INDEX, MATCH and OFFSET Formula
05:12

With this INDEX, MATCH array formula I show you how you can look up multiple criteria in and find matches in multiple columns.
INDEX, MATCH Multiple Array Formula
03:54

Use zoom buttons on your charts to allow the reader to get a closer look at the data. With this trick you can fit a lot more on your dashboard and give the reader the option to zoom in if they want to.
Zoom Images VBA Code
12:22
2 More Sections
About the Instructor
Mynda Treacy
4.5 Average rating
2,244 Reviews
21,010 Students
2 Courses
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.