Excel: Power Pivot – Gain Better Insights into Your Data
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: Mike Thomas
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: CPE 2.0
-
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.
- 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.
- Introduction
- The Data Model 00:01:38
- Creating a Pivot Table 00:02:03
- My Demo File .xlsx 00:03:23
- Why Use The Data Model? 00:04:46
- Compared To a Worksheet, The Data Model Can Store More Data 00:05:16
- Create Pivot Tables From Multiple Lists 00:06:51
- Databases, Text Files, Web Pages, and Sharepoint - The Data Model 00:07:41
- DAX - Data Analysis Expresions 00:08:35
- New Way of Working 00:09:01
- Power Pivot 00:10:32
- Power Pivot Demo 00:13:34
- Text File Example 00:27:47
- Importing Data Into The Data Model 00:29:39
- Creating a Pivot Table From Multiple Data Sources 00:45:43
- Adding External Data 00:58:54
- Power Pivot and Calculated Columns 01:12:39
- Speaker Comments 01:39:50
- Presentation Closing 01:40:41
- .CSV 00: 00:28:46
- Cell 00:02:47
- Column Headings 00:23:58, 01:26:17
- DAX - Data Analysis Expresions 00:08:42, 01:12:44
- Pivot Table 00:02:31, 00:15:27, 01:00:16
- Power Pivot 00:01:40, 00:10:32, 00:50:48
- Query 00:36:56
- Row 00:05:41, 00:59:56
- Table 00:45:31, 01:05:07, 01:19:19
- Text File 00:27:48, 00:25:45
- Workbook 00:10:55
- Worksheet 00:05:33
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.
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.
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.
.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.
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.
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.
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.
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.
DAX - Data Analysis Expresions: 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.
