347 647 9001+1 714 797 8196Request a Call
Call Me

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 the Author

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.


Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

Post ID = 71674