Using the New Features in Pivot Tables in Excel 2010

Pivot Table in Excel 2010

PivotTables are like databases. They are tables that can be manipulated in many ways to obtain different kinds of outputs. As the name indicates, the pivot is like an axis around which various data outputs are managed. This is especially useful when you have interrelated data. In simple words, it is like a library of data where you can read either the history section or the literature section.

To see how exactly this works let us take an example.

Let us take that there have been sales of certain items, according to date, Item & Price. All these have been put as structured data into the cells of a new spreadsheet. The data are entered as

  • Order Number
  • Date
  • Product Code
  • Product Name
  • Quantity
  • Price
  • Total Price

Image

Select all the cells containing the data. Now go to Insert tab and click the PivotTable ribbon. A dialog box will open asking you to choose the data you want to analyze.

The data range will be preselected (the cell numbers that contain your data). If you are newly creating or new to creating this table, select the New worksheet option and click OK to proceed.

 

Image

The data range will be preselected (the cell numbers that contain your data). If you are newly creating or new to creating this table, select the New worksheet option and click OK to proceed, else, you can insert it into an existing worksheet.

 

Image

 

You will see an empty worksheet. Don’t panic! Your data is safe. In Excel 2007 you could simply drag and drop to create the PivotTable. Data tables in Excel 2010 are populated in a different way.

 

Image

 

You have to check all the boxes on the ‘choose fields to add report’ to create your pivot table. Click the boxes in the order you want to see the data in the pivot table.

If you want to see Date as first instead of Product Code, check Date and then Product Code so on and so forth.

Or you can drag each item, in corresponding area. Drag dates to Column Area, and Product Code to Row Area.

You can GROUP dates to MONTH, QUARTER or YEAR. Simply Right click on any date, and select GROUP.

Select Month & Year to group dates.

Now drag, Quantity / Price, in Values area, to get depth analysis.. like,

  • Which product sold maximum, on which date!!
  • What product Sold lowest.
  • Sort by Highest Sold or
  • Day-wise break up of sales in each month.

In Value area, you can click and select different option, to change SUM to COUNT , AVERAGE or MAX

 

Image

You just need to drag & sort each item in different area, and play with it.
With a little experiment, you can able to extract many data crunching analysis.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube