Amazing Reports and Data Analysis with Excel Pivot Tables
4.5 (78 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.
925 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Amazing Reports and Data Analysis with Excel Pivot Tables to your Wishlist.

Add to Wishlist

Amazing Reports and Data Analysis with Excel Pivot Tables

Learn to easily create powerful management reports and analyze data in minutes using Excel Pivot Tables
4.5 (78 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.
925 students enrolled
Created by Ian Littlejohn
Last updated 11/2016
English
Current price: $20 Original price: $30 Discount: 33% off
30-Day Money-Back Guarantee
Includes:
  • 2.5 hours on-demand video
  • 12 Articles
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Create management reports in minutes
  • Analyze business data effectively
  • Learn to format Pivot Tables
  • Calculate trends
  • Calculate percentage contributions
  • Create a range of calculations such as rankings and difference from
  • Learn to create frequency analysis with Pivot Tables
  • Use Slicers to analyze data in seconds
View Curriculum
Requirements
  • Excel 2007 / 2010 / 2013
Description

COURSE UPDATE

  • Expert Guidance on Creating Amazing Reports and Data Analysis with Excel Pivot Tables - This course is great in explaining how to create amazing reports and data analysis with Excel Pivot Tables. The structure of the course has been well planned - each lesson is presented with great clarity in a concise, right to the point manner - Michael Choke

Amazing Reports and Data Analysis with Excel Pivot Tables

Create amazing reports and analyze data in minutes with Excel Pivot Tables. This course focuses on creating and understanding how to use Excel Pivot Tables to quickly analyze and understand your business data. Pivot Tables make it easy to compile management reports and generate new insight and intelligence from your data in minutes.

This Excel Business Intelligence training course is for all business users who are required to develop management reports, analyze data or ask ad-hoc questions of their data. McKinsey Consulting in a recent report entitled 'Big data: The next frontier for innovation, competition, and productivity' emphasizes that data skills will become the basis for competition, innovation, growth and productivity. The report also highlights the need for around 1.5 million managers in United States with data skills.

This course has over 25 video lectures that take you step by step through creating management reports and analyzing data with a couple of clicks of the mouse. The course is structured into 9 sections which takes you from creating simple management reports to sophisticated data analysis.

Learn techniques to easily aggregate your data, produce reports, understand trends, filter your data, create interactive reports and use sophisticated calculations.

So begin your journey today with Excel Business Intelligence.

Who is the target audience?
  • Excel users who want to analyze business and marketing data and create management reports
Curriculum For This Course
Expand All 41 Lectures Collapse All 41 Lectures 02:40:48
+
Introduction
5 Lectures 15:44

Introduction to the course and provides an explanation of the Excel 2013 Business Intelligence tools and technologies

Preview 03:11

Please review the notes on the introduction to the course.

Introduction to using the course
00:32

Please download the training data file to be able to follow the lessons and activities.

Download the Training Data Set
00:19

In this lesson we will review how to structure your data correctly so that tools such as PivotTables and Power View can easily work with your data.

Correctly Structure your Data
08:17

In this lesson we review how to use Excel Tables as a data source for PivotTables.

Correctly Structure Data Using Tables
03:25
+
Creating Excel Pivot Tables
7 Lectures 27:10
Introduction to Creating your First PivotTable
00:33

In this lesson we review how to setup and create a PivotTable.

Create a Pivot Table
06:15

In this lesson we review how to add different Dimensions to your PivotTable. Dimensions allows the ability to analyze data from many different perspectives and point of view.

Preview 04:17

In this lesson we will review how to add more than one measure to your PivotTable.

Add More Than One Measure
05:23

Number formats can make your reports more readable. For example instead of 340063, rather have 340 K. This lessons reviews how to formats thousands and millions.

Changing the Number Format
07:35

Changing the Number Format
1 page

The Windows Control Panel option Region and Locations controls many of the formatting options that Excel uses. Learn to change the default options to support your way of working with Excel.

Changing Region and Language Options
03:07
+
Pivot Table Menu Options
3 Lectures 15:22
Introduction to Pivot Table Menu Options
00:12

In this lesson we will explore the PivotTable menu options. This lessons reviews the different menu options and how they effect the PivotTable.

Pivot Table Menu Options
10:16

In this lesson we will learn how to format the PivotTable and change options such as styles, sub totals and grand totals.

Format Pivot Tables
04:54
+
Changing the Method of Aggregation
3 Lectures 13:19
Changing the Method of Aggregation
00:15

Learn to change the method of calculation from Sum to Average, Count, Max and Min.

Changing the Method of Aggregation
08:16

Learn to sort your PivotTables easily. Learn to sort numeric and text columns with ease.

Sorting Data
04:48
+
Trend - Time Analysis with Pivot Tables
5 Lectures 25:07
Introduction to Time Analysis
00:19

Time analysis allows us to understand trends. Trends provide important information to users such as are sales moving up, down or staying stable? Learn to easily analysis data by Year, Quarter, Month or Day.

Time Analysis with Pivot Tables
09:11

Management reports often display Jan, Feb, Mar etc across the columns of an Excel spreadsheet. In this lesson learn to easily create the same effect using a couple of mouse clicks.

Preview 03:35

In this lesson we review how to create our own custom formulas to display the Week Number and WeekDay. The custom formulas are then added to the PivotTable and provide a new dimension to the reporting capabilities.

Creating Custom Time Formulas
08:09

Sparklines create a small line or column graph in a cell in the spreadsheets. In this lesson we learn how to add sparklines to display trends that have been calculated in the PivotTable.

Using Sparklines to Analyse Trends
03:53
+
Filtering Pivot Tables
6 Lectures 27:32
Introduction to Filtering and Slicing Data
00:33

In this lesson we review how to easily filter the PivotTable and to ask questions of our data.

Filter Text Fields
09:20

Top 10 analysis is an important method of identifying which items are the Top or Bottom contributors. This lesson shows how to use the Top 10 analysis feature in PivotTables.

Top 10 Analysis
04:10

This lesson shows to add a field to the Report Filter and to be able to filter the PivotTable with fields that are not part of the PivotTable.

Using the Report Filter
03:26

Slicers are a new technology that have been added in Excel 2010. Slicers make it easy to filter the data in a PivotTable. Please note that this lesson is only for Excel 2010 and 2013.

Preview 05:18

In this lesson we show how to filter multiple PivotTables using one or more Slicers. This feature provides the capability to easily create interactive PivotTables. Please note that this lesson is only for Excel 2010 and 2013.

Using Slicers to Filter Multiple PivotTables
04:45
+
Show Value As Calculations
6 Lectures 24:38
The Show Value As Calculations
00:21

Percentages are often much easier to understand than the long numbers that are in management reports. Learn to easily create percentage calculations with the click of your mouse.

Percentage Calculations
05:32

In this lesson we learn how to create percentage calculations when you have hierarchies of data.

Parent Percentage Calculations
02:24

The difference from calculation makes it easy to calculate variances. In this lesson we show how to easily create a calculation

Difference From Calculations
06:32

Pareto analysis is a powerful method of analysis that allows you to identify the 20% of products or customers that create the 80% of Profit.

Pareto Analysis
07:29

The percentage of calculation allows you to easily create benchmark calculations. The percentage of calculation allows you to set an item as a 100 % benchmark and all the items are calculated as a percentage in relation to the benchmark.

Percentage of Calculation
02:20
+
Frequency Analysis
3 Lectures 06:38
Introduction to Frequency Analysis
00:30

Download the Employee Master file to use for the frequency analysis lesson.

Download the Employee Master data source
00:03

Frequency analysis is a powerful method of understanding your data. Frequency analysis allows the user to count the number of occurrences or events and to group according to a dimension. A common example of this form analysis is in Human Resources. Human Resources often need to count the number of employees who are in specific age groups such as 20 to 30 years old, 30 to 40 etc.

Frequency Analysis
06:04
+
Custom Calculations
2 Lectures 03:45
Creating Custom Calculations
00:12

Custom calculations allow the user to create new calculations that are not in the original data source. This lesson shows how to create the Profit Margin calculation.

Creating Custom Calculations
03:33
+
Conclusion
1 Lecture 00:22
Conclusion
00:22
About the Instructor
Ian Littlejohn
4.4 Average rating
1,253 Reviews
8,509 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.