Pivot Table Set Up


M

Mike

I have a pivot table that looks as follws. Column A is the name os the
expense (there will be dozens of different expenses however I have only shown
two), column B is the Department name, column C is the location of that
particular expense and department, and columns D and thereafter represent the
monthly budget amount.


A B C D E
1 Description Department Location Jan-10 Feb-10
2 Salaries Administration Baton Rouge 3,363 3,203
3 Salaries Executive Baton Rouge 11,844 11,280
4 Salaries Sales / Revenue Baton Rouge 24,215 23,062
5 Salaries Operations Baton Rouge 39,670 37,781
6 Salaries Operations Chiefland 11,634 11,080
7 Salaries Administration Clarksville 2,681 2,554
8 Overtime Administration Baton Rouge 500 500
9 Overtime Executive Baton Rouge 200 200
10 Overtime Sales / Revenue Baton Rouge 400 400
11 Overtime Operations Baton Rouge 0 0
12 Overtime Operations Chiefland 0 0
13 Overtime Administration Clarksville 50 50


What I would like to be able to do is create a pivot table. In the Page
portion of a pivot table will be the Department and the Location. In the Row
portion of the pivot table will be the Description. In the Data portion of
the pivot table I want the monthly budget amounts. The column headings should
be the individual months.

I would like to create a pivot table such that when I select say
Administration and Baton Rouge it would look like this:


Department Administration
Location Baton Rouge

Description Jan-10 Feb-10
Overtime 500 500
Salaries 3363.22 3203.07

How do I place the column headings in the pivot table format dialog box so
that I can get this result?
 
Ad

Advertisements

M

Max

Here is the priceless technique ..
Put Department, Location into PAGE
Put Description into ROW
Put "Jan-10", "Feb-10" into DATA, one below the other (it'll show as Sum of
...)
Click to Finish. Then go to the pivot sheet, drag "Data" & drop it over
"Total" (this is the crucial step) ... and voila, you should get exactly the
set-up that you seek. Dress up the fieldnames to suit. Elation? zonk the YES
below
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top