Online Excel Pivot Tables Tutorials

LEARN EXCEL POWER PIVOT

Power Pivot is an add-in to the desktop version of the Windows version of Excel.

Excel Power Pivot is a database in Excel, it provides the capabilities to work with millions of rows of data, create relationships between tables and use the DAX (Data Analysis eXpressions) formula language.

In this series of tutorials you will learn to activate Power Pivot, create calculations, analyse data using Pivot Tables, create measures, create relationships between tables and develop dashboards.

DOWNLOAD

SalesData.xlxs

Tutorial 1

Welcome to our comprehensive series on Excel Power Pivot!

In this series, we will explore the powerful capabilities of Power Pivot, a feature introduced in Excel 2010 that enhances data handling by integrating a database within Excel. This tutorial is perfect for those who want to learn how to efficiently manage and analyze large datasets directly in Excel.

Activation of Power Pivot: Learn the prerequisites and step-by-step process to enable Power Pivot in Excel.
Data Handling: Understand how to work with large datasets, managing millions of rows seamlessly.
Creating Relationships: Discover how to create relationships between tables, eliminating the need for VLOOKUPs.
DAX Formulas: Get acquainted with DAX, the new query language, and see how it parallels Excel's formula creation.

Tutorial 2

In this video, we will guide you through the process of loading data into Power Pivot, an essential skill for managing large datasets efficiently within Excel. Whether you're importing data from various databases, CSV files, or existing Excel tables, this tutorial will show you how to get started.

Tutorial 3

In this tutorial, we will delve into how to add simple calculations to your tables in Power Pivot. This session is perfect for users looking to enhance their data analysis by integrating new fields and performing calculations within their Power Pivot tables.

Learn to add new columns to your Power Pivot tables, facilitating more advanced data manipulation.
Discover how to write formulas directly within Power Pivot to calculate sales, costs, and profits.
Understand how to use field names in your calculations and navigate the formula bar effectively.
See how to leverage newly created fields in additional calculations, such as determining profit margins.

Tutorial 4

In this tutorial, we will explore how to use pivot tables and pivot charts to analyze and present data effectively. Building on our previous tutorials, we will show you how to transform your raw data into meaningful insights using these powerful tools.

How to use Data Analysis Expressions (DAX) functions in Power Pivot.
Methods to extract specific date information, such as year, month, and weekday from your data.
How to create new calculated fields using DAX functions.
Techniques for incorporating these new fields into your pivot tables and pivot charts for enhanced data analysis.
How to sort and organize your data effectively within Power Pivot.

Tutorial 5

In this tutorial, we will dive into using functions within Power Pivot formulas, allowing you to perform advanced data analysis with ease. We'll explore how to extract specific information from your data, such as year, month, and weekday, and how to utilize these new fields in your analysis.

Discover how to use common DAX functions like YEAR, MONTH, and WEEKDAY to extract and utilize date information.
Learn how to add new calculated fields to enrich your data analysis.
See how to use slicers to filter data by newly created fields.
Understand how to format and sort your data correctly for better visualization and analysis.
How to automatically update your data in pivot tables without manual refreshes.

Tutorial 6

In this session, we delve into the concept of measures, an essential calculation tool that enhances your data analysis. Unlike calculated columns, measures provide a dynamic way to compute values that are not directly visible in your data tables but are crucial for effective data visualization and interpretation.

Detailed walkthrough on creating a measure, with practical examples.
How to use the distinct count feature to calculate unique values.
Performing advanced calculations such as average sales per customer using DAX functions.
Insights on why measures are more efficient for large data sets and how to manage them effectively.
Real-world examples demonstrating the application of measures in different business scenarios.

Tutorial 7

In this session, we focus on the SWITCH function, an incredibly useful tool for handling complex if-then conditions in your data analysis. This tutorial is perfect for those who have already grasped the basics of measures and are looking to enhance their skills in managing complex criteria within PivotTables.

Tutorial 8

In this session, we continue by focusing on how to create relationships between tables. We will be using a human resource data source with two tables: an employee master list and a training list. This tutorial will guide you through the process of establishing relationships within Power Pivot, enabling you to seamlessly integrate and analyze data across multiple tables using pivot tables, pivot charts, and formulas.

Learn how to import CSV files into Power Pivot and ensure your data is correctly formatted.
Understand the importance of relationships in databases and how to link tables using common fields.
Discover how to use related tables in pivot tables for comprehensive data analysis.
Explore how to use the DAX function 'RELATED' to perform lookups between tables, enhancing your data analysis capabilities.

Tutorial 9

In this session, we will dive into creating a dynamic four-chart dashboard. Utilizing the human resource data from previous tutorials, we'll demonstrate how to effectively use relationships between tables to create interactive visualizations directly in Excel. By the end of this tutorial, you'll be equipped to build comprehensive dashboards that enhance your data analysis capabilities.