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.

Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

Kapazität: 64 GB, Flash Card Typ: MicroSDXC, Flash-Memory-K...

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . 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.

Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

Kapazität: 64 GB, Flash Card Typ: MicroSDXC, Flash-Memory-K...

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . 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 (click to enlarge)

Prepared pivot table
Advertisement

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

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
Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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
Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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

Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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

Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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
Advertisement
#1
Fitbit Versa Health & Fitness Smartwatch
Fitbit Versa Health & Fitness Smartwatch*
by FITET|#Fitbit

FitBit VERSA. Leichte und wasserfeste Smartwatch, mit der Ge...

Price not available zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 13:32 . 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
Advertisement
#1
Fitbit Versa Health & Fitness Smartwatch
Fitbit Versa Health & Fitness Smartwatch*
by FITET|#Fitbit

FitBit VERSA. Leichte und wasserfeste Smartwatch, mit der Ge...

Price not available zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 13:32 . 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:

Advertisement
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . 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:

Advertisement
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-08-09T11:40:56+00:00By |Categories: Excel 2016, Office 2016|
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