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.6 (67 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.
868 students enrolled
Take This Course
  • Lectures 41
  • Length 2.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works


Find online courses made by experts from around the world.


Take your courses with you and learn anywhere, anytime.


Learn and practice real-world skills and achieve your goals.

About This Course

Published 10/2014 English

Course Description


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

What are the requirements?

  • Excel 2007 / 2010 / 2013

What am I going to get from this course?

  • 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

Who is the target audience?

  • Excel users who want to analyze business and marketing data and create management reports

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.


Section 1: Introduction

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


Please review the notes on the introduction to the course.


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


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.


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

Section 2: Creating Excel Pivot Tables
Introduction to Creating your First PivotTable

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


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.


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


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

Section 3: Pivot Table Menu Options
Introduction to Pivot Table Menu Options

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


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

Section 4: Changing the Method of Aggregation
Changing the Method of Aggregation

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


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

Section 5: Trend - Time Analysis with Pivot Tables
Introduction to Time Analysis

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.


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.


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.


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.

Section 6: Filtering Pivot Tables
Introduction to Filtering and Slicing Data

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


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.


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.


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.


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.

Section 7: Show Value As Calculations
The Show Value As Calculations

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.


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


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


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.


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.

Section 8: Frequency Analysis
Introduction to Frequency Analysis

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


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.

Section 9: Custom Calculations
Creating Custom Calculations

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.

Section 10: Conclusion

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Ian Littlejohn, Excel Business Intelligence and Power BI Trainer

Ian is a trainer that specializes in Microsoft Excel Business Intelligence tools and techniques making it easy for learners to harness the power of Excel PivotTables, PivotCharts, PowerPivot, Power Query, PowerView and Power BI.

Ian has been training learners on these powerful technologies for a number of years making it easy for the business user 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 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.

Ready to start learning?
Take This Course