Pivot Table: Filter Issues & Row/Column size

[liquidmettle]

Hello Everyone,

I've recently delved into the realm of pivot tables and I feel I have the relative hang of them.

Say I have the following categories: Town, Program, Activity and the values are time spent.

I have a pivot table that has Town name as columns and activities grouped by program as rows.

Here are issues I'm encountering:

1. If I use a filter on any of the categories to remove blanks or zeros, the pivot will only update existing categories and not add new ones automatically.

For instance, if town A, B, and D all currently have data and I apply a filter to remove (blank), town C will not show up in the pivot upon refresh when data is added for it. This is a problem because it means I can't have a clean pivot table without manually selecting that yes, I now want Town C to be part of the filter as well.

I should clarify that I've added the filter from the "choose fields to add to report" menu instead of adding fields to the "filters" area. Would this make a difference?

It is not possible to not have blanks in the data source for this file (as not all lines will be used when users are entering data).

2. When a pivot does add a new program or activity that now has data entered, it resets the row size. Since the pivot is part of a document where other things are going on, this is less than ideal. Does anyone have a handy macro to update the pivot on the activesheet and keep the rows and columns it occupies the a predetermined size?

Thank you all!

[Melvinrobb]

1. Right-click on an entry in the pivot table for the field in question (Ex. right click on town A), and select "field settings". Then select the option "Include New Items in Manual Filter"
2. You can right click on a pivot table, and select "Pivot table options" and deselect "Autofit column widths on update". Not sure if that will fix the row size reset. I am surprised it is doing that. I would suggest having your pivot table on its own separate tab.

[liquidmettle]

Hi Melvin,

Your answer to part 1 was spot on!

For part 2, I have a total of 12 pivot tables- one for each month of data. Each pivot is on the sheet where data for its respective is entered. This layout works well for my purposes except for the resizing of the rows.

I have all rows that are relevant set to 35 height. The rows the pivot table occupies change to 21 height when updated (I know there are macros to set a specific width/height, and I can easily select a big enough range that the pivot won't over-extend). Really I was curious if there was an easy way to tell the PT not to resize its rows (much like charts can be told to ignore row and column size changes).

I had already unticked the autofit column width on updates in my own exploring, but that you for the suggestion just the same!

I had thought about a sheet for just the pivot tables but that might be a headache planning for their maximum possible expansions.

Let me ask you this: If I did put them all on their own sheet, is it possible to write macro to print a specific pivot table (and had it fit the paper or a specific area?). If I can do this, I can just hide the tab with the pivots and allow the user to print or PDF the table they want to see.

Thoughts?

[Melvinrobb]

You probably could start a new post, asking specifically for a macro that changes row size to "x".
Why not have one tab, with one pivot table, and a slicer to filter for each month? I suppose you can't do that if your raw data is in different sources. The best long term solution is to enter your data in one table, but...
Perhaps just asking for a macro is your best bet.