Microsoft PowerPivot for Excel 2010 – Adding Pivot Table

Who hasn’t used Pivot Table before?  Everyone uses it in Excel; it is very powerful tool to extract meaningful information from data.  When we do data analysis in PowerPivot, we are going to use the same tools.  PowerPivot allows you to handle more data and provides extra analytical functions, i.e. Data Analysis Expressions (DAX).

Add a PivotTable

  • In the PowerPivot window, on the PowerPivot Home ribbon, click PivotTable.   There is also a PivotTable button on the Excel ribbon but Excel PivotTables cannot access your data in PowerPivot, so make sure you are creating PivotTable in PowerPivot window.

image

  • Select PivotTable.

image

  • Select New Worksheet.
  • Excel adds an empty PivotTable to the location you specified.

image

  • You would also see the PowerPivot Field List.  It contains two sections: a field section at the top for adding and removing fields, and a section at the bottom for rearranging and repositioning fields.

image

  • Select the empty PivotTable.
  • In the PowerPivot Field List, scroll down and locate the FactSales table.
  • Select the TotalSales field.
  • In the DimChannel table, select the ChannelName field.
  • In the DimDate table, select the CalendarQuarter field.
  • In the PivotTable Field List, move this field from the Values box into the Column Labels box.
  • The bottom section of PowerPivot Field List should look like the following picture. 

image

  • Rename the PivotTable by double-clicking Sum of TotalSales in the first cell, and typing Sales by Channel.

image

  • Highlight the data cells in the PivotTable, right-click and select Format Cells.
  • In the Format Cells window, select Number and enter 0 in the Decimal places dropdown. Select the checkbox for Use 1000 Separator.
  • Click OK.

image

  • Here is the new format.

image

  • You can choose to only show the first quarter of every year in your analysis by removing the arrow of other quarters.

image

  • Or you may want to just look at the Online Sales.

image

This is nothing new so far; apart from the fact that you are working on 4 million records.  Do you think you can build the following Profit by Category Pivot Table on your own?

image

 

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

9 Responses to Microsoft PowerPivot for Excel 2010 – Adding Pivot Table

  1. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Slicer | Technologies and your business

  2. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Pivot Chart | Technologies and your business

  3. Pingback: Microsoft PowerPivot for Excel 2010 – Creating Relationships Among Data | Technologies and your business

  4. Pingback: Microsoft PowerPivot for Excel 2010 – Importing Data | Technologies and your business

  5. Pingback: Why Microsoft PowerPivot? | Technologies and your business

  6. Pingback: Microsoft PowerPivot for Excel 2010 – Introduction | Technologies and your business

  7. Pingback: Excel 2010 – PowerPivot | Technologies and your business

  8. Pingback: Microsoft PowerPivot for Excel 2010 – Data Analysis Expressions (DAX) | Technologies and your business

  9. Pingback: Wish you a Merry Christmas and a Happy Analytics Year! « Technologies and your business

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: