Organize and find data with Excel pivot tables

Pivot tables in Excel are a versatile reporting tool that allows you to easily extract information from large data tables without using formulas. Turntables are extremely user-friendly. They move or rotate data fields from one location to another so that the data can be viewed in different ways.

Remark † The instructions in this article apply to Excel 2019, 2016, 2013, 2010; and Excel for Mac.

Contents
  1. Enter pivot table data
  2. Create pivot table
  3. Add data to pivot table
  4. Filter pivot table data
  5. Edit pivot table data

Enter pivot table data

The first step in creating a pivot table is entering data into a worksheet. Enter the details shown in the image below to follow this tutorial.

Keep the following points in mind:

  • You need at least three columns of data to create a pivot table.

  • It is important to enter the data correctly. Errors caused by incorrect data entry are the cause of many data management problems.

  • Do not leave blank rows or columns when entering data. It contains do not leave a blank line between the column headings and the first row of data.

Create pivot table

Follow these steps to create a pivot table using tutorial data:

  1. Highlight cells A2 And D12

  2. Select Insert

  3. In the Tables group, select pivot table to open the Create PivotTable dialog box.

  4. Select Existing worksheet as the location of the pivot table.

  5. Place the cursor in the Location text box.

  6. Select cell D15 on the sheet to enter a link to that cell in the location bar.

  7. Select Okay

An empty pivot table appears on the sheet with the upper left corner of the pivot table in cell D15. The PivotTables pane opens on the right side of the Excel window.

At the top of the PivotTables pane are the field names (column headings) from the data table. The data areas at the bottom of the panel are linked to the pivot table.

Add data to pivot table

Panel data areas PivotTable fields are mapped to the corresponding areas of the PivotTable. When you add field names to the data area, the data is added to the pivot table. Depending on which fields are in which data area, different results are obtained.

You have two options for adding data to a pivot table:

  • Drag the field names from the PivotTables pane and drop them into the PivotTable on the worksheet.

  • Drag the field names to the bottom of the PivotTables panel and place them in the data area.

Drag the following field names to the highlighted data areas:

  • Total sales in the Filters area.
  • Region in the column area.
  • Sales Representative in the line area.
  • Orders in range.

Filter pivot table data

The PivotTable has built-in filtering tools that refine the results in the PivotTable. Data filtering uses certain criteria to limit the data displayed in a PivotTable.

  1. Select the down arrow on a column label in a pivot table to open the drop-down list of filters.

  2. Uncheck the box next to Select all to clear all fields in the list.

  3. Check the box next to West And north

  4. Select Okay

The pivot table shows the order totals for representatives in the western and northern regions.

Edit pivot table data

The results displayed in the pivot table change:

  1. Rearrange the pivot table by dragging data fields from one data area to another in the panel pivot table fields.

    If you closed the PivotTable Fields pane, select a cell in the PivotTable and select Analysis List of fields

  2. Apply filtering to get the results you want.

Leave a Reply

Your email address will not be published.