Introduction to PowerPivot for Excel 2010
Summary: Use Microsoft Excel 2010 and the Microsoft PowerPivot for Excel 2010 to create powerful, easy-to-use Business Intelligence solutions.
Last modified: September 12, 2012
Applies to: Excel 2010 | Office 2010 | VBA
Published: February 2011
Provided by: Steve Hansen, Microsoft Visual Studio MVP and founder of Grid Logic.
Microsoft Excel 2010 has many features that were designed to help you acquire, clean, and analyze external data. These features make Excel 2010 a perfect tool for building business solutions. PowerPivot is a Microsoft Excel 2010 add-in that makes it easier than ever to acquire and analyze data in a rich and interactive manner.
This Visual How To gives you a look at the basic concepts of how to import data into PowerPivot, and then use the features in PowerPivot to facilitate your data analysis. The examples show you how you can use PowerPivot for Microsoft Excel to acquire, to clean, and to efficiently analyze large amounts of data.
This section discusses three key concepts that are used to create a PivotTable by using PowerPivot in Microsoft Excel 2010:
Importing Data Using PowerPivot
First, you must import data into PowerPivot. You can import data from various sources including Microsoft Analysis Services, databases, text files, or from data that is stored in an Excel workbook.
The following example shows how to load data from a CSV (comma separated value) text file.
To load data from a CSV text file
Enhancing Data Using PowerPivot
After you import the data into PowerPivot, you can enhance the data by adding additional calculated columns, creating relationships, or combining the data. Enhancing the data enables you to add value to your analysis by providing a more useful dataset.
The following steps split the City State column of data into separate City and State columns.
To create a calculated column
The following steps calculate the average employee salary based on the annual payroll (displayed in 1000’s) and the number of employees.
To calculate an average employee salary
Analyzing PowerPivot data with a PivotTable
Now that the data is imported and enhanced, you can analyze it by using native Excel PivotTables or PivotCharts.
In the following steps, you add a PivotTable to the worksheet. Then you add data that enables you to see which states have the largest number of employees for a given year.
To analyze employee counts for a given state
When the data is in the PivotTable, you can use filters to further change how the data is displayed in the PivotTable. The following are some examples of filtering and modifications that you can make.
To list only the top ten items based on 2007
To sort the list in descending order
Microsoft PowerPivot for Excel 2010 provides unprecedended analytical capability to Microsoft Excel 2010 users. By using PowerPivot, you can quickly acquire data from a wide array of sources, add calculated fields to data, create relationships between data, and then analyze the data by using PowerPivot together with Microsoft Excel PivotTables and PivotCharts.
Because PowerPivot removes the row and column limitations of Excel, you can import and process much more data, and then enjoy blistering-fast calculations that can process millions of rows while taking advantage of today’s multi-core processors and gigabytes of memory.
When you import data by using PowerPivot, the data that you import stays inside the workbook. There is no requirement to manage external data connections as you move the workbook from a laptop to a desktop. Since the data is highly compressed, the resulting file size is more manageable.
Although Microsoft Excel has always been a great business intelligence tool, coupling Excel with PowerPivot results in an easy-to-use, high performance, self-service business intelligence platform.
About the Author
Steve Hansen is the founder of Grid Logic, a Minnesota-based consulting firm specializing in business intelligence and information worker solutions. A developer, frequent author and speaker at technical conferences, Steve is a Microsoft MVP for his work with Visual Studio. One part code jockey, one part finance geek; Steve also has an MBA from the University of Minnesota with a concentration in finance.
Steve would like to thank Grid Logic's Sean Van Dyk for his assistance with this article.