Contains paid Promotion

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.

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.

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.

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
Santander BestCredit Ratenkredit 300x250
Anzeige

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
300x250
Anzeige

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
Santander BestCredit Ratenkredit 300x250
Anzeige

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
300x250
Anzeige

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
Anzeige

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
Anzeige
Anzeige

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
Anzeige

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.

kautionsfrei.de
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
Anzeige

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.

Anzeige

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
Anzeige

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
Anzeige

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.

300x250
Anzeige

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.

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.

300x250
Anzeige

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.

Become Guest Writer

We publish your guest contribution free of charge in German and English
incl. backlink to your website and identification of the author

Anzeige
Blogverzeichnis Bloggerei.de

Monthly Bestseller Charts:

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Anzeige

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Anzeige