Looking for corporate training?
We offer custom onsite training and discounted class email@example.com
212 • 684 • 5151
We provide computers to use during class. This course is available on PC only.
Students may retake the course once within six months if there is an available seat.
You will make use of the PowerPivot add-in to import data from various sources and create a dynamic report.
This course is for students with a sound working knowledge of Microsoft Excel and general computing proficiency, including those who will be using Excel to make business decisions.
No group classes scheduled.
Contact us about corporate or private training:
PowerPivot is an Excel add-in that allows the user to analyze much greater quantities of data, as well as build sophisticated data models.
PowerPivot provides several unique advantages over using Excel's standard features. A few of these include:
Generally speaking, in Excel, we are limited to using a single dataset as the source of a Pivot Table (we will exclude the internal data model from this discussion). But with PowerPivot, you can select different data sets, create relationships (effectively creating a relational database), and build a single Pivot Table from these multiple sources.
There are approximately 1,048,576 rows (and 16,384 columns) available on every Excel worksheet. This is more than most of us would ever need, but for some, it is not enough. Excel users that work with large sets of data generally agree that once data goes beyond 700,000 rows, you run the risk of Excel crashing on you. PowerPivot, on the other hand, increases row capacity up to 100 million rows of data, thereby vastly increasing the amount of data that can be collected and analyzed. The more data collected, the more accurate your projection.
PowerPivot incorporates both Excel functions, as well as DAX (Data Analysis eXpressions) functions. DAX is a special type of programming that increases the specificity of potential results. One example of a DAX function is CALCULATE, which works like a SUMIFS in Excel, except you can expand beyond the standard SUM operation to include any function with the filter effect added to it.
In PowerPivot, two types of calculations are possible: Calculated Columns and Measures. A Calculated Column is similar to creating a Calculated Field in your Pivot Table. Calculated Fields is an area of Excel that never received much investment by Microsoft, so it remains somewhat archaic, unpolished, and largely ignored by most Excel users. Calculated Columns in PowerPivot, on the other hand, are much more progressive. Measures are aggregate calculations that exist solely for Pivot Tables
With both Calculated Columns and Measures, PowerPivot employs a very easy-to-use auto-population method. Not only does the function name appear in a dropdown when you begin to type (like regular Excel), but the table name and column name appear in a similar fashion, making it incredibly easy to enter calculations.
Data Analytics is a new, trendy method of gathering large amounts of transactional data and then performing powerful analyses on this data for Business Intelligence (BI) purposes. PowerPivot serves as Microsoft’s BI engine. PowerPivot collects, organizes, and analyzes your data. You can then create Pivot Tables and Pivot Charts or go beyond this and use Power BI to create amazing visuals and dashboards.
The list of advantages that PowerPivot offers is a long one, but we will stop here for now. Remember, that PowerPivot is a free add-in for Excel that works closely with Excel to create powerful analyses. Enabling PowerPivot means that you can open up your data to more variety of sources, greater quantities, specialty functions, ease of calculating, and BI-related visuals. PowerPivot does not replace Excel, it works with Excel and greatly enhances it.