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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,995
Messages
5,834,779
Members
430,321
Latest member
yemisimi11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top