• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error message when using data>filters with sorting

cricket1001

New Member
I have a worksheet (1) that includes DOB, Age, DOH, Years Worked as headings. I have all the data highlighted including the headings then data>filter so that I can sort the info by any heading. I tried to do a sort in the years worked column from Largest to Smallest, but I keep getting an error message saying that I have to make all the merged cells the same size. But there aren't any merged cells. I have hidden rows, which are civilians, so I can sort only the officers. I have another worksheet (2) in the same workbook that has only the officers with their DOB, age, DOH, years worked, and I can do the data>filters and then sort. Can I not do this when I have hidden rows? Is there another way of doing a sort in data>filters where I can choose only officers and then sort by DOH?

Thanks,
Pat
 

Chihiro

Excel Ninja
Hidden row should have no relevance... but I'm not sure "HOW" you are trying to filter and sort list.

Try following.

Select range, apply Filter to range. Then use one of the column to filter out civilians. Then use filter button to sort by years worked.

If it doesn't work, then there must be merged range. Make sure that there aren't any merged range, be it horizontal or vertical merge.

If you have issue after checking both, then I'd recommend uploading sample workbook (i.e. make copy of your workbook, then replace sensitive info with desensitized info, otherwise retaining original's setup and data structure).
 

cricket1001

New Member
Thanks for your reply. I just found out what was merged and I don't understand why it would effect the filters and sorting because it wasn't a part of the cells I was filtering or sorting. I had merged about 5 cells that were at least 10 rows below the cell range I was using in the filters and sorting. I unmerged those cells and voila the filtering and sorting now works. But I don't understand. I have the title of the worksheet in row A1:M1 and that is merged. But that didn't bother the filters and sorting. I thought the same would be true for merged cells below the range I was using in the filters and sorting, A2:M35. I just tried to merge the cells F38:F41 and and again the filters and sorting won't work. Why would merged cells outside the range of cells used in the filter cause the filter not to work? Would there be a work-around so that I could merge some sells below the range of cells to be filtered.
 

pecoflyer

Active Member
Merged cells are nothing but trouble and should be avoided as often as possible.For cosmetic purposes " Center across selection" will do the trick.
 

Marc L

Excel Ninja
Hi !​
Why would merged cells outside the range of cells used in the filter cause the filter not to work?
Just 'cause it's how Excel works !​
The entire merged cells must be included in the range to filter and - maybe - your filter may work …​
 

Chihiro

Excel Ninja
A2:M35. I just tried to merge the cells F38:F41 and and again the filters and sorting won't work
This leads me to believe, your filter range does include range that is merged. Likely due to some data appearing beyond A2:M35 range.

If you manually select A2:M35 range, and if there are not overlapping filtered range. You can sort. Tested on Excel 2019 and confirmed.

However, as others have mentioned, merged cell, in anything but the final report for visual organization is bad. If you need to perform any operation; filter, calculate etc, on the data, then avoid use of merged cell at all cost.
 

cricket1001

New Member
Thanks for all of your input.
"Pecoflyer" How do I "Center Across Selection"? I put that in the search box in excel and nothing came up with "Center Across Selection".
 

GraH - Guido

Well-Known Member
To the why "merged cells" are evil, simply convert your range to a table (which has auto filter always on). If it contains merged cells, they will be gone and you won't be able to force them inside the table (the option is greyed out). As a single data field cannot be part of multiple columns.
 
Top