Contains paid promotion

Advertisement

With ease pivot tables
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.

offers Microsoft Excel the pivot function for this, 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
SanDisk Ultra 128GB microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1, FFP
Unverb. Preisempf.: € 52,99 Du sparst: € 23,00 (43%) Prime Preis: € 29,99 zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 16. October 2018 um 9:12 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

With ease pivot tables 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.

offers Microsoft Excel the pivot function for this, 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
SanDisk Ultra 128GB microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1, FFP
Unverb. Preisempf.: € 52,99 Du sparst: € 23,00 (43%) Prime Preis: € 29,99 zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 16. October 2018 um 9:12 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

1. Define data area in Excel

1. Define data area in Excel

In our example, we have created a table with fictional 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 on.
  • 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 scope of the pivot table to do this later on a separate sheet.

See picture: (click to enlarge)

Mark for Pivot Table Data Area
Advertisement

The template for the Pivot Table should then appear on the worksheet as follows to begin the configuration.

See picture:

Prepared pivot table
Schluesselnotdienst Aktion - kautionsfrei.de
Anzeige

In our example, we have created a table with fictional 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 on.
  • 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 scope of the pivot table to do this later on a separate sheet.

See picture:

Mark for Pivot Table Data Area
Advertisement

The template for the Pivot Table should then appear on the worksheet as follows to begin the configuration.

See picture (click to enlarge)

Prepared pivot table
Schluesselnotdienst Aktion - kautionsfrei.de
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.

PS:
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)

Columns line and values-per-drag-and-drop
300x250_Students
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.

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

Columns line and values-per-drag-and-drop
300x250_Students
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.

On the one hand, 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)

Value Field Settings
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 at the same time, the percentage may also be at the same time to have both the absolute and the relative value at a glance.

See picture: (click to enlarge)

3.2 Wertfeldeinstellungen Pivot
Value field settings 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 because it is also quite quick and easy to create.

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

On the one hand, 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:

Value Field Settings
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 at the same time, the percentage may also be at the same time to have both the absolute and the relative value at a glance.

See picture:

3.2 Wertfeldeinstellungen Pivot
Value field settings 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 because it is also quite quick and easy to create.

4x CHIP TESTSIEGER Handyversicherung_600x500
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 Editor 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)

Filter in pivot tables
Anzeige
Samsung T835 Galaxy Tab S4 LTE Tablet-PC, 4GB RAM schwarz
Samsung T835 Galaxy Tab S4 LTE Tablet-PC, 4GB RAM ...*
von Samsung

SAMSUNG T835 Tab S4 26,72cm 10,5 Zoll 4+64GB LTE black

Unverb. Preisempf.: € 759,00 Du sparst: € 144,21 (19%) Prime Preis: € 614,79 zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 16. October 2018 um 9:14 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

To make your Pivot Table even more comfortable, you can use the “Filter” field in the Pivot Editor 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:

Filter in pivot tables
Anzeige
Samsung T835 Galaxy Tab S4 LTE Tablet-PC, 4GB RAM schwarz
Samsung T835 Galaxy Tab S4 LTE Tablet-PC, 4GB RAM ...*
von Samsung

SAMSUNG T835 Tab S4 26,72cm 10,5 Zoll 4+64GB LTE black

Unverb. Preisempf.: € 759,00 Du sparst: € 144,21 (19%) Prime Preis: € 614,79 zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 16. October 2018 um 9:14 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

5. Create a Pivot Chart

5. Create a Pivot Chart

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

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

After all, after you create 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 once in 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.

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 leaves everything clearly arranged on a spreadsheet, and can be easily adapted.

Print Friendly, PDF & Email

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

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

After all, after you create 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 once in 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.

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 leaves everything clearly arranged on a spreadsheet, and can be easily adapted.

Print Friendly, PDF & Email
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

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
Die Amazon.de VISA Karte
 Preis nicht verfügbar zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 17. October 2018 um 2:33 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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
Die Amazon.de VISA Karte
 Preis nicht verfügbar zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 17. October 2018 um 2:33 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-09-28T15:51:16+00:00By |Categories: Excel 2016, Office 2016|Tags: , |
In order to optimize our website for you and to be able to continuously improve it, we use cookies. By continuing to use the website, you agree to the use of cookies. Ok