# 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 day**and 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**.

With pivots we can answer the questions asked above **answer in a few SECONDS**!

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 a**list** 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 the**Select checkboxes** (then the placement is done by Excel) or by using **Drag and drop**distribute 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 use**Right 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-called**Pivot 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,****Quickly answer questions about the data and thus****Gain insights and knowledge,****Visualize data and convert it into graphics and dashboards, and of course****easily impress your colleagues and your boss 🙂**

And all of this with just a few clicks, without wasting a lot of time.

With Excel pivot tables, you can quickly get a grip on large amounts of data and be** many times more productive than the ordinary Excel user**who does not know or does not use this important tool.

- Where can I get free legal advice
- Can Venezuela indict its president
- How will we live in 2050
- Who are famous people from Mobile Alabama
- Is tea tree oil good for acne
- What are the best hip flexion exercises
- Are gift cards recyclable
- Is Utkal University good for pharmacy?
- Are feet at the root of wellness health
- What is xanthan gum
- How much is Odell Beckham's contract
- How do power generation companies make money
- How good is the Kaveri engine
- How does a Winchester 1200 model shoot
- Why should you have your dog groomed?
- What is the future of instant messaging
- Do you feel better without Facebook?
- Does Indiegogo work for Nigerians
- Is the subconscious critical
- How do you advertise on iAds
- What is a multisystem television
- What do you think of working women
- Can a person really forgive
- Are all laptops approved for gaming?