How to Create a Pivot Table in Excel

By Techwalla Computers Editor

One of the most effective ways to summarize data in Microsoft Excel is to create a pivot table. In the absence of advanced reporting software, pivot table reports are sufficient at presenting data from a Microsoft Excel List, multiple ranges within Excel, a database or external data sources.

Create a Basic Pivot Table From an Excel List

Step 1

Ensure your data source contains all data elements you want to include in your report. All data ranges need to have labeled rows, columns and associated data values.

Step 2

Select the "PivotTable and Pivot Report" option from the Data menu. This option launches a wizard to aid you in creating a report.

Step 3

Choose the "Microsoft Office Excel List or database" data source option from the wizard. This option specifies that your data resides in an internal spreadsheet.

Step 4

Specify PivotTable as your desired report. This triggers the data range prompt to appear.

Step 5

Highlight the range that contains the data you would like to include. Start from the top left corner of your data set and end with the bottom right cell.

Step 6

Decide whether you want the report to display in the worksheet with the raw data or in a separate worksheet. Although it is cleaner to have the report in a new worksheet, there is no functional difference between the two options.

Step 7

Drag and drop the data objects from the pivot table field list onto the worksheet. The report areas include space for row, column, data and page field values. You can move the objects around until the report layout is satisfactory.

Make a Pivot Table From Multiple Ranges

Step 1

Follow Steps 1 and 2 in Section 1.

Step 2

Choose "Multiple Consolidation Ranges" as your data source. This will allow Excel to pull from more than one list.

Step 3

Specify how many page fields you want, from zero up to four. You can either create them manually or allow Excel to create them.

Step 4

Add all ranges that contain report data. You can add, delete and browse for additional .xls files that include relevant information.

Step 5

Complete Steps 6 and 7 in Section 1.

Generate a Pivot Table From External Files

Step 1

Follow Steps 1 and 2 within Section 1.

Step 2

Choose "External Data Source." Excel lists five options as possible data sources: dBase, Excel, HT8PRD, MS Access Database and Visio Database Samples.

Step 3

Specify the appropriate data source(s). Browse through your directory to select the correct file.

Step 4

Complete Steps 6 and 7 in Section 1.