Advertisement
Advertisement

Create Pivot Table and Pivot Chart in Excel

As long as you create simple spreadsheets with just a few spreadsheets in Excel, where you can organize the existing data with lightly set auto filters, you do not necessarily need the pivot function.

Create Pivot Table and Pivot Chart in Excel

Topic Overview

Anzeige

But as soon as the columns with the displayed values continue to grow, it quickly becomes confusing and you scroll from left to right, up and down and back again. At the latest then you will want to have a function that allows you to display only the most important data columns in a clearer way.

Luckily, Microsoft Excel offers the pivot function, which is rarely used because it looks a bit complex at first glance, but it is not.

I would like to explain in more detail how you can use Pivot to sort your data with simple steps.

Nord VPN testen
30 day money-back-guarantee
Advertisement

Create Pivot Table and Pivot Chart in Excel

As long as you create simple spreadsheets with just a few spreadsheets in Excel, where you can organize the existing data with lightly set auto filters, you do not necessarily need the pivot function.

Create Pivot Table and Pivot Chart in Excel

Topic Overview

Anzeige

But as soon as the columns with the displayed values continue to grow, it quickly becomes confusing and you scroll from left to right, up and down and back again. At the latest then you will want to have a function that allows you to display only the most important data columns in a clearer way.

Luckily, Microsoft Excel offers the pivot function, which is rarely used because it looks a bit complex at first glance, but it is not.

I would like to explain in more detail how you can use Pivot to sort your data with simple steps.

Nord VPN testen
30 day money-back-guarantee
Advertisement

1. Define data area in Excel

1. Define data area in Excel

In our example, we have created a table with fictitious product data (product name, product number, price, etc.) from which we want to pick specific values ​​using the pivot function in Excel to continue working with them.

  • First, go to the “Pivot Table” item on the “Insert” tab.
  • In the following window you will be prompted to define the data area which will serve as a source for our Pivot Table later.
  • Simply mark the entire table space of your created table (not the whole sheet!)
  • In the last step you have to decide if you want to insert your pivot table in the same or in a new worksheet.

We have made it to better represent all content in the example in the same worksheet, however, it is recommended depending on the extent of the pivot table to do this later on a separate sheet.

See picture: (click to enlarge)

Datenbereich für Pivot Table festlegen
Advertisement

The template for the pivot table should then be displayed on the worksheet as follows to begin the configuration.

See picture: (click to enlarge)

2. Vorbereitete Pivot Tabelle
Advertisement

In our example, we have created a table with fictitious product data (product name, product number, price, etc.) from which we want to pick specific values ​​using the pivot function in Excel to continue working with them.

  • First, go to the “Pivot Table” item on the “Insert” tab.
  • In the following window you will be prompted to define the data area which will serve as a source for our Pivot Table later.
  • Simply mark the entire table space of your created table (not the whole sheet!)
  • In the last step you have to decide if you want to insert your pivot table in the same or in a new worksheet.

We have made it to better represent all content in the example in the same worksheet, however, it is recommended depending on the extent of the pivot table to do this later on a separate sheet.

See picture: (click to enlarge)

Datenbereich für Pivot Table festlegen
Advertisement

The template for the pivot table should then be displayed on the worksheet as follows to begin the configuration.

See picture: (click to enlarge)

2. Vorbereitete Pivot Tabelle
Advertisement

2. Columns, Rows and Values in Pivot Tables

2. Columns, Rows and Values in Pivot Tables

In the first step, we’ve already laid the foundation for our pivot table, and can now begin inserting and configuring the desired values.

On the right side, the editing area is shown in which the column headings of our previously marked as data source table are displayed. You can simply drag and drop them into the fields (columns, rows, values and filters) displayed in the lower part of the editing area, and then piece by piece the most important data via the interface to the original table Drag into your pivot table.

Note:
You can customize the headings in the Pivot Table at any time later. So there does not have to be “Sum from … etc” stand.

See picture: (click to enlarge)

Spalten, Zeilen und Werte in Pivot einfügen
Advertisement

In the first step, we’ve already laid the foundation for our pivot table, and can now begin inserting and configuring the desired values.

On the right side, the editing area is shown in which the column headings of our previously marked as data source table are displayed. You can simply drag and drop them into the fields (columns, rows, values and filters) displayed in the lower part of the editing area, and then piece by piece the most important data via the interface to the original table Drag into your pivot table.

Note:
You can customize the headings in the Pivot Table at any time later. So there does not have to be “Sum from … etc” stand.

See picture: (click to enlarge)

Spalten, Zeilen und Werte in Pivot einfügen
Advertisement

3. Adjust value field settings in the pivot table

3. Adjust value field settings in the pivot table

Of course, the pivot function in Excel offers much more than just being able to create a new one from an existing spreadsheet.

For one thing, you are not limited to inserting only one value into the fields (columns, rows, values, filters), but you also have the option of implementing several equal column values and retrieving different values from them, and on the other You can also customize the individual field settings very individually.

  • To do this, first click once in your Pivot table to make the editing area on the right-hand side appear.
  • Then go to the lower area in which you have set column values to any value, and there you can use the drop-down menu the options with the item “value field settings”

See picture: (click to enlarge)

3.1 Wertfeldeinstellungen
Advertisement

In the following pop-up window, you can now adjust the displayed values of the corresponding field by, for example, displaying the mean value, the number or the total.

Or if, for example, you have inserted 2 equal column values in the Pivot edit area, then the sum and the percentile percentage can be at the same time to have both the absolute value and the relative value at a glance.

See picture: (click to enlarge)

3.2 Wertfeldeinstellungen Pivot
3.3 Wertfeldeinstellungen Pivot

As you can see, here you have a variety of options from a previously large data salad to filter one data individually, and on the other to significantly increase the functionality of the original table.

Of course you can not achieve this with the simple autofilter function in this form, which is why wherever a large amount of data comes together a pivot table is easy to offer as it is also quite quick and easy to create.

Landingpage
Anzeige

Of course, the pivot function in Excel offers much more than just being able to create a new one from an existing spreadsheet.

For one thing, you are not limited to inserting only one value into the fields (columns, rows, values, filters), but you also have the option of implementing several equal column values and retrieving different values from them, and on the other You can also customize the individual field settings very individually.

  • To do this, first click once in your Pivot table to make the editing area on the right-hand side appear.
  • Then go to the lower area in which you have set column values to any value, and there you can use the drop-down menu the options with the item “value field settings”

See picture:

3.1 Wertfeldeinstellungen
Advertisement

In the following pop-up window, you can now adjust the displayed values of the corresponding field by, for example, displaying the mean value, the number or the total.

Or if, for example, you have inserted 2 equal column values in the Pivot edit area, then the sum and the percentile percentage can be at the same time to have both the absolute value and the relative value at a glance.

See picture:

3.2 Wertfeldeinstellungen Pivot
3.3 Wertfeldeinstellungen Pivot

As you can see, here you have a variety of options from a previously large data salad to filter one data individually, and on the other to significantly increase the functionality of the original table.

Of course you can not achieve this with the simple autofilter function in this form, which is why wherever a large amount of data comes together a pivot table is easy to offer as it is also quite quick and easy to create.

4. Filter function in Pivot Table

4. Filter function in Pivot Table

To make your Pivot Table even more comfortable, you can use the “Filter” field in the Pivot edit area and drag one or more parameters from the column values.

These do not necessarily have to be included as values in the “Columns, Rows, Values” fields, since the filter function as well as all other data always work with dynamic reference to the original table.

See picture: (click to enlarge)

 4.-Filtern-in-Pivot-Tabellen
Advertisement

To make your Pivot Table even more comfortable, you can use the “Filter” field in the Pivot edit area and drag one or more parameters from the column values.

These do not necessarily have to be included as values in the “Columns, Rows, Values” fields, since the filter function as well as all other data always work with dynamic reference to the original table.

See picture:

 4.-Filtern-in-Pivot-Tabellen
Advertisement

5. Create a Pivot Chart

5. Create a Pivot Chart

The term “Pivot Chart” has certainly come before you, and also sounds very important and extremely complicated.

But I can reassure you because the opposite is the case.

After all, after you’ve created your pivot table (or more) from a data source, the pivot chart is nothing more than a graphical representation of the data displayed in the pivot as a chart.

At the latest with the Excel version 2016, this has become much easier than with the previous versions.

  • Simply click 1x into your created Pivot table
  • Then in the tab “Paste” on “Pivot Chart”
  • And here you already get suggestions for suitable graphics from which you can choose

Of course, you can also customize these charts (Pivot Chart) according to your wishes, and configure.

Advertisement

The practical thing about a Pivot Chart is that as soon as you make changes to the pivot table or use the appropriate filter functions, the chart adapts dynamically, and you do not have to create quite a few charts.

This keeps everything clearly arranged on a spreadsheet, and can be easily adjusted.

Blogverzeichnis Bloggerei.de

The term “Pivot Chart” has certainly come before you, and also sounds very important and extremely complicated.

But I can reassure you because the opposite is the case.

After all, after you’ve created your pivot table (or more) from a data source, the pivot chart is nothing more than a graphical representation of the data displayed in the pivot as a chart.

At the latest with the Excel version 2016, this has become much easier than with the previous versions.

  • Simply click 1x into your created Pivot table
  • Then in the tab “Paste” on “Pivot Chart”
  • And here you already get suggestions for suitable graphics from which you can choose

Of course, you can also customize these charts (Pivot Chart) according to your wishes, and configure.

Advertisement

The practical thing about a Pivot Chart is that as soon as you make changes to the pivot table or use the appropriate filter functions, the chart adapts dynamically, and you do not have to create quite a few charts.

This keeps everything clearly arranged on a spreadsheet, and can be easily adjusted.

Blogverzeichnis Bloggerei.de

Search the net for other topics:

About the author:

Michael W. Suhr
Michael W. SuhrWebdesigner / MBA
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:





You might also be interested in:

Search the net for other topics:

About the author:

Michael W. Suhr
Michael W. SuhrWebdesigner / MBA
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:





You might also be interested in:

Popular Posts:

1509, 2021

Microsoft Excel: Create vacation planner 2022 with public holidays and weekends

September 15th, 2021|Categories: Uncategorized, Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

We explain how you can create your own vacation planner 2022 in Microsoft Excel. And of course with a display of public holidays and weekends.

1409, 2021

Create annual calendar 2022 in Microsoft Excel

September 14th, 2021|Categories: Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

In our tutorial we describe how you can create an annual calendar for 2022 with a display of the calendar week and public holidays in Excel, and use it anew every year.

Word & Excel Templates:

See more

Word CV Templates:

See more

Monthly Bestseller:

Smart-Home

Bestseller Kategorie Smart-Home
Angebote ansehen

Homeoffice

Bestseller Kategorie Homeoffice
Angebote ansehen

Smartphone & Zubehör

Bestseller Kategorie Smartphone und Zubehoer
Angebote ansehen

Popular Posts:

1509, 2021

Microsoft Excel: Create vacation planner 2022 with public holidays and weekends

September 15th, 2021|Categories: Uncategorized, Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

We explain how you can create your own vacation planner 2022 in Microsoft Excel. And of course with a display of public holidays and weekends.

1409, 2021

Create annual calendar 2022 in Microsoft Excel

September 14th, 2021|Categories: Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

In our tutorial we describe how you can create an annual calendar for 2022 with a display of the calendar week and public holidays in Excel, and use it anew every year.

Word & Excel Templates:

See more

Word CV Templates:

See more

Monthly Bestseller:

Smart-Home

Bestseller Kategorie Smart-Home
Angebote ansehen

Homeoffice

Bestseller Kategorie Homeoffice
Angebote ansehen

Smartphone & Zubehör

Bestseller Kategorie Smartphone und Zubehoer
Angebote ansehen
2021-09-12T09:16:22+02:00By |Categories: Microsoft Excel, Microsoft Office, Office 365|Tags: , , |

Title

Most read:

Search by category:

Go to Top