+ Reply to Thread
Results 1 to 7 of 7

Help removing Blank from Slicers please

  1. #1
    Forum Contributor
    Join Date
    12-18-2013
    Location
    Kings Lynn, England
    MS-Off Ver
    Excel 2019
    Posts
    234

    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
    Office 365 ProPlus
    Posts
    5,855

    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
    Forum Contributor
    Join Date
    12-18-2013
    Location
    Kings Lynn, England
    MS-Off Ver
    Excel 2019
    Posts
    234

    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
    Office 365 ProPlus
    Posts
    5,855

    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
    Forum Contributor
    Join Date
    12-18-2013
    Location
    Kings Lynn, England
    MS-Off Ver
    Excel 2019
    Posts
    234

    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
    Office 365 ProPlus
    Posts
    5,855

    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
    Forum Contributor
    Join Date
    12-18-2013
    Location
    Kings Lynn, England
    MS-Off Ver
    Excel 2019
    Posts
    234

    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)

Similar Threads

  1. [SOLVED] Issue with Removing Pivot Tables from Slicers via VBA
    By kadams99 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-13-2018, 08:55 AM
  2. [SOLVED] Disconnect Slicers, Change Pivot Table Source, Reconnect Slicers: a problem
    By Geoff. in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2015, 02:27 PM
  3. Replies: 9
    Last Post: 02-24-2014, 06:12 PM
  4. Removing "(Blank)" from pivot table slicers
    By tnovak in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-18-2014, 08:11 AM
  5. removing blank values (blank or "0") from a dynamic chart
    By rob78 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-26-2011, 04:19 PM
  6. Removing Blank Cells
    By pickslides in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2011, 12:48 AM
  7. Removing blank cells
    By eclipsenetworkz in forum Excel General
    Replies: 2
    Last Post: 05-16-2008, 07:49 PM

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