Formatting Pivot Sheets

Question

I would like to use Pivot to create a summary sheet for all of my locations. Prior to using Pivot, I built summary sheets in the following summary format Enterprise Division Location This allowed me to use =SUM(CHILDREN()) to roll up division and enterprise totals from all locations. I would like to rebuild this structure over a Pivot sheet, where the pivot provides the Location values, and I add the Enterprise and Division rows. However, when I do this, every time the pivot re-runs, it removes the heirarchy and re-orders my locations alphabetically instead of in the order I have arranged them. I don't believe I can use a second sheet to summarize because the Pivot keeps moving the rows, so I'd be afraid that any cell links would be unreliable. The locations are fairly stable, but I would say we do add 2-3 locations/year, so I do need this to be somewhat dynamic. Any suggestions are appreciated.

Julio S. · Accepted Answer

Hi @AFlint,

If you're intending your destination sheet to be updated from time to time with new values and yet keep the hierarchy within your Enterprise and Divisions, you may achieve this by ensuring the source sheet has the Enterprise, Division, Location and Budget (or the relevant metric you are measuring) in separate columns instead of rows with hierarchy. You can then designate Enterprise and Division as rows in the Pivot destination, Location as the Column and add Budget in the Values section with SUM. Like this, any time you would insert new divisions or companies, the correct hierarchical structure would be maintained and when adding new locations, new Columns would be inserted. See an example below of what I built:

Source Sheet:

Pivot Configuration:

Destination Sheet:

I hope this can offer some guidance and be of help when designing your solution. If you'd need further advise about your current sheets and configuration, please add some screenshots (please remember to hide or delete any confidential information there) so that the road-block you are facing is more evident.

Cheers!

Julio