Excel PivotTable is one of the most useful tool and sadly least understood tool also. Like cross tabulation in statistics, a PivotTable show how data is distributed across categories. For example, you can analyse data and display how different products sell by region and by quarter. Alternately, you can analyse income distribution and consumer preference by gender and age bracket. Excel PivotTable answers very useful questions on the data. Let us start with how to create PivotTable in Excel.
Creating PivotTable in Excel:
Step1: Select the data on which you want to include in the PivotTable.
Step2: Click on Insert Tab
Step3: Click PivotTable. The Create PivotTable dialog box appears.
Step 4: Click a data source. If you already selected a range in the current workbook, the range appears here. Just verify the data range selected covered all the data points.
Step 5: Click to select where to place report. If you want to place the report in the existing worksheet, type the location.
Step 6: Click Ok. Now you will see the PivotTable Field list.
Step 7: We need to place elements in a way that we need the data to be presented. So let us have quick overview on the PivotTable Layout.
PivotTable layout consist of several elements: Report Filter, data, columns and rows. You can use the PivotTable Field list to organize these elements, When working with the PivotTable, you can bring the Field List into view by clicking anywhere in the PivotTable, then click the Options Tab and then clicking Field List. Report Fields enables you to filter the data that appears in your report. Row fields appear as row labels down the left side of your PivotTable and Column fields appear as Column lables across the top of your PivotTable. You place your continuous data field in the Value box. Field placed in the Values box make up the data area. You can also arrange and rearrange field layouts.
Step 8: Click to select the fields you want to include in your PivotTable.
Step 9: Click and Drag fields among the report, Column,Row Lab and Value boxes.
Step 10: Click on the field header and then choose your sort and filter options.