Table ? Pivot Table ? Power Pivot Table ? - Chaos Abounds

[Blanche]

I've been trying to follow on line instructions to create a worksheet (multiple pivot tables) from data that doesn't leave blank rows between the tables, and have not been successful. I tried using a data model, and attempted PowerPivot but still have not been successful, so I don't know if my thinking/following directions is faulty, or if Excel is the wrong tool.

Ideally the tables would display cleanly minimizing the space between data segments. If you look at the PivotTable tab, you'll notice that there's 30 blank lines between the fist two tables. I've uploaded sample data. The original has MANY more rows, and columns which I would normally use for slicers. Normally I can use a single slicer for multiple pivot tables, but since I've been trying to use the data model, that no longer works. Since I'm just trying to find a clean display, I don't want any calculations, and suppressed sub-totals on the pivot tables.

If you look at the Data tab, you can see where I filtered each table to display the Department-Wide data, and it is displays nice and clean, but imaging if you have 20 divisions with over 200 stations, and columns with cities, jurisdictions, districts, etc. to dissect the data. I know you can use slicers with tables, but each table requires their own slicer so that is unyielding.

Any assistance you can give would be greatly appreciated!

Traci

 

[vletm]

Why do You have many tables in Data-tab?
Have one table and many things would be smoother.

PivotTable-tab work just as You've done ... You've left there 'enough' empty rows between pivot-tables.

 

[Blanche]

The multiple tables are the time sequence data elements. Turnout, travel, response, etc. represent a different start end date/time. The data represented is a 90 Percentile of a MUCH larger data file (> 1 M rows of records, as each unit has a unique dispatch, en route, arrival, etc. timestamp). If you can think of a different way to represent the times, I would greatly appreciate it.

 

[vletm]

As I wrote: Have one table and many things would be smoother.
Now, Your Data-tab has five times same ... sample ... data.
First of all, You should able to show more realistic sample data ( if data is five times same, then there needs to be only one time that! ).
Next ... You should able to show ... what do You really need to get from that?

 

[Blanche]

Here's the table with "realistic" sample data. I was just using "9"s as placeholders. What I was hoping to do was have the pivot tables act similarly to the standard tables in that when the slicer is used, the data would fill the viewing space without the blank spaces between pivot tables. If there was a way to use a single slicer for multiple standard tables that would be great, but every Excel article I've found that feature was only available for Pivot Tables.

 

[vletm]

One sample:
I left there those five data-tables ...
... those needs minor modifications
Pivot-table -sheet has one Pivot-table with four slicers.
... all values are sums of ... something.
Your snapshot's left side ... hmm?

 

[Blanche]

Because the data is the 90 percentile of a much larger data set, what I'm displaying is the summary of the department - division - station - city - jurisdiction - station responsibility, etc. so I don't need any calculations or totals by the pivot table. I see you left the table set-up, removed the headers on the lower tables and generated a pivot table for the entire range. VERY SLICK!!! That gets me going in the right direction. Thank you. Thank you. Thank you!