Create a Pivot Table Report in Microsoft Excel 2010 application

In the following tutorial, we will guide you on how to create a pivot table for a given source data in Excel 2010 application. In the previous tutorial, we already introduced to you the importance of pivot tables and how and when to use a pivot table.

To create a Pivot table, do the following

1) Firstly, have the source data ready for which you want to create a pivot table report.

Figure 1

2) Then, click on any cell in the source data.


NOTE: - ensure that the source data has column headings and there are no blank spaces in the range of cells that contain the source data.


3) Now, click on the “Insert” tab and then in the ‘Tables’ category, click on the drop down arrow of the ‘pivot table’ icon and choose “Pivot Table” from the drop down list.

Figure 2

4) Now, in the “Create Pivot Table” dialogue box thus opened, under ‘where you want the pivot table report to be inserted’, click the radio button that says “New worksheet” and also ensure that “Select a table or Range” radio button is selected and finally click on ‘OK’ button.

Figure 3

Tip: if you have the source data in another workbook, include the workbook name and worksheet name as follows

[workbookname]sheetname!range


5) Once you click the OK button in the previous step, a new worksheet appears on the screen with a pivot table layout present in it.

Figure 4

6) Now, to add fields to the pivot table report, do the following

a. In order to place a field in the default area of the layout section, select the checkbox that is present next to the field name in the field section. The non-numeric fields by default will be added to the Row labels area and the numeric fields by default will be added to the Values area and the data and time hierarchies will be added to the column labels area.

Figure 5

b. To place a field in a specific area you want, right click on that particular field name in the field section and choose an area from the drop down list.

Figure 6

c. You can also drag a particular field name into the specific area you want. To do so, click on the field name and hold your mouse and drag the filed to the area that you want in the layout section.


7) So, now this is how the pivot table looks after adding each field to the specific area in the layout section. In our illustration we have added the Exam and Year fields to the row labels area and Number of Takers to the Values area.

Figure 7

Tips: -

• The pivot table field list can be used to rearrange the fields of the pivot table at any time by right clicking on the field name and then selecting the area that you want to place the field or else by simply dragging the fields in the field list between the areas present in the layout section.

• If you make any changes to the source data, the changes will be reflected in the pivot table when you refresh the pivot table (PivotTable tools, Options, Data category, Refresh button).

• If you want to include additional rows into the source data and have these rows reflected in the pivot table report, click (PivotTable Tools, Options, Data category, and Change Source Data button).


So, this is how you can create a pivot table in Microsoft Excel 2010 application.


Read Other Applications