Office Excel 2010 Tutorial: Pivot Tables

The Pivot Table concept is not new in Excel 2010, it was already present in Excel 2007 but some noteworthy improvements have been made to it.

We will discuss the following in this post:

  • What is a Pivot Table?
  • How to create a Pivot Table in Excel 2010
  • What’s New in the Excel 2010 Pivot tables

What Is A Pivot Table?

The Pivot tables are used to summarize, analyze, explore and present your data. A Pivot table is a way to extract data from a long list of information and present it in a more meaningful and user friendly understandable format. For example, lets say that we have the data of student scores in a spreadsheet, you could turn this into a pivot table, and then view only the Math scores for each pupil.

How To Create A Pivot Table In Excel 2010

Here are the steps to create a Pivot table in Excel 2010.

Step 1: First of all, please make sure to select the data range for which you want to make the pivot table.

Pivot Table 1

Step 2: Insert the Pivot Table by going to the Insert tab and then clicking the Pivot Table icon.

Pivot Table 2

Step 3: Select the target cells where you want to place the pivot table. For starters, select the New Worksheet option.

Pivot table connection

Step 4:  The new worksheet will open and you will be able to see the pivot table that you just created, you can now generate the report from this table and can perform various operations on this table for better visualization and presentation of data. Just for example I calculated the sum of all of the selected cells.

Pivot Table Insert


In the right side, you will see the Pivot Table Panel which contains many useful options to work with the Pivot table.

Pivot table Panel

What’s New In The Excel 2010 Pivot Table

As discussed in the introductory paragraph, Pivot Tables were also present in Microsoft Excel 2007, lets see what new enhancements have been made to the Excel 2010 Pivot tables.

1. It includes a new feature called ShowValues As, right click the Pivot table and choose Show Values As, you will see many new options here. It helps you in trying out several different calculations until you get exactly what you were looking for.

Pivot Show As

2. It offers many new calculations, almost six new calculations have been added to it.

3. It includes some great visual totals. Visual Totals refer to what totals should be shown when one or more members are filtered. You can turn this feature ON of OFF, simply right click the Pivot Table and choose the Pivot Table Options option here.

Pivot table Options

In the PivotTable Options window, go to the Tools & Filter tab and check/uncheck the Include filtered items in set totals option to enable/disable it.

PivotTable Visual Style

The enhanced pivot table feature of Excel 2010 add feathers to this great product by Microsoft. You can read a more detailed post on the new features of Excel 2010, here. Enjoy!

Advertisement
  • nintendo dsi r4

    Offer the old menu bar for people (most of my clients) who don't want to learn the new menu bar. You can finally modify the ribbon to some extent in 2010 however my clients just want their old ribbon bar. Frankly I have no issue with the new menu bar but I'm one person and most of my clients don't like it so prefer to stick with office 2003. MS could make money selling the new version if they just offered the old menu as a choice with the new ribbon.

  • nintendo dsi r4

    Offer the old menu bar for people (most of my clients) who don't want to learn the new menu bar. You can finally modify the ribbon to some extent in 2010 however my clients just want their old ribbon bar. Frankly I have no issue with the new menu bar but I'm one person and most of my clients don't like it so prefer to stick with office 2003. MS could make money selling the new version if they just offered the old menu as a choice with the new ribbon.

  • nintendo dsi r4

    Offer the old menu bar for people (most of my clients) who don't want to learn the new menu bar. You can finally modify the ribbon to some extent in 2010 however my clients just want their old ribbon bar. Frankly I have no issue with the new menu bar but I'm one person and most of my clients don't like it so prefer to stick with office 2003. MS could make money selling the new version if they just offered the old menu as a choice with the new ribbon.

  • Nakodari

    Install UBitMenu to get old menu and toolbar in Word 2010, Excel 2010, and PowerPoint 2010. Here you go: http://www.addictivetips.com/windows-tips/get-c

  • Nakodari

    Install UBitMenu to get old menu and toolbar in Word 2010, Excel 2010, and PowerPoint 2010. Here you go: http://www.addictivetips.com/windows-tips/get-c

  • raucus

    “2. It offers many new calculations, almost six new calculations have been added to it.”

    What does this mean? Is “many” exactly “almost six”? And is “almost six” exactly 5, or is it possible to have 5.5 new calculations?

  • Scott

    I sure wish Microsoft would add the ability to cross-tab on first and last values in the Pivot Tables, like you can in Access. All they seem to think people want to do is count or do statistics. I just want to know what option people picked and have it in a nice pivot table format.

  • Jonathan

    Excellent, your site is much easier to use than the MS Excel Help Function that just returns dummy videos…

  • Kubilay

    @nintendo, what is the purpose of a new Office 2010 when you don’t want to use the ribbon? It’s not making any sense to me because the power in Office 2010 comes from the easy ribbon. By the way, microsoft is now planning everything with a ribbon bar! Like the new Windows 8 Explorer… So instead of trying to stick with old technologies, why don’t you guys train your customers or employees for new technologies so they can be more productive?
    Just saying.

  • Johano

    @Kubilay… i can see where you’re coming from. I totally support advancements in technology that work for people, but i’m old school. I don’t even like using my mouse if i can help it, but this visual GUI ribbon they have is overkill. I mean really, if your abilities relay souly upon seeing instead of completely understanding functions then those same clients are going to be left behind. I miss the drop down menus, a few key strokes and poof i get EXACTLY what i want instead of endless uselss pretty options. I’m more for raw data, which is what excel is supposed to be used for. Leave the pretty work to be done on Word or PowerPoint.

  • Nilesh

    How can I get data in previous manner/regular data after converting into pivot table ?

  • Nilesh

    How can I get data in previous manner/regular data after converting into pivot table ?

  • Grace7954

    No wonder why MS sales this much, this tool is so powerfull, i just tried it and the result is Insane, crazy, i don’t even have to work on MS access whooww

  • PR

    Amazing..

  • Calre Murphy

    Note: The link to “a 1 minute video” above at the top of this page took me to a 3 min video of Smartsheet, a cloud-based spreadsheet product… where I needed to sign up for a 30 day free trial. The link did not go to what I was expecting which I thought would be a 1 minute video demo on “Office Excel 2010 Tutorial: Pivot Tables”.

  • learntnothing

    How is this a tutorial?

  • Pam L.

    this was NOT a tutorial. Having never working with Pivot Tables before, I learned absolutely nothing from this.

  • ali asgari

    are there anybody to answer all this questions??????who response our questions here???????? I have a lot of

  • gabriela

    But in my offer menu is not the pivot table, where can I find it, if is not there-INSERT_PIVOT TABLE

  • elspeth

    I hate the new filter function in the pivot tables. It takes too long to hide individual lines.