Slicers

[DaleWilson]

I am new to using slicers and need heeeeelp. I have a basic table (not a pivot table). Currently have 6 column. It will be a shared file so users will be add rows and it will grow. I know I can filter each column from the header but would like to use slicers to filter multiple columns at the same time time to drill down to find certain data. Problem is, currently when I start drilling down I may end up with the desired data on just a few rows, which resizes my slicers to fit into those few rows or eliminates some slicers altogether. Is there a way to separate the slicer from the cells, so to speak. I can move them to another sheet and split the window but this will be shared and I can see having to fix it all the time. Hopefully I have asked the question to represent my issue.

Thanks so much for any help provided. I can add screen shots it it will help.

[#2]

@DaleWilson https://www.wallstreetmojo.com/slicers-in-excel/

[#3]

@DaleWilson 

I just want to make sure that you're aware not only of slicers but also of the recently introduced FILTER and other "Dynamic Array" functions.

From your description of what you're trying to do, I think it's entirely possible that you'd be able to accomplish it with FILTER. It allows you to use criteria from multiple columns as the basis for the filtering it does..... And you can do it on a separate sheet in the same workbook quite easily (thus creating a form of Dashboard, extracting data from the larger table based on current need).

Is the spreadsheet you have one you can share or does it contain lots of confidential info (if the latter, then it should be rendered anonymous before sharing)?

Here's a link to a YouTube video that explains and demonstrates the dynamic array functions, if you'd like to just pursue it on your own. https://www.youtube.com/watch?v=9I9DtFOVPIg

[#4]

@wsantos 

Thanks for the reply. That's exactly how I would like my slicers to work but they don't. Maybe I created the table wrong from the beginning.  When I I select an option in my slicer it narrows it down to the desired rows but the cells at the bottom of the sheet disappear. The bottom of the page turns to a grey background.

[#5]

@mathetes Thank you. I will check it out now.

[#6]

@mathetes Two screen shots attached. One is what is happening. Second is what I would like to happen.

[#7]

@DaleWilson 

Perhaps you may share a sample file instead of screenshot to check how your table is organized.

[#8]

@DaleWilson  what i believe is happening is that excel just doesn't have any more cell rows 'defined'.  You could try to 'add' rows to excel to make sure you always have additional blank rows so the slicer is clipped (you should be able to scroll to a spot outside the table and add some spaces or something or maybe someone else will chime in with a 'better' way).  OR you could just move your table down and put the slicer(s) ABOVE the table.  Even 1 row and you can adjust the height of row 1 to fit the size of your slicer(s).  Then if you 'freeze' the top of the table the slicer will also be 'frozen' in view.

[#9]

@mtarler OR build a pivot table first. the slicers behave better when the pivot resizes. I'd also look into using powerbi instead.

[#10]

@DaleWilson 

And an hour before you posted this you said you were going to try out the FILTER function. What happened with it? It is new, to be sure, and only on the most recent releases of Excel, so you may not have been able to get it to work if you're using an older Excel. But the feedback would be helpful.

I still think it might do what you're wanting to do, and be less mysterious, since you'd be in control.

[#11]

@mathetes I am experimenting with the filter function right now.

[#12]

@DaleWilson , can you confirm that the slicer is not set to move and size with cells? 

[#13]

@TheAntony Yes. That is the way I had it set up.  I finally found the problem. My table was set up incorrectly. There was one column that was a table of its own...separate from the rest of the table. Once I reset up the table, then the slicers did not resize.  

[#14]

@DaleWilson , Glad everything worked out!