+5 votes
75 views
in Office by (242k points)
reopened
Create an Pivot Table with Excel - this is how it works

1 Answer

+3 votes
by (1.6m points)
 
Best answer

Advantages of a pivot table
Create pivot table in Excel
1st step:
2nd step:
3rd step:
4th step:
5th step:
6th step:
7th step:
8th step:
9th step:
10th step:
quick start Guide

Would you like to use a pivot table in Excel? We'll show you what it is and how you can easily create it in Excel..

image image

A pivot table is a special type of Excel table. Existing data can be evaluated here without changing the original table. This results in a summarized form that only contains the most important information.

Advantages of a pivot table

Above all, a pivot table facilitates clarity, as only the required data is displayed in a compressed form. The original table could have several thousand rows, but most of it contains unimportant information. Your pivot table, on the other hand, only has a handful of rows and columns, but only contains the values ​​that you actually need.

You can read even more about pivot tables in Excel here..

Create pivot table in Excel

In the following, we will explain the individual steps for creating a pivot table using an example: In a company, the sales of various employees are to be evaluated. There are also Mr. A, B, D and F, as well as Ms. C and E. They served customers 1-7. The table shows the seller, sales and the respective branch.

The goal is to create a pivot table . It should be listed which seller made how much turnover. As a detail, it can be included how much sales were made by which customer. In this example, the specified branch is no longer required as information . It is no longer displayed in the pivot table..

Follow our step-by-step instructions or take a look at the brief instructions .

1st step:

image
First you need to enter your values ​​in Excel . Give each column in your table a meaningful heading .

2nd step:

image
Mark then your table. Open the " Start " tab . Click Format As Table . Now you need to choose a design for your table. For clarity, we recommend a simple design in black or gray.

3rd step:

image
A new window is opening up. When you have marked your table (as described in the previous step), all you have to do is click " OK ". If you have already defined headings for the columns (as described in step 1), keep the checkmark next to " Table has headings ". This is extremely useful for creating the pivot table later.

4th step:

image
Click a field outside of your table. Then select the tab " Insert " the " PivotTable off".

5th step:

image
In the new window, enter your formatted table under " Table / Area: ". You have several options for this: You can mark the existing table with the mouse or enter the row or column information yourself. Alternatively, you can use the values ​​" formatted as a table " by just entering " Table1 ". You have already formatted the values ​​as a separate table. If you have already formatted values ​​other than a table in the Excel file, you must adapt the number accordingly So if your values ​​are in the second formatted table, you must enter " table2 ". Then click OK .

6th step:

image
You have now created an empty pivot table . Now you need to fill it with data.

7th step:

image
On the right side you can choose from different values. Check the box next to your base value . This is the value on which the other values ​​depend . In the example, the base value is " seller ", because the sales generated by the sellers are to be determined. The heading " Salesperson " and the associated data have now been automatically sorted into the " Lines " section .

8th step:

image
Now you have to put a tick next to the value, which depends on the base value. In this example, this is " Sales ". You can see that the heading and its content have been sorted under " Values ".

9th step:

image
If you want, you can now set a third dependency: In the example scenario, the pivot table should also show the sales generated per salesperson by a specific customer. Therefore you have to click on the heading " Customer " and drag it into the " Columns " field. Depending on the scenario, you now have to adapt the column names of the pivot table to your requirements.

10th step:

image
Finally, you can apply a filter . For example, a base value can be hidden . For example, if you want to display all salespeople except for Ms. E, right-click on Ms. E's cell . Select the " Filter " option in the menu . There click on " Hide selected elements ". In this way you can hide any row in the pivot table.

quick start Guide

  1. Enter your values ​​in an Excel spreadsheet . Make sure that each column has a meaningful heading .
  2. Now mark your table. Then click on " Format as table " in the " Start " tab and select a design .
  3. If you already marked your table in the previous step, all you have to do is click OK . If you have already defined headings for the columns, leave the checkmark next to " Table has headings ".
  4. Now select a field outside of your table . Then click on the " Insert " tab and then on " PivotTable ".
  5. Under " Table / range: " " Table 1 " or select your table. Then click OK .
  6. Put on the right side of a hook your base value to the empty to fill pivot table.
  7. Then check the value that depends on the base value . This value is now automatically set under " Values ".
  8. You can define a third dependency . To do this, the heading must be inserted in the " Columns " field .
  9. Now you can set a filter , for example to hide the base value. Right click on the parameter to be hidden. Then click on " Filters " and then on " Hide Selected Items ".

...