Excel Dashboards and Data Analysis Masterclass
Excel dashboards are a powerful way to leverage Excel functionality, build and manage better presentations and improve your Excel and data visualization skills. In this course I’ll show you how you can turn Excel into your own, personal Business Intelligence tool and create Interactive Charts and awesome Dashboards in Microsoft Excel.
Learn how to make 4 well-designed Dashboards from Scratch!
Content and Overview
In this course my goal is to give my students the practical knowledge, with real-world examples and step by step instructions
, to create professional and designer-quality Dashboards in Excel.
With over 100 Lectures, quizzes, assignments, real-life Excel projects+SAMPLES and weekly updates, learning advanced Excel techniques has never been easier. This course is a concise and practical go-to guide for creating Interactive Charts and awesome Dashboards in Microsoft Excel. It also expands in to Pivot Tables, Pivot Charts, Conditional Formatting, Functions, Formulas and Macros.
You’ll have lifetime access to watch the videos whenever you like. If you’re not 100% satisfied there is a 30 day money back guarantee!
Plus you will get fast and responsive support within 24 hours.
What are you waiting for? Enroll now! (it is 100% no risk deal)
Why is this course different?
It’s super practical. Free downloadable Excel working files are included to allow you to follow along using the same material I use throughout the lectures. You can download all of them.
It’s comprehensive and fast. Microsoft Excel charts are complex, that’s why the course is broken down into bite-sized pieces
The course also expands in to Conditional Formatting and Functions-Formulas
I update the Excel class monthly with new lectures!
You will learn the latest versions of Microsoft Excel (2019,2016 and 2013) but the concepts are compatible with Excel 2010 and 2007.
Compatible with Microsoft Office for Macintosh
What am I going to get from this course?
Learn the Dashboard Creation Process from start to finish
Create 4 complete Professional Dashboards: Business Sales Dashboard, KPI Dashboard, Pivot/Slicer Dashboard and Traffic lights Dashboards
How to create amazing data visualizations in a matter of seconds!
How to import and work with Externel Data in Excel
Modifying an Excel chart visually: styles and colors, create a pictograph, Excel’s camera tool, shapes, effects, text etc.
Learn useful functions like VLOOKUP, INDEX, MATCH, HLOOKUP, SUMPRODUCT, CHOOSE etc
How to create mini charts like Sparklines and make data analysis fun!
Advanced Excel chart types like Pyramid charts, Pareto charts, Gantt charts, Gauge-Speedometer charts, Waterfall Charts, Bullet charts and Waterfall charts.
How to create Form Controls like Combo Box, Scroll Bar, List Box, Check Box etc
How to Protect your Dashboard
Bonus material : Excel most useful shortcuts
Enrol now and enjoy the course!
Who is this course for?
Everyone who wants to master Excel Graphs, Pivot Charts and Dashboards
Students with willingness for learning
Only basic prior experience in Microsoft Excel or spreadsheets is required to get the most out of this Excel course.
At the end of this Microsoft Excel class you will be given a Certificate of Completion.
Testimonials for this Course
“I learned so many new excel tricks from this course. It covers almost everything. Andreas impressed me for updating his course so often! It worth every cent. Great job!” – Chris K
“Outstanding course! Very thorough and easy to understand. Sample spreadsheets make it very simple to follow along and provide hands on work. I would highly recommend this course.” – Kelli Kellen
“A strongly recommend the course for all type of excel users. The lecturer follows a systematic and inspiring approach to present the most important techniques for data visualization with Excel.” – Tommy Kalman
*** learning is more effective when it is an active rather than a passive process *** Euripides Ancient Greek dramatist
Hi, I’m Andreas and I want to thank you for taking this course on Excel Dashboards and Charts. In this course, I’ll teach you how to create stunning dashboards and advanced charts.
DASHBOARD DESIGN PRINCIPLES
An effective data model provides the foundation upon which your dashboard or report is built
DASHBOARD SOURCE DATA
DASHBOARD FUNCTION TOOLBOX
In this lecture we are going to learn about the OFFSET Function. We’ll talk about what the formula is, learn some examples and how to use the OFFSET function to build dynamic named ranges.
In this video tutorial, we are going to talk about Excel ROWS and COLUMNS Formulas. So let’s dive in!
The SUMPRODUCT function multiply values from two or more ranges of data, add the results and return the sum of the products.
The Microsoft Excel CHOOSE function returns a value from a list of values based on a given position.
The CHOOSE formula is simple to write.
=CHOOSE(some number, value 1, value 2, value 3....)
and CHOOSE will pick a value based on some number.
DASHBOARDS COMPONENTS & ADVANCED CHARTS
Sparklines are small,
simple charts that are easy to make, easy to understand, and small
enough to fit in a single cell alongside your data.
Learn everything about sparklines and why they are useful for dashboards.
Sparklines are one of the best chart types to include in dashboards
Sparklines were first introduced by Edward Tufte in his book Beautiful Evidence. Tufte says: “A sparkline is a small intense, simple, word-sized graphic with typographic resolution.” Stephen Few expands Tufte's definition in his book Information Dashboard Design: “Their whole purpose is to provide a quick sense of historical context to enrich the meaning of the measure. This is exactly what's required in a dashboard.”
In this Dashboard each item of information is displayed in a way that can be quickly examined to assess performance.
An in-cell chart is useful because it can instantly provide trending details.
Learn how to make an instant, in-cell bar graph.
Similar to sparklines, conditional formatting provides a way to visualize data and make worksheets easier to understand. Conditional formatting is more flexible, applying specified formatting only when certain conditions are met. By applying conditional formatting to your data, you can quickly identify variances in a range of values with a quick glance. It allows you to automatically apply formatting—such as colors, icons, and data bars—to one or more cells based on the cell value.
Data Bars are horizontal bars added to each cell, much like a bar graph. Using the data bars conditional formatting option can sometimes serve as a quick alternative to creating a chart
In this Lecture we will create a combination of Data Bars , Icon Sets and special fonts to emphasize key metrics visually.
In this lecture, we are going to learn how to use symbols in formulas and insert them into our chart. The symbols are also known as Unicode characters. Especially we are going to use up and down arrows.
Hi everyone, in this lesson, I am going to talk about the rollover method and how we can use it to show us dynamically on-demand chart details.
Specifically, we will create a table of these products and a dynamic chart showing us the rating of each product. To do that, I will use formulas, with the help of INDEX, MATCH, AVERAGEIF, COUNTIFS, IFERROR and HYPERLINK functions. Also, I will use the Rollover Method, User Defined Functions or UDF, Macros, VBA code, Advanced Filter and conditional formatting.
You will learn a lot of new staff in this video tutorial, so pay attention because it’s a crafty tutorial.
Let’s dive in!
Found in many dashboards, the bullet graph serves as a replacement for dashboard gauges and meters.
Have you ever seen a population pyramid chart? In this lecture you are going to learn how to create an age pyramid chart with simple step-by-step instructions.
There are times that we want to compare some data, for example product sales, for a specified period. A great choice is by creating and using bi-directional bar charts in Excel.
In this video tutorial you'll learn how to make awesome thermometer charts.
An Excel Waterfall chart shows the cumulative effect of positive and negative amounts, based on a starting value.
In this video tutorial we are going to make a Box and Whisker Diagram. Box and Whisker Charts (Box Plots) are commonly used in the display of statistical analyses. Excel doesn’t offer a box-and-whisker chart, but you can create your own custom Box and Whisker charts, using stacked bar or column charts and error bars. the box-and-whisker plot shows the minimum, first quartile, median, third quartile, and maximum of a set of data. Statisticians refer to this set of statistics as a five-number summary.
Quiz of Section:8
FORM CONTROLS & DYNAMIC CHARTS
Learn how to create awesome dynamic charts with INDEX function.
Here is a dynamic way of presenting the information, for example
month by month, based on your projections. A pretty effective way of
presenting the information using form controls.
Learn all the steps to create a powerful dynamic chart and the usefuleness for the dashboards.
In this lecture we are going to learn how to make a check box and how we can use it for a dashboard. A check box turns on or off a value that indicates an opposite and unambiguous choice. Check boxes are one of the simplest yet effective form controls to use them in a Dashboard
List boxes are another great tool for creating Dashboards. With a list box you can control a chart or a bunch of charts.
If you cannot use data tables, you can use OFFSET formula to make dynamic ranges for chart data.
An easy and automatic way to create a chart that depends on a data set with a varying number of rows, using Table feature.
Did you know that your charts will dynamically adjust as you filter the underlying data? Learn how in this lecture.
PIVOT TABLES & CHARTS TECHNIQUES
Pivot tables allow you to manipulate report output for ad hoc and interactive analysis.
The Pivot table is composed of the following areas:
Learn how to create a Pivot Table easy.
In this lecture, we are going to learn how to create and modify a Pivot Table
A PivotChart can help you make sense of this data. While a PivotChart shows data series, categories, and chart axes the same way a standard chart does, it also gives you interactive filtering controls right on the chart so you can quickly analyze a subset of your data.
Learn the two main methods to create a Pivot Chart.
Slicers provide buttons that you can click to filter PivotTable data. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is shown in a filtered PivotTable report.
Learn how to present Pivot Charts in an interactive way using Slicers and various ways to modify a Pivot Chart.
You can filter the data in a pivot chart directly using field buttons.
How can we change the colors, elements and layout of a chart? Also learn an easy way to filter the data inside the chart, using field buttons.
PIVOT TABLES & CHARTS TECHNIQUES
BUSINESS SALES DASHBOARD
In this Lecture we are going to learn how to make a mock-up of the dashboard
PIVOT / SLICER DASHBOARD
Learn how to create effective professional dashboards using Pivot Charts and Pivot Tables - Part:1
Learn how to create effective professional dashboards using Pivot Charts and Pivot Tables - Part:2
Learn how to create effective professional dashboards using Pivot Charts and Pivot Tables - Part:3
TRAFFIC LIGHTS DASHBOARD
In this 4-part lecture you'll learn how to create a really impressive Sales Dashboard with Traffic Lights and Sparklines. These techniques can be used on any Dashboard.