I have trouble conceptualising things like this. Any chance you could post a sample file?
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.
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
I have trouble conceptualising things like this. Any chance you could post a sample file?
You bet I can. I even will!
(I actually would have earlier, but didn't have the time to sanitize...)
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
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)
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.
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)"Originally Posted by eferrero
Thanks Ed!
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
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.
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)"
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Thanks! This is still helping in 2013!
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.
Bookmarks