Excel: Power Pivot: Taking Pivot Tables to the Next Level

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 Microsoft Excel, puts the power into PivotTables by overcoming many of the limitations and frustrations experienced by advanced Excel users. With Power Pivot, you can create PivotTables from multiple Excel-based lists without relying on VLOOKUP, analyze large datasets without sacrificing performance, combine data from multiple sources into a single report, and build powerful calculations that enhance your data analysis capabilities.

In this practical webinar, you will learn how to use Power Pivot and the Excel Data Model to create more efficient, scalable, and insightful reports. Through hands-on demonstrations, attendees will gain a better understanding of how to import, relate, and analyze data from multiple sources while leveraging advanced PivotTable functionality.

Your Benefits For Attending:
  • Learn how to import and manage data within Power Pivot.
  • Understand how to create and maintain relationships using the Excel Data Model.
  • Discover the benefits of using the Data Model for advanced reporting and analysis.
  • Build PivotTables from multiple related Excel tables without using VLOOKUP.
  • Combine data from related external and internal sources into a single PivotTable.
  • Create powerful calculated fields to enhance your PivotTable reporting and analysis.

Attend this webinar to expand your Excel skill set and learn techniques that can help you work more efficiently with large and complex datasets. You'll gain practical knowledge that can immediately be applied to improve reporting, streamline analysis, and unlock the full potential of your PivotTables.

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 PivotTable from related Excel tables
  • Creating a PivotTable from related data sources, including external sources
  • Creating powerful calculated fields in a PivotTable
Who Should Attend:
This training is designed for Excel users (Excel 2010 and later for Windows) who want to learn how to use Power Pivot to enhance their reporting and data analysis capabilities. Attendees should have at least an intermediate knowledge of Excel and be familiar with formulas and creating PivotTables.

Level: Basic
Format: Live Webcast
Instructional Method: QAS Self-Study (Traditional)
NASBA Field of Study: Computer Software & App (2 hours)
Program Prerequisites: None
Advance Preparation: No
  1. Introduction
  2. Topics To Cover 00:03:02
  3. Introduction Demo File 00:03:38
  4. What Is Power Pivot And Why Do You Need It? 00:03:47
  5. How To Activate A Power Pivot 00:13:36
  6. Multi-Table Pivot Tables File 1 00:15:16
  7. The Data Model 00:15:42
  8. Adding Tables Into The Data Model 00:20:55 
  9. Create A Relationship Between Two Tables 00:22:48
  10. Multi-Table Pivot Tables File 2 00:29:47
  11. Duplicates 00:36:50
  12. Creating a Pivot Table From Multiple Data Sources 00:50:00
  13. .CSV File 00:52:49
  14. Importing Data Directly Into The Data Model 00:59:52
  15. Unique Count 01:08:48
  16. Calculated Fields - DAX - Data Analysis Expressions 01:19:47
  17. Calculated Columns File 2 01:29:58
  18. Speaker Comments 01:37:39
  19. Presentation Closing 01:39:29
  • 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.

  • .CSV 00:09:17, 00:52:52
  • Cell 00:05:59, 00:25:19
  • Column 00:06:00, 00:07:36, 00:18:14, 00:22:00, 01:22:49
  • Column Headings 00:18:18
  • Data Model 00:15:42, 00:16:38, 00:21:06, 00:51:56, 00:56:36, 00:59:22, 01:16:30
  • DAX - Data Analysis Expressions 00:11:47, 01:20:06, 01:24:01
  • Formula 00:06:04, 00:11:55, 01:20:02
  • Formula Bar 01:23:41
  • Pivot Table 00:03:13, 00:05:40, 00:08:58, 01:02:56, 01:09:07
  • Power Pivot 00:03:03, 00:03:45, 00:05:11, 00:10:36, 00:14:37, 00:16:16, 00:22:08, 01:00:01
  • Ribbon 01:21:13
  • Row 00:18:15, 00:58:07
  • Table 00:03:16, 00:05:24, 00:19:02, 00:37:00, 0:50:32, 01:14:56
  • VLOOKUP 00:06:03

.CSV: Comma-Separated Value files are text files where each field of data is separated by a comma. This is an effective means to export data from QuickBooks that you, in turn, wish to analyze in Excel.

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.

Formula: A formula is an expression which calculates the value of a cell.

Formula Bar: A toolbar at the top of the Microsoft Excel spreadsheet window that you can use to enter or copy an existing formula into cells or charts. It is labeled with function symbol (fx). By clicking the Formula Bar, or when you type an equal (=) symbol in a cell, the Formula Bar will activate.

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.

Ribbon: The "ribbon" is the strip of buttons and icons located above the work area that was first introduced in Excel 2007. The ribbon replaces the menus and toolbars found in earlier versions of Excel. Above the ribbon are a number of tabs, such as Home, Insert, and Page Layout.

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.

VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.

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

Power Pivot is a free add-in for Excel that is included but not enabled by default in most Excel versions. To activate it, go to File > Options > Add-ins, select COM Add-ins from the Manage dropdown, click Go, and check the box next to Microsoft Office Power Pivot. Click OK and the Power Pivot tab appears in the Ribbon. Power Pivot is available in Excel 2010 (as a separate download), Excel 2013, 2016, 2019, 2021, and Microsoft 365—but importantly, it is not available in all editions. It is available in Excel for Microsoft 365 (any edition), Excel 2013/2016/2019/2021 Professional Plus, and Excel Standalone. It is NOT available in Excel Home, Excel Home & Student, or Excel Standard editions. Mac versions have limited Power Pivot support. If the Power Pivot add-in does not appear in your COM Add-ins list, your edition of Excel does not include it. Mike Thomas covers how to verify availability and activate Power Pivot in Aurora Training Advantage's Excel: Power Pivot: Taking Pivot Tables to the Next Level webinar before proceeding to hands-on exercises.
Building a multi-table Data Model in Power Pivot starts with loading each table. For Excel worksheet tables, select the table and click Power Pivot > Add to Data Model—it appears as a linked table in the model. For each additional table, repeat the process. Once all tables are loaded, switch to Diagram View in the Power Pivot window (View > Diagram View) to see a visual representation of all tables. To create a relationship, drag a column from one table to the matching column in the related table—for example, dragging CustomerID from the Orders table to CustomerID in the Customers table creates a many-to-one relationship. The relationship is indicated by a line connecting the tables. The Data Model requires that the lookup-side column (the one-side of the relationship) contains unique values. Relationships can also be created via Design > Create Relationship for more explicit control. Common Data Model structures include a central fact table (e.g., Sales) surrounded by dimension tables (Products, Customers, Calendar). Mike Thomas walks through multi-table model setup in Aurora Training Advantage's Excel: Power Pivot: Taking Pivot Tables to the Next Level webinar.
Duplicate values in the lookup column of a relationship table are a common source of errors in Power Pivot multi-table models. The Data Model requires that the column on the one-side of a relationship contains unique values—if duplicates exist, you cannot create the relationship and the PivotTable will produce incorrect results. To check for duplicates, Power Pivot provides a Distinct Count aggregate option, and Power Query's Remove Duplicates feature can clean a table before loading. Common causes of duplicates in lookup tables include importing data from a system that generates multiple entries for the same customer or product ID, or accidentally importing the same data twice. When building a model, always verify that ID columns in your dimension tables are unique—use Power Pivot's Data View to sort the column and scan for duplicates, or use Excel's Remove Duplicates command before loading. A clean, duplicate-free lookup column is a prerequisite for accurate multi-table analysis. Mike Thomas addresses duplicate handling as part of the model-building process in Aurora Training Advantage's Excel: Power Pivot: Taking Pivot Tables to the Next Level webinar.
Distinct Count (also called Unique Count) is an aggregation type available in Power Pivot-backed PivotTables that counts the number of unique values in a field, rather than the total number of rows. In a standard PivotTable, Count simply counts all rows including duplicates—so counting CustomerID in a sales table counts every transaction, not every unique customer. Distinct Count returns only the unique customer count, answering the question How many different customers placed orders? This metric is fundamental in customer analysis, inventory management, and many operational reports. To use Distinct Count, right-click the field in the PivotTable Values area, select Value Field Settings, and choose Distinct Count from the list—this option only appears for fields in a Data Model PivotTable, not a standard PivotTable. Distinct Count is also available as a DAX Measure using DISTINCTCOUNT([column]), which can then be used in more complex calculations. Mike Thomas demonstrates Distinct Count as a key Power Pivot capability in Aurora Training Advantage's Excel: Power Pivot: Taking Pivot Tables to the Next Level webinar.
Importing CSV (Comma-Separated Value) data directly into the Power Pivot Data Model—bypassing the Excel worksheet entirely—is the recommended approach for large datasets that would be unwieldy in worksheet cells. In the Power Pivot window, click Home > Get External Data > From Text. Browse to the CSV file, configure the delimiter (comma, tab, or other), set column headers, preview the data, and click Finish. The data loads into the model as a standalone table without creating a worksheet. Alternatively, using Power Query (Data > Get Data > From File > From Text/CSV), you apply any needed transformations and then choose to Load To > Only Create Connection (to avoid creating a worksheet table) while checking Add this data to the Data Model. This Power Query path is more flexible for data cleaning before loading. Once in the model, the CSV-based table participates in relationships and DAX calculations like any other table. Refreshing re-reads the CSV file from its original location. Mike Thomas demonstrates CSV import into the Data Model in Aurora Training Advantage's Excel: Power Pivot: Taking Pivot Tables to the Next Level webinar.