Microsoft Excel PowerPivot Training
Toronto, Mississauga, GTA, SW Ontario
PowerPivot for Excel is a free easy-to-use data analysis tool that you can use to perform powerful data analysis with Microsoft Excel 2010, 2013 & 2016 This tool allows you to bring business intelligence to your desktop (no need for the IT dept to help). We provide customized Excel PowerPivot training in the Toronto, Mississauga, GTA, Hamilton, KW, SW Ontario area. We can work with your own data – produce Power Pivot pivot tables the same day.
Below is a suggested list of topics but these can be modified to suit your needs.
Get information or a quote on Excel Power Pivot Training
Send us an email Call 905.271.1119
Microsoft Excel PowerPivot Training
Excel PowerPivot Overview
- Comparing Pivot Tables to Power Pivot
- Extended capabilities of Power Pivot
- Excel features which are similar in both tools
Creating a new Power Pivot File
- Creating a new Power Pivot workbook
- Load a table into Power Pivot data model
- Importing data from other files into Power Pivot
- Creating link relationship with common fields between tables
- Adding a calculated columns to the table (e.g. LEFT, multiple etc.)
Pivot Tables and Charts with PowerPivot Data
- Creating a pivot table from the Power Pivot table
- Adding a pivot chart from PowerPivot data
- Using Slicers with pivot charts and pivot tables
Calculated Fields (Measures) in Power Pivot
- Creating a Calculated Field based on a column (can be used in a Power Pivot pivot table)
- Naming and editing a Calculated Field
- Deleting a Calculated Field
- Calculated Fields or Calculated Columns which to use?
Overview of DAX (Data Analysis Expressions)
- What are DAX functions?
- Key points when creating DAX formulas
- Creating sample DAX formulas (SUM, AVERAGE, MAX.etc.)
- Types of DAX functions (why is SUMX different?)
- Comparison of DAX functions to Excel functions
- DAX data types
- Evaluating expressions with the CALCULATE() function and filter functions
- Using the IF function
LOOKUP & Relationships
- RELATED function versus VLOOKUP in Excel
- Creating a RELATED column
- Examining the RELATED column in a Pivot Table
Refreshing Data in Power Pivot
- Manually refresh data
- Automatic refreshes
- Changing a data source
Who may be interested? Managers, financial analysts, business analysts, Data analysts, business intelligence professionals and any person looking for a better understanding data sourced from multiple sources and/or large volumes of data.
5 Reasons why you
should learn Excel PowerPivot
Multiple Source Tables
Link many different tables together and create one pivot table
Connect to Databases
Connect to different data sources (databases, text files Excel files etc.)
Many new and powerful DAX functions (Data Analysis Expressions)
Many of the DAX formulas in PowerPivot are similar to regular Excel formulas – this makes it easier for you to learn
PowerPivot is free
It comes with most versions of MS Excel 2010 & 2013 it just has to be enabled
Excel Power Pivot
extends the power of
Excel Pivot Tables
- Possible to import millions of rows of data.
- Link many different tables together and create one pivot table.
- Create calculated columns and calculated fields using DAX formulas.
- Many new and powerful DAX functions (Data Analysis Expressions)
- Connect to different data sources (databases, text files Excel files etc.)
- Very efficient compression algorithms to handle very large data sets in computer memory – overcomes existing limitations for massive data analysis.
Excel Power Pivot
PowerPivot for Excel is a free add-in that you can use for powerful data analysis in Excel 2010 & Excel 2013. This brings “self-service” business intelligence to your personal computer. Excel PowerPivot has a window for importing and preparing data plus an additional window (PowerPivot tab) on the ribbon that you can use to manipulate your data.
Excel PowerPivot includes a wizard that you can use to import data from many different sources, such as; databases on an intranet, public data feeds, to Excel spreadsheets and/or text files on your own PC. Data is imported into PowerPivot for Excel as tables. The imported tables are shown as separate sheets in the PowerPivot tab (very similar to worksheets in an Excel workbook). Note that PowerPivot for Excel provides significantly different and enhanced functionality from what is available in an Excel worksheet.
The tables that you work on in the PowerPivot tab window is stored in a “Data Model” (an analytical database) which is inside the Excel workbook. Another very powerful feature is that the PowerPivot data tables can be connected by creating relationships between the various tables using a common field. Because PowerPivot data is in Excel, it is immediately available to PivotTables and PivotCharts in Excel. PowerPivot supports files up to 2GB in size and enables you to work with up to 4GB of data in memory.
As well as the pivot tables & charts that help you to analyze your data, Excel PowerPivot also has Data Analysis Expressions (DAX) formulas. DAX is a new formula language that extends the data manipulation capabilities of Excel to enable more sophisticated and complex grouping, calculation, and analysis. Creating DAX formulas is very similar to creating Excel formulas, using arguments, operators and values.
in Excel Power Pivot
Calculated fields (used to be called measures) are a very powerful feature in Power Pivot. You will definitely use them many times in data analysis. Calculated fields are usually aggregate formulas such as total sales calculated for time, region or product.
Many calculated fields are easy to create, such as those using a standard aggregation such as SUM, AVERAGE, MAX & MIN and are created automatically by using the AutoSum feature in the Power Pivot window or by simply dragging a field into the Values area (these are implicit calculations). Other calculated fields can be more complex, such as filtering results, these require a formula you create using DAX.
Excel and PowerPivot
As noted above, there are differences between the PowerPivot tab window and the regular Excel window in terms of how you work with data in each. There are some also other important differences that we want to call out specifically:
- PowerPivot data tables can only be saved in workbooks with the following file types: Excel Workbook (*.xlsx), Excel Macro-Enabled Workbook (*.xlsm), and Excel Binary Workbook (*.xlsb). PowerPivot data is not supported in workbooks with other formats.
- The PowerPivot window does not support Visual Basic for Applications (VBA). You can use VBA in the Excel window of a PowerPivot workbook.
- In regular Excel PivotTables, you can group data by right-clicking a column heading and selecting Group (often for dates). In PowerPivot PivotTables you have to use calculated columns to create groups.
About our Training
We provide onsite fully customized training targeting your specific needs – not just a pre-set fixed agenda. The training can be with your own data and this means you are productive even on the training day, you will see your solutions with your own pivot tables. We work with you and build your training agenda based on your training objectives and goals. In PowerPivot there are many functions and while most people will use the SUM and AVERAGE functions the less common functions may be very useful for your data. These formulas and functions can also be combined so there are thousands of combinations.
We provide onsite training in the Toronto, GTA, Mississauga, Oakville, Burlington, Hamilton, Niagara, Kitchener, Waterloo, Guelph, Concord, Markham, Pickering, areas. We come to your location so you and your team do not have to drive across town – no wasted time. We offer customized MS Excel PowerPivot training for employees, managers, executives involved in Financial, Accounting, Marketing, Sales etc. in Toronto, Mississauga, Brampton, Ontario, Kitchener, Waterloo, Hamilton, and Cambridge, Canada. Also onsite training allows employees who cannot attend for the entire day to attend for the hours they have available.
We Courses on Microsoft Excel, PowerPivot, Access database training, Microsoft Project, Office 365 & SharePoint End-User training. In MS PowerPivot courses the trainer covers data analysis, PowerPivot pivot tables, graphs, sorting and filtering, Microsoft Excel training also includes MS Excel formulas, Microsoft Excel VLOOKUP Function for data automation, Microsoft Excel HLOOKUP Function for data automation, Excel functions for sales analysis. MS Project for Gantt Charts and schedules. We also offer onsite training in other Microsoft Office software: PowerPivot, MS Project, MS PowerPoint, MS Word, MS Outlook, MS Access, Office 365 and SharePoint end-user training.