Introduction to PowerPivot for Excel 2010

  • Article

Office Visual How To

Summary:   Use Microsoft Excel 2010 and the Microsoft PowerPivot for Excel 2010 to create powerful, easy-to-use Business Intelligence solutions.

Applies to: Excel 2010 | Office 2010 | VBA

Published:   February 2011

Provided by:   Steve Hansen, Microsoft Visual Studio MVP and founder of Grid Logic.

Overview

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.

Download PowerPivot for Excel 2010

Code It

This section discusses three key concepts that are used to create a PivotTable by using PowerPivot in Microsoft Excel 2010:

  • Acquiring external data with PowerPivot.

  • Manipulating and cleaning data in PowerPivot.

  • Analyzing data from PowerPivot in Excel by using a PivotTable.

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

  1. Open a new workbook in Excel and then click the PowerPivot tab.

  2. Click the PowerPivot Window button on the ribbon.

  3. In the PowerPivot window, click From Text. As Figure 1 shows, there are several other options available to you when you import data; you can import from a database, a multidimensional source, data feeds, or text files.

    Figure 1. Importing external data in PowerPivot


    Importing external data in PowerPivot

  4. In the In the Table Import wizard, click Browse and locate the Totals.txt file.

    Figure 2. Contents of Totals.txt


    Contents of Totals.txt

  5. Change the friendly connection name to Data and select Use first row as column headers.

  6. Click the drop-down arrow in the Quality Flag column, clear Select All, and then select (Blanks) to filter out all records that have incomplete data. Note that you can add filters on other columns; this is a fast, easy way to clean data and focus on the items that are relevant to your needs.

  7. Clear the Quality Flag and Qtrly Payroll column headings to prevent these fields from being included in the imported data. Note, however, that even though the fields are not included in the imported dataset, PowerPivot implements any filters that are applied to the fields when it imports the data.

  8. Click Finish and then click Close after the data is imported.

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

  1. Select the first empty cell under the Add Column label, and then type the following in the formula box at the top of the worksheet:

    =left('totals'[City State],len('totals'[City State])-4)

  2. Double-click the column heading CalculatedColumn1 to select it, and then change the name to City.

  3. Select the first empty cell underneath the Add Column label, and then type the following in the formula box:

    =right('totals'[City State],2)

  4. Double-click the column heading CalculatedColumn1 to select it, and then change the name to State.

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

  1. Select the first empty cell underneath the "Add Column" label, and then type the following in the formula box:

    =if('totals'[Employee Count]=0,0,(('totals'[Annual Payroll]*1000)/'totals'[Employee Count]))

  2. Double-click the column heading CalculatedColumn1 to select it, and then change the name to Avg Salary.

  3. Click the Avg Salary column heading to select the whole column, and then on the ribbon, in the Formatting group, apply the Accounting format.

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

  1. On the PowerPivot ribbon, click PivotTable, and then click OK to create the PivotTable in a new worksheet.

  2. Drag and drop the following columns into the following report areas that are located on the bottom of the PowerPivot Field List task pane as shown in Figure 2:

    • EmployeeCount into Values

    • State into Row Labels

    • Year into Report Filter

    Figure 3. PowerPivot Field List task pane


    PowerPivot Field List task pane

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

  1. Right-click the first row label in the list, select Filter, and then click Top 10.

  2. Choose Filter by Sum of Employee Count and then click OK.

  3. Click Column Labels and clear 2006.

To sort the list in descending order

  1. Right click the first item underneath the 2007 column heading.

  2. Select Sort and then Sort Largest to Smallest.

Read It

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.

 See It

Watch the video

> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/0ab0c8ae-3794-46fd-9200-5951a5af9ff4]

Length: 00:08:33

Explore It

 

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.