How to Insert a Pivot Table in Excel - Pivot Table Basics

(This article was first published on the author's blog - MadAboutExcel.com. Click here to visit the blog)

Pivot Table, as you might be knowing, is one of the most sophisticated and useful features of MS Excel. I believe most Excel users must have used this feature at some point and many use it quite regularly. But there is still quite a large group of people who work on Excel but are not aware of the usefulness of Pivot Tables and hence don't want to take the trouble of learning it. Again, there are many users who think Pivot Tables is a very difficult tool to learn and simply give up before starting.

So, I thought of writing an article which covers all the basics of Pivot Tables and explains the concept in a very simple manner.

Pivot Tables is actually a very simple but equally useful tool to learn. It is used to make sense of raw data and modifies the data in such a way that one can extract useful information from it and take decisions based on it.

I wouldn't lecture you on the usefulness of Pivot Tables, but would now take you straight to the practicals. Download the example file by clicking here and then follow the steps given below. This would not be a comprehensive discussion on Pivot Tables. Rather, this article would get you started and will familiarize you with Pivot Tables.

Click HERE to download the Example File.

Let's get started.

INSERTING PIVOT TABLE

Let us now learn how to insert a pivot table in an excel file. I hope you have downloaded the example file by now. If not, please download and open the file so that you can work along with me.

In the example file, you'll see that there are 580 rows of data having a total of 6 fields. The data shows the sales figures of different products on different dates and the carriers used to ship the items.

We have the data all right, but can we make any sense out of it? Hardly any. Let's make a pivot table from this data and see how we can use this data.

Before inserting a pivot table, make sure any cell in the data is selected. Then go to Insert > Pivot Table. The Create Pivot Table dialogue box will pop up. You'll see that Excel has automatically selected the data for you. You can either chose to insert the pivot table in the same worksheet or in a new worksheet. Let's simply click OK for now to insert the pivot table in a new worksheet.

A new sheet would get inserted in the workbook and Pivot Table Fields pane will appear on the screen. Now drag Category field into the Filters area, Product field into the Rows area and Amount field into the Values area.

To learn more about click here

The table thus created is what we call a pivot table. This Pivot Table shows us the amount of sale for each product.

Now let me explain the logic behind what I have done. I dragged the Product field in the Rows area because I wanted to have the different products to appear in the rows one below the other. Next, I dragged the Amount field in the Values area, because I wanted to know the total sales of each product.

So now we know that ‘Furniture and Décor' is the highest selling item and Clothing, Apparels and Lug is the lowest selling item. In other words, we can take decisions on the basis of such analysis, which is not possible with just raw data.

FILTER

Now let us see the use of Filter field. I had dragged Category field into the Filters area. I did this because I wanted to know the total sales for each product in a single category. So if I select, say, Baby Products from the drop-down I can see the sales figures for the different products in the baby products category.

SORT

Now let's learn how we can sort data in a pivot table to make it even more helpful. Just right-click on any cell in the Sum of Amount column, go to Sort and select Sort Largest to Smallest. And now we can see that the products are arranged in the order of their sales figures.

CHANGE SUMMARY CALCULATION

You may now say that you don't want to know the total sales for each product. Instead, you want to know the number of sales made. Let me show you how you can change the type of calculation that you want to use. Just right-click on any cell in the Sum of Amount column and select Value Field Settings. This Value Field Settings dialogue box will open where you'll have the option to select the type of calculation you want.

Select Count and click OK. We can see that 19 out of 46 sales made were for Furniture and Décor.

TWO-DIMENSIONAL PIVOT TABLE

You might be wondering why I have left the Column area blank. Let me explain. The pivot table that we have made above is a one-dimensional pivot table. We just know one information from it and that is the number or the amount of sale for each product.

Now, drag Product Carrier field to Columns area. We now have a two-dimensional pivot table from which we can extract even more information. For example, if you want to know value of Furniture and Decor products that were shipped via UPS we can easily get the answer. 633.5 Great isn't it?

CONCLUSION

So, above was an introduction to the Pivot Tables feature in Excel. I hope you would have done the exercise with me, and if you did you would have got a good clarity on what pivot tables are and how powerful this tool is. The more you practice using pivot tables, the stronger your data analysis skills will become.

See you soon with another useful Excel article.

Happy Excelling!