Excel table greying out and adjust slicer size

[keinz]

Hi everyone,

Looking to get some assistance on this problem I cannot seem to solve...

I cleaned up a data dump into a Table so that I can apply a slicer to filter the data.

I am not using the Pivot table - as it cannot be adjusted to how I want the data to be viewed.



Anyways... for this table I have 2 slicers that I want to filter two types of data

1. Date
2. Department

The sheet has around 3000 rows and the slicer is roughly about 20 row tall and 3 rows wide.
Whenever I filter the data with the slicer and there are less rows than 20, it will resize the slicer.

In the event there are only 1 row of data - the slicer will then become unusable unless I manually resize it.
Whats weird is when the filters are applied, the bottom of the sheet is all greyed out - which is what I think is causing the slicer to resize?

I did format the slicer:
1. Disable resizing and moving (checked)
2. Don't move or size with cells (checked)
3. Locked (checked)


Yet it still happens...
I also tried protecting the worksheet, no change.

So my main question would be... how do I prevent the slicer from resizing?


Thank you in advance

 

[Alex Blakenburg]

If you position your cursor under the table and go Ctrl+Downarrow, what it the last row number you can see ?
Do you have the rows under the table set to hidden ?

 

[keinz]

If you position your cursor under the table and go Ctrl+Downarrow, what it the last row number you can see ?
Do you have the rows under the table set to hidden ?

yes i do have rows filtered out - as they are blanks and have formulas for when new data is added in

 

[Alex Blakenburg]

My understanding is that you are using an Excel Table. Formulas in Excel tables automatically expand to new rows when you add rows to the table, so you should not need to filter them out.

Filtering should not cause you an issue. Hiding rows does.
Have you hidden rows ?

the bottom of the sheet is all greyed out

 

[keinz]

No i have not hidden rows

I did set the formatting to hide 0's - unless thats considered hiding rows?

Ideally I would like to not show the 0s to keep it clean

 

[Alex Blakenburg]

Cell formatting shouldn't affect it.
You didn't answer my post #3 though.

"If you position your cursor under the table and go Ctrl+Downarrow, what it the last row number you can see ?"

 

[keinz]

Cell formatting shouldn't affect it.
You didn't answer my post #3 though.

"If you position your cursor under the table and go Ctrl+Downarrow, what it the last row number you can see ?"

sorry about that

when I go ctrl+Downarrow - the last row number i see is 3668 then after that its all grey'ed out

 

[Alex Blakenburg]

Which means that all the rows below that are hidden, which I believe is what is causing your issue.
You need to unhide those rows.

Click on the row number at the bottom t to select the row then shift drag down (you may see an cells row number box pop up briefly) then right click and Unhide.
OR click in the top left of the sheet to select the whole sheet then right click on any row no and Unhide

 

[keinz]

Which means that all the rows below that are hidden, which I believe is what is causing your issue.
You need to unhide those rows.

Click on the row number at the bottom t to select the row then shift drag down (you may see an cells row number box pop up briefly) then right click and Unhide.
OR click in the top left of the sheet to select the whole sheet then right click on any row no and Unhide

tried both ways

nothing happened

the last row is still 3668 and then is greyed out after that

 

[Alex Blakenburg]

Do you know if you have any event macros running ?
If you are not sure can you tell me if the file an xlsx file ? if not what is the file extension