How To Set Up A Pivot Table Excel

Contextures

Create a Pivot Tabular array in Excel

Follow these like shooting fish in a barrel steps to create an Excel pivot table, so you lot can speedily summarize Excel data. Spotter the short video to see the steps, or follow the written steps. Become the free workbook, to follow along. There'due south besides an interactive pivot table beneath, that you can try, before you build your ain!

NOTE: This tutorial gives you lot a quick overview of creating a pivot table, with a curt video, and basic steps. For detailed tutorial, go to the How to Program and Ready a Pivot Table folio.

Create a Pivot Table

Watch this video, to see the steps for creating a pivot tabular array in Excel 2022 and later. At that place are written instructions below the video, and the full Video Transcript is at the end of the page.

Preparing Your Pivot Tabular array Data

Earlier y'all create a pivot table, organize your information into rows and columns, and create an Excel Table. There are instructions here.

In this example the source data contains data about food sales, in 2 regions -- East and West.

pivot table data

Creating a Pivot Tabular array

Subsequently your source information is prepared, you can create a pin table.

Beginning, follow these steps, to meet which pivot tabular array layouts are suggested by Excel.

Select any cell in the source data table.
On the Ribbon, click the Insert tab.
In the Tables group, click Recommended PivotTables.
In the Recommended PivotTables window, scroll down the list, to see the suggested layouts.
Then, click on a layout, to see a larger view.
Click on the layout that you want to use, then click OK.

Cheque the Pivot Table

As soon as you click the OK button, a pivot table is created in your workbook.

The pivot table is located on a new sheet
The pivot table has the layout that you selected from the Recommended PivotTables dialog box.
When you select a jail cell inside the pivot table, a PivotTable Field List appears, at the correct of the worksheet.

In the PivotTable Fields pane, you lot can see that:

Category is in the Rows expanse
Quantity is in the Values expanse, as Count of Quantity.
At that place are no fields in the Filters area or in the Columns expanse

pivot table and field list

Modifying the Pivot Table

You lot can change the layout of the pivot table, afterward it'southward been created.

In this example, we'll add the TotalPrice field to the pin table.

In the PivotTable Field List, add a cheque mark to the TotalPrice field.
The TotalPrice field is automatically added to the pivot tabular array, in the Values area, every bit Sum of TotalPrice.

Excel added the TotalPrice field to the Values surface area considering it is a number field.

add fields from field list

Alter a Value Field Function

When you added the TotalPrice field, Excel automatically added it to the Values area, and used the Sum office to summarize the prices.

Excel did that because:

TotalPrice is a number field
Every row in the source information, for the TotalPrice field, contains a real number.

However, when the pivot tabular array was created, the Quantity field was added to the Values area, considering it's a number field likewise. But the Quantity field shows a count, instead of a sum.

Why did Quantity use the Count office, instead of Sum?

Although most of the values are numbers, there is at least ane text value in the Quantity cavalcade, as you tin come across in row 12, in the screen shot below.
If any values in a field are not-numeric, Excel uses the Count role for the Value field, instead of the Sum part.

pivot table data

Try This Pivot Table

Earlier you lot build your own pivot table in an Excel workbook, you can run across how a pivot table works, past trying the interactive Microsoft Excel example shown beneath.

Below the interactive pivot table, there are a few things yous can effort, to meet different results in the pivot.

Note: You tin can download a copy of the file, to test the pivot table features on your ain computer.

Change the Filters

Above the information in the pivot tabular array, there are filter fields - State, Business organisation Type (BusType), and Alluvion.

Make the following changes to the pin table filter fields, to change what the pivot table is showing:

In cell B1, click the driblet down arrow, to run into a drib-down listing of Country codes
- Uncheck 1 or more of the state code, to remove that information from the pin tabular array brandish
- At the bottom of the drop downward list, click the OK push
- The value in cell B1 changes from (All) to (Multiple Items)
- The pivot table shows the total amounts for the selected states but, instead of all us
In cell B3, click the drop down arrow, to encounter a drop-downwardly list with Y and North, for Alluvion
- Select Y (yes), then click the OK button
- The value in prison cell B3 changes from (All) to Y
- The pivot table shows the total amounts for simply the insurance policies that have inundation insurance coverage

Sort the Pivot Table Values

Currently, the pivot table is sorted alphabetically past the values in the Rows area -- the Construction type, and the Region.

To see the data in a dissimilar mode, you lot tin can follow these steps to sort by the policy count

In column A, select 1 of the cells with a region name, such as cell A8 (E)
In cell A6, with the Row Labels header, click the drop down pointer
Point to Region, and then click Sort by Value
In the Sort by Value (Region) dialog box, click the pointer at the right side of the "Select Value" box
Click on Count of Policy
For Sort Options, click the radio button for Largest to Smallest
Click the OK push

Later on you click OK, in the pin table, under each construction type, the regions are listed from largest to smallest, based on the policy count field.

pivot table regions sorted by count of policy

Get the Sample File

Click here to download the zipped sample file with the Region Sales data. The zipped file is in xlsx format, and does not comprise macros.

Video Transcript: Create a Pivot Tabular array

Hither is the full transcript for the Create a Pivot Table video.

A pin tabular array is a great way to summarize data in Excel. Hither we take a table with sales records.

We tin can see where things were sold, what we sold, and how much and how much money we got on each sale. We can scroll down and see row after row of information.

Now I could create formulas to summarize things, just with a pivot table it will just be a few clicks to go totals. In Excel 2022 information technology's easier than ever to create a pivot table considering in that location's a new tool to help you with some suggested arrangements for your information.

I'm going to click whatever cell in this table.
On the Insert tab, at the left, there'due south a Tables grouping and here's the new tool, which is Recommended Pivot Tables. I'll click that.

It opens upwardly a new window, and based on the data that I've got here, it's suggesting some layouts

So the first ane is showing the cities and and then taking the total price and giving me the full sales in each city. That might exist useful if I'm interested in where things are selling.
If we want to know what's selling, hither'southward a layout that has the unlike product categories and then for each region how much we sold.
Below that, nosotros can run across those product categories once again, and just the total price, instead of broken out by region.

Yous tin scroll down, there are lots more options. If I get down further, we're looking at the production categories and then the production names beneath that. This time it'southward the quantity, rather than the cost.

I'll select that one. I'll click OK, and it puts a new sheet in the workbook.

Hither's the layout that I selected. At present information technology's got the quantity. I'g besides going to add the total toll. Over hither, I can see all the fields from that data sheet.

I'll click on total price. That puts another cavalcade here where the values are.

This one went in as Sum of Total Cost. This one is Count of Quantity. If I go back and look at my information, I can see that going down the quantity column, there's i item here that's text rather than a number.

So Excel sees this not as a number cavalcade, simply equally either text or mixed numbers and text. And then it defaults to Count. Only we tin can change that.

I'll only right click on one of those numbers, Summarize Values By, and instead of Count, I'll click on Sum.

And then now we can see the total quantity and the total price for each product category and product.

Information technology'south a quick way to get started. You can alter the layout after you've selected one of the sample layouts, but yous're up and running very quickly.