Display fields side by side in columns - pivot table

[wikurbia]

I am collecting data from project managers to report to ourfunding body about people employed. They require it in this format<o></o>

Ref<o></o>​

Gender<o></o>​

Age<o></o>​

<o></o>

Male<o></o>

Female<o></o>

Prefer not to say<o></o>

16-24<o></o>

25-29<o></o>

30-34<o></o>

35-39<o></o>

40-44<o></o>

45-49<o></o>

50-54<o></o>

55-59<o></o>

60-64<o></o>

65+<o></o>

Prefer not to say<o></o>

1234<o></o>

2<o></o>​

<o></o>

<o></o>

<o></o>

1<o></o>​

1<o></o>​

<o></o>

<o></o>

<o></o>

<o></o>

<o></o>

<o></o>

<o></o>

<o></o>

1527<o></o>

<o></o>

3.5<o></o>​

<o></o>

<o></o>

0.5<o></o>​

<o></o>

3<o></o>​

<o></o>

<o></o>

<o></o>

<o></o>

<o></o>

<o></o>

<o></o>

<tbody>
</tbody>

I resisted collecting it in that format as it would then be difficult to analyse. So I set up a spreadsheet as follows, with dropdown (data validation) lists for the categories<o></o>

Co. Ref<o></o>	Gender<o></o>	Age<o></o>	FTE<o></o>
1234<o></o>	Male<o></o>	25-29<o></o>	1<o></o>
1234<o></o>	Male<o></o>	30-34<o></o>	1<o></o>
1527<o></o>	Female<o></o>	35-39<o></o>	3<o></o>
1527<o></o>	Female<o></o>	25-29<o></o>	0.5<o></o>

<tbody>
</tbody>

When you analyse that in a pivot table by adding the Gender and Age fields to the columns you get<o></o>

<o></o>	Female<o></o>​	Male<o></o>​
Ref<o></o>	25-29<o></o>	35-39<o></o>	25-29<o></o>	30-34<o></o>
1234<o></o>​	<o></o>	<o></o>	1<o></o>​	1<o></o>​
1527<o></o>​	0.5<o></o>​	3<o></o>​	<o></o>	<o></o>

<tbody>
</tbody>

I was expecting to have to create 2 separate pivottables then combine the data, but wonder is there an easy way to get the two fields to display separately within one pivot table so that I have all the Gender categories then all the Age categories the same as the reporting format? It seems such a simple thing, but so far I haven't found a way. We are currently on 2010, but will move to 2016 next year if that makes a difference.

 

[BGY23]

To get male and female at the top you have put the "Gender" column in the columns are of the pivot table. Drag it to the rows area.

You may also wnat to try the calssic layout.

Click into the pivot table
Go to Analyze> pivtatable options> and on the display tab select classic pivottable layout.