Excel create a pivot table:Creating a basic pivot table from Excel data

Excel create a pivot: YOUR OBJECTIVE

You have an Excel spreadsheet that contains many records, in this case a lengthy list of all customer orders of past months. Now you want to analyze this table from a different perspective. For example, you would like to find out the total value of each customer’s orders as well as the total value of all orders. In addition, you want to break down the sales numbers from your Northern, Midwest and Eastern sales regions.

This learning module “Excel create a pivot”will show you how to analyze your data using a PivotTable.

Excel create a pivot table: HOW DOES IT WORK?

Before you start creating a pivot table, it is advisable to first consider what type of information you want to obtain. Having done that, you can proceed to create the pivot table.

Excel create a pivot table:First click on any cell within the table for which you want to create the pivot table. (In the current example, click any cell in your customers orders table.) Then go to the Insert tab. There, in the Tables group, click the PivotTable button.

The Create PivotTable dialog box will open. Here you can make the following settings:

  1. The Select a table or range option is already filled in for you. It refers to the table or range of cells comprising the table where the cursor was before you clicked the button. It specifies the table or cell range on which the pivot table will be based. If the information shown is incorrect, you can enter or modify it in the Table/Range input box accordingly.
  2. The New Worksheet option is also preselected. To create the pivot table on a new worksheet, leave this setting as it is.
  3. Select the Existing Worksheet option to place the pivot table on an existing worksheet. In the Location field, you must then specify the cell address (e.g., H17) that will be used as the upper-left cell of the pivot table.

Assuming you’ve chosen the New Worksheet option, when you click . An additional worksheet with the following two areas will appear in your Excel file:

The area where the PivotTable will be created, and

The PivotTable Field List task pane

Excel create a pivot table: CAUTION

Should the PivotTable Field List task pane not appear, you can make it appear by clicking in the Show group, on the Options tab of the PivotTable Tools.

When you create a pivot table, the PivotTable Tools context tab will appear with buttons for modifying your pivot table.

Excel create a pivot table: CAUTION

If the PivotTable Tools context tab does not appear, it is because your cursor is outside the area reserved for the pivot table. To activate the PivotTable Tools, click anywhere in the area reserved for the PivotTable.

Excel create a pivot table:Defining the pivot table layout

Using the PivotTable Field List task pane, you can now define the layout and associated computations/analysis for the pivot table. For this, all column headers from the Excel table you are analyzing (in this example, the customer orders table) are available to you in the upper section of the PivotTable Field List.

In the lower section of the PivotTable Field List section, there are four fields with the following functions:

  1. Report Filter: The Report Filter area enbles you to set filters that refine (reduce) the amount of data included in your pivot table. Select values from your source file for the filter that specify, for example, a category or a group that occurs in your data.
  2. Column Labels: Specify here the data from your source file that will provide column headings for your new pivot table.
  3. Row Labels: Specify here the data from your source file that will serve as row headings in your new pivot table.
  4. Values: Specify here the values to be calculated later with an arithmetic operation, e.g., the SUM function.

Excel create a pivot table:To move desired fields into the pivot table, first click the field name, e.g., Customer, and then, while holding the left mouse button depressed, drag it into the field area of your choice. When you release the mouse button, the field remains in the field area into which you dragged it. As soon as you drop the field into one of the four areas, the PivotTable area will automatically change to reflect that.

Continue moving other fields in the same fashion. Drag a field name into a field area in which you want to use it. Each field name that has already been used in one of the field areas will appear in in the field list, formatted in bold letters, with a checkmark next to its name. It can happen that you do not use all the possible fields in the pivot table. Those that remain unused are not formatted bold and their checkbox remains empty.

Excel create a pivot table:In the following example, you can see that the customer names (Customer field) will define the pivot-table rows while the sales areas (Sales Area field) define the columns. Where they intersect in the pivot table, the numeric value from the corresponding ORDER VALUE will appear. Since this is a pivot table, should there be multiple orders from a particular customer, the orders for that customer will be added (summed) together (SUM of ORDER VALUE). Below, you see the pivot-table layout and the PivotTable that results from it:

A report filter that lets you to filter by Customer Group (A, B or C)
(outlined in yellow on the following PivotTable)

The Order value per Sales Area
(outlined in green)

Order value per Customer
(outlined in red)

The total value of all orders
(blue area on the following PivotTable)

Excel create a pivot table:HINT

Even after creating the pivot table, you can always change its formatting: You can change the column widths, for instance, format the values as dollar amounts, use different colors and much more. But, if you like, you can also change the pivot table structure.

Excel create a pivot table:HINT

Using the  button on the PivotTable Field List, you can change task pane view. There are five different ways to view the task pane’s elements. The task pane layout changes in accord with the view you select:

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook


Kein Banner zum Anzeigen