Der Blog für digitale Kompetenz

Der Blog für digitale Kompetenz

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.

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.

Create Pivot Table and Pivot Chart in Excel

Topic Overview

Anzeige

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.

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.

Create Pivot Table and Pivot Chart in Excel

Topic Overview

Anzeige

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
cshow
Ads

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
cshow
Ads

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
cshow
Ads

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
cshow
Ads

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

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.

Kauf auch du bei der Nummer 1!
Anzeige
cshow
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

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

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

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.

cshow
Ads

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.

cshow
Ads

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 for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
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:

Search for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
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:

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones
Ads

Popular Posts:

Search by category:

Autumn Specials:

Anzeige
Go to Top