Interactive Dashboards with Excel Pivot Tables & PivotCharts
4.4 (73 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.
832 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Interactive Dashboards with Excel Pivot Tables & PivotCharts to your Wishlist.

Add to Wishlist

Interactive Dashboards with Excel Pivot Tables & PivotCharts

Take your reports to the next level with Excel developing powerful interactive dashboards in a few clicks of the mouse
4.4 (73 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.
832 students enrolled
Created by Ian Littlejohn
Last updated 10/2016
English
Current price: $20 Original price: $30 Discount: 33% off
30-Day Money-Back Guarantee
Includes:
  • 1.5 hours on-demand video
  • 7 Articles
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Create interactive dashboards with Pivot Tables and Pivot Charts
  • Learn best practices for dashboard development
  • Create a Sales Dashboard
  • Create a Human Resource Dashboard
  • Create a Finance Dashboard
  • Create Column, Line and Pie PivotCharts
  • Learn best practices for Pivot Chart development
  • Create Sparklines and Data Bars in your Dashboards
  • Improve your effectiveness to develop management reports and gain new insight and intelligence from your data
  • Filter your data effectively with Report Filters and Slicers
View Curriculum
Requirements
  • Students should have a basic knowledge of Excel Pivot Tables and Pivot Charts
  • Excel 2010 or 2013
Description

Great explanation ... Check!. Course has a great flow to it ... Check!. Lessons are presented clearly ... Check!. Each lesson has just the right amount of information ... Check! - Michael Choke

______________________________________________________________________

About the course:

This course is for all Excel users who wish to learn how to create powerful interactive dashboards using Pivot Charts and Pivot Tables. This course will teach you to turn your Excel data into powerful tables, graphs and dashboards.

In this course you will learn to create three different dashboards.

The first dashboard will focus on Sales information creating key metrics, trends, top 10 analysis and a percentage contribution.

The second dashboard will focus on Human Resource data and display key metrics, number of employees by age, number of employees by years of service and gender profile.

The third dashboard focuses on financial information and allows the user to understand the variance of the Profit from previous months and how much it has changed from month to month. In addition techniques and tools such as sparklines, data bars, trendlines and slicers are also included.

This course is very important for all Excel users who are required to develop management reporting or to perform data analysis and wish to gain insight and intelligence from their data.

The course is designed to take you through step by step to create the interactive dashboards using simple drag and drop techniques. This course does not include any custom programming or macros. All the dashboards and techniques can be followed by any Excel user who understands the basic principles of Excel.

An introductory knowledge of Pivot Tables and Pivot Charts will assist you in the course. However you can follow the step by step instructions if you do not have an in-depth knowledge of Pivot Tables and Pivot Charts. Please note that you will require Excel 2010 or 2013 for this course. The course is presented using Excel 2013.

Who is the target audience?
  • Business users who analyze data and create management reports using Excel
  • Excel users
Curriculum For This Course
Expand All 29 Lectures Collapse All 29 Lectures 01:36:13
+
Introduction
1 Lecture 02:33

Introduction to the course. In this lesson we preview the three dashboards that we will be creating in the course.

Preview 02:33
+
Interactive Sales Dashboard
10 Lectures 27:16

Download the Training Data for the Sales Dashboard

Please download the Dashboard Training Data file 1. The Excel file has the data required to complete the first dashboard.

Load the Dashboard Training Data 1 file
00:09

This lesson reviews the steps that will be followed to create the first Sales Dashboard.

Preview 2 pages

In this lesson we review the completed Sales Dashboard.

Preview 03:19

In this lesson we will create the key metrics for the Sales Dashboard. The key metrics include the Total Sales, Total Order Quantity and Total Profit.

Key Metric Pivot Table
03:58

In this lesson we create a Line graph displaying the Sales by Year and Quarter. The Line graph will display the trend for the Sales over this period of time.

Year and Qtr Trend
05:06

Top 10 Analysis is an important method of getting insight into your data. In this lesson we will create a Pivot Table displaying the Top 10 Customers by Profit and also display the Sales value for the Customer.

Top 10 Customers by Profit
03:28

In this lesson we will create a 3 dimensional Pie chart displaying the percentage contribution for each of the Regions in the data set.

Percentage Profit by Region
03:58

Slicers are the technology that allow the filtering and asking questions of your data. In this lesson we will add Slicers to our dashboards and show how to create interactive dashboards with this powerful technology.

Interactive Slicers
05:02

Time Slicers are a new Excel 2013 technology that allows you to use a Date field as a Slicer in your dashboard. In this lesson we add a Timeline slicer to our dashboard.

Interactive Timelines
02:07

In this section we have the completed dashboard in an Excel file. Please download the Excel file to view the completed dashboard.

Completed Dashboard 1
00:08
+
Human Resource Interactive Dashboard
10 Lectures 37:54

Please download the Human Resource Excel data source to use for the Training data. Please use the downloadable files section to download the Excel file

Download the Training Data Set
00:10

In this lesson we review the completed Human Resource dashboard

Preview 01:58

In this lesson we will create the key metrics for the Human Resource dashboard. The key metrics will include the total number of employees, the average age of the employees, the average length of service of the employees and the breakdown between male and female.

Key Metrics
06:56

In this lesson we will create a column chart displaying the number of employees for each department.

Employees by Dept Column Chart
04:24

Age Analysis is an important part of analyzing Human Resource data. Knowing the number of employees that you have between the ages of 20 and 29, 30 and 39 etc. is important in management reporting. In this lesson we will create a table displaying the age analysis of the employees by gender.

Age Analysis by Gender
04:01

In this lesson we will create a three dimensional Pie chart that displays the breakdown by Gender. The chart will display the percentage of Males and the percentage of females in the Human Resource dashboard.

Percentage Gender Pie Chart
04:38

Percentage by Gender
04:38

In this lesson we will learn how to analyze the employees by the number of years length of service. We will analyze how many employees have between 0 and 2 years, 3 to 5 years and so on.

Analysis by Length of Service
04:12

In this last lesson we will add an interactive slicer to the dashboard. The slicer will display the different departments in Employee Master file.

Adding Slicers
06:52

Download the completed Human Resource dashboard from the Resources section.

Download the Completed Human Resource Dashboard
00:05
+
Finance Dashboard
7 Lectures 26:02

Please use the Training Data file from the first dashboard.

Download the Training Data File
00:09

In this lesson we will review the completed Finance dashboard.

Preview 02:58

In this first lesson we will create the Pivot Table that displays the Profit by Year and Month. We will also calculate the difference from the Previous Month.

Difference from Previous Month Pivot Table
04:52

In this lesson we will add icons to represent whether the Profit moved up, down or stayed the same in the Pivot Table. We will also be adding Data Bars to show the magnitude of the change in the Profit and also a Ranking for the Month.

Adding Data Bars
10:48

In this lesson we will add a sparkline to show the trend of the Profit over the Year. The sparkline is a mini line graph that fits into an individual cell in the spreadsheet.

Adding Sparklines
04:20

In this last step we will add interactive slicers which will allow the user to select Country or the Business Segment.

Adding Slicers to the Dashboard
02:52

Download the Completed Dashboard

If you would like to download the completed dashboard. Please download the Excel file from the Resource section.

Download the Completed Finance Dashboard
00:03
+
Conclusion
1 Lecture 00:19
Conclusion
00:19
About the Instructor
Ian Littlejohn
4.4 Average rating
1,243 Reviews
8,490 Students
11 Courses
Excel Power Tools, Power BI and Google Data Studio Trainer

Ian is a trainer that specializes in Microsoft Excel Business Intelligence tools and Google Data Studio.  Ian is an experienced trainer that teaches techniques and tools making it easy for learners to harness the power of Excel PivotTables, PowerPivot, Power Query,  Power BI and Google Data Studio.

Ian has been training learners on these powerful technologies for over 10 years making it easy for business users to easily create management reports, develop interactive dashboards and generate new insight and intelligence from business data.

Ian has over 10 years of Management Consulting experience and he brings this knowledge and skills to his training course showing Excel and Google Data Studio users how to easily create sophisticated management reports, perform data analysis and create amazing interactive dashboards without using any complex programming or specialized tools.

Ian has consulted and worked with major organizations in the Banking, Insurance, Manufacturing, Telecommunications and Logistics industries across a number of countries and continents.