How to Create Pivot Table in Excel

Pivot Tables are interactive tables that allow users to group and summarize large amounts of data into a concise table format that is easy to report and analyze. Pivot Tables can sort, count, add data, and are built into many spreadsheet programs. Excel allows you to quickly create pivot tables by dragging and dropping relevant information into the corresponding boxes. You can filter and sort data to find patterns and trends.

Table of Contents

Build Pivot Table

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/b2/Create-Pivot-Tables-in-Excel-Step-1-Version-2.jpg/v4-728px-Create- Pivot-Tables-in-Excel-Step-1-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/b2/Create-Pivot-Tables-in- Excel-Step-1-Version-2.jpg/v4-728px-Create-Pivot-Tables-in-Excel-Step-1-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,” bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Load the spreadsheet you want to create a Pivot Table for. Pivot Table allows you to create a visual report of the data in the spreadsheet. You can perform calculations without entering any formulas or copying cells. You need a spreadsheet containing several items to create a Pivot Table.

You can both create a Pivot Table in Excel from an external data source, such as Access, and insert a Pivot Table into a new Excel spreadsheet.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/71/Create-Pivot-Tables-in-Excel-Step-2-Version-2.jpg/v4-728px-Create- Pivot-Tables-in-Excel-Step-2-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/7/71/Create-Pivot-Tables-in- Excel-Step-2-Version-2.jpg/v4-728px-Create-Pivot-Tables-in-Excel-Step-2-Version-2.jpg”,”smallWidth”:460,”smallHeight”:344,” bigWidth”:728,”bigHeight”:544,”licensing”:”<div class=”mw-parser-output”></div>”}

Make sure your data meets the needs of your pivot table. Pivot tables are not always what you need. To take advantage of the pivot table’s features, your spreadsheet needs to meet a few basic criteria: ^{[1] X Research Source}

The worksheet must have at least one column containing duplicate values. That is, at least one column repeats the data. In the example given in the next section, the “Product Type” column has two entries: “Table” or “Chair”.
The table must contain data. These are the data to be compared and calculated in the table. For example in the next section, the column “sales” is the column with the data

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/71/Create-Pivot-Tables-in-Excel-Step-3-Version-2.jpg/v4-728px-Create- Pivot-Tables-in-Excel-Step-3-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/7/71/Create-Pivot-Tables-in- Excel-Step-3-Version-2.jpg/v4-728px-Create-Pivot-Tables-in-Excel-Step-3-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,” bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Start the Pivot Table wizard (the wizard). Click the “Insert” tab at the top of the Excel window. Select “PivotTable” on the left side of the Insert tab.

If you are using Excel 2003 or earlier, click the Data menu and select PivotTable and PivotChart Report… (PivotTable and PivotTable Report…)

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/4d/Create-Pivot-Tables-in-Excel-Step-4-Version-2.jpg/v4-728px-Create- Pivot-Tables-in-Excel-Step-4-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/4d/Create-Pivot-Tables-in- Excel-Step-4-Version-2.jpg/v4-728px-Create-Pivot-Tables-in-Excel-Step-4-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,” bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select the data you want to use. By default, Excel selects all data from the worksheet. You can click and drag to select individual parts of the worksheet, or enter cell ranges yourself.

If using an external data source, click “Use an external data source” Choose Connection… (Select Connection). Access a database connection stored on the computer.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/b8/Create-Pivot-Tables-in-Excel-Step-5-Version-2.jpg/v4-728px-Create- Pivot-Tables-in-Excel-Step-5-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/b8/Create-Pivot-Tables-in- Excel-Step-5-Version-2.jpg/v4-728px-Create-Pivot-Tables-in-Excel-Step-5-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,” bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Choose a location for the Pivot Table. After selecting the range, select the location in the same window. By default, Excel tabulates in a new work window, allowing you to switch back and forth by clicking the tabs at the bottom of the window. You can set up a Pivot Table in the same table as the data, you can choose the cell location. ^{[2] X Research Source}

When satisfied with the selection, select OK . The Pivot Table will be created and the interface will change.

Pivot Table Configuration

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/80/Create-Pivot-Tables-in-Excel-Step-6-Version-2.jpg/v4-728px-Create- Pivot-Tables-in-Excel-Step-6-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/80/Create-Pivot-Tables-in- Excel-Step-6-Version-2.jpg/v4-728px-Create-Pivot-Tables-in-Excel-Step-6-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,” bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Add row fields. Creating a Pivot Table is basically sorting data by rows and columns. What do you need to add or define the structure of the table. Drag a field from the Field List on the right side to the Row Fields of the Pivot Table to populate it.

For example, your company sells two products: tables and chairs. You have a spreadsheet with the quantity (Sales) of each product (Product Type) sold in 2 stores (Store). You want to see how many products each store has sold.
Drag the Store field to the Pivot Table Row Field. A list of stores will appear row by row.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/6a/Create-Pivot-Tables-in-Excel-Step-7-Version-2.jpg/v4-728px-Create- Pivot-Tables-in-Excel-Step-7-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/6a/Create-Pivot-Tables-in- Excel-Step-7-Version-2.jpg/v4-728px-Create-Pivot-Tables-in-Excel-Step-7-Version-2.jpg”,”smallWidth”:460,”smallHeight”:344,” bigWidth”:728,”bigHeight”:545,”licensing”:”<div class=”mw-parser-output”></div>”}

Add column field. Like rows, columns allow you to sort and display data. In the example above, the Store field is added to the Goods Field. To see how many each type sold, drag the Product Type field to the Column Field.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/4d/Create-Pivot-Tables-in-Excel-Step-8-Version-2.jpg/v4-728px-Create- Pivot-Tables-in-Excel-Step-8-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/4d/Create-Pivot-Tables-in- Excel-Step-8-Version-2.jpg/v4-728px-Create-Pivot-Tables-in-Excel-Step-8-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,” bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Add a value field. Now that you’ve sorted an organization, you can add visible data to a table. Click and drag the Sales field to the value field in the Pivot Table. You’ll see a table showing both product and store sales, and a Total column on the right side. ^{[3] X Research Sources}

With the steps above, you can drag fields to the corresponding cell below the Fields list on the right side of the Excel window instead of dragging them into the table.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/e/eb/Create-Pivot-Tables-in-Excel-Step-09.jpg/v4-728px-Create-Pivot-Tables- in-Excel-Step-09.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/eb/Create-Pivot-Tables-in-Excel-Step-09.jpg/ v4-728px-Create-Pivot-Tables-in-Excel-Step-09.jpg”,”smallWidth”:460,”smallHeight”:344,”bigWidth”:728,”bigHeight”:545,”licensing”:” <div class=”mw-parser-output”></div>”}

Add more fields to each section. Pivot tables allow you to add more fields to each section, giving you more control over data display. With the example above, you can create several types of tables and chairs. The spreadsheet will record the product as a chair or table (Product Type), but the exact model is the table or chair sold (Sample).

Drag the Form field to the Column Fields section. Columns show breakdowns of revenue for each row sample and as a whole. You can change the order of the stickers by clicking the arrow button in the box in the lower right corner of the window. Select “Move Up” or “Move Down” to change the order.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/de/Create-Pivot-Tables-in-Excel-Step-10-Version-2.jpg/v4-728px-Create- Pivot-Tables-in-Excel-Step-10-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/de/Create-Pivot-Tables-in- Excel-Step-10-Version-2.jpg/v4-728px-Create-Pivot-Tables-in-Excel-Step-10-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,” bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Change the way data is displayed. You can change the way the value is displayed by clicking the arrow icon in the Value box. Select “Value Field Settings” to change how the value is calculated. for example, you can display data as a percentage instead of a population, or an average instead of a total.

You can add the same field to the Value box multiple times to take advantage. In the above example, the total sales of each store are displayed. When you add another Sales field, you can change the value setting to show the second Sales as a percentage of total sales.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/e/e6/Create-Pivot-Tables-in-Excel-Step-11.jpg/v4-728px-Create-Pivot-Tables- in-Excel-Step-11.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/e6/Create-Pivot-Tables-in-Excel-Step-11.jpg/ v4-728px-Create-Pivot-Tables-in-Excel-Step-11.jpg”,”smallWidth”:460,”smallHeight”:346,”bigWidth”:728,”bigHeight”:547,”licensing”:” <div class=”mw-parser-output”></div>”}

Learn to manipulate values. When it comes to changing how the value is calculated, you have several options depending on your needs.

Sum – This is the default for the value field. Excel will sum the values of the selected fields.
Count – Counts the number of cells containing the selected field’s data.
Average – Calculates the average of the values of the selected field.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/4a/Create-Pivot-Tables-in-Excel-Step-12.jpg/v4-728px-Create-Pivot-Tables- in-Excel-Step-12.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/4a/Create-Pivot-Tables-in-Excel-Step-12.jpg/ v4-728px-Create-Pivot-Tables-in-Excel-Step-12.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

Add filters. The “Report filter” area contains fields that you can access through the pivot table’s data summary display by filtering the data. They work the same way as report filters. For example, setting the Store field as a filter instead of a Label row allows you to select individual stores to check sales, or check out all stores at once.

Using PivotTables

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/e/ee/Create-Pivot-Tables-in-Excel-Step-13.jpg/v4-728px-Create-Pivot-Tables- in-Excel-Step-13.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/ee/Create-Pivot-Tables-in-Excel-Step-13.jpg/ v4-728px-Create-Pivot-Tables-in-Excel-Step-13.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

Sort and filter the results. One of the main functions of the Pivot Table is the ability to sort the results and display a clear report. Each label is sorted and filtered by clicking the down arrow next to the title. You can sort the list or filter to show specific items. ^{[4] X Research Sources}

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/cd/Create-Pivot-Tables-in-Excel-Step-14.jpg/v4-728px-Create-Pivot-Tables- in-Excel-Step-14.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/c/cd/Create-Pivot-Tables-in-Excel-Step-14.jpg/ v4-728px-Create-Pivot-Tables-in-Excel-Step-14.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

Update spreadsheet. Pivot tables automatically update when you change spreadsheet data. This is great for tracking spreadsheets and checking changes.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/da/Create-Pivot-Tables-in-Excel-Step-15.jpg/v4-728px-Create-Pivot-Tables- in-Excel-Step-15.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/da/Create-Pivot-Tables-in-Excel-Step-15.jpg/ v4-728px-Create-Pivot-Tables-in-Excel-Step-15.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:” <div class=”mw-parser-output”></div>”}

Change pivot table. Pivot tables allow you to change the position and order of fields easily. Drag the fields to different positions on the pivot table until satisfied.

This is the time to learn the name of the pivot table. Moving data to different locations is called “pivoting” because you change the direction the data is displayed.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/27/Create-Pivot-Tables-in-Excel-Step-16.jpg/v4-728px-Create-Pivot-Tables- in-Excel-Step-16.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/27/Create-Pivot-Tables-in-Excel-Step-16.jpg/ v4-728px-Create-Pivot-Tables-in-Excel-Step-16.jpg”,”smallWidth”:460,”smallHeight”:344,”bigWidth”:728,”bigHeight”:545,”licensing”:” <div class=”mw-parser-output”></div>”}

Create Pivot Charts. You can use Pivot Charts to present vivid visual reports. You can create a Pivot Chart directly from a Pivot Table, the process is very quick.

Advice

If you use the Import Data command from the Data menu, you have more options for importing data from your Office Database connection, Excel file, Access database, Text file, ODBC DSN’s , web pages, OLAP and XML/XSL. You can then use the data as usual.
If using AutoFilter (Access “Data”, “Filter”), disable this function when creating the pivot table. Triggered after table creation.

Warning

If you use existing data in your spreadsheet, make sure that the columns in your selection range are named separately.

Steps

Build Pivot Table

Pivot Table Configuration

Using PivotTables

Advice

Warning

Related Posts