Excel Tips Tutorial: How to Use Pivot Tables in Microsoft Excel
Before starting Pivot Table, you must know what is Pivot Table and how much powerful it is.
Excel pivot tables are very useful and powerful feature of MS Excel. They can be used to summarize, analyze, explore and present your data.
Here are some example uses of pivot tables:
Step 1: Select the data
Select the data range from which you want to make the pivot table. Select B3 to H1082 range as below
Step 2: Go to Insert ribbon and click on new Pivot table option
To insert a new pivot table in to your spreadsheet, go to Insert ribbon and click pivot table icon and select pivot table option.
Step 3: Select the target cell where you want to place the pivot table.
A pivot table wizard is appeared where you can specify the pivot table target location etc. Select “New worksheet” option and your pivot table will be placed in newly created worksheet.
Step 4: Make your first pivot report
The pivot report is very powerful. To make powerful analysis, all you have to do is drag and drop fields in to the pivot table grid area. You can also control this by using the “Pivot table panel”.
The pivot report is divided in to header and body sections. You can drag and drop the fields you want in each area. The body itself contains three parts. Rows, Columns and Cells. You can use any fields in these areas too.
I have just select the following criteria
You can design your pivot report by going through the design tab and select the desired design layout
- Summarizing data like finding the average sales for each region for each product from a product sales data table.
- Listing unique values in any column of a table
- Creating a pivot report with sub-totals and custom formats
- Making a dynamic pivot chart
- Filtering, sorting, drilling-down data in the reports without writing one formula or macro.
- Transposing data – i.e. moving rows to columns or columns to rows
- Linking data sources outside excel and be able to make pivot reports out of such data.