Excel Pivot Tables 101

PIVOT TABLES EXPLAINED VERY SIMPLY!

Mastering pivot tables and using them safely is not that easy, especially with so many complicated instructions floating around on the net.

Or maybe not?

With the help of everyday examples, by the end of this article you will understand why pivot tables are so powerful and you will be a big step further along the way Excelhero to become.

Do not you think?

Then try to describe to me what a cash register in the supermarket does with the scanned products.

Correct!

It adds up the prices of the individual products and tells us the total amount to be paid for our purchase.

In this example, the cash register simply summarizes all individual values ​​as a sum. Pivot tables can do the same thing - and much more!

What are pivot tables and what can you use them for?

Another example:

Let's imagine we have one big mountain with lego bricks and I ask you:

• How many Lego bricks are there in total?
• How many of them are red, green, blue ...?
• How big are these on average?
• How big is the smallest stone?
• How big is the biggest?
• What part do the red bricks have in all Lego bricks?
• Or I add a handful of new Lego bricks in different colors and ask yourself: What percentage has the number of blue Lego bricks increased?

Answering these questions is relatively easy -however, we would probably be busy all dayand would certainly make one or two careless mistakes.

This is where pivot tables come into play.

Pivot tables are one of the most powerful tools in Excel.

Because pivot tables help us to summarize large amounts of data according to certain criteria.

The result of this is made available to us in a clear table.

In addition to simply adding up data, a large number of other calculations can be carried out. This way you can quickly gain insight into your data.

1. Create a pivot table

In order to create our first pivot table, we first need data that is sent to us in the form of alist or Data table are present.

For the example here we use a data list that lists our Lego bricks and includes the following columns:

Color / size / category / number

In the first step we mark the data that we want to summarize with a pivot table and then go to:

Insert -> PivotTable

The dialog now opens "Create PivotTable"

The area that we have already marked before is selected here. However, we also have the option of adjusting this area again using the button.

Next we are asked where we want to place the new pivot table - on a new or an existing worksheet.

We choose in our example "New worksheet" and press"OK".

So, the first step is done - we have created our first pivot table.

However, this is still empty, so continue to the next point.

2. Change the arrangement of a pivot table

As soon as the pivot table has been created, the area opens on the right-hand side"PivotTable Fields" (also called "field list"). Here we see a listing of all previously selected columns in our data list or data source and can now fill the individual areas of the pivot table with these columns. So we now determine the arrangement within the pivot table.

Next, we'll distribute them "PivotTable Fields", So the output columns of our data list or data source, in the individual areas of the pivot table:

We have four areas to choose from:

Filters, columns, rows and values.

We can place each of the PivotTable fields by either using theSelect checkboxes (then the placement is done by Excel) or by using Drag and dropdistribute the fields to the four areas and also define the hierarchy of the data manually.

Here I have them now Size in the columns pulled that Color in the lines, the Category in the filter and the Number in the range of values.

As a result, we get a pivot table that shows us how many Lego bricks per color and size are in our Legoberg.

We can change the arrangement of the pivot table as we wish and also drag several fields into one area or drag a field multiple times into the values ​​area.

Showing the various arrangement options here with dozens of screenshots makes little sense, so it's best to watch them in the video.

3. Perform simple calculations using pivot tables

By default, the values ​​are shown as a sum in a pivot table, but this can be changed.

The easiest way to do this is to use Right click in the value area click on one of the values ​​and then under "Summarize values ​​according to" choose a different setting.

We have the following options:

Summarize values ​​according to

• total
• Number (shows the number of summarized data records)
• Max (shows the largest value of the summarized data sets)
• Min (shows the smallest value of the combined data sets)
• Product (multiplies all summarized records and shows us the result)
• ...

But we can also use the pivot table Calculations get employed.

It is about the representation of how the individual values ​​relate to one another.

To do this, you have to useRight click in the value area click on one of the values ​​and then under "Show values ​​as"select a different setting.

We have the following options:

Show values ​​as

• % of the total
• % of the total column result
• % of the total row result
• % of...
• % of the previous line total
• % of the previous column total
• % of the previous total result ...
• Difference of ...
• % Difference from ...
• Result in ...
• % Result in ....
• Ranking by size (ascending) ...
• Ranking by size (descending) ...
• index
• ...

There are a lot of different setting options - but don't worry: Most of the time you only use 2–3 of the settings, but then use them again and again.

I have put together a small overview of the various setting options for you as a video.

So with Excel pivot tables you can Quickly and easily answer a large number of questions that we have about the data, without having to laboriously split up, group and then recombine our data using formulas.

4. Use conditional formatting to highlight certain values

In addition to summarizing and calculating the data with pivot tables, we also have the option of using Start -> Conditional Formatting to view the different To highlight values ​​in color or with Data bar or Traffic lights to provide.

We notice outliers more quickly and we have a better overview of larger pivot tables.

Such colored tables do well on dashboards and reports. They make it easier to draw conclusions from the data.

You can find a small preview of how this works in the video.

5. Create pivot charts or pivot graphics

We can go one step further and also use the data from a pivot table as a graphic in so-calledPivot charts convert and thus understand our data even better.

To do this, we click in the pivot table and can now click

Insert -> Charts

or

PivotTable Tools -> Analyze -> PivotCharts Create diagrams.

Then the pivot chart can be selected and over the area Pivotchart tools or can be formatted and edited accordingly by right-clicking.

You can find a small preview of how this works in the video.

6. Conclusion: Why pivot tables are the most powerful tool in Excel

Pivot tables are one of the most powerful tools in Excel because with them you can:

• compress large amounts of data into manageable tables,