Excel 2010: Create Pivot Table & Chart

Excel 2010 has an option of creating pivot table, as name implies it pivots down the existing data table and tries to make user understand the crux of it. It has been extensively used to summarize and glean up the data. Contrasting to Excel 2007, Excel 2010 provides very easy way to create pivot tables and pivot charts.

For better understanding, we will use an excel worksheet filled with simple sample data, with four columns; Software, Sales, Platform and Month as shown in screenshot below.

clip_image001

To start out with making pivot table, make sure that all rows and columns are selected and record (row) must not be obscure or elusive and must be making sense. Navigate to Insert tab, click PivotTable.

clip_image002

You will reach Create Pivot Table dialog box. Excel fills in data range from first to last selected columns and rows. You can also specify any external data source to be used. Finally choose worksheet to save the pivot table report. Click OK to proceed further.

clip_image003

Pivot table will appear. Now we will populate this table with data fields which is being present at the right side of the Excel window. Excel 2010 has changed the way you insert pivot table when compared with Excel 2007 where dragging and dropping did the trick. Just enable the field’s checkboxes seen at the right side of the window and Excel will automatically start populating pivot table report.

clip_image004

We start off with enabling Platform field, and then other fields. Excel start filling cells in a sequence you want to populate. The Platform field will come first in the Pivot Table as shown in screen shot.

clip_image005

If you want to populate in a different way, just change the sequence.

For Instance: In order to summarize data by showing Month field first and then other fields. Enable Month field first and then other fields.


clip_image006

For more filtering options, click on Row Labels drop-down button, you will see different options available to filter down and summarize it in more better way.

clip_image007

For creating chart of pivot table, go to Insert tab, click Column select an appropriate chart type. In this example we will create a simple 3-D Column chart.

clip_image008

Excel will create chart out of your data. Now resize it for a better view. Chart content can be changed by using the options at the bottom-left of its area.

clip_image009

For Instance: If we want to view software apps developed only in .NET platform, simply click Platform button, and select .NET from the its options and then Click Ok

clip_image010

Pivot Table and Chart will only show software and month in which .NET platform is used for development.

clip_image011

Now you can create more filtered and dapper-up charts by using pivot table report. Usage of pivot table and chart not ends here, it is an ever growing feature of excel and has endless dimensions.

Advertisement
  • Pingback: Excel 2010 SmartArt

  • Pingback: Insert Slicer In Pivot Tables & Charts – Excel 2010

  • Sarah

    This is great, it’s exactly what I need to do with one step missing!! In the example where you have the Platform field as the header, how would you sum all of the values underneat for each category?? Thanks :-)

    • Nik

      Hello Sarah,

      I know it is kind of late but still, you need to move “Sales” from ‘Row Labels” field to “Values” field.  This field is right to ‘Row Labels” field and is not shown on the screenshots above.  Drag-N-Drop the the “Sales” row to “Values”and you will get things sorted just as on the pictures above.

      Hope that helps!
      -Nik

  • Ramesh

    Thanks for the Tips. Can i get some download files?

    http://tipsindeed.com/excel/complete-guide-to-pivot-tables-in-excel.html

  • Melvin

    This is great! Short, consice yet detailed and to the point. Great job! I learn alot from this small but enormously educative presentatios. Thank You

  • santhosh

    Thats really great job done. Very short and very clear. Learnt very much in very less time..

  • Yadiga

    Too good.. thanks a ton.

  • Ni

    I have created a pivot table using Excel 2010 but some cells are blank, missing data! any tips please?