Create a Quick PivotTable

Creating a Quick Pivot Table

PivotTables are one of Excel’s tools for giving you better views of your data. By creating a Pivot Table from your flat, tabular data, you’re giving your records visual dimension – you can see your records from various 3D perspectives, which is why they’re called PivotTables—you can spin them, turn them on a pivot point, which is typically a field within the data that best defines why you’re storing the data in the first place. For a customer list, it might be the customer type. For a list of products you sell, it might be the vendor field. For employees, it might be the department they work in – you get the idea.

Here, we’re going to create a Pivot Table from a list of foods sold by a company, and we want to group them by their Category, see the City they’re sold in, and look at the total sales. Having decided on that ahead of time will make the process easier, so do take the time to plan a bit before diving in.

On the Insert tab, I’ll choose Pivot Table, and in the resulting dialog box, note that Excel has already selected the full range of my data – from cell A4 through H248. If you see that Excel has missed some columns or rows, you can select the range shown and then drag or use the CTRL and arrow keys within your data to fix that.

Next, I’m going to tell Excel where to put the Pivot Table. Unlike a subtotal report, the Pivot Table doesn’t take over the data – it’s added as a separate object, using or referring to your data.

I’ll choose New Worksheet, and then click OK.

In the resulting new sheet, I now have the Pivot Table Fields panel, and a space on the sheet that will soon contain my Pivot Table.

Using the list of fields from my data that appear in the Pivot Table Fields panel, I drag Category into the Filter box – this is where you put the field that will be your pivot point, or main focus of the data.

Then, I drag City into the Rows box, and Total Sales into the Values box. As I drag these fields into the boxes, you see the Pivot Table form on the worksheet, in the cells on the left – and note that the Total Sales is automatically summed, the most common choice for numeric field data. If I drag the wrong field in? Just drag it back out, dropping it off in the Fields list, and go back for the field you wanted.

I can now refine what I see in the Pivot Table by clicking the All drop list on the Filter field (Category) to see only one of my Categories, or see just a few – like Cookies and Crackers (after clicking the Select Multiple Items checkbox).

So – with just a few clicks and 3 drags with my mouse, I have a Pivot Table – and an instant, simple view of the total sales numbers for my various types of food, by City – not something I could have easily seen by looking at the 400 rows of my list!