Skip to main content

Creating Pivot Tables using Excel 2010

Pivot tables look great, take up less screen space, are easy to navigate and let you use and analyse data easily. For example, a worksheet listing customers, products and quarterly sales can produce a pivot table allowing you to show how much of each product was sold, to which customer and annual totals. Sound complicated? It isn't, here's how you can create pivot tables with ease:

Open the worksheet you want to use - with column or row headings and no blank rows or columns. Click any blank cell on the worksheet, then on Insert, and then on Pivot Table. A dialogue box will appear asking you to Choose the data you want to analyse. Use the mouse to highlight the data, or enter the cell names into the Select a table or range box. Choose whether you want the pivot table to appear in a New Worksheet or the Existing Worksheet and click OK.

A pivot table will appear, along with a pivot table field list and four area boxes.

Tick the check box next to each field to choose the data you want to work with. You will see the pivot table change as you click or unclick each field. Within the pivot table, you can use the + or - buttons to see more or less detail.

As you can see, Excel automatically places the field names into one of the four boxes below the field list; Report Filter, Column Labels, Row Labels or Values. You can work with the data in your pivot table by dragging and dropping the fields you want to manipulate into the Report Filter box.

Dropping any field into the Report Filter box creates a box above the pivot table. Click the arrow key to choose which data to display and calculate in the main pivot table. You can further customise your pivot table by dragging fields into the Column Labels, Row Labels or Values boxes.

It is that easy? Yes. You have now successfully created a pivot table in Excel 2010 without breaking a sweat. Pivot tables make navigating and analysing large volumes of data much easier. To see how to apply a similar feature to a small amount of data without creating a pivot table, please see the article "Importing Data into Excel 2010".