Pivot tables - moving columns around

[juanito]

Hi - here's a simple-enough looking problem which I haven't found the answer to!


My source data is organised in a fairly straightforward table: columns for GL account, cost centre, category (actual or budget), and then 12 columns for monthly values.


The output format is also pretty basic: report filter is by cost centre, and we then show GL accounts in rows and monthly values in columns, with a total - first for Budget then for Actual. (So column order is: Jan...Dec, Total - first for Budget, then for Actual.)


All I want to do is show the totals figures - budget and actual - in the first columns, and then show the monthly values further out to the right. So my column order would be: total budget; total actual; budget month-by-month; actual month-by-month. I can't figure out how to do this pivot table currently situates the totals within each category group (budget/ actual).


Thanks!

 

[Meedan]

You will just have to use Calculated Field in the pivot table.


CLick on your pivot table - In Ribbon Goto Formulas - Calculated Field


In Name Type - Total

In Formula you just have to add all the months.

From Fields dropdown below slect January then enter + then Feb then + ... till Dec.


In Values - Keep Total in top.

In Column Labels - Keep Values last.


This will give you - Budget Total then budget month by month - Actual Total then actual month by month.

 

[juanito]

Hi Meedan -


I've tried doing that, and also calculating the total in the source data.


But my problem is the column order. I don't want the order you show at the end of your post - what I want is: Budget total; Actual total; budget month by month; actual month by month.


Can it be done?

 

[Meedan]

Lets wait for HUI cause if there's a solution HUI will definately know how to do it.


If you are in a hurry, you can use two pivot tables to reflect the data as you need by hiding the row labels in 2nd pivot.