Microsoft Excel Pivot Tables Tutorial


PivotTables are used to separate and organize data so that segments of it can be easily analyzed. They are often used for measuring sales statistics. For example, PivotTables help quickly determine which sales associate sold the most product or which sales region brought in the most revenue. The filtering arrows at the top of each column within the PivotTable are used to sort out the required information. Once a PivotTable is created, it can be used again to separate and compare other data. This speeds up reporting because sorting through huge pieces of data manually is eliminated.

Excel's PivotTable sums it all up

You must own a licensed version of Microsoft Excel 2003 or later. Create a table that contains data for creating a PivotTable Report. Use the sample data here as a test example.

Layout the data in columns and rows

Click on cell "A1" and then go to the Data menu on the toolbar and select "PivotTable Report and Pivot Chart Report."

Use the Data toolbar menu

Select "Microsoft Office Excel list or database" as the option under "Where is the data that you want to analyze?" Next, select "PivotTable" as the option under "What kind of report do you want to create?"

The PivotTable and PivotChart Wizard

Verify that the highlighted range is what you would like to use as the source for your new PivotTable. If it is not correct, manually adjust it by highlighting the correct range. When finished, click "Next."

The default data range

Check "New worksheet" as the option for "Where do you want to put the PivotTable report?" Otherwise, choose to place it in the same worksheet where the data is housed.

Optional locations are availalbe

Drag and drop fields onto the table as shown in the illustration. When finished, click "OK." (Note: If you have Excel 2007, the fields for the PivotTable using the drag and drop method are located in the PivotTable Field List located on the right side of the screen.)

PivotTable and PivotChart Wizard Layout screen

Keep the default Formatting and Data Options or check and uncheck them as desired. Name your PivotTable or keep the default name. Press "OK" when finished.

PivotTable Options dialog box

Test your sample PivotTable by clicking on the Filter drop-down menu in the Data column. Check the "Sum of 1st Qtr Sales." Uncheck all other boxes within the list.

Filter the data

Click the "Refresh Data" icon located on the PivotTable toolbar after new information is added to the source of the PivotTable in the adjacent worksheet. The actual PivotTable report is a read-only display and cannot be altered. This helps to prevent unauthorized or accidental tampering with the data source.

Refresh Data icon is on the PivotTable toolbar

Related Searches

Promoted By Zergnet



Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!