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.
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.
No comments:
Post a Comment