How to create a pivot table in Excel 2007 – Part I – a quick and basic intro

Excel function tutorials

Seems like it’s been eons since I posted an Excel tutorial, so I thought I’d write a brief intro on how to create . Pivot tables are unnecessarily mysterious and perplexing to many people, but as you’ll soon see, they’re easy to set up and a convenient way to look at spreadsheet data once you get the hang of it. There are multiple ways to set up a pivot table, but this tutorial goes through how I usually do it, and the method works for both Excel 2007 and 2003.

First, suppose we have the data below, which shows two managers, their employees, and the number and amount of sales each employee made in 2008 and 2009:

step1

Let’s pretend we want to find out how many sales each manager, Bob and Bill, had in 2009. How would we do this using a pivot table?

Step 1: Highlight the data you’re interested in analyzing. Again, different people have different ways of building pivot tables, but this just happens to be the one I use most often:

step2

Step 2: In Excel 2007, go to the “Insert” tab (circled in red below), and click on the “PivotTable” icon. (In Excel 2003, go to the menu item “Data” and select “PivotTable and PivotChart Report”. Click on “Next”, and then “Finish”)

step3

Once you click on the icon, a pop-up window will appear:

step4

which prompts you to fill in blanks on where the data is that you want to analyze and a few other things. Since we already highlighted the data in Step 1, the range of the data has already been pre-populated for us. Just hit the “OK” button and the pop-up window will go away and you’ll see the following appear in your worksheet (click image to enlarge):

step5

You’ll notice there are several areas with labels on them, like “Drop Row Fields Here” and “Drop Column Fields Here”. I think this is probably the most confusing part of building a pivot table. Chances are, you will most often use the Row and Data Fields areas, so let’s focus on these first.

Step 3: Build your pivot table by clicking and dragging items. We’re interested in looking at the data by manager, so the first thing we want to do is click on “Manager” in the field list menu (click image to enlarge):

step6

and then drag the entire word into the “Drop Row Fields Here” Section.

step7

Once you drop “Manager” into the “Drop Row Fields Here” area (circled in red above), you’ll see the data appear, with “Bill” and “Bob” since these are the only two “Managers” we have in our data. In Excel 2007, you can also drag the word into the “Row Labels” area within the pop-up window but I’m ignoring the additional features of 2007 for now.

Next, we wanted to look at the total # of sales by Manager, so we repeat the same thing we did above by clicking on the “# of sales” item in the list and dragging it, this time, to the “Drop Data Items Here” section (click image to enlarge):

step8

Again, once “# of sales” is dropped into the data items area, the numbers immediately appear, summing the “# of sales” together by manager.

step9

One final step. Remember that our data was for 2008 and 2009, so the # of sales by manager that we see is the total for both years. Suppose we just wanted to see the # of sales for this year. We could do this by clicking on “year” in the field list and dragging “year” to the “Drop Page Fields Here” area (click image to enlarge):

step10

Again, as soon as you drop “year”, you’ll see a drop-down menu appear that defaults to “all” (click image to enlarge):

step11

Click on the drop-down menu and you can select “2009” from the list instead (click image to enlarge):

step12

As soon as you choose “2009”, the figures change to show “# of sales by manager in 2009” for a total of 54 sales instead of 87 :

step13

I’ll write more tutorials on how to work with pivot tables, but this is just a way to get you started. Feel free to leave comments with questions about pivot tables and I’d be happy to try to cater future tutorials toward answering the most popular ones. Hope this has been helpful!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

8 Feedbacks on "How to create a pivot table in Excel 2007 – Part I – a quick and basic intro"

Mrs. Accountability

I will have to check this out, I LOVE Excel! Sounds like it could come in handy at work. Thanks for sharing this tutorial.



Arturas

It should be interesting for you to take a look at a web based BI solution with flex pivot tables and charts – http://www.flexmonster.com



Financial Samurai

Wow, what a GREAT explanation to help all those young financial analysts out there!

Are you loving the bull market in finance again?



carol

Hello,
My name is Carol. I am a finance webmaster & maintaining some good quality finance related sites & blogs with good PR. I while searching the good finance sites i found your site which will fulfill my requirement. Like as per my experience i do believe that if you want to make your site popular in front of search engine (specially Google) you need to do healthy link exchange like content & Article link exchange. Because this only help you to get high traffic & pr soon.
So, I am giving you the proposal. Will you be my healthy finance content link partner. In return i will also fulfill your requirement with my huge good financial resources.

For any further information please feel free to mail me or just come for free chating with this below mail id….
nancysix00@gmail.com

Waiting for your quick positive response.
Thanks,
Carol.



michael ante

I would like to ask whether I could use this pivoting of tables in my database which involves water billing. To tell you the truth up until now my database is very crude and I know it is not automated. I spend huge amount of time just by doing billings for my customers. If ever, may I consult something from you? I really need this for my job. Thank you so much and God bless you.



jeanette

What a great explanation! Could you write more indepth on pivot table. I would like to learn more about it.

Could you also write how to create Macro in excel 2003 and 2007. Thanks.



suman

Very easy & effective explanation . Really helpful . Thanks a lot.



penis extender results

Asking questions are truly fastidious thing if you are not understanding anything
completely, however this paragraph gives pleasant understanding yet.