course bg
EduPristine>Blog>Pivot Table in Excel

Pivot Table in Excel

February 24, 2015

Imagine that you have an important presentation tomorrow morning and you just have this night to make it. So after your long day at work when you sit to make your presentation you realize that the data is huge and you just require a part of information from it. Well, Excel always knew that someone, somewhere shall be stuck in such kind of situation and therefore it came up with Pivot Table. Pivot Tables are one of the most significant features of Excel. Pivot tables helps in summarizing and tabulating the data and is most helpful when you are working on a huge data. It saves a lot of time and provides you the insights which are hard to find otherwise.

How to create a Pivot Table?

1.  This is the data that we shall use to create our Pivot Table. In our case we are creating this pivot table to know the sum of six monthly sales in London and Sydney.

Creating pivot table in excel

2.  To create a pivot table for your data, go to insert in the ribbons menu and click on the dropdown of pivot table. You shall see two options select the option of Pivot Table. Alternatively you can use shortcut : Alt + N + V.

Pivot table option in Excel

3.  You shall see the dialog box that automatically contains the table range. Verify the table range and if it is wrong then you can edit it. You also have the option of placing the pivot table wherever you want. To avoid confusions we shall select the option of new worksheet. If you want to place it in the existing worksheet then select that option and mention the cell reference in the location tab.

Pivot table dialog box in Excel

4.  You will see that a pivot table field list has been created in a new sheet. The first half of the field list contains the names of the fields from your data. The second half is the layout section which contains report filter, Column labels, Row labels and values. By default, nonnumeric fields are added in Row labels, numeric ones are added in values and the date and time hierarchies are added in column labels. If you want you can change the order by dragging the field name to the desired layout section.

Pivot table field list

5.  In our case, we select the fields of Month, London and Sydney as we want to know their six monthly sales. You will see that the fields have automatically moved to their default layout area. The Value area in the layout has automatically selected the Sum function, but if you want to select any other function like average, maximum, minimum etc then you can click on the dropdown, select the option of Value Field settings and select your desired function.

Different options in Pivot table field list

6.  You will see that your table has been created. But in our case since we require the sum of six monthly sales, we will click the dropdown of row labels and select only the first 6 months ( Jan – June)

filtering data in Pivot table

7.  You will see your required data in the Pivot Table.

Creating Pivot table in Excel

If you think that this article was helpful to you, then share it with your friends and colleagues and if you have any doubts, then feel free to mention it in the comments box below.

About Author

avatar EduPristine

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.

easy excel tips Excel Excel Basics excel shortcuts excel tips Financial Accounting keyboard shortcuts Pivot Tables quick tips


Interested in this topic?

Our counsellors will get in touch with you with more information about this topic.

* Mandatory Field

Post ID = 71674