Excel Dashboards and Data Analysis Masterclass
4.5 (997 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
7,682 students enrolled

Excel Dashboards and Data Analysis Masterclass

Create 4 Eye-Catching Dynamic Microsoft Excel Dashboards from Scratch (Excel Dashboard Templates + Workbooks Included)
Bestseller
4.5 (997 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
7,682 students enrolled
Last updated 3/2020
English
English
Current price: $104.99 Original price: $149.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 9.5 hours on-demand video
  • 2 articles
  • 84 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Create 4 Professional Interactive Excel Dashboards from scratch
  • Create over 10 Amazing Interactive Non-Standard Charts in Microsoft Excel. BONUS: Excel Workbook files + Sample files included
  • Improve general Excel knowledge - Pivot Tables and Pivot Charts, Conditional Formatting, Functions, Formulas and Macros
  • Understand and Identify the Principles of Data Analysis
  • Build Interactive Dashboard Reports with Buttons and Drop Down Menus
  • Learn from an instructor with over 18 years of experience with Microsoft Excel, teaching thousands of students in his own computer learning school
  • Analyze Excel data using Excels based Functions
  • Course updated regularly
  • An instructor ready to answer your questions in less than 24 hours
  • Watch high-quality video lectures with lifetime access
  • Quizzes and exercises
  • Certificate of completion
Requirements
  • This course was recorded with Excel 2019, Excel 2016 and Excel 2013 but it's compatible also with Excel 2007 and 2010
  • It is also compatible with Microsoft Office for Mac
  • Only basic prior experience in Excel or spreadsheets is required to get the most out of this Excel course
Description

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?

  • Students

  • Entrepreneurs

  • Business Professionals

  • 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

Who this course is for:
  • Everyone who wants to master Excel Dashboards
  • Business Professionals
  • Students
  • Entrepreneurs
  • Students with a willingness for learning
Course content
Expand all 91 lectures 09:32:29
+ INTRODUCTION
2 lectures 08:11

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.

Preview 06:22
DOWNLOAD: Project Files & Course Study Tracker &Important Notes for New Students
01:49
+ DASHBOARD DESIGN PRINCIPLES
4 lectures 13:57
Dashboard Creation Process
02:38
Color Tips & Layouts
04:57

An effective data model provides the foundation upon which your dashboard or report is built

Building an Effective Data Model
02:58
Understanding the Dashboard Design Principles
3 questions
+ CHARTING TECHNIQUES
7 lectures 56:38
Introducing Excel Charts
10:12
Formatting Charts
12:48
Advanced Charting and Formatting Tips
06:01
Camera Tool
01:56
Useful Keyboard Shortcuts and Tips
05:47
How to Make a Dynamic Chart with Drop-Down Lists and Filters
10:59
Practicing Charting Techniques
3 questions
+ DASHBOARD SOURCE DATA
4 lectures 15:51
Importing External Data
02:48
Importing External Data from Text File
04:59
Importing Data From Excel Spreadsheet
04:31
Importing Data from Microsoft Access
03:33
+ DASHBOARD FUNCTION TOOLBOX
10 lectures 54:02
INDEX and MATCH Functions
06:34

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.

OFFSET Function
04:07

Hello everyone!

In this video tutorial, we are going to talk about Excel ROWS and COLUMNS Formulas. So let’s dive in!

ROWS and COLUMNS Functions
04:49
Text Functions: LEFT, RIGHT, MID, LEN, TEXT, TRIM, LOWER, UPPER, PROPER, REPT
10:55
SMALL and LARGE Functions
04:53
VLOOKUP Function
06:13

The SUMPRODUCT function multiply values from two or more ranges of data, add the results and return the sum of the products.

SUMPRODUCT Function
03:29
SUMIFs and COUNTIFs Functions
05:21

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.

CHOOSE Function
03:21
Create a Timesheet using INT and MOD Functions
04:20
DASHBOARD FUNCTION TOOLBOX
DASHBOARD FUNCTION QUIZ
3 questions
+ DASHBOARDS COMPONENTS & ADVANCED CHARTS
21 lectures 02:29:24
TOP and BOTTOM Ranking Chart
07:22

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 and Win/Loss Charts
04:33

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.

Create a Dashboard using Sparklines
04:27

An in-cell chart is useful because it can instantly provide trending details.

Learn how to make an instant, in-cell bar graph.

In-Cell Charts & REPT Function
02:33

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

Comparisons & In-Cell Charts with Conditional Formatting
03:40

In this Lecture we will create a combination of Data Bars , Icon Sets and special fonts to emphasize key metrics visually.

Highlight Comparisons
06:30

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.

Symbols in Formulas & Charts - IF Function
03:57

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!

Rollover Method-how to create a 5-star rating system with Excel Dynamic On-Deman
24:29
Excel Animated Chart using VBA Code
08:46
Excel Infographics: How to Create a People Graph
08:27
Dot Plot Chart & Dumbbell Chart
11:23
Top 5 ways to create a Funnel Chart
16:02
Excel Small Multiples
04:46
PANEL CHART
08:01

Found in many dashboards, the bullet graph serves as a replacement for dashboard gauges and meters.

BULLET CHART ~ Ideal for Dashboards
03:57

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.

POPULATION PYRAMID CHART
04:04

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.


BI-DIRECTIONAL BAR CHART - Comparing Data
03:21
A thermometer chart can help us to compare targets with actual values. So it's an excellent chart which help us to measure performance or the percentage of a task.

In this video tutorial you'll learn how to make awesome thermometer charts.

THERMOMETER CHART ~ Measuring Performance
04:14

An Excel Waterfall chart shows the cumulative effect of positive and negative amounts, based on a starting value. 

WATERFALL or BRIDGE CHART ~ For Sales Analysis
06:10

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.

BOX AND WHISKER DIAGRAM ~ STDEV, QUARTILE & MEDIAN Function
09:31

Quiz of Section:8

DASHBOARDS COMPONENTS: SPARKLINES - MINI CHARTS
3 questions
+ FORM CONTROLS & DYNAMIC CHARTS
10 lectures 01:03:26

Learn how to create awesome dynamic charts with INDEX function.

Combo Box & INDEX Function
03:17
Option Buttons
05:45

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.

Scroll Bar
05:32
Scroll Bar Example: Create a Dynamic Heat Map
05:12
Scroll and Sort Table with Formulas & Conditional Formatting
24:49

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

Check Box
04:15

List boxes are another great tool for creating Dashboards. With a list box you can control a chart or a bunch of charts.

List Box - INDEX & COLUMN Function
06:12

If you cannot use data tables, you can use OFFSET formula to make dynamic ranges for chart data.

Dynamic Data Range & OFFSET Function
03:55

An easy and automatic way to create a chart that depends on a data set with a varying number of rows, using Table feature.

Dynamic Chart with Tables
02:00

Did you know that your charts will dynamically adjust as you filter the underlying data? Learn how in this lecture.

Dynamic Chart with Data Filters
02:29
+ PIVOT TABLES & CHARTS TECHNIQUES
6 lectures 21:54

Pivot tables allow you to manipulate report output for ad hoc and interactive analysis.

The Pivot table is composed of the following areas:

  • Filters
  • Rows
  • Columns
  • values

Learn how to create a Pivot Table easy.

The anatomy of a Pivot Table
02:14

In this lecture, we are going to learn how to create and modify a Pivot Table

Variances in Pivot Table & Filtering
05:19
How to use the GETPIVOTDATA function
04:42

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.

Create a Pivot Chart
03:09

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.

Pivot Slicers as Buttons to Filter Data
03:40

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.

Formating Pivot Chart & Field Buttons
02:50

PIVOT TABLES & CHARTS TECHNIQUES

PIVOT TABLES & CHARTS TECHNIQUES
3 questions
+ BUSINESS SALES DASHBOARD
8 lectures 50:29

In this Lecture we are going to learn how to make a mock-up of the dashboard

Prepare a Mock-Up of your Dashboard
07:21
Setup of Calculation Worksheet
07:03
Complex Lookup Calculations with +SUMPRODUCT Function
05:40
Preparing Data for Bullet and Clustered Stacked Chart
07:20
Create Metrics with Sparklines & Conditional Formatting
04:53
Starting to Build your Dashboard
09:17
Dashboard-Final Touches
04:52
Create Beautiful Background and Design for our Sales Dashboard
04:03
+ KPI DASHBOARD
5 lectures 46:37
Preparation of the KPI Dashboard
07:43
Set Up a Sorting Procedure for KPI’s
03:55
Get The Data Sorted - Use of RANK, LARGE, CHOOSE, ROWS, INDEX & MATCH Formulas
12:43
Add Visualizations & In-Cell Charts
09:43
Final Touches
12:33