The fourth video in my "Complete Introduction to" series focuses on Excel Power Pivot. Power Pivot is another incredibly powerful feature within Excel. It was first introduced to Excel in 2010 as an add-in, which was freely available to all Excel 2010 users. In Excel 2013 Microsoft integrated Power Pivot into Excel, but sadly created separate licencing for the product so now you require a Pro Plus license to access it.
Why is Power Pivot so important to Excel users? Because it is a powerful data analysis tool that does not have a number of the restrictions that Excel does and eliminates the need to use complicated formulas like VLookUp and HLookUp. It can store millions of records of data, easily create relationships between different tables of data and introduces the DAX formula language, which is broadly based on the Excel formulas we already know. Once data is loaded into Power Pivot, you can use Pivot Tables and Charts to analyze the data, just as you would using standard Excel. When you start using DAX (Data Analysis eXpressions) in PowerPivot, you will quickly see the similarities it shares with standard Excel formulas.
In the video I create some simple calculations to show the concept of a calculated column and how to add a measure to the Power Pivot data model. Measures are calculated in the context of the Pivot Table or Pivot Chart and this allows the creation of powerful business intelligence calculations within Excel. In addition to the standard Excel formulas, DAX adds a wide range of new formulas to enhance your data analysis capabilities. One example is the distinct count function which counts the number of unique entries in a field, something that would be very helpful in Excel!
Relationships are another powerful capability in Power Pivot. You will see in the video how quick and easy it is to create the relationship between two Human Resource tables, and once there is a relationship between the two tables, it is easy to create Pivot Tables using fields from both tables
Power Pivot is another amazing Excel technology that will drastically improve your data analysis skills and also the speed to understand your data, without the steep learning curve associated with learning other data analysis programs.