Visually Effective Excel Dashboards - NEW!
4.7 (227 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,333 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Visually Effective Excel Dashboards - NEW! to your Wishlist.

Add to Wishlist

Visually Effective Excel Dashboards - NEW!

Actionable Excel Tips (Templates & Workbooks Included) You Can Use Right Now to Create Eye-Catching Excel Dashboards
4.7 (227 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,333 students enrolled
Created by Leila Gharani
Last updated 8/2017
Current price: $10 Original price: $120 Discount: 92% off
5 hours left at this price!
30-Day Money-Back Guarantee
  • 10 hours on-demand video
  • 13 Articles
  • 24 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • 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
View Curriculum
  • Familiarity with Excel
  • You are able to write basic formulas and are familiar with the Excel spreadsheet environment
  • Demonstration is done with Excel 2016 but the techniques work with any version of Excel - unless stated in the individual videos

Have you ever felt that your Excel dashboards and reports are more content-driven and not visually effective? Wouldn’t it be great to have both?

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 screencast.

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

Who is the target audience?
  • Business Professionals who use Excel to create dashboards and reports
  • Data analysts
  • You currently use Excel and want to improve your skills
  • You'd like to learn how to create interactive reports
  • You'd like to improve the design of your existing Excel reports
Students Who Viewed This Course Also Viewed
Curriculum For This Course
98 Lectures
A Foundation in Dashboards
6 Lectures 29:17

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.

Preview 05:38

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. 

Get the Most from the Course

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.

Download Course Files Here

This video gives you an idea of the hands-on lectures you can expect in the course. It 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.

Example of Advanced Techniques: Changing Chart Ranges

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

A Dashboard Primer

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.

New project? This is Your Checklist
Dashboards - Before You Start
5 Lectures 27:01

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.

First Steps for your Dashboard Project

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

Create a Mockup (Wireframe) in Excel

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

Useful Excel Keyboard Shortcuts

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

4 Time-Saving Tips for Excel

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.

Proper Spreadsheet Architecture & Workflow
Report Design Principles
4 Lectures 17:43

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.

Preview 03:48

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.

Color - Best Practice

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.

A Color Scheme that fits with your Report

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

10 Concrete Design Tips for Better Excel Reports

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

Excel Report Design - Test Your Knowledge
3 questions
Dashboard Source Data & Formats
5 Lectures 21:55

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.

Preview 01:43

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.

Source: Excel Spreadsheet

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.

Source: Microsoft Access

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.

Source: Text / Data / CSV File

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.

Source: Excel Add-ins
Dashboard Formula Toolbox
14 Lectures 01:57:11

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.

Excel Formulas You'll Need

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.

Preview 11:25

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.

Index Match for Complex Lookups - Advanced

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.

Index for Dynamic List Selection

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.

SUMIFS, COUNTIFS, AVERAGEIFS to Handle Multiple Criteria

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

LARGE and SMALL for Sorting

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.

ROW(S), COLUMN(S) for Indexing

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.

ROW as Unique Identifier for Lookups

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.

CHOOSE for Flexibility

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.

Text Function for Formatting

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

N Function for Tracking

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.

GetPivotData: Extract data Efficiently from Pivot Tables

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. 

Preview 11:41

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.

Choose & Name Manager for Flexible Ranges (Alternative to Indirect)
Form Controls for Interactive Dashboards
7 Lectures 29:55
Introduction to Form Controls - Active X vs. Form Controls

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

Preview 05:11

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

Check Box - Check / Uncheck Option

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.

Spin Button - Move Up & Down Lists

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.

Option Button - Choose One Option Only

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.

List Box - Select From a List of Option

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.

Scroll Bar - Scroll Through Graphs & Tables
Advanced Chart Techniques
4 Lectures 28:23
Chart Overview & When to Use Which Graph

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

Improve Charts 1: Add Total Values to Stacked Column Charts

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.

Improve Charts 2: Add Series Labels Inside Chart

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. 

Improve Charts 3: Error Bars for Additional Control
KPI Dashboard - Setup
6 Lectures 35:08

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. 

Preview 06:13

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).

Dashboard Wireframe

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.

Spreadsheet Setup

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!

Setup of Raw Data 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.

Setup of Calculation sheet

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.

Download Dashboard till here & practice along - Draft01
KPI Dashboard - Scrolling Table & Bars
7 Lectures 01:07:56

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

Setup a Combo Box to Ease Selection

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. 

Complex Lookup with Index & Match

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.

Scrolling Data Table

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.

Conditionally-Formatted Data Bars

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. 

Arrows for Deviations

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

Formatting of Scrolling Data Table

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.

Download Dashboard till here & practice along - Draft02
KPI Dashboard - Dynamic Headers
3 Lectures 11:48

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

Conditional Comments in Header

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.

Adding Shapes & Text boxes

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.

Download Dashboard till here & practice along - Draft03
8 More Sections
About the Instructor
Leila Gharani
4.7 Average rating
811 Reviews
3,941 Students
4 Courses
Excel Instructor, Business Consultant

I help companies use Excel better to improve their reports by providing customized training sessions, Dashboard Training and Enhanced Visualization Techniques. I also help improve processes, by designing interfaces and tools with VBA for Excel. 

I am a Certified Microsoft Excel Expert and have over 15 years of experience implementing and training users on Management Information Systems of different sizes and nature - these include SAP BW and Oracle HFM. My background is: Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert & Project Manager. My passion is teaching and solving difficult Excel problems. I am also addicted to learning and enjoy taking online courses on a variety of topics.

Currently, the Excel training sessions I offer are offline & onsite. However, all the classes that I've myself taken, have been online. I find them to be very practical and effective. For this reason, I decided to bring my offline classes to the online world. "Data Visualization Secrets for Impressive Reports" is my first course for this.

I place great value on keeping my training sessions not only informative but also interesting. With technical topics like Excel, students learn the most when they attempt to solve a problem on their own. They are most engaged when they make mistakes and attempt to fix these. For this reason, I make sure to include enough examples and exercises for students to download and work with.

I look forward to having you in my classes.