Exclude (blank) from Pivot table without filtering it out

Dosaparks

New Member
Joined
Nov 23, 2010
Messages
12
Hi All,

I have looked for answers to this one but haven't been successful finding an answer that applied to my situation.

I have a 'data' sheet where each month I paste data to. Various pivot tables and charts are then automatically updated to account for the new data.

To ensure that each pivot table picks up all data, I have made my data source 5,000 rows (one month may be 2,000 the next may be 4,000).

The issue is that due to not all 5,000 rows having data in them, all my pivot tables are reporting a (blank) value.

I understand I can filter the pivot tables so that (blank) is unselected however this results in the table no longer being 'select all'. As the next month's data comes through, as the pivot table is not select all, it won't automatically pick it up (for example, the next dump of data has an 'Oct-13' category however this doesn't appear on the pivot tables as they are only set to pick up Jul, Aug, Sep with (blanks) unselected (and now also with 'Oct-13' unselected'.

It actually isn't the pivot tables I'm worried about but my pivot charts linked to them. They have a (blank) bar which looks a bit silly.

Would be great if there was just a setting on the pivot chart to ignore blanks but I'm thinking I may need a macro which updates all pivot tables to 'select all BUT (blank)'.

Any ideas?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,146
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you have Excel 2007 or later, set the data source up as a Table; if not, use a dynamic range. See Debra's page here
 

Dosaparks

New Member
Joined
Nov 23, 2010
Messages
12
Thanks for your reply.

The 'table' option does look like a good idea.

Unfortunately it is not automatically expanding though when new data comes through. I have an advanced filter macro that drives the new data onto the 'data' worksheet.

So basically

> the data gets dumped into a 'raw' worksheet,
> the user selects their criteria (only show me these products and these regions etc.
> They click 'Update Charts' which triggers an advanced filter to the 'Data' worksheet (filters the 'raw' data into a separate worksheet)
> The macro also refreshes the pivot tables which updates the charts.

So when they click 'update charts', the new data is coming across into the 'Data' worksheet, but the 'Table' I have setup is not including it. So the pivot tables fail to pick up the new data.

If I manually 'convert to range' and then re-apply the table, it picks it up however when I record a macro it doesn't actually provide any code for this action (I thought I could just add that step to my overall macro and it would be fine)

So I think I am close, just need to work out how to get the table to update to include the new data.

Thanks for your time looking into this.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,146
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you're adding the data to the sheet using a macro, you don't actually need a table or dynamic range, you can simply name the range after you add the data:
Code:
Sheets("data sheet name").Range("A1").Currentregion.Name = "PivotData"
for example.
 

Dosaparks

New Member
Joined
Nov 23, 2010
Messages
12

ADVERTISEMENT

That works perfectly!

I don't know a lot about macros but I am so inspired to learn when I see the wand waving wizardry that appears on these forums.

Thanks for waving your wand in my direction :)

Let me know if I can 'credit' you points or something for solving my problem. Not sure if I'm supposed to mark this one as solved.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,146
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
We don't do points or any of that stuff, nor do we close questions (there may always be a better/different answer to be posted later) so a simple thank you is all that's required. :)
 

jpford

New Member
Joined
Oct 8, 2014
Messages
1
I actually may have stumbled on another macro-free solution for this. In the Pivot Table, click the drop-down carrot for the field you want to remove blanks from, click "label filters," then "does not equal," and type in the box "(blank)." This will filter out any (blank) records, but does not seem to restrict new entries or require clicking the "(blank)" box the same way directly filtering it does.
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top