How to Create a Pivot Table in Excel

How to Create a Pivot Table in Excel. 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

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

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

Step

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

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

Step

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

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

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

Follow Steps 1 and 2 in Section 1.

Step

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

Step

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

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

Step

Complete Steps 6 and 7 in Section 1.

Generate a Pivot Table From External Files

Step

Follow Steps 1 and 2 within Section 1.

Step

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

Step

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

Step

Complete Steps 6 and 7 in Section 1.