+ Reply to Thread
Results 1 to 7 of 7

Help removing Blank from Slicers please

  1. #1
    Registered User
    Join Date
    12-18-2013
    Location
    Kings Lynn, England
    MS-Off Ver
    Excel 2019
    Posts
    68

    Help removing Blank from Slicers please

    Hi,
    I have put together a file that has stock store in internal and external stores.
    The first tab that shows just the stock by location seems to work okay, but the next two tabs I have use by date on, and end up with a field in the slicers that says Blank, and makes it a bit muddly to read.
    What I would like ideally, is all of the data on the first tab in one graph, but I am not sure how to do that with all of the Blanks that appear?
    Could anyone offer any help on this please?

    Thanks on advance.
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,349

    Re: Help removing Blank from Slicers please

    Well... your table structure is the cause.

    You should get rid of D, E, I & J columns (in Sheet8 for an example). These aggregate calculation should be done using PivotTable.
    That way, you shouldn't have so many blanks.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    12-18-2013
    Location
    Kings Lynn, England
    MS-Off Ver
    Excel 2019
    Posts
    68

    Re: Help removing Blank from Slicers please

    Hi,
    many thanks for your reply.
    I have tried getting rid of the four columns you mentioned (using sheet 8 as the example), and unfortunately it does not seem to make any difference. That is what I am struggling with, as if you remove those columns then there is no gaps in the dataset (as you were alluding to with the structure ) it still has the blanks!! I have attached what I have done for reference.
    Thanks again.
    Attached Files Attached Files

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,349

    Re: Help removing Blank from Slicers please

    Do you mean something like attached?

    See Sheet3 & how data structure is changed in Sheet8.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-18-2013
    Location
    Kings Lynn, England
    MS-Off Ver
    Excel 2019
    Posts
    68

    Re: Help removing Blank from Slicers please

    That's great thanks. Is there a way to create a slicer for the content of column B on Sheet 3? If 1 was to put a "1" in sheet 8 on each row, that would do it I think, but would probably make the graph show everything twice?
    Thanks again for your help.

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,349

    Re: Help removing Blank from Slicers please

    You mean you want slicer based on value field? That's not really possible.

    Unless you create intermediate table and create pivot table off of it. However, by doing so, you'll lose detail on other columns (Use by, Kilo's etc).

    Another method, though I'd not recommend it, is to add helper column with COUNTIFS().

  7. #7
    Registered User
    Join Date
    12-18-2013
    Location
    Kings Lynn, England
    MS-Off Ver
    Excel 2019
    Posts
    68

    Re: Help removing Blank from Slicers please

    Hi,
    thanks for your reply. I think that has answered all my questions and has allowed me to do what I wanted.
    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1