Formating issue in Pivot table in excel 2003

[rahulgupta335]

Hi Guys,


I am very new to Excel reporting and need to create a report by use of pivot table.


Here is my sample data :


Country Sector Benchmark BenchMark_weight Account order country_weight

India oil BTC_1 100 1 10

japan oil BTC_1 100 2 11

US oil BTC_1 100 3 12

UK oil BTC_1 100 4 13

PK oil BTC_2 200 5 66

HK oil BTC_2 200 6 98

NZ oil BTC_2 200 7 23

OZ oil BTC_2 200 8 9


and my sample report which I need to achieve is :


Break Down BTC_1 India japan US UK BTC_2 PK HK NZ OZ

oil 100 10 11 12 13 200 66 98 23 9


Guys I am not able to include Benchmark (BTC_1 and BTC_2)in my pivot table in this format along with country name.

Please help me to achieve this format in Excel 2003.


--

Thanks,

Rahul

 

[Hui]

Rahul


Firstly, Welcome to the Chandoo.org forums.


You may want to have a read of:


Select your data area including headings

Insert, Pivot table, using the wizard select a destination

You need:

Row label: Sector

Column Label: Country

Values: Sum of Country Weight

When complete

Right Click on the Row and Column Grand Totals and Remove Grand Total (Unless you want them)

You can re-order the countries by selcting them and drag the edge to a new location

 

[rahulgupta335]

Hi Hui,

Thanks for quick reply. This is really very helpful.

But my problem is After doing the above procedure also I am not able to include Benchmark (BTC_1 and BTC_2)in my pivot table in this format.


Break Down BTC_1 India japan US UK BTC_2 PK HK NZ OZ

oil 100 10 11 12 13 200 66 98 23 9


Can you help me out on this.

 

[Hui]

If you gotothe Field Chooser and drag the Benchmark to the Top or Bottom of the Column Header row it will add te Benchmark as a field it's behaviour will be different if you add it above or below the County Field

 

[rahulgupta335]

Hi Hui,

Thanks for this reply. Please ignore my ignorance but I am still not got the desired output. When I fellow the above way, I am getting Repetition of Benchmark field column after each column of Country, where in this case I want single column for all the country column which fall under that related benchmark.

Can you please suggest in this. I am really stuck here.