You might have heard of Pivot Tables as an interesting and very useful feature of Excel.
A Pivot Table can be crucial when it comes to data analysis and presentation. If you’re wondering “How can I build a Pivot Table?”, don’t you worry. We got you covered.
This guide will show you how to make a Pivot Table in Excel.
In this guide you’ll find:
- Pivot Table Definition
- Creating a Pivot Table
Pivot Table Definition
Before deep diving into the details, it’s very important to understand what a Pivot Table is, and why it is that crucial.
Some people might think that a Pivot Table is a complicated tool used by engineers to present data. However, it’s not… or at least not necessarily. In fact, a Pivot Table is a great and interactive representation of data, but the idea behind it is to make all those values easily accessible and understandable by anyone. This can be used to make life easier and less complicated.
In general, Pivot Tables are great tools that give you the opportunity to play around with data, adding them into a table, swapping rows and columns and hiding all those unnecessary values, without editing the permanent structure of the table itself.
Creating a Pivot Table
Assuming you have a large amount of data, the first thing you have to do when you want to create a Pivot Table is clicking Insert in the top menu and hit the button PivotTable.
At this point, you’ll be asked to select a table or range. Make sure to select all data that you want to include in the Pivot Table. To do so, you can simply click and drag the selection to cover the area of interest, or you could click the arrow on the right of the white box labelled as Table/Range. This second option will allow you to select data from another tab.
You can then decide if you want the Pivot Table to appear in a new worksheet or in an existing one. In case you select the New Worksheet option, a new worksheet containing the Pivot Table will be automatically created at the end of the process.
If you check the Existing Worksheet box, you’ll have to decide where to make the Pivot Table appear. In this case, select the cell in your Excel document that you want to use as a starting point for building the Pivot Table.
Don’t overthink all of this, you’ll be able to move the table afterwards.
Once you’ve checked all the boxes and you’re happy with your selection, hit OK.
An empty Pivot Table now appears on the screen. You immediately notice that a panel on the right of the screen shows all the labels taken from your original data. Those are going to be the fields that will influence the Pivot Table you’re creating.
At this point, all you need to do is drag all those relevant fields that you can find in that list and drop them in the areas in the bottom part of the panel. The areas are: Filters, Rows, Columns, Values.
Obviously, the data will move into the table according to the areas they are included in.
In general, all basic elements should be included into Rows and Columns and all details and crucial parameters should be considered as Values. Ideally, you’d use the Filters area to include geographical filters or similar kinds of data.
This might sound confusing, but it only takes some practice before it will become clear and simple.
Pivot Table Example
Hopefully, a good example will be more effective and help you learning. Let’s now go through the process of creating a real Pivot Table.
Let’s assume that we are at work and our boss just gave us a list of all his shops and would like to find out how many items were sold by geolocation and for what price.
Well, first of all we want to create the table following the process explained above. So: Insert, Pivot Table. Then we select the right Data Range and the area where we want to build the table. This part should be pretty straightforward.
Now, we notice that all labels included in the original data are listed as fields. Let’s take some of those that we consider as relevant for our analysis and drag them into the areas below the list.
First, we want to drag Store and Item into the Rows area. In fact, we want to consider these parameters as starting the point for our data presentation. Let’s make sure that Store is above Item in the box. Then, we add the list price into Values and Country into Filters. This way we’ll be able to go through the table filtering by country.
Obviously, this is just a Pivot Table example. You’re able to move your data into the different areas and add more values and filters according to your needs.
You’ve just learned how to make a Pivot Table in Excel. Obviously, as we’ve already explained, this is a simple process but can become tricky.
Before building big Pivot Tables full of values and filters, we recommend you practice with some simple stuff. It will be very helpful to figure out how to use the Pivot Table Fields and where to drag them to obtain the most useful table to suit your needs.
Time and practice will make the difference. However, consistency and willingness to learn will do the rest.
Pivot Tables are an incredible feature that makes the data presentation process easier and smooth. This is a great feature of Microsoft Excel. If you don’t own Microsoft Excel, you should know that it is included into the Microsoft Office collection and you can decide to either purchase the single software or the entire collection. Find out more on our guide about How Much Does Microsoft Office 365 Cost? Deals, Price and Free Trial.