Excel: Power Pivot: Gain Better Insights into Your Data

Access this expert-led webinar instantly, available anytime on-demand.

Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

Power Pivot, a free add-in for Excel, written by Microsoft, puts the "power" into Pivot Tables (hence the name!). Power Pivot removes many of the limitations and frustrations that many advanced users find with Pivot Tables. For example, using Power Pivot you can…

  • Create Pivot Tables from multiple Excel-based lists without using VLOOKUP
  • Create Pivot Tables from large datasets without worrying about file size or performance
  • Combine together related data from multiple data sources (Excel, database, text file, etc) into a single Pivot Table
  • Create powerful calculations on the data in your Pivot Tables

Objectives

In this training, you will learn, using practical examples, how Power Pivot provides Business Intelligence functionality & reporting within the familiar environment of Excel.

Why You Should Attend
Your data is only as good as the information you can derive from it. Power Pivot enables you to gain better business insights and make more informed business decisions

If you want to take your reporting capabilities to the next level by learning how to leverage the functionality of Power Pivot, this is a must-attend training session!

Topics covered
  • Importing data into Power Pivot - the why and how
  • Using the Data Model to create and manage relationships
  • The benefits of using the Data Model
  • Creating a Pivot Table from related Excel tables
  • Creating a Pivot Table from related data sources (including external sources)
  • Creating Calculated Columns using DAX

Who Should Attend

This training is aimed at users of Excel (2010 and above for Windows) who wish to learn about Power Pivot.  Attendees should have at least intermediate knowledge of Excel and be familiar with formulas and creating Pivot tables.

 IMPORTANT NOTE: Power Pivot may not be available for your version of Excel. If you are unsure whether this training is relevant for your version of Excel, please check with your IT department.

Level: Intermediate
Format: Live webcast
Instructional Method: Group: Internet-based
NASBA Field of Study: Computer Software & Applications
Program Prerequisites: None
Advance Preparation: None

  1. Introduction
  2. The Data Model 00:02:07
  3. Creating a Pivot Table 00:02:18
  4. My Demo File .xlsx 00:03:06
  5. Why Use The Data Model? 00:04:40
  6. Compared To a Worksheet, The Data Model Can Store More Data 00:04:44
  7. Create Pivot Tables From Multiple Lists 00:05:32
  8. Databases, Text Files, Web Pages, and Sharepoint - The Data Model 00:05:47
  9. DAX - Data Analysis Expressions 00:06:10
  10. New Way of Working 00:06:25
  11. Power Pivot 00:07:48
  12. Power Pivot Demo 00:08:19
  13. Power Pivot 1 File 00:33:25
  14. Importing Data Into The Data Model 00:35:12
  15. Power Pivot 2 File 00:48:48
  16. Power Pivot 4 File 01:06:33
  17. Ice Cream Orders File 01:08:28
  18. Creating a Pivot Table From Multiple Data Sources 01:09:23
  19. Building a Pivot Table From The Data Model 01:15:33
  20. Deleting Source Data 01:17:45
  21. DAX - Data Analysis Expressions 01:22:24
  22. Power Pivot 1 File - DAX Examples 01:23:24
  23. Speaker Comments 01:38:06
  24. Attendee Questions 01:38:34
  25. Presentation Closing 01:42:27
  • Mike Thomas

    Mike Thomas

    Mike Thomas has worked in the IT training business for 26 years. His expertise and experience covers designing and delivering training courses, creating written training materials (Quick Reference Guides and step-by-step tutorials), recording and editing video-based tutorials and providing support t [...]

    View Full Profile

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or [email protected].

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.

  • Cell 00:02:52
  • Column 00:12:40, 01:31:19
  • Column Headings 00:12:44, 00:20:39
  • Data Model 00:02:07, 00:06:06, 00:16:16, 00:31:18, 00:48:09, 01:15:33
  • DAX - Data Analysis Expressions 00:06:10, 01:22:24, 01:32:37
  • Pivot Table 00:01:49, 00:09:06, 00:23:33, 01:09:31
  • Power Pivot 00:01:46, 00:07:48, 00:22:35
  • Power Query 00:36:08
  • Query 00:30:24, 00:35:11, 00:39:32, 00:45:25
  • Row 00:05:09, 00:12:42
  • Table 00:43:39, 00:56:05
  • Workbook 00:31:17
  • Worksheet 00:03:16, 00:09:23

Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

Column Headings : The column heading or column header is the gray-colored row containing the letters (A, B, C, etc.) used to identify each column in the worksheet. The column header is located above row 1 in the worksheet.

DAX - Data Analysis Expressions: Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel data models. DAX is a formula language and is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values.

Data Model: A Data Model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook. Within Excel, Data Models are used transparently, providing tabular data used in PivotTables and PivotCharts.

Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.

Power Pivot: Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.

Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.

Query: A database query extracts data from a database and formats it in a readable form. A query must be written in the language the database requires; usually, that language is Structured Query Language (SQL). For example, when you want data from a database, you use a query to request that specific information.

Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.

Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.

Text Files : Raw data files that often have file extensions such as .TXT or .CSV. TXT files are sometimes tab-delimited (meaning each field is separated by a tab character) while CSV files are comma-delimited.

Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.

Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

Customer Satisfaction Guarantee
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

Frequently Asked Questions

The Power Pivot Data Model offers several significant advantages over storing data in standard Excel worksheets. First, capacity: Excel worksheets are limited to approximately 1 million rows, while the Data Model handles tens of millions of rows using highly efficient columnar compression—often at a smaller file size than the equivalent worksheet data. Second, multi-table PivotTables: the Data Model allows PivotTables to draw from multiple related tables simultaneously, eliminating the need to VLOOKUP or SUMIF data together into one flat table before analysis. Third, DAX calculations: the Data Model provides access to the DAX formula language with Measures and Calculated Columns far more powerful than standard PivotTable calculated fields. Fourth, external data integration: you can import data from databases, text files, web pages, and SharePoint directly into the model without pasting it into worksheets. Fifth, performance: the Data Model's in-memory engine calculates aggregations faster than worksheet-based PivotTables on large datasets. Mike Thomas explains these benefits in Aurora Training Advantage's Excel: Power Pivot: Gain Better Insights into Your Data webinar.
Loading data into the Power Pivot Data Model can be done through several paths depending on the source. For Excel tables on worksheets, select the table, go to Power Pivot > Add to Data Model—the table appears as a linked table in the model and updates automatically when the worksheet data changes. For external sources (databases, CSV files, other Excel workbooks, SharePoint lists, web pages), use Power Pivot > Get External Data or the newer Power Query approach (Data > Get Data), which loads data directly into the model after applying transformations. In the Power Pivot window, the Home > Get External Data menu provides direct connection wizards for SQL Server, Access, text files, and other OLEDB/ODBC sources. Multiple tables from different sources can coexist in the same model simultaneously. Once loaded, you define relationships between tables in the Diagram View to enable cross-table PivotTable analysis. Deleting the source data from the worksheet (after loading to the model) reduces file size dramatically while the model retains the data. Mike Thomas demonstrates multiple import methods in Aurora Training Advantage's Excel: Power Pivot: Gain Better Insights into Your Data webinar.
Creating a PivotTable from multiple related tables in Power Pivot is straightforward once the Data Model is set up and relationships are defined. In the Power Pivot window, verify your tables are loaded and relationships created in Diagram View. Then return to Excel and go to Insert > PivotTable. In the dialog, choose Use this workbook's Data Model and click OK. The PivotTable Field List shows all tables in the model, each expandable to reveal their fields. You can drag fields from different tables into the PivotTable—for example, product name from the Products table into Rows, and revenue from the Sales table into Values—and Power Pivot automatically traverses the relationship to produce the correct result, with no VLOOKUP required. This is the core power of the Data Model: what previously required creating a flat, merged table (often using time-consuming VLOOKUP formulas) now happens transparently at analysis time. Adding a third table—such as a calendar table—enables date-based slicing across all related data. Mike Thomas demonstrates this complete workflow in Aurora Training Advantage's Excel: Power Pivot: Gain Better Insights into Your Data webinar.
Calculated Columns in DAX (Power Pivot) and Calculated Fields in regular PivotTables both add derived calculations to a PivotTable, but they work very differently. A standard PivotTable Calculated Field is defined within the PivotTable interface and works only within that PivotTable—it's limited to simple arithmetic on the aggregated totals, often producing incorrect results when applied to subtotals and grand totals. DAX Calculated Columns are added to the actual data table in the Power Pivot model and can reference any column in the same row, use complex DAX functions (IF, RELATED, date functions, text functions), and are reusable across any PivotTable built from the model. DAX Measures (a different form of DAX formula) are even more flexible—they evaluate dynamically based on filter context, always producing correct results at every level of aggregation. For any serious analytical work involving multi-table data, calculations requiring context awareness, or large datasets, DAX Calculated Columns and Measures are vastly superior to standard PivotTable Calculated Fields. Mike Thomas covers this distinction in Aurora Training Advantage's Excel: Power Pivot: Gain Better Insights into Your Data webinar.
Yes—one of the practical advantages of the Power Pivot Data Model is that once data is imported from an external source (CSV, database, another workbook), it is stored independently in the model's compressed in-memory engine. You can delete the raw data worksheet entirely and the Data Model retains all the data, available for PivotTables and DAX calculations. This can dramatically reduce Excel workbook file size, since large datasets in worksheet cells consume significant space, while the same data in the compressed model may take a fraction of the size. Note that linked tables—Excel Tables added to the model via Add to Data Model—maintain a live link to the worksheet and cannot be deleted from the source without losing the model connection. For data imported via Get External Data or Power Query, the model is independent of the worksheet. Refreshing the model re-imports data from the original source files or databases. Mike Thomas demonstrates removing source data after model loading in Aurora Training Advantage's Excel: Power Pivot: Gain Better Insights into Your Data webinar.