Free Excel Tutorial for Engineers : Pivot Table

When you have large tables in excel, there are a lot of subheads and each subhead has many different entries. Even after using the filter option we can’t get a summary of the data without changing the order of the entries. In such cases a pivot table helps you to segregate information and view the information in a more concise format. A pivot table allows you to find the total of all the values under a subhead, find the count of the entries for each subhead and so on. In a pivot table it is necessary to have all the subheads correctly entered and that each column has a clear heading at the top. To create a pivot table, follow the steps given below:

  1. Go to the insert ribbon, and select the pivot table option.

  1. In the dialog box that appears, select the required range of cells to be included and the worksheet where you want to display the pivot table.

[Note: You can insert the pivot table in the same worksheet or in a new worksheet. Pivot table also allows you to use data from a different workbook to create a pivot table.]

  1. In the right hand menu that appears on the worksheet area select the fields that you want to include in the pivot table. The pivot table automatically gives you the output.

You can also change the output data of the pivot table. Select a cell in any of the fields of which you need to change the format, go to the options ribbon, click on the field settings option below the active field and select the required format. Some of the options provided are sum, average, product, count of values, etc.

Also, by using the drag the fields between areas you can change the look of your pivot table. By placing the field in report filter area you can use only the summary of the field in your pivot table. By adding a field to the column or row labels you can align the data row or column wise. By adding a field into the value field you will get the total values of the inserted fields. While selecting the pivot table option you may note that you get 2 options - Pivot Table and Pivot Chart. By using the pivot table you can create a comparison table. The Pivot chart on the other hand allows you to create both table and a graphical representation of the data.