Organize and Find Data With Excel Pivot Tables

Businessman analyzing data
Marnie Burkhart / Getty Images

Pivot tables in Excel are a versatile reporting tool that makes it easy to extract information from large tables of data without the use of formulas. Pivot tables are extremely user-friendly in that by moving or pivoting, fields of data from one location to another so we can look at the same data in a number of different ways.

of 05

Enter the Pivot Table Data

Screenshot of Excel showing example data table

The first step in creating a pivot table is to enter the data into the worksheet — enter the data into cells A1 to D12 as seen in the image above. When doing so, keep the following points in mind:

  • At least three columns of data are needed to create a pivot table.
  • It is important to enter data correctly. Errors, caused by incorrect data entry, are the source of many problems related to data management.
  • Leave no blank rows or columns when entering the data. This includes not leaving a blank row between the column headings and the first row of data.
of 05

Creating the Pivot Table

Screenshot of Excel showing Pivot Table creation

Follow these steps to begin creating your Pivot Table, using the data we previously entered into the worksheet.

  1. Highlight cells A2 to D12.
  2. Click on the Insert tab of the ribbon.
  3. Click on the Pivot Table to open the Create Pivot Table dialog box —
    by pre-selecting the data range A2 to F12, the Table/Range line.
  4. Choose Existing Worksheet for the location of the pivot table.
  5. Click on the Location line in the dialog box.
  6. Click on cell D15 in the worksheet to enter that cell reference into the location line.
  7. Click OK.

A blank pivot table should appear on the worksheet with the top left corner of the pivot table in cell D15. The Pivot Table Field List panel should open on the right-hand side of the Excel window.

At the top of the Pivot Table Field List panel are the field names (column headings) from our data table. The data areas at the bottom of the panel are linked to the pivot table.

of 05

Adding Data to the Pivot Table

Screenshot of Excel showing how to add data to a Pivot Table

The data areas in the Pivot Table Field List panel are linked to corresponding areas of the pivot table. As you add the field names to the data areas, your data is added to the pivot table. Depending on which fields are placed in which data area, different results can be obtained.

You have two choices when it comes to adding data to the Pivot Table:

  • Drag the field names from the Pivot Table Field List panel and drop them on the Pivot Table in the worksheet.
  • Drag the field names to the bottom of the Pivot Table Field List panel and drop them in the data areas.

Drag the following field names to the noted data areas:

  • Total Sales to the Report Filter area
  • Region to the Column Labels area
  • Sales Rep to the Row Labels area
  • Orders to the Values area
of 05

Filtering the Pivot Table Data

Screenshot of Excel showing Filtering of Pivot Table data

The Pivot Table has built-in filtering tools that can be used to fine-tune the results shown in the Pivot Table. Filtering data involves using specific criteria to limit what data is displayed by the Pivot Table.

  1. Click on the down arrow next to the Region heading in the Pivot Table to open the filter's drop-down list.
  2. Click on the checkbox next to the Select All option to remove the check mark from all the boxes on this list.
  3. Click on the checkboxes next to the West and North options to add check marks to these boxes.
  4. Click OK.

The Pivot Table should now show only the order totals for the sales reps that work in the West and North regions.

of 05

Changing the Pivot Table Data

Screenshot of Excel showing how to change table arrangement

To change the results shown by the Pivot Table:

  1. Rearrange the pivot table by dragging the data fields from one data area to another in the Pivot Table Field List panel.
  2. Apply filtering to get the desired results.