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

Published on July 4, 2017

Pivot Tables in Excel – The Basics

Pivot Table_Basics_Featured_Image

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 TableThe 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.

FrequencyDistribution2

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.

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

Pivot_Table1

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.

Pivot_Table2

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.

Pivot_Table3

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 units sold. 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.

FrequencyDistribution4

Select Count and click OK. We can see that 157 out of 580 units sold were Furniture and Décor.

Pivot_Table4

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 how many units of Sports and Outdoor products were shipped via UPS we can easily get the answer. 45! Great isn’t it?

Pivot_Table5

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.

This article is just the beginning. There is a lot more that you can do with Pivot Tables. If you really want to master this powerful tool, I’ recommend you join the bestselling course by Chris Dutton called Microsoft Excel – Data Analysis with Excel Pivot Tables.

I also recommend you read my other articles on Pivot tables as well by clicking HERE.

Lastly, I’d ask you to comment below and tell me what you liked or didn’t like about this article. I’d love to hear from you.

See you soon with another useful Excel article.

Chao!

 

GET MY EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE

Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide for FREE!

Thank you for subscribing.

Something went wrong.

HUGE LIMITED TIME DISCOUNT ON THE BEST ADVANCED EXCEL COURSE ENROLL NOW!!
EXCEL PRINT TIPS
9 Excel Print Settings You Probably Don’t Know About
excel_look_professional
Making Excel Worksheets Look Professional – A Case Study
14 Excel tips
14 Excel Tips – Make Yourself Super-Efficient in Excel
DATA CLEANING EXCEL
Ultimate Guide to Data Cleaning in Excel – 11 Super-Powerful Data Cleaning Techniques
FREEZE PANES
Freeze Panes in Excel – The Complete Guide
HOW TO LEARN EXCEL
How to Learn Excel and Be a Spreadsheet Champion
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
BECOME AN EXCEL NINJA COMPLETE REVIEW
Become An Excel Ninja – Course Review
BEST EXCEL COURSES
10 Best Online Courses to Make You an Excel Wizard
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
How to Sort Excel Worksheet in Alphabetical Order
How to Sort Excel Worksheets in Alphabetical Order
VBAPOST1
How to Add And Name A New Sheet At The Same Time Using Excel VBA
ROUNDING NUMBERS IN EXCEL
Rounding Numbers in Excel – The Complete Guide
number_of_words
How to Find the Number of Words in a Text String in Excel
HOW TO FIND IF A TEXT STRING CONTAINS SPECIFIC TEXT IN EXCEL
How to Find if a Text String Contains Specific Text in Excel
How To Indentify ULP case in a text string
How To Identify If A Text String Is In Upper, Lower Or Proper Case
Pivot Tables
How to Insert a Pivot Table in Excel – Pivot Table Basics
How To Convert Pivot Table Into Frequency Distribution
How to Convert Pivot Table into a Frequency Distribution in Excel

GET MY EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE

Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide for FREE!

Thank you for subscribing.

Something went wrong.

Share
Tweet
+1
Pin
Share
Stumble
Enjoyed this video?
"No Thanks. Please Close This Box!"