Advanced Excel TutorialsExcel Assessment TestExcel Formulas and FunctionsExcel Interview QuestionsExcel Test for Interview Candidates

Excel Pivot Table Tutorial For Beginners

Pivot Table in Excel for Beginners

By name it might seem scary at first when starting with Pivot Table but it’s one of the very amazing features of the Excel which let you dissect a data properly whether it be Sum, Average, Product, Charts and much more.

Today we are going to start teaching you about Pivot Tables and its many applications but don’t worry we are not going to overwhelm you as we are going to look at the Pivot Table from the eyes of a beginner.

Let’s start simple and create our very first Pivot Table.

Creating a Pivot Table

Pivot Table with Fixed Range on a New Sheet

On the below screenshot you can see our sample data which contains sales reps across various Regions.

So, in order to create the Pivot Table, you have to select the data set including the headers and then go the Insert tab and from tables group select the option Pivot Table. Refer to below screenshot to understand more.

Now once you do that you will see the following window

From here we have to observe a couple of things as we can see it is showing our selected range which is ‘Sales Data’!$A$1:$G$44 against Table/Range: option and then at the bottom its showing where do you want to place the Pivot Table and now one thing to note is that Pivot Table is dynamic so you need to give it more space as it expands so let’s for now put it on a different sheet.

And once you click OK you will be transferred to a newly created sheet which will look something like below

Now you can observe from the image that we are indeed on a new sheet and you can see our Pivot Table begins from cell A3 and on the very left you can see an option called PivotTable Fields. We can go in-depth about it when we explore it further.

Pivot Table with a Table or an Existing Sheet.

Now let’s explore creating Pivot Tables with an Excel Table. To clear your doubts, we can also create Pivot Tables with Fixed Range on existing sheets and I choose this example to kill two birds with one stone.

So, what are the benefits of creating a Pivot Table with an Excel Table, well the biggest one is that we can increase/decrease the data in the Excel Table and the Pivot Table will update based on the inputs.

Let’s first convert our Range to an Excel Table in two simple steps.

  • Select the entire range of your Excel Data
  • Go to the Insert tab of the Excel Ribbon and then from Tables group select Table and you will be greeted with an option like in the below image.
  • Hit OK as we have headers on our data and that’s it, your data is now converted to a Table and you can see that Excel will make some format changes on its own which you change based on your requirements

Now we are going to create a Pivot Table on a sheet named Analysis which is a blank sheet we have inserted on our spreadsheet.

So again, like the last time select the entire data and repeat the process by going to Insert Ribbon and selecting the option PivotTable and you will be greeted with a window that looks something like this.

Please note that table name between ours and your example might differ.

Now you can see against the Table/Range option it is showing us the table name which in our case is Table1.

Now we are going to choose the option Existing Worksheet and create our Pivot Table on the worksheet named “Analysis” on cell A2 and so select the option and then we can enter the location but the better and much smarter way is clicking the upward arrow and then navigating to sheet where you want to place the Pivot Table. Once you hit the arrow you will see a window like below.

Then simply switch to the sheet you want to place the Pivot Table and select the cell and the window then will show content like something in the below image, based on the sheet which in our case is named “Analysis” and we have selected cell A2.

Once you hit enter you will go back to the original Create PivotTable window and from there you hit okay and we have done it. You can see from below image that we have created a Pivot Table on an existing sheet named analysis and it starts from cell A2

Pivot Table with Fixed Range on a New Sheet

On the below screenshot you can see our sample data which contains sales reps across various Regions.

So, in order to create the Pivot Table, you have to select the data set including the headers and then go the Insert tab and from tables group select the option Pivot Table. Refer to below screenshot to understand more.

Now once you do that you will see the following window

From here we have to observe a couple of things as we can see it is showing our selected range which is ‘Sales Data’!$A$1:$G$44 against Table/Range: option and then at the bottom its showing where do you want to place the Pivot Table and now one thing to note is that Pivot Table is dynamic so you need to give it more space as it expands so let’s for now put it on a different sheet.

And once you click OK you will be transferred to a newly created sheet which will look something like below

Now you can observe from the image that we are indeed on a new sheet and you can see our Pivot Table begins from cell A3 and on the very left you can see an option called PivotTable Fields. We can go in-depth about it when we explore it further.

Pivot Table with a Table or an Existing Sheet.

Now let’s explore creating Pivot Tables with an Excel Table. To clear your doubts, we can also create Pivot Tables with Fixed Range on existing sheets and I choose this example to kill two birds with one stone.

So, what are the benefits of creating a Pivot Table with an Excel Table, well the biggest one is that we can increase/decrease the data in the Excel Table and the Pivot Table will update based on the inputs.

Let’s first convert our Range to an Excel Table in two simple steps.

  • Select the entire range of your Excel Data
  • Go to the Insert tab of the Excel Ribbon and then from Tables group select Table and you will be greeted with an option like in the below image.
  • Hit Ok as we have headers on our data and that’s it, your data is now converted to a table and you can see that Excel will make some format changes on its own which you can then change based on your requirements

Now we are going to create a Pivot Table on a sheet named Analysis which is a blank sheet we have inserted on our spreadsheet.

So again, like the last time select the entire data and repeat the process by going to Insert Ribbon and selecting the option PivotTable and you will be greeted with a window that looks something like this.

Please note that table name between ours and your example might differ.

Now you can see against the Table/Range option it is showing us the table name which in our case is Table1.

Now we are going to choose the option Existing Worksheet and create our Pivot Table on the worksheet named “Analysis” on cell A2 and so select the option and then we can enter the location but the better and much smarter way is clicking the upward arrow and then navigating to sheet where you want to place the Pivot Table. Once you hit the arrow you will see a window like below.

Then simply switch to the sheet you want to place the Pivot Table and select the cell and window will show content like something in the below image, based on the sheet which in our case is named “Analysis” and we have selected cell A2.

Once you hit enter you will go back to the original Create PivotTable window and from there you hit okay and we have done it. You can see from below image that we have created a Pivot Table on an existing sheet named analysis and it starts from cell A2

Understanding Pivot Table

Now as we discussed in our intro a Pivot Table is an amazing tool to summaries the data and we are going to see how a Pivot Table functions and what role everything plays in it from the eyes of a beginner.

Pivot Table Fields

PivotTable fields are essentially just columns of data we have in our range so as you can see in the image below it shows us the fields and they are just columns of data in our Table which you can see in the next image to that.

And that is why you have to keep your field names unique and must have header for each column when you create a Pivot Table.

Pivot Table Areas

There are 4 kind of areas when it comes to Pivot Tables and in each area, we add fields to make our Pivot Table and we can either simply select and drag the field or right click on the field and add it to any area.

The four areas determine what we see in the Pivot Table. Let’s see what each area does on its own.

Filters

As the name suggest a filter area adds any field to filter the data by so for instance you add any field from which you want to see data for only particular area and that is where filter comes in handy.

Columns

Again, as the name implies any field you add it to the column section will show up as a column on the Pivot Table.

Rows

As the name implies again, this is for adding any field to the Pivot Table on a row.

Values

Finally, values area is the area where most of the Pivot Table magic happens and you can use this field to calculate following.

  1. Sum
  2. Count
  3. Average
  4. Max
  5. Min
  6. Product
  7. Count Numbers
  8. StdDev
  9. StdDevp
  10. Var
  11. Varp

Applications from Pivot Table

Okay now let’s have a look at some examples of how we can extract summary of the data from the Pivot Table.

Example 1

Now based on our data lets find out the number of units of an item sold based on a particular region.

For doing that we are going to do following.

  • Drag or Add the region field to filter area.
  • Drag or Add the item field to the row area.
  • Drag or Add the units field to the values area.

Once we have done that, area on the Pivot Table will look below

And our Pivot Table will now look like following

As you see just by following simple steps, we can get the sales unit by items summed up by using few simple steps.

Now since we wanted to see a particular region, we can simply select the region for which we want to see the sale from the filter we created on our Pivot Table.

So, as we can see from image above our filter is in cell B1. Once you select the filter you will see following.

We can select any region from the options and we are going to opt for central and see the changes on the figures. Observe below the image for the change in figures when we do the same.

Also note that you can additionally sort and filter using the Pivot Table and for instance in the picture above you can sort and filter by clicking the option on the Row Labels column and sort and filter as you like.

Example 2

We are going to look at another example to understand it better and this time we are going to find out the average unit cost of an item based on the region Central.

So, we are going to do clear all ticks from our existing Pivot Table fields and that should bring us back to zero with a clear Pivot Table.

Now we will do following

  • Drag or Add Region field to filter area
  • Drag or Add Item field to Rows area
  • Drag or Add Unit Cost field to the Values area

Now our Pivot Table and fields should look like something below

You can see that the value field is assuming we want sum of unit cost but that is not useful to us as we want average so in order to change we are going to right click on the “Sum of Unit Cost” option and select the option Value Field Setting. After that you will see the the window like below and select the average option from the list and hit OK.

Now you should have average values showing on the Pivot Table like the image below but you also have grand total option which is useless in this context so lets remove that.

Click anywhere inside the Pivot Table and now you should see PivotTable Tools on Excel Ribbon and from there select the option Design. Have a look at the image below to understand it better.

The second option is the Layout group is Grand Totals from there select the option Off for Rows and Column and the Grand Total should disappear and once you select the region Central from the filter, we should have the desired results and we can format the cost to only show two decimal places to make it better and it should look like the figure below.

Pivot Charts

Pivot Charts are another amazing feature of the Pivot Table, they are interactive chart that work on the basis of the data and fields selected on the Pivot Table.

Let’s insert a simple chart using the Pivot Table we created in the Example 2

Creating a Pivot Chart

In order to that we have to first click anywhere inside the Pivot Table and then go the Insert option on the Excel Ribbon and from the charts group select the option Pivot Chart and once we do that, we will see the window like on the below image.

After that once we will select the Clustered Column chart and select Ok and we will see a chart based on the options we have selected will now be shown on the sheet like in the image below

The best part about this is that the chart will change based on the option we select on the Pivot Table so for instant let’s change the region to East from filters. You can see in the image below the data along with the chart has been updated to show now the values form the East Region.

Question/Answers

Question 1

In order to create the Pivot Table, one must have following information present in all columns of the data source.

  1. Numbers
  2. Text
  3. Column Headers
  4. Column Footers

The Answer is option C Column Headers, A Pivot Table cannot be created without any headers missing in the source data.

Question 2

Which of the following statement is correct about the Pivot Table?

  1. A Pivot Table cannot be used to create a chart on the sheet.
  2. A Pivot Table cannot be created on an existing sheet.
  3. Both
  4. Neither

The Answer is D as a Pivot Table can be used to create a Pivot Chart and a Pivot Table can be created on an Existing sheet.

Question 3

Which of these areas are available on a Pivot Table?

  1. Filters, Columns, Footer, Header
  2. Filters, Rows, Footer, Values
  3. Filters, Rows, Column, Values
  4. Neither

The right answer is column C.

Question 4

Using the Data given create a Region filter and find out the total units sold in the region west

The correct answer is

  1. 2121
  2. 691
  3. 231
  4. 241

The correct answer is 231. You can find the result based on the Pivot Table based on the image below.

Question 5

Using the data sheet given create a Pivot Table to find out the average cost of item binder accurate to 2 decimals in the west Region.

The correct Answer is

  1. 19.99
  2. 11.49
  3. 8.19
  4. 11.54

The correct answer is A. Refer to the Pivot Table in the image below to see the correct answer