Sorting a sheet with drop down does not work properly

Question

I am using Excel 2010 on Windows 7.

I have an Excel sheet (created by someone else who has since left) that has 13 columns, and all but the last have drop down lists. However, when I try to sort using one of the drop down lists, it does not sort the last column and the first row of data. I noticed, when I undid a sort, that it had highlighted all the data except the last column and the first row. How do I get it to include these in the sort?

Thanks!

Answer 1

My initial feeling on this is that when the drop-down lists were created there were only 12 columns of data, with the 13th added at a later date.

The drop-down lists are probably the Filter drop-downs.

Try this:

Click into your data

Select the Data Ribbon / Sort & Filter Group / Filter

This will toggle the Filters off

Then

Select all 13 columns (Just in case you have blank lines lurking in your data)

Click the same filter button again.

You should have all 13 columns with filter drop-downs now and they will always sort all the data.

May I also suggest that a list of data can be formatted as a table.

Home Ribbon / Styles Group / Format as Table.

This very effective at keeping data together.

Answer 2

Hello,

1. What is the file format of the Workbook?
2. How is the drop down list created? Is it using data validation or combo box?
3. Does it work properly when sorted without using the drop won list?

Reply with required details and we would be happy to assist.

Thank you.

Answer 3

Hi Suroora, I just wanted to check back that the ideas worked. If so, please click the Found this helpful button. If not let me know and I'll see what else may come to mind!

Answer 4

Thank you, Nayana and Mike.

Nayana, the workbook was created in Excel 2003 and I've opened it in Excel 2010 (our computers were finally upgraded!). I don't really know the answer to your second question. It seems to work fine without using the dropdown list.

I'm going to try following Mike's instructions and will get back to you if it doesn't work, or let you do if it does. Thanks for your help!