Pivot table is a very powerful and helpful tool available in
excel for data processing. It helps in summarizing large amount of data with
ease. It can be used to derive sum, averages, count from the data stored in a
spreadsheet in various combination. For
e.g. geography / product wise total sales revenue or Department/location wise
headcount etc.
The screenshot 1 below shows the various components of an
Pivot tables and the screenshot 2 shows the Sales revenue generated by each
product in various geography with an option to select the Sales Person (in the Report
filter).
Screenshot 1: (Pivot table components)
Screenshot 2: (Pivot table example)
Below mentioned are the
steps to make Pivot table.
- Select the data on which you want to create a pivot table.
- Click Insert à PivotTable à PivotTable
3. Select a table range (by default you should get the data range selected in this) & cell where you want to put the new pivot table. Select New worksheet to put the pivot table in new worksheet. Click OK.
4. A new worksheet will be added in the file as per Screenshot 1 above.
5. Drag fields in the pivot layout. Notice the changes in Pivot table after dragging each field
- Drag “Product Name” into Row Labels
- Drag “Geography” into Column Labels
- Drag “Revenue ($)” into Values
- Drag “Sales Person” into Report Filter
- Select the data and change the Number format as required
That’s it, the pivot table as shown in the example above is
ready. You can try different slice and dice options of the pivot table and
derive meaningful information out of your data.
No comments:
Post a Comment