Improve Your Pivot Tables with PowerPivot for Excel 2010
- Downloading and Installing PowerPivot
- Getting your Data into PowerPivot
- Linking an Excel Table to PowerPivot
- Importing Data into PowerPivot
- Adding DAX Calculations to the Grid
- Creating Pivot Tables from PowerPivot
- Defining a Relationship Between Worksheets
- Using Automatic Relationship Detection
- Using Explicit Relationships
- Using the RELATED Function in the PowerPivot Grid
- Adding Slicers to Drive All Pivot Elements
- Adding DAX Calculations to the PivotTable
- Entering a Measure
- Using the X Functions
- Using CountRows and Distinct
- PowerPivot Is a Home Run
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
PowerPivot is an amazing new free tool from Microsoft that works with Excel 2010 to make pivot tables easier and more powerful. The tool was developed by the SQL Server Analysis Services team at Microsoft. That team is used to dealing with large data sets, and PowerPivot certainly can take Excel data to the next level.
PowerPivot offers several advantages:
- Mashes up data from anywhere in Excel. PowerPivot can load text files, Access, SQL Server, Oracle, Teradata, Sybase, Atom, or anything that is ODBC-compliant.
- Loads more than a million rows into Excel. PowerPivot easily handles 2 million, 20 million, or even 200 million records.
- Uses the new calculation functions offered in the DAX language to perform calculations in the grid or in a pivot table.
- Joins data from multiple worksheets into a single pivot table without doing VLOOKUP functions.
- New time intelligence functions handle fiscal years, parallel periods, and more.
This article will take a look at each of those advantages one at a time.
Downloading and Installing PowerPivot
There are two versions of PowerPivot. You need to download the version that matches your version of Excel 2010. Open a blank workbook in Excel 2010. Select File, Options. On the right side of the screen, you will see a section for About Microsoft Excel. Notice if the version number indicates 32-bit or 64-bit.
Open your favorite web browser and browse to http://www.PowerPivot.com. Click Download near the top center of the page. You will then have choices for two different downloads:
- If you have a 64-bit version of Excel 2010 installed, choose PowerPivot_for_Excel_amd64.msi.
- If you have a 32-bit version of Excel 2010 installed, choose PowerPivot_for_Excel_x86.msi
Close Excel. Download and run the installation package. Once PowerPivot finishes installing, open Excel 2010. You should now see a PowerPivot tab in the ribbon.
){kind=small}
The PowerPivot ribbon tab is one-third of the PowerPivot interface. There is also a PowerPivot window and a PowerPivot Field List that you will encounter later in this article.