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

On Demand Webinar

Webinar Details $219

  • Rated:
  • Webinar Length: 100 Minutes
  • Guest Speaker:   Mike Thomas
  • Topic:   Business Administration, Taxation and Accounting, Finance, Business Skills, Software
  • Credit:   CPE 2.0
All Access Membership

Power Pivot, a free addin for Excel, written by Microsoft, puts the "power" into Pivot Tables (hence the name!). Power Pivot removes many of 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

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 powerful calculated fields in a Pivot Table

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.

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

Guest Speaker

  • Mike Thomas

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 training@auroratrainingadvantage.com.

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.