Microsoft PowerPivot for Excel 2010 – Adding Slicer

I have used Excel in my everyday work since 1993 when Excel 5.0 was released.  Microsoft added new features to every version of Excel; however, I never felt so excited about a new release until Excel 2010.  Excel 2010 has many enhancements.  Some of them are behind the scenes; e.g. 64 bit edition which waive the 2GB file size limit; multithreading helps speed up data retrieval, sorting, and filtering in PivotTables.   Some are visible and very powerful data visualization tools; e.g. conditional formatting, sparkline, and slicer.  In this blog, I am going to discuss slicer, how it can be used to slice-and-dice the PivotTable.

Add Slicers to a PivotTable

This time we work on the Profit by Category PivotTable,

  • Click anywhere on the Profit by Category PivotTable.
  • Click Options in the PivotTable Tools area.
  • Click Insert Slicer.

image

  • In the Insert Slicers window, locate the Stores table and select RegionCountryName.

image

  • Select ChannelName under the DimChannel table.
  • Select ProductCategoryName under the ProductCategory table.
  • Select ProductSubcategoryName under the ProductSubcategory table.
  • Click OK.
  • 4 Slicers are added to the worksheet.

image

Format Slicers

  • Click the grey border and drag the Slicers so that all of them can be seen.

image

Rename Slicer
  • Right-click the ProductSubcategoryName Slicer and select Slicer Settings.
  • In the Caption box, type Subcategory.

image

  • Click OK.

image

Add Slicer Columns to see more content
  • Select the RegionCountryName Slicer.
  • Select Size and Properties.
  • Select Position and Layout.
  • Enter 4 in the Number of columns dropdown.

image

  • Click Close.
  • Resize the Slicer until all content is visible.

image

Use Slicers to Analyze your PivotTable

I can produce the profit for Boxed Games sold Online in China with just a few mouse clicks.

image

You want to see the profit for Audio products through Reseller in US, please give me 2 seconds.

image

Let’s look at how Slicers can work with the Profit % by Category PivotChart.

Add Slicers to a PivotChart

  • Click anywhere on the Profit % by Category PivotChart.
  • Click Analyze in the PivotTable Tools area.
  • Click Insert Slicer.
  • In Insert Slicers window, select CalendarYear and CalendarMonth under the DimDate table.
  • Select RegionCountryName under the Stores table.
  • Click OK. You can format RegionCountryName Slicer the same way that we just did for the PivotTable and format other Slicers as needed.Use Slicers to Analyze your PivotChartWhat do you notice when you compare the following 2 diagrams, the Profit % by Category for Canada in 2007 and 2009?image

    image

    Can you imagine how these new features, PowerPivot, and Slicer can improve the turnaround time of report generation?  I can and this is why I am so excited about Excel 2010!

     

  • 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 Slicer

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

    2. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Pivot Table | 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: