I have developed, in Excel 2003, (now don’t yell at me about using his ancient version of Excel - I just haven’t seen any good reason to upgrade) a rather large workbook consisting basically of dates and numbers representing the income of a small company that I do books for. This needs to be sorted on a particular column showing in what form the income came in or went out. (Check numbers, etc.) This workbook has been broken down into months, so there are 12 worksheets in total, from January to December.
The problem is: the sheets representing January thru November have been sorted with no problem. However, after I select the required data in the December worksheet, the menu item “sort” under the “DATA” section has been grayed out, and the program will not sort anything. I’ve checked the formatting, which all seems OK - ie., just the same as the previous eleven worksheets, and all looks fine.
If any of you Excel gurus have any idea of what may be wrong, I would be most grateful. I’ve need this data to give to our tax accountant for the 2017 taxes, so this is a little critical. Thanks in advance for any light you can shed on this.
In googling this (no worries, my Google Fu is strong), I found two potential issues
1> The worksheet is protected.
2>
"I just had this problem myself, and here’s what I discovered for my particular problem (relates to Excel 2010 in my case): the file had been sent to me with a portion of the worksheet having table formatting applied to it. If I selected only within the area that had been formatted with Table formatting, or only outside of it, I was OK to filter. Select anything both inside and outside the table-formatted area? No more Filter (and many other Data ribbon functions as well).
To confirm if this is your issue, look at the ribbon when you select inside the area where you are able to filter. You should see the Table Tools ribbon appear (a Design tab should be underneath it). Click in an area of the spreadsheet where you selected and noticed the issue of the Filter function graying out, and the Table Tools ribbon should disappear.
If this is the case, I fixed it by clicking within the table-formatted area, going to the Table Tools ribbon (under the Design tab) and clicking Convert to Range. A dialog box asking if you want to convert the table to a normal range will pop up; click Yes. You may see a slight difference in your worksheet, but after this, you should be able to select any columns you want and filter them all."
Let me know if either of these options resolve anything. The second one is basically the same as **guizot’s **answer, but with instructions.
Went back to the offending unsortable program after letting it sit in memory for several hours. Opened it, tried selecting and sorting one more time, and it worked perfectly. Go figure.
They say that the definition of insanity is trying the same thing time after time, and expecting different results. I guess when messing with computers this rule isn’t inviolate.
Thanks for those of you who tried to help. I really appreciated it, even it I didn’t have to try the methods you folks presented.
It happened to me when I took one file and appended it to another, and then tried to sort. The part that I had appended was somehow considered to be a separate table–apart from the existing data. (I don’t know why that happens.) So I couldn’t sort the whole thing, and had to indicate that the appended data was just more of the same data, (not a separate table), by doing the “convert to range” thing. It was–in total–several thousand rows, so it took me a while to locate the problem. The part that was considered “not a range” didn’t visually appear any different.
Hey, no worries. I wasn’t taking a shot at you or anything, I just found those instructions on line and noticed they covered the same Convert to Range thing you had mentioned but had instructions on how to get there, which aren’t always obvious or intuitive in Excel.
