How to Quickly Create Pivot Tables in Excel 2010

Pivot tables are amazing things when one learns how to work with them. This post will simply cover how to create a pivot table from a raw data set.

First, I am starting with my dataset, which is a list of all the countries of the world and their population projections from 2011-2020. The data comes from the United Nations.

Click Here to download the sample file.

Why would I want to Pivot the data? If I want to analyze the data in any way or look at it differently than how it is displayed in the original format, I will want to put it in a pivot table.

Let’s begin; it will be over before you know it.

Then I go to the insert tab and click the create pivot button. A pivot table menu should pop up like the one below.

Notice that your selection is in the Table/Range box. Most of the time you want the pivot to be on a new sheet, so make sure the “New Worksheet” radial button is marked, then hit OK.

Now you should see your pivot table, you may want to enable classic view if you have worked in pivot tables in previous versions of Excel. Classic view enables you to drag and drop fields into the pivot table. I prefer this method over the new on in 2010.

Enable Classic View

Right click anywhere in the pivot table and select “PivotTable Options”, then go to the display tab and make sure that classic view is checked. See below:

Now you should be able to drag the fields (columns from the data source) into the places where you want them in the pivot table.

Click and drag “COUNTRY” to the “Drop Row Fields Here” area.

Now repeat with the “YEAR” field and drop it in the “Drop Column Fields Here” area.

Finally click and drag the “POPULATION” field to the “Drop Value Fields Here” area of the pivot table.

Congratulations! You should now be done, but if for some reason it does not use a “sum” in the values are you may have to manually change it. To do this, right click on the field that is highlighted above (Yours probably says “Count of POPULATION” if you’re doing this) and select “field value settings” from the menu. Make sure you select “Sum” from the list of options. (You can use the others if they suit the specific thing you’re using the PivotTable for)

This is the first in what will surely be a series of posts about PivotTables.