Pivot Table Excel | help and tutorial guide

One of the best features in Excel for analysing and presenting data is the pivot table. But if you’re a beginner to Excel and pivot tables leave you confused, don’t worry, help is at hand! Take a look around our website and the mystery will be solved.

Master Excel to get a higher-paying job – Click here for an exclusive course!

So what can pivot tables do? Well, imagine you have a set of data, containing people’s names, addresses, occupations and ages. Here are some of the things you could easily find out from that data using a pivot table:

• What is the most popular occupation for someone under 21?
• How many graphic artists live in Oklahoma City?
• Are there more accountants in Dallas or Houston?
• How many people with the surname Obama live in the Zip code 20500?
• and so on…

And you can find that all out from just one pivot table. Useful? You betcha! So let’s get on with it. As we’re on the subject, let’s take the example above, of a list of people’s names, addresses, occupations and ages:

You might be wondering what all the fuss is about, as it’s fairly easy to pick out the data you want from that list. However, we’re only using a small sample of data for ease of explanation. But imagine if that data ran into hundreds or thousands of lines. Then the benefits of pivot tables in excel become clear. Let’s set one up. From the “data” menu, select “Pivot Table Report”. You will then get a choice of data sources, you would most commonly select “Microsoft Excel list or database”, so let’s select that now. You will then be asked what data you want to use. You can type in the references, but it’s easier to highlight the data by clicking on the top left cell and dragging down and to the right to highlight all the data, as below:

Click next and choose whether you want the pivot table to appear on the same sheet as the data source, or a separate sheet. If you’re dealing with a large dataset, it’s often easier and neater to use a new sheet within the same workbook. Once you select Finish, you’ll be presented with the following screen, which is a blank pivot table:

Don’t let this confuse you, it is honestly much easier than it may appear. Think of this grid as a graph with different axes. To begin with, choose two fields you want to analyze against each other and drag them onto the appropriate section of the pivot table. For example, let’s analyze this group of people to see where the retail employees live. To do this, drag occupation on to the row fields section and city on the data section. The result will probably be something like this:

Confused? Don’t be. Pivot tables are powerful. This one is currently set to “count” the cities by occupation. So the 2 next to accountant means there are accountants in two different cities. Click on the “2″ and a new sheet will open containing those two lines of data, as follows:

Perhaps you’d prefer to list the subtotals by city, rather than the number of different cities. To do this, you need to alter the parameters of the excel pivot table. Double click on the “city” header within the pivot table and choose “count”. You’ll then see the data displayed with a count of cities by occupation, as follows:

By now you should be starting to understand the power of pivot tables within excel to analyze data, view it in different ways and prepare management reports. Here at pivot tables | excel | help, we firmly believe the best way to understand is to experiment, so off you go…