Avoiding (blank) in row label fields

[Ken Puls]

I'm working with a team member on setting up a file to keep track of a maitnenace checklist. She's got tasks that will be repeated daily, weekly, monthly, quarterly, etc.. Her initial attempt was to set up a page for each type of task and was manually entering them all on a summary sheet. Yikes!

She asked for advice, so I'm working with her on setting up a master data page, and we're creating pivot tables to show the weekly, monthly, etc... pages.

Here's the thing though.... there are no numbers to throw in the "Data" section of the report. We're basically resummarizing most of the info in the row label fields, and building a list that way. I have the file set to display in tabular format with repeating labels (Excel 2010.)

The kicker is that if there are blank cells in my data, the table fills them in with "(blank)". Changing the pivot table options to display nothing for blank cells doesn't seem to have any effect... I'm guessing this is for the values section, not the labels section.

I can skirt it by putting a single quote in every blank cell in my data area (it's not blank then), but that's a maintenance nightmare and really shouldn't be necessary. There is no way I can rely on users to keep that up.

I can also apply conditional formatting to the pivot table to colour the cells white if they contain the value "(blank)". It works, but when the pivot table expands, the conditional format range doesn't get adjusted. I know I can get around it by covering a huge amount of cells with the conditional format, but that seems like a lot of overhead.

Does anyone have any better suggestions on how to hide blank values in the row labels field, or an I stuck with workarounds? It seems to me that there should be a built-in way to make this happen.

[JeffreyWeir]

I have trouble conceptualising things like this. Any chance you could post a sample file?

[Ken Puls]

You bet I can. I even will!

(I actually would have earlier, but didn't have the time to sanitize...)

[eferrero]

Hi Ken,

Just go to the Pivot Table, overwrite one of the (blank) cells with a space - all the (blank) cells are then shown as space, so they look really blank.

Or, right-click a Row-label heading, select Filter -> Label Filters, does not equal (blank)

[Ken Puls]

...go to the Pivot Table, overwrite one of the (blank) cells with a space - all the (blank) cells are then shown as space, so they look really blank.

Tricky! And it works. Weird... not exactly intuitive, is it? I tried to just delete the value, but it doesn't like that at all.

Or, right-click a Row-label heading, select Filter -> Label Filters, does not equal (blank)

That one unfortunatley doesn't work for me as it filters out the entire row. I want the rest of the data, just don't want the blanks to show as "(blank)"

Thanks Ed!

[eferrero]

Tricky! And it works. Weird... not exactly intuitive, is it? I tried to just delete the value, but it doesn't like that at all.

Not too weird if you think of it as renaming a label value. We have always had this ability when grouping fields in a pivot table. Using it on field values can become very confusing, very quickly - I would document any changes made this way somewhere in the workbook if I were you.

[Ken Puls]

Interesting... I guess that does make sense in a way then. I still wish it would just show blanks as blanks by default though. I'd expect that it would work the other way around, to be honest... show nothing if there are blanks and let me rename it if I wanted "(blank)"

[winxalot]

Thanks! This is still helping in 2013!

[nivenor1]

Hi,
On the Mac 2011 version of excel, even putting zeros into all of the value cells does not solve the issue of (Blank) row labels in the event that the first value column has a zero...Anyone able to explain that? If I add .0000000001 to all the cells in the values Column 1, all works just fine, (blank) labels disappear. So I solved it, but I don't like the idea that I had to alter my data, even by a teeny amount, to do so.

Hi Ken,

Just go to the Pivot Table, overwrite one of the (blank) cells with a space - all the (blank) cells are then shown as space, so they look really blank.

Or, right-click a Row-label heading, select Filter -> Label Filters, does not equal (blank)