How to lock a column width in a worksheet with slicers and a pivot search....???

[Jibbah24]

Good morning all....

I am in need of some urgent help with an issue I'm having with formatting in a worksheet. I've have images that I hoped would help explain my problem but am being advised I can add them....



Image 1 shows a worksheet which includes a pivot chart and a number of slicers.


Image 2 shows that behind the bottom three slicers you will see a search box with the arrow normally showing to the right which allows me to search that slicer as there isn't a search function built in.

I found this work around explained here..... excelcampus.com/pivot-tables/add-search-box-to-slicer/

Everything works fine until I change the search parameters.


Image 3 shows the result of my having selected on India in the top left slicer. The column in which the search box is in (U) has narrows considerably which has messed the formatting up.

I have managed to prevent this from happening with the actual slicers as there is an option in the Properties - 'Don't Move or Size with Cells' but this is not available for the search box.

Would be extremely grateful for any assistance with this issue.

Cheers
Spencer

[kersplash]

Can you attach your workbook?

Go Advanced -> Manage Attachments -> Upload

[teylyn]

Hello, I assume you use pivot tables with just the filter visible to achieve the slicer / searchbox trick. You need to open the Pivot Table Options for each of these pivot tables and untick the box to "Autofit column widths on update".

cheers, teylyn

[Jibbah24]

Please see the pictures below...

Pic 1.JPG

Pic 2.JPG

Pic 3.JPG

I have already unchecked the 'Autofit Columns on update option'

Thanks in advance

[teylyn]

This is hard to solve without seeing the original file. I'm sure it is some of the pivots automatically adjusting column width. The culprit may not even be visible on the screenshot. Check all of column U to see if there is a pivot table that changes its column width automatically.

[Jibbah24]

I'm unable to upload a file small enough to show the issue.....

I'm a man on the edge

[kersplash]

Delete most of the data, leaving a small desensitised sample. Refresh the pivot tables and then check that the issue is able to be reproduced with the sample size used.