Home > Articles > Computer Software > Business Office Software > Spreadsheet Software > Microsoft Excel

Improve Your Pivot Tables with PowerPivot for Excel 2010

  • Print
  • + Share This
  • 💬 Discuss
Excel pivot tables were one of the best data analysis tools available in Excel. In Excel 2010, the power of pivot tables skyrocketed because of the new PowerPivot add-in from Microsoft. In this article, Bill "MrExcel" Jelen walks you through the five benefits of PowerPivot (and shows you solutions to five problems pivot tables could never resolve).

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.

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.

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus