Step By Step PowerPivot using Excel Data

This post is a step by step beginner’s guide to PowerPivot.  I will show the basics of creating a PowerPivot workbook using Excel data.  PowerPivot has dramatically improved the way in which we can analyze our data in Excel.  Using PowerPivot in an Excel PivotChart provides the added benefit of Slicers.   Slicers are a cool new way to quickly slice and dice your data. 

I am using data that I pulled off the US Census web page. I was able to download three CSV files (Population Change, Population Density, Apportionment ). I did a couple of quick data modifications.  The data in the files is stored with a new column for each year. I created a new column called year and unpivoted the data. I then took the data from the three files and merged them into one Excel spreadsheet. I also create a 2nd workbook for the region information.

…Dowload Source Spreadsheet

There are two easy ways to pull data from an existing Excel spreadsheet. You can open an existing spreadshhet and convert the data into Excel Table or you can open a blank spreadsheet and import the data. I will first show how to import data within a existing spreadsheet.

  1. Start open the census spreadsheet in Excel 2010 (Make sure PowerPivot is already installed)
  2. Under the PowerPivot ribbon select “Create Linked Table”. Select the entire set of census data. This will convert the existing set of data into an Excel Table and will open the PowerPivot browser.
    1. Inside the PowerPivot browser rename the table to Population
    2. Now that the data is in PowerPivot, the data can be sorted by any column, really quickly.

      

  3. Close the PowerPivot browser and import the region data.
    1. Open the Region worksheet. Hit Create Linked Table. Select the data range.
    2. Now the Region data has been added as a 2nd table in PowerPivot.
    3. Rename the table to Region.
  4. We now have two separate datasets. We need to tell PowerPivot how these two sets of data are related.
    1. Under the Design Ribbon select Create Relationship. The Create Relationship popup will appear.
    2. Select the State Column from each of the two tables that have been created.
    3. You can see that this column now has a relationship.

     

  5. Now lets build a simple report.
    1. From the PowerPivot browser (or back in Excel under the PowerPivot Ribbon), select the PivotTable dropdown and select PivotChart.
    2. Place the Chart on a new Worksheet
    3. Under the PowerPivot Field List you will see two data sets (Population and Region)
    4. Add the following
      1. Slicers Vertical: Region, Division, State (you can use the state from either Population or Region)
      2. Slicers Horizontal: Year
      3. Legend Fields: Year
      4. Axis Fields: State
      5. Values: Population(The default will make this a SUM)

 

The slicers that have been added are a fast and easy way for you to slice and dice (filter) your data.  Each slicer is aware of the other slicers in the PowerPivot dataset.  You will notice that if you click on the Region Slicer, lets say South, both the Division and State slicers will be updated to list the Divisions and States in the South.

…View the final spreadsheet (In order to see PowerPivot in action, you will need to open the document in Excel)

You can leave a response, or trackback from your own site.

Leave a Reply