Pivot Table query

jarl1947

New Member
Joined
Sep 26, 2002
Messages
48
I have created a pivot table which totals incidents across a range of activities. I want to show each of the incidents as a % of the total in a column alongside the actual totals. I found a past tip which seemed to do just that.
The tip I used suggested dragging 2 versions of the field I which to total, right clicking the second and selecting the % of column option from the resulting dialogue box. WhenI tried it the %'s were displayed as separate rows underneath each of the numerical totals.

Can anyone suggest were I went wrong? Thanks.
I'm using Excel 2000.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you want them side by side, put your mouse over the Data button until the cursor turns into a black cross with arrows at the end. Then click and drag to the right.
 

jarl1947

New Member
Joined
Sep 26, 2002
Messages
48
Andrew, thanks for the reply. Tried it but can't get my mouse to produce the black cross with arrows. I know the kind of cursor you're describing but it doesn't seem to work for me. This isn't one of those things that will only work in later versions of Excel,is it?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
jarl1947 said:
Andrew, thanks for the reply. Tried it but can't get my mouse to produce the black cross with arrows. I know the kind of cursor you're describing but it doesn't seem to work for me. This isn't one of those things that will only work in later versions of Excel,is it?

It works for me in Excel 2000. What does your Pivot Table look like?
 

jarl1947

New Member
Joined
Sep 26, 2002
Messages
48

ADVERTISEMENT

Andrew, her is my pivot table (Hopefully! First time I've tried to copy one to the Board).
July Example.xls
BCDE
5Count of CategoryCount of Incident Type
6CategoryTotalCategory
7Car crime3Car crime
8Drink/Drug Issue7Drink/Drug Issue
9Env/Litter61Env/Litter
10Harassment3Harassment
11Nuisance behaviour31Nuisance behaviour
12Quads/Youth Disorder23Quads/Youth Disorder
13Tenancy Breach1Tenancy Breach
14Vandalism/Void related15Vandalism/Void related
15Warden Abuse18Warden Abuse
16Grand Total162Total
Sheet1
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you add Category into the Data field again and set it as % of Column you should get this:
Book4
CDEF
1CategoryDataTotal
2Car crimeCount of Category3
3Count of Category21.85%
4Drink/Drug IssueCount of Category7
5Count of Category24.32%
6Env/LitterCount of Category61
7Count of Category237.65%
8HarassmentCount of Category3
9Count of Category21.85%
10Nuisance behaviourCount of Category31
11Count of Category219.14%
12Quads/Youth DisorderCount of Category23
13Count of Category214.20%
14Tenancy BreachCount of Category1
15Count of Category20.62%
16Vandalism/Void relatedCount of Category15
17Count of Category29.26%
18Warden AbuseCount of Category18
19Count of Category211.11%
20Total Count of Category162
21Total Count of Category2100.00%
Sheet2


If you mouse over the Data button (D1 in this example) the cursor should change as I described, unless the sheet is protected, in which case the cursor will turn into a hand.
 

jarl1947

New Member
Joined
Sep 26, 2002
Messages
48

ADVERTISEMENT

Andrew, Thanks for your patience. I can get to the situation illustrated in your post. However, once there my mouse pointer stubbornly refuses to change to the familiar black arrowed cross. The sheet is not protected.

I don't know if there is anything else you can suggest. I'll understand if not, but I really am frustated this apparently simple solution will not work for me.
Thanks again.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Here is a macro to try:

Code:
Sub Test()
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Data")
        .Orientation = xlColumnField
        .Position = 1
    End With
End Sub
 

jarl1947

New Member
Joined
Sep 26, 2002
Messages
48
Andrew, how embarassing is this! Tried you Macro (thanks) but got an error message: Run Time Error 1004 "Unable to set Orientation Property of PivotField class" I'm obviously missing something.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
jarl1947 said:
Andrew, how embarassing is this! Tried you Macro (thanks) but got an error message: Run Time Error 1004 "Unable to set Orientation Property of PivotField class" I'm obviously missing something.

Sorry, I don't know what that something can be, except for worksheet protection. Can you do it with a simple pivot table in a new workbook?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,070
Messages
5,570,018
Members
412,304
Latest member
citrus
Top