Microsoft Excel - Dashboards

Testimonials (117 reviews)

Features

Premium video tutorials

Award-winning instructors

Personalized learning

Get certified

Learn at your own pace

Mobile (learn on-the-go)

Unlimited tests and quizzes

Regularly updated content


Overview

Take your Excel skills to a whole new level by learning how to build dynamic Excel dashboards to show off your data. This online course will teach you the data visualization skills you need to design any dashboard in Excel exactly the way you want.

You should come to this course with a comfort level of building IF functions at a minimum, and knowledge of Pivot Tables is an asset, but not a requirement. At the end of the course, you will have gained the practical skills you need to create beautiful, functional and interactive Excel dashboards.

Highlights:
•    42 practical tutorials.
•    Understand the goals of data visualization.
•    Learn to use conditional formatting, from data bars to icon sets to add finesse to your dashboard.
•    Review the Excel logic functions which are key to adding dynamics to your dashboard.
•    Explore the 5 rules of effective charting.
•    Start by building bar charts, column charts, pie charts and line charts to display your data.
•    Build more complex charts like scatter plots, combination charts and more to really tell your story.
•    Add interactivity to your dashboard with the MATCH () and INDEX () functions.
•    Leverage pivot tables within your dashboard to add even more interactivity.
•    Learn “black magic” dashboarding tricks, like using Emojis and VLOOKUP to further enhance your data.

Video tutorials are recorded in Microsoft Excel 2016 for PC. Learn more about how a GoSkills Excel certification can boost your career.

Want to be a more efficient Excel user? Start learning 200 of the best Excel shortcuts for PC and Mac.

Once enrolled, our friendly support team and tutors are here to help with any course related inquiries.

Microsoft Excel - Dashboards
Focus video player for keyboard shortcuts
Auto
  • 720p
  • 540p
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

Summary

Skill level: Beginner
Certificate: Yes (Excel certification)
Lessons: 42
Accredited by: CPD
Pre-requisites: None
Versions supported: 2016
Video duration: 3h 55m
Estimated study time: 21h for all materials

Accreditations and approvals

CPD - The CPD Certification Service.


Syllabus

1

What is a Dashboard?

The goals of data visualization and dashboards, what they should do, and what they shouldn't.

Video time: 05m 00s

2

Getting Started

How do you go from a blank spreadsheet to a dashboard?

Video time: 05m 02s

1

Cell Highlighting

Dynamically highlighting values that fall in the top or bottom ranges of your data sets.

Video time: 05m 44s

2

Data Bars

The easiest way to add easy to read "bling" to your Dashboard.

Video time: 05m 37s

3

Icon Sets

Adding stoplights, check marks, flags and other icons to your Dashboard.

Video time: 05m 50s

4

Color Scales

Applying dynamic heat maps to your data in order to show outliers and trends.

Video time: 04m 47s

5

Logic Function Review

The key to adding dynamics to your dashboard starts with Excel's Logic Functions.

Video time: 06m 00s

6

Formula Based Conditional Formats

Leveraging the logic of the IF() function to drive conditional formats based on formulas.

Video time: 05m 34s

7

Understanding Conditional Formatting Rule Precedence

Understanding why you can define two different conditional formatting rules, yet end up with a mixture of both.

Video time: 05m 21s

1

5 Rules of Effective Charting: Rules 1-2

Exploring two rules that help ensure your charts convey their intended message.

Video time: 05m 24s

2

5 Rules of Effective Charting: Rules 3-5

Exploring the final three rules that help ensure your charts convey their intended message.

Video time: 05m 24s

3

Bar Charts

Bar Charts are useful for comparing values to each other. This lesson examines features that can be tweaked to optimize their consumption.

Video time: 05m 27s

4

Column Charts

Column Charts are useful for comparing values to each other. This lesson explores ways to help make them as effective as possible via some of their available options.

Video time: 04m 09s

5

Pie Charts

Pie Charts can be useful for comparing values as a % of the whole. This lesson explores way to help make them more effective.

Video time: 06m 46s

6

Line Charts

Useful for showing trends over time, this lesson explores line charts and some of their options.

Video time: 06m 03s

7

Scatter Plots

This chart type can be useful for identifying clusters and outliers.

Video time: 06m 30s

8

Sunburst Charts and Treemaps (Excel 2016+)

Sunburst and Treemap charts are intended to show the breakdown of the source data.

Video time: 06m 38s

1

Combination Charts - Column and Line

Combination charts open a whole new world of charting. In this lesson we combine column and line charts together to make a compelling chart.

Video time: 04m 42s

2

Combination Charts - Area and Line

This lesson shows a trick to include an extra data series in your chart that fades in and out of view as it's needed.

Video time: 06m 12s

3

Plotting Data Using the Secondary Axis

When you need to show values of a different scale, there is no better way than to add an additional axis.

Video time: 05m 03s

4

Bullet Charts

This complex looking chart conveys a TON of information comparing a single target value against multiple different markers.

Video time: 04m 53s

5

Waterfall Charts

Waterfall charts help break "net change" into increases and decreases, giving more information about the business cycle.

Video time: 06m 26s

6

Sparklines

In this lesson, we will look at sparklines: what they are and how to create and modify them.

Video time: 06m 08s

7

Forecast Sheets (Excel 2016)

Generating forecast charts in Excel is only a few clicks away, resulting in a chart that you can customize and update later.

Video time: 06m 03s

8

Micro Charting

Leveraging Micro Charts to build a very quick visual summary of your key metrics.

Video time: 05m 11s

1

Understanding the MATCH() Function

The MATCH() function may not look like much, but it can pull back the position of a data element: something that is key for dynamic dashboarding

Video time: 06m 06s

2

Pinpoint Data with INDEX(MATCH())

Using a combination of the INDEX() and MATCH() functions to dynamically restate the dashboard source information

Video time: 06m 25s

3

Benefits of NA Values

Many users fear #N/A values appearing in their spreadsheets. This module explores how to create this result with the NA() function, and why it's fantastic for charts

Video time: 06m 07s

4

Data Validation

Drop down lists avoid the risk of "Garbage Out" by preventing your users from putting "Garbage In"

Video time: 05m 37s

5

Working with Form Controls

A variety of different objects that add some visual style to your dashboards, as well as provide mechanisms to force valid data entry

Video time: 06m 13s

1

Intro to Pivot Tables

A brief introduction/reminder about the power of Pivot Tables

Video time: 05m 59s

2

Applying Conditional Formats to Pivot Tables

Understanding the tips and tricks to get conditional formats working properly on Pivot Tables

Video time: 05m 04s

3

Filtering and Linking Dashboards Using Slicers

Highly visible and engaging, these devices can provide your users with a method to easily select valid object or filter in to just their slice of the pie

Video time: 05m 51s

4

Extracting Data Points with GETPIVOTDATA()

Leveraging the GETPIVOTDATA() function in order to extract specific data points from a Pivot Table

Video time: 05m 20s

5

Using Pivot Charts

The ins, outs and shortcomings of Pivot Charts

Video time: 05m 26s

1

Understanding Custom Number Formats

Excel has an entire language for displaying values just the way you need them. Don't see your format in the list? Let's look at how to roll your own!

Video time: 06m 22s

2

Displaying Variances with Custom Number Formats

A classic cost-accounting trick to display variance in a easy to read way (and avoid watching your audience do math in their heads at each review)

Video time: 05m 19s

3

Conditional Formatting of Chart Axes

Leveraging Custom Number Formats in order to provide conditional formatting for chart axes

Video time: 05m 58s

4

In-Cell Charts Using the REPT() Function

Leveraging the REPT() function and a special font in order to build charts in cells via formulas

Video time: 05m 54s

5

Display Emojis in Your Dashboards

Did you know that Excel can display Emojis in the worksheet? Why not leverage that ability to help convey your message?

Video time: 03m 28s

6

Displaying Indicators on Charts

Sometimes showing the chart just isn't enough and you want to display a text message with an indicator on the chart canvas. This lesson explores how to make that happen

Video time: 04m 19s

7

VLOOKUP() for Pictures

A cool trick that shows how we can dynamically look up pictures and return them to our worksheet

Video time: 06m 16s

Download syllabus