Results 1 to 9 of 9

Thread: Avoiding (blank) in row label fields

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,720
    Articles
    76
    Blog Entries
    14

    Avoiding (blank) in row label fields



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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, CMA, MS MVP (Excel)

    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.

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    324
    Articles
    0
    I have trouble conceptualising things like this. Any chance you could post a sample file?

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,720
    Articles
    76
    Blog Entries
    14
    You bet I can. I even will!

    (I actually would have earlier, but didn't have the time to sanitize...)
    Attached Files Attached Files
    Ken Puls, CMA, MS MVP (Excel)

    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.

  4. #4
    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)

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,720
    Articles
    76
    Blog Entries
    14
    Quote Originally Posted by eferrero View Post
    ...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.

    Quote Originally Posted by eferrero
    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!
    Ken Puls, CMA, MS MVP (Excel)

    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.

  6. #6
    Quote Originally Posted by Ken Puls View Post
    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.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,720
    Articles
    76
    Blog Entries
    14
    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, CMA, MS MVP (Excel)

    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.

  8. #8
    Thanks! This is still helping in 2013!

  9. #9

    Similar problem, Mac 2011 excel (Blank)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.



    Quote Originally Posted by eferrero View Post
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •