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