4.64 out of 5
4.64
4371 reviews on Udemy

Visually Effective Excel Dashboards

Actionable Excel Tips (Templates & Workbooks Included) You Can Use Right Now to Create Eye-Catching Excel Dashboards
Instructor:
Leila Gharani
20,911 students enrolled
English [Auto-generated] More
How to approach an Excel dashboard project from idea to delivery
How to add interactivity to your Excel reports - No VBA required
Impress management with reports that make the decision-making process faster
Clever use of Excel core functionality for responsive spreadsheets
Learn how to create a professional KPI dashboard from scratch
Create impressive, non-standard Excel charts using smart techniques
Build Pivot Table-based dashboards for easy interactivity
Use powerful visual techniques so your content has more effect

Creating an impressive, interactive Excel Dashboard from scratch is not easy!

I used to stare at my empty sheet and wonder where do I even start?

You need a structured process in place to go from idea to outcome in a smooth way.

You’ll find out all about it in the course. We setup two fully functioning dashboards from scratch.

This course doesn’t just teach you a set of functions and features, but how you can use them together to create impressive reports.

It teaches you how to approach a new Dashboard project and how to structure your work so maintaining the dashboard becomes very easy not just for yourself but for anyone else.

Note: Course recently updated to include new content on dependent combo-boxes and info-graphic charts in Excel

What students are saying

“This course provides a lot of great tips which you can immediately apply to your spreadsheets, no matter what standard of user you are. I have been working in excel for a number of years and I am amazed at the number of simple tips I have learnt in such a short time. Fantastic.” — Carl Postians

“I learned something new and very useful in the first 10 minutes…awesome!” — Nancy Bowens

What you learn

This course teaches you to create well-designed Excel dashboards so you and your colleagues can see trends and make quick decisions informed by data.

This Excel Dashboard course comes with templates, tools and checklists that can be applied directly to your Excel reports.   

  • Excel dashboard & Report templates   

  • Checklists and design guidelines   

  • Excel workbook to follow along

What differentiates this course from the other dashboard courses? It includes:

  1. The entire process from idea to delivery – every single step without skipping key points.

  2. Tried-and-trusted techniques that are not only applicable to Excel dashboards but any Excel report.

  3. Includes Excel templates, tools and checklists that you can use for your own Excel reports.

  4. Relevant and up-to-date with industry needs right now, as it’s based on feedback and suggestions from finance professionals. 

  5. Lots of ideas and inspiration to help you come up with own report design

Complex topics are taught in a simple way by an instructor with 17 years of experience.

The course is designed to have an even balance between talking head and screen-cast.

Content is clearly organised so more advanced learners can jump in at any point and follow along with the relevant project file.

A Foundation in Dashboards

1
Introduction

This lecture introduces you to the topics covered in this online Excel Dashboard course - make sure you watch it to see if the course is for you.

2
Get the Most from the Course

I provide a brief summary of the structure of this online Excel report and dashboard course. Depending on your needs right now and how you'd like to learn, you could either jump in when I create the Excel KPI dashboard or the Regional (pivot slicer) dashboard from scratch or you can take the course from beginning to end. 

3
Download Course Files Here

You can download the main course files here - you will find a demo workbook where you can follow along with me when I cover the formulas required for dashboards, Excel Form Controls and advanced Excel charting techniques. You can also download the files from the source data section when I demonstrate different raw data sources and also the final KPI dashboard and Regional (Slicer/ Pivot table) dashboards.

4
A Dashboard Primer

What exactly is a dashboard? Find out in this lecture.

5
New project? This is Your Checklist

Based on my experience as project manager, I take you through the checklist of questions you need answered, when you start a new Excel report or Excel dashboard project. Don't forget to download the PDF document for this lecture.

Dashboards - Before You Start

1
First Steps for your Dashboard Project

Once you've gone through the checklist, these are the first steps you need to take to start with your new Excel report or Excel dashboard project. Downloadable PDF is included.

2
Create a Mockup (Wireframe) in Excel

Use the Vector Elements (graphics) included in this sample Excel file, to create mock-ups (layouts) for your Excel dashboard projects.

3
Useful Excel Keyboard Shortcuts

In this lecture I share with you my favorite Excel keyboard shortcuts when I design reports and dashboards in Excel.

4
4 Time-Saving Tips for Excel

Use these 4 time-saving Excel tips to start quicker and work faster. It makes creating reports and dashboards in Excel much easier.

5
Proper Spreadsheet Architecture & Workflow

How should you setup your Excel spreadsheet report? What's the optimal method of organizing your report and calculations? How should the data flow? In this lecture I share with you the optimal method for creating Excel dashboards and reports and how to organize your tabs for best results.

Report Design Principles

1
Layout - Best Practice

What makes a good Excel report? How you design and present the information makes a big difference in how readable your reports are. Find out what makes a good report layout in this lecture.

2
Color - Best Practice

Color plays an important role when creating Excel reports. How do you pick your colors? which color combinations work well together? Watch this lecture to find out.

3
A Color Scheme that fits with your Report

How do you come up with a color scheme for your Excel reports? How can you find the RGB code in Excel? All covered here as well as a sample color palette you can download for own use.

4
10 Concrete Design Tips for Better Excel Reports

These concrete design tips help you create better Excel reports - faster.

5
Bonus: Setting a Default Theme for New Workbooks

In this video, I show you how to set your own default theme in Excel so that every time you create a new workbook, the theme colors that are used are the ones set by you and NOT the Microsoft Office Default theme. Unfortunately setting your own default theme in Excel is not as easy as in Microsoft Word or PowerPoint where you can just tick mark - "set as default theme". In Excel you have to follow a few steps which I show in the video.

6
Excel Report Design - Test Your Knowledge

Test your knowledge on the topics learnt in the design section.

Dashboard Source Data & Formats

1
Where is Your Source Data?

The source data for your dashboard could come from different systems. This lecture provides you with a quick overview of what to expect in this section and how you can prepare for different source systems.

2
Source: Excel Spreadsheet

One possible source is to have the data in an Excel spreadsheet. The data could be in separate tabs - it could be in an Excel table or it could be in an Excel Pivot table. It could be in a tabular format or a non-tabular format. What do I mean by tabular and how do you handle cases where data is non-tabular? Find out in this tutorial.

3
Source: Microsoft Access

One possible source for the raw data of your Excel dashboard is Microsoft Access. In this lecture I show you how to import data from Ms. Access to Excel - either as an Excel Table or as a Pivot Table. I also show you how to optimize the connection properties so that when the data is changed in Access, the updated information is available in Excel.

4
Source: Text / Data / CSV File

CSV files (data file or text files) are easily extracted from most systems. It's tempting to open the files directly in Excel and then copy and paste the data in your Excel dashboard report. But doing this, is a manual process which you have to repeat every single time. A better way would be to set up an open connection to the CSV file so that every time the file is replaced or appended, you just have to refresh your data set in Excel and have the latest data available. No copy and pasting necessary. Find out how in this lecture.

5
Source: Excel Add-ins

Another common source of data is third party BI tools. Most come with an Excel-addin which enable you to connect and retrieve data from them. In a lot of cases the data sets retrieved don't come in a tabular format.

Dashboard Formula Toolbox

1
Excel Formulas You'll Need

This lecture provides an overview of the formulas covered in this section. These are Excel formulas that help you handle any situation (whether tabular or not) to transform your data and prepare it the way you need for your reports. You can download the Demo Excel workbook and follow along with me.

2
Index Match for Complex Lookups - Basics

INDEX MATCH is my most used formula when I create Excel reports. This lecture takes you through the basics of the INDEX and then the MATCH formula and how these two formulas work well together. This formula combination is not just for dashboards but for any type of analysis you need to do where you have large data sets.

3
Index Match for Complex Lookups - Advanced

Make sure you understand the basics of INDEX MATCH before moving to more advanced (actually real life) cases. Real business cases are often more complex and that's when you need to use this version of Index Match.

4
Index for Dynamic List Selection

the INDEX formula is so versatile. This video shows INDEX from another angle to give you a better idea of all that Index is capable of doing.

5
Realistic Case: Index for Dependent Drop-down Lists
6
SUMIFS, COUNTIFS, AVERAGEIFS to Handle Multiple Criteria

SUMIFS is a great formula, not to just to sum by different criteria but also to grab data out of data tables by criteria. COUNTIFS, does a count based on different criteria and AVERAGEIFS calculates the average based on single or multiple criteria. Watch this lecture to find out more about the potential of these great formulas.

7
LARGE and SMALL for Sorting

LARGE and SMALL Excel formulas are great for dashboards and any reports that require ranking.

8
ROW(S), COLUMN(S) for Indexing

Sometimes you might need indexes in Excel, especially when you're creating ranking reports. Watch this lecture to see how the ROW and COLUMN formulas would be of use here.

9
ROW as Unique Identifier for Lookups

When you create ranking reports in Excel, you need to be sure that you have unique ranks - even if two categories get the same rank and have an identical number, you still need to create a unique rank so you can organize them one after the other. Watch this lecture to find out how the ROW function could help you with that.

10
CHOOSE for Flexibility

CHOOSE function can really simplify long IF formulas. It's a great function to know and have on hand, especially if you use form controls in Excel.

11
Text Function for Formatting

Excel's TEXT function is great to use when creating Excel reports and you'd like to combine numbers with text and still keep the number formatting. Find out how it works and what you need to watch out for when using this function in your Excel reports.

12
N Function for Tracking

N function is a very simple function and can be use for "other" purposes. Find out for what, in this video.

13
GetPivotData: Extract data Efficiently from Pivot Tables

GetPivotData is a great Excel function when you want to extract your data from your pivot table in a dynamic way. It is independent of the cell position. The only requirement is that the value you want to extract is "visible" in your pivot table. Why would you want to extract data from a pivot table in a dynamic way? Find out more in this lecture.

14
INDIRECT for Flexible References

INDIRECT function can be a bit confusing at first but it's a great way to get dynamic ranges. In this lecture I show you how this works and later when we create the Slicer based regional dashboard we use this to a lookup on images. 

15
Choose & Name Manager for Flexible Ranges (Alternative to Indirect)

INDIRECT function is volatile - CHOOSE and name managers are alternative ways to get dynamic ranges and they are not volatile. Find out how they can work together in this lecture.

Form Controls for Interactive Dashboards

1
Introduction to Form Controls - Active X vs. Form Controls
2
Combo Box - Select From a Drop Down List

Learn how to use Excel's Combo Box Form control here.

3
Check Box - Check / Uncheck Option

Check boxes are great for allowing the user to select different options. Find out how check boxes work in Excel in this lecture.

4
Spin Button - Move Up & Down Lists

The spin button, changes the value in the cell by clicking the up and down arrows. Find out how you can Excel's spin button form control in this lecture.

5
Option Button - Choose One Option Only

When you want to restrict the options available to the user to just one option, you can use Excel's Option Button form control. Find out what you need to do if you are using more than one option button groups in your reports and how you need to configure the option button form control in Excel.

6
List Box - Select From a List of Option

List Box is another interactive form control that allows the user to pick an item from a list. Find out to configure the form control list box in this lecture.

7
Scroll Bar - Scroll Through Graphs & Tables

Use Excel's scroll bar from control to scroll through a graph or an Excel table. Find out which function works best here and how you can configure the Scroll bar form control.

8
Bonus: Dependent Combo Boxes (2nd list automatically resets)

Advanced Chart Techniques

1
Chart Overview & When to Use Which Graph
2
Chart Basics (for those who need to get familiar with basic features)
3
Improve Charts 1: Add Total Values to Stacked Column Charts

This lecture shows you an important technique I often use, when improving the readability of Excel charts.

4
Improve Charts 2: Add Series Labels Inside Chart

This is another technique often used to design better Excel graphs. These techniques will equip you to create non-standard Excel charts which not just impressive but also quick to read.

5
Improve Charts 3: Error Bars for Additional Control

The error bar technique is one I often use when I can't get the effect I want in a chart using the standard chart options. It's meant for statistical analysis, but error bars can do so much more for business reports. Find out how it works here and later we use this technique to create a non-standard chart for the KPI dashboard. 

6
Pro Technique: Changing Chart Ranges

This video shows you how to create dynamic chart ranges - i.e. "From - To" Charts. This means the user selects from which category or date they want to view the data, and to which category / date, and the chart only visualizes the values for the categories in between. This means the chart needs to work with dynamic ranges which change in size.


7
Info-graphics in Excel: Non-standard but Impressive Bar Chart
8
Dynamic Map Chart with Drop-down

KPI Dashboard - Setup

1
KPI Dashboard Overview

In this lecture you can see an overview of the finished KPI dashboard in Excel - this will give you a good idea of what we're setting out to achieve in the next few sections. 

2
Dashboard Wireframe

In this lecture I share with you my version of the dashboard mock-up / wireframe and how I start with drawing it out on a piece of paper and then how I bring it in Excel (using Excel shapes to draw out each section of the dashboard).

3
Spreadsheet Setup

In this lecture we start with a new blank Excel spreadsheet and start to setup our Excel file to build the dashboard on. We setup our QAT (Quick Acces Toolbar) and also define the color palette for the dashboard.

4
Setup of Raw Data Sheet

Here we take a look at the raw data and their format. The raw data is non-tabular which means we need to dig through our formula toolbox to find just the right ones to be able to prepare the data for the dashboard. This makes it all the more fun!

5
Setup of Calculation sheet

Now we start to setup the calculation sheet - here we will prepare the data for the dashboard. Organization is key for this tab - we need to break down the sheet to different sections where we do the calculations for each part of the dashboard. We can limit formatting here to some color coding but we don't need to worry about any extensive formatting here since the purpose of this tab is for data preparation only.

6
Download Dashboard till here & practice along - Draft01

Here you can download the draft Excel KPI dashboard which includes only the part we have completed in this section. Download this if you haven't followed this section but want to jump in and work together on the next section.

KPI Dashboard - Scrolling Table & Bars

1
Setup a Combo Box to Ease Selection

In this lecture we setup a combo box to be able to select the division. 

2
Complex Lookup with Index & Match

Here we setup the calculations required to grab the respective apps that belong to each of the divisions which are selected from the combo box. We need to dig inside our formula toolbox and use a version of the INDEX MATCH formula. 

3
Scrolling Data Table

In this lecture we add another level of complexity to our dashboard by adding a scroll bar so we can scroll through our table on the dashboard page. This scroll bar, will depend on the calculations we did in the previous lecture - we just need to write smart formulas on the dashboard page to get the scrolling effect.

4
Conditionally-Formatted Data Bars

Now that we have the scrolling in place, we add conditionally formatted data bars (that looks like a bar chart actually) to be able to see the deviation of actual values to previous year values.

5
Arrows for Deviations

Here we cover another conditional formatting method to get up and down arrows that show the deviations of actual values to budget. We don't use Excel's default conditional formatting symbols, but we bring in our own set of symbols here. 

6
Formatting of Scrolling Data Table

Now we just need to format the scrolling table so it fits well on our Excel dashboard page.

7
Download Dashboard till here & practice along - Draft02

Here you can download the draft Excel KPI dashboard which includes only the part we have completed in this section. Download this if you haven't followed this section but want to jump in and work together on the next section.

KPI Dashboard - Dynamic Headers

1
Conditional Comments in Header

In this lecture we setup the calculations required to create conditional comments for the header of our Excel KPI report.

2
Adding Shapes & Text boxes

Using shapes and text boxes in Excel, we bring over the result of the calculations to the header of our Excel  dashboard and arrange them for optimal presentation.

3
Download Dashboard till here & practice along - Draft03

Here you can download the draft Excel KPI dashboard which includes only the part we have completed in this section. Download this if you haven't followed this section but want to jump in and work together on the next section.

KPI Dashboard - Top/Bottom KPI Based on Selection

1
Show Top 6 Matches with Unique Identifier

In this lecture we start with the calculations we need to be able to setup a graph that shows the top 6 / bottom 6 categories that are over / under budget respectively. This part has two form controls that control the graph so we have to start slow and simple to make sure we don't over complicate things.

2
List Box to Select KPI for Top 6 Matches

Once we have setup the basic calculations, we add a list box so the user can pick between different KPI's for the graph.

3
Option Button to Toggle Between Top OR Bottom 6

Now we add the option button so the user can pick between top or bottom 6 categories. 

4
Actual to Budget Comparison - Sorted Bars with Error Bar Technique

We finally have all the necessary calculations and now we setup the non-standard Excel charts that compares actual values to budget values per KPI - it is also a sorted graph based on the top or bottom 6 selection. The error bar technique works great here.

5
Finalize Top / Bottom Report on Dashboard

Now that everything is setup and working, we bring it all over to the dashboard page. A few things you need to watch out for when you do this - all covered here.

6
Download Dashboard till here & practice along - Draft04

Here you can download the draft Excel KPI dashboard which includes only the part we have completed in this section. Download this if you haven't followed this section but want to jump in and work together on the next section.

KPI Dashboard - Scrolling Line Chart with Check Boxes

1
Complex Lookup to Prepare data for Scrolling Chart

To get the calculations for our monthly profit line chart we need a more complex index and match formula to extract the data in a dynamic way from our raw data table. 

2
Scroll-able Line Chart

Once we have the calculations setup, we create our scroll-able line chart so we can scroll through each category. We notice some problems (such as a crashing line for actual values) which we will amend in the next lectures.

3
Trick to Avoid Crashing Lines in Line Chart

This trick is great for any line charts you create in Excel. If you want to avoid data points from being plotted (i.e. avoid the crash of the line to zero), you need to apply this technique.

4
Check Box to Control Series Visibility (Hide or Show)

In this lecture we add check boxes so we can  toggle each of the line series on and off - i.e. visible and invisible. Find out how we do this in this lecture.

5
Line Series Tweaks for Better Readability

When you create line series charts in Excel, it's best to show the label for each of series at the end of the line. Find out how to do this - especially for a line that gets longer as new data is added - In this lecture I show you a trick for just that.

6
Finalize Profit Comparison Section on Dashboard
7
Download Dashboard till here & practice along - Draft05

Here you can download the draft Excel KPI dashboard which includes only the part we have completed in this section. Download this if you haven't followed this section but want to jump in and work together on the next section.

KPI Dashboard - Final Touches

1
Restrict Dashboard Area and Format for Printing
2
Dashboard Protection, Hyperlinks and Final Touches
3
Download Dashboard till here - Final

Regional (Pivot / Slicer) Dashboard - Using Slicer as Buttons

1
More Interactivity - Pivot Slicers as Buttons (also without Pivot Tables)

For this next dashboard that we build from scratch, find out how you can use pivot table and slicers to create interactivity in your reports.

2
Column Chart Controlled by Slicer Buttons

In this lecture we create a non-standard Excel column chart - with an average line - that also highlights the biggest category in a different color - this chart is not only conditionally formatted, but it also use Pivot slicers to control the view.

You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.6
4.6 out of 5
4371 Ratings

Detailed Rating

Stars 5
2812
Stars 4
1229
Stars 3
255
Stars 2
55
Stars 1
20
90459721da122bdaa71184ad8eeb666b
30-Day Money-Back Guarantee

Includes

11 hours on-demand video
14 articles
Full lifetime access
Access on mobile and TV
Certificate of Completion