Visually Effective Excel Dashboards - NEW!
4.7 (244 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,385 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
Best Selling
4.7 (244 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,385 students enrolled
Created by Leila Gharani
Last updated 8/2017
English
Current price: $30 Original price: $120 Discount: 75% off
30-Day Money-Back Guarantee
Includes:
  • 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
Requirements
  • 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
Description

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
Curriculum For This Course
98 Lectures
10:02:59
+
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
03:08

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
00:48

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
12:51

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

A Dashboard Primer
02:23

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
04:29
+
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
03:19

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
00:51

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

Useful Excel Keyboard Shortcuts
11:29

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
08:47

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
02:35
+
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
03:20

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
04:31

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

10 Concrete Design Tips for Better Excel Reports
06:04

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
06:24

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
04:41

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
06:51

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
02:16
+
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
02:07

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
10:13

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
08:36

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
11:01

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

LARGE and SMALL for Sorting
02:45

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
06:32

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
07:12

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
09:21

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
14:56

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
03:44

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
11:29

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)
06:09
+
Form Controls for Interactive Dashboards
7 Lectures 29:55
Introduction to Form Controls - Active X vs. Form Controls
02:08

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
05:18

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
02:22

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
05:01

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
03:38

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
06:17
+
Advanced Chart Techniques
4 Lectures 28:23
Chart Overview & When to Use Which Graph
02:26

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
04:57

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
08:33

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
12:27
+
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
02:44

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
09:37

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
09:49

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
06:33

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
00:12
+
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
12:30

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
13:58

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
13:26

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
12:01

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
09:21

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

Formatting of Scrolling Data Table
06:27

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
00:13
+
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
03:31

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
08:02

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
00:15
8 More Sections
About the Instructor
Leila Gharani
4.7 Average rating
835 Reviews
4,019 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.