How to Organize and Find Data With Excel Pivot Tables GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office

Organize and Find Data With Excel Pivot Tables

By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on October 24, 2019 Tweet Share Email Marnie Burkhart / Getty Images Tweet Share Email MS Office Excel Word Powerpoint Outlook 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. They move, or pivot, fields of data from one location to another so that data can be looked at in a number of different ways. The instructions in this article apply to Excel 2019, 2016, 2013, 2010; and Excel for Mac.

Enter the Pivot Table Data

The first step in creating a pivot table is to enter the data into the worksheet. To follow along with this tutorial, enter the data shown in the image below. Keep the following points in mind: At least three columns of data are needed to create a pivot table. It is important to enter the 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.

Create the Pivot Table

Follow these steps to create a pivot table using the tutorial data: Highlight cells A2 to D12. Select Insert. In the Tables group, select PivotTable to open the Create PivotTable dialog box. Choose Existing Worksheet for the location of the pivot table. Place the cursor in the Location text box. Select cell D15 in the worksheet to enter that cell reference into the location line. Select OK. A blank pivot table appears on the worksheet with the top left corner of the pivot table in cell D15. The PivotTable Fields panel opens on the right side of the Excel window. At the top of the PivotTable Fields panel 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 the Pivot Table

The data areas in the PivotTable Fields panel are linked to corresponding areas of the pivot table. As you add the field names to the data areas, data is added to the pivot table. Depending on which fields are placed in which data area, different results are obtained. You have two choices when it comes to adding data to the pivot table: Drag the field names from the PivotTable Fields panel and drop them on the pivot table in the worksheet.Drag the field names to the bottom of the PivotTable Fields panel and drop them in the data areas. Drag the following field names to the noted data areas: Total Sales to the Filters area.Region to the Columns area.Sales Rep to the Rows area.Orders to the Values area. Screenshot

Filter the Pivot Table Data

The pivot table has built-in filtering tools that 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. Select the Column Labels down arrow in the pivot table to open the filter's drop-down list. Remove the check mark next to Select All to remove the check mark from all the boxes in the list. Place a check mark next to West and North. Select OK. The pivot table shows the order totals for the sales reps that work in the West and North regions.

Change the Pivot Table Data

To change the results shown in the pivot table: Rearrange the pivot table by dragging the data fields from one data area to another in the PivotTable Fields panel. If you closed the PivotTable Fields pane, select any cell in the pivot table and select Analyze > Field List. Apply filtering to get the desired results. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Create a Report in Excel The Do's and Don'ts of Entering Data in Excel How to Count Data in Selected Cells With Excel's COUNTIF Function How to Combine the ROUND and SUM Functions in Excel Excel SUMIFS: Sum Only Values Meeting Multiple Criteria How to Limit Rows and Columns in an Excel Worksheet How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Sort Data in Excel How to Create a Pivot Table in Google Sheets How to Print Labels from Excel Splitting the Screen in Excel How to Use the MONTH Formula in Excel Round up Numbers in Excel With the ROUNDUP Function How to Use AVERAGEIF Function in Excel How to Delete Pivot Tables in Excel Make a High-Low-Close Stock Market Chart in Excel Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies