[FAQ's: OD] How to make your very First Pivot Table in MS Excel

[Siddharth Rout]

Most of us are reluctant in experimenting with Pivot Tables. Once we get the hang of it, trust me, it is difficult to live without it...

Imagine this...

You are working in a company and your core job is compiling data and deriving relevant information from it. So the first question that will come to your mind is...

Q. How do I go about it?

OR

Q. What kind of information can I get from the data?

There can be many more questions which can come to your mind. As we go through the Pivot table Tutorial, it will get more clearer.

Basically you can use Pivot table to analyse the data by comparing, checking out for prominent patterns or trends.

1. Getting the data ready

Make sure that the data is sorted as per the column headings and are in the same format. It doesn't matter if the data is not sorted but make sure you remove any kind of "validation-list", "filters" etc..

2. Creating your First Pivot Table

Select the relevant range, (if we go as per the data shown in the picture above then highlight the entire cells).

Click on the PivotTable and PivotChart Report under the Data menu as shown in picture below.

You will be presented with a wizard as shown below.

Once you click that, it will ask you "Where is the data you want to use?" Since we have already selected the data before starting the wizard it will automatically populate the data range. In case you want to add/modify your range, you can do that now as well....

Once you are done, click on finish.

a) Managing layout of the pivot table

On the PivotTable toolbar, drag field buttons to the labeled areas on the PivotTable diagram as shown in the picture below. The lines show where to drag them. They necessarily don't mean that you have to exactly drag and drop where the picture indicates...

If you want to rearrange the fields, drag them from one area to another. To remove a field, drag it outside the PivotTable report.

3) Elements of a PivotTable report

a) Row field: A PivotTable report that has more than one row field has one inner row field. Any other row fields are outer row fields. Items in the outermost row field are displayed only once, but items in the rest of the row fields are repeated as needed.

b) Column field: A field from the source data that you assign to a column orientation in a PivotTable report.

c) Page field: Page fields allow you to filter the entire PivotTable report to display data for a single item or all the items.

d) Data field: Data fields provide the data values to be summarized. Usually data fields contain numbers, which are combined with the Sum summary function, but data fields can also contain text, in which case the PivotTable report uses the Count summary function.

If a report has more than one data field, a single field button named Data appears in the report for access to all of the data fields.

4) Giving Final touches

Move button(s) around if required to get the relevant layout. Remember you can also take a field out in case you don't want it. Simply drag a field outside the PivotTable report.

If you want a more concise report then you can drill down on the details or hide information as required.

If the field is organized in levels of detail, you can click on the "down button" to see which lower-level items are selected for display. A double check mark means that some or all of the lower-level items are displayed.

Once you are done you have your very first Pivot table...