expanding sort and filter excel 2013

Question

I've had a problem sorting in excel 2013.  This is for me to mail merge from excel 2013 to word 2013. I've done mail merge for years and never had this problem. In excel - I set up columns with first name, last names, address, etc. and will sort the spreadsheet alphabetically by last name, when the window asking to expand the selection comes up, I always click "expand" to keep the information all together with the first name, address, etc.  In the last 5 times I've done this, the addresses, emails, etc. have not stayed with the names. In other words - names, addresses and emails are all out of order. Would this make a difference if there is a hidden column or two when I sort?  I'm very frustrated with this problem. HELP!

Answer 1

You asked: Would this make a difference if there is a hidden column or two when I sort? 

The answer is yes, if at least one of the hidden columns has no entries.  The 'expanded' section only extends to any row/column that has no entries in cells adjacent to the area you are expanding.  Graphics below show results of starting a sort just choosing the LName column then responding Yes to "expand" option with a Custom Sort. I used Custom Sort so you can see what it expands to.  If you just use A-Z/Z-A sorting it happens so quickly you can't really see it unless you watch very, very closely.

As you can see, because of the empty column D, the street/city/state/zip/phone data doesn't get included for the sort.

BUT!!  By simply adding a 'dummy' label in row 1 to give the section continuation across otherwise empty rows, you get what you expect:

Answer 2

Thank you very much! I thought I'd lost my mind. This makes perfect sense. I will know what NOT to do again.