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.
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.
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.
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.
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.
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)
7. You will see your required data in the Pivot Table.
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.
Related links you will like:
Comments