Excel Dashboard
Tags: Excel Dashboard
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.
Last updated 2022-01-10 | 4.5
- 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.
What you'll learn
* 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 this course is for:
- 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.
Course content
12 sections • 112 lectures
Course Overview and Introduction to Excel Dashboards Preview 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.
Ideal Data Formats Preview 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.
Getting Your Design Right Preview 05:57
Charts and Tricks for Trending Preview 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.
Secondary Axis Preview 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.
Smoothing Data Preview 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.
Sparklines Preview 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.
Formatting Tricks Preview 02:37
In this lesson I show you how to use Excel’s Shapes to emphasise and annotate anomalies in the data.
Highlighting Comparisons Preview 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.
Top and Bottom Ranking Preview 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.
Frequency Distribution Preview 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.
Target vs Variance Charts Preview 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.
Performance Against Target Range Preview 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.
Bullet Graphs Excel 2007/2010 Preview 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 Preview 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.
Win/Loss/Draw Conditional Format Preview 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.
Custom Number Formats Preview 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.
Dynamic Labels Preview 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.
Text Formulas Preview 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.
Symbols in Formulas and Charts Preview 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.
Adding Series to Charts Preview 03:55
We often need to add extra series to charts, in this video I show you the easy way to achieve this.
Embedding Graphics in Charts Preview 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.
Small Multiples Charts Preview 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.
In Cell Charts Preview 07:47
Panel Charts Excel 2007/2010 Preview 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 Preview 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.
Step Charts Preview 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.
Supplementary Excel Lecture: Excel 2013 Chart Formatting Preview 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 Preview 07:03
Supplementary Excel Lecture: Excel 2013 Combo Charts Preview 02:00
Plotting multiple types of charts in the one chart in Excel 2013 is easy with the new Combo Chart feature.
Form Controls Introduction Preview 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.
Check Boxes Preview 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.
List Boxes Preview 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.
Option Buttons Preview 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.
Group Boxes Preview 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.
Scroll Bar Preview 03:43
Scroll bars allow the user to create a moving image of their data. For example; scrolling through the months of the year.
Buttons Preview 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.
Combo Box with Macro Preview 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.
Dynamic Date Filters 4 Ways Preview 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)
Animating Charts Preview 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.
INDIRECT Function Tricks Preview 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.
Dynamic Named Range with OFFSET Preview 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.
Dynamic Named Range with INDEX Preview 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>
Dynamic Ranges for Charts Preview 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!
Rollover Technique Preview 13:14
Make your charts dynamically change when you hover your mouse over a cell.
Scroll and Sort Table Preview 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.
Dynamic Unique List Preview 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.
Supplementary Excel Lecture: Inserting Drop Down Lists Preview 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.
PivotTables and PivotCharts Preview 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.
Slicers Preview 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.
GETPIVOTDATA Function Preview 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 :-)
Capture Slicer Selection in Formula Preview 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'.
Slicer & PivotChart Olympic Dashboard Overview Preview 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.
Supplementary Excel Lecture: Creating PivotTables Preview 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.
Supplementary Excel Lecture: Choosing Fields Preview 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.
Supplementary Excel Lecture: PivotTable Layout Preview 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.
Supplementary Excel Lecture: Filtering PivotTables Preview 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.
Supplementary Excel Lecture: Modifying PivotTable Data Preview 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.
Supplementary Excel Lecture: Insert a Table and Style Options Preview 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.
Supplementary Excel Lecture: Add Rows and Columns to a Table Preview 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.
Supplementary Excel Lecture: Perform a Function in a Table Preview 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.
Supplementary Excel Lecture: Summarize a Table with a PivotTable Preview 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.
Supplementary Excel Lecture: VLOOKUP Formula Sorted List Preview 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 Preview 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.
Supplementary Excel Lecture: HLOOKUP Formula Sorted List Preview 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 Preview 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.
Supplementary Excel Lecture: CHOOSE Function Preview 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.
Supplementary Excel Lecture: INDEX and MATCH Formula Preview 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.
Supplementary Excel Lecture: IF Function Preview 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.
Supplementary Excel Lecture: Nested IF Formula Preview 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.
Supplementary Excel Lecture: IF AND Formula Preview 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 Preview 02:43
Supplementary Excel Lecture: IF NOT Formula Preview 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.
Supplementary Excel Lecture: IFERROR Function Preview 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.
Supplementary Excel Lecture: SUMIF and SUMIFS Formulas Preview 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.
Supplementary Excel Lecture: AVERAGEIF and AVERAGEIFS Formulas Preview 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.
Supplementary Excel Lecture: COUNTIF and COUNTIFS Formulas Preview 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.
Supplementary Excel Lecture: SUMPRODUCT Formula Preview 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.
Linking to Access Preview 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.
Incorporating New Data Automatically Preview 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.
Auto Refresh PivotTables Preview 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.
Supplementary Excel Lecture: Introduction to Macros Preview 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.
Supplementary Excel Lecture: Recording a Macro Preview 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.
Supplementary Excel Lecture: Assign a Macro to a Button or Shape Preview 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.
Supplementary Excel Lecture: Run a Macro Upon Opening a Workbook Preview 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.
Supplementary Excel Lecture: How to Inspect and Modify a Macro Preview 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.
Quick Re-cap Preview 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.
Example Dashboard Tour Preview 03:47
Pyramid Chart Preview 08:06
Array Formula Alternative to SUMIFS Preview 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.
LARGE and SMALL Array and DAVERAGE Preview 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.
Rank Values with PivotTable Preview 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.
Scatter Chart Preview 08:31
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.
DMAX, DMIN, DAVERAGE Preview 05:43
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.
Formatting Quickly Preview 06:12
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.
Checking and Publishing Preview 03:39
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.
Excel Web App - Introduction Preview 05:35
Preparing Your File for Excel Web App Preview 04:41
Publish with Excel Web App Preview 09:59
Password Protection Preview 13:52
Supplementary Excel Lecture: Workbook Passwords Preview 03:40
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: Protecting Workbooks Preview 03:08
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: Unlocking Cells Preview 04:00
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.
Dashboard Overview Preview 13:07
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.
SUMIFS Formula Preview 07:43
In this tutorial I show you a clever way you can use the SUMIFS formula instead of a lookup formula.
INDEX, MATCH and OFFSET Formula Preview 05:12
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 Multiple Array Formula Preview 03:54
With this INDEX, MATCH array formula I show you how you can look up multiple criteria in and find matches in multiple columns.
Zoom Images VBA Code Preview 12:22
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.
Homework Challenge Preview 1 page
Use the workbook containing sample data and instructions and practice what you've learnt. Note: the homework sample data is included in the Zip file in the Materials section in Lecture 1.
Note: don't watch the videos in this section until you've attempted the homework as these videos show you my version of the homework, and it's better practice if you aren't influenced by what I have done.
Mynda's Homework Dashboard Part 1 Preview 08:52
Part 1 is a tour of my homework dashboard. If you prefer to analyse your data using PivotTables as opposed to formulas, then this dashboard is for you.
Mynda's Homework Dashboard Part 2 Preview 18:03
Part 2 goes behind the scenes to see how I analysed the data and incorporated the interactivity into the dashboard.
Thanks for Joining Preview 03:43
It's a wrap. An overview of what you've learnt before you're set free to start creating your own amazing dashboard reports.
This course includes:
- 12.5 hours on-demand video
- Access on mobile and TV
- Certificate of completion
Related Courses
Recent Posts
5 Successful Strategies for Fashion Startups to Implement in 2021
Over 50 big fashion retailers experienced a huge jump in revenue in 2020 under the cloud of COVID-19. Even famous brands like Levi’s and Nike ...Read more
Learn Big Data through these courses
Big data plays a crucial role in a company’s development. We need analysts to provide better insights and make rational decisions. The data is so ...Read more
5 Amazing Online Graphic Desgin Courses In 2022
Graphic Design is one of the growing professions in today’s life and can make a lot of money. But if you want to gain information ...Read more
Why do we recommend a career in finance?
The outcome of every business is cash. It doesn’t matter if it’s a small-scale or large-scale business. The results are always cash. Some of this ...Read more
Interesting Big Data Projects You Need to Watch Out
In the past five years, huge evolvements have been seen in big data projects, big data analytics, and machine learning. However, several issues of development ...Read more
