07-24-2020 08:52 AM
07-24-2020 08:52 AM
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.
07-24-2020 09:10 AM
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
07-24-2020 09:19 AM
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.
07-24-2020 10:39 AM
@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.
07-24-2020 11:22 AM
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.