Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sort - max number of columns?
We've recently made a very long spreadsheet with about 80 columns. BAD
things happen! When people do a lazy sort - that is, ctrl home, Sort and use the populated defaults, Excel only "selects" 64 columns. Of course this totally pooches the data and creates disconnects. No problems when the user selects the whole spreadsheet (by selecting the upper lefthand corner), sorts and fills in the criteria themselves. My question is, is this for real or am I imagining it? Is there a limit of 64 columns in Excel on that lazy sort? We're dealing with the workaround, I just want to know I'm not crazy. Karen F. |
#2
|
|||
|
|||
Hi Karen,
I don't like the way that is implemented at all because it almost guarantees that everybody is going to destroy a lot of data sooner or later and probably many times over. I would suggest that you never rely of Excel to pick your sort area based on a single cell selection which it assumes you will want the current region (Ctrl+Shift+asterisk). Likewise if you had a few cells selected it would only sort the selected cells. Instead use Ctrl+A unless of course you have Excel 2003 which is also in itself dangerous because the change Ctrl+A. If you are using Excel 2003 use Ctrl+Shift+SpaceBar instead of Ctrl+A the select ALL cells and not move the activecell, before invoking the sort. What probably happened is that you had an empty column in Column 65 Of course you can sort columns independently of other columns, but that is not what you want and is not what people wanted to sort when they suddenly destroy all their data. Use CTRL+Z if you catch your mistake immediately and hope you didn't have an event macro. More information on sorting http://www.mvps.org/dmcritchie/excel/soriting.htm More information on shortcut keys in Excel http://www.mvps.org/dmcritchie/excel/shortx2k.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "KarenF" wrote in message ... We've recently made a very long spreadsheet with about 80 columns. BAD things happen! When people do a lazy sort - that is, ctrl home, Sort and use the populated defaults, Excel only "selects" 64 columns. Of course this totally pooches the data and creates disconnects. No problems when the user selects the whole spreadsheet (by selecting the upper lefthand corner), sorts and fills in the criteria themselves. My question is, is this for real or am I imagining it? Is there a limit of 64 columns in Excel on that lazy sort? We're dealing with the workaround, I just want to know I'm not crazy. Karen F. |
#3
|
|||
|
|||
Karen,
No, there isn't any limit (at least, none that I am aware of.) Are you sure you don't have a hidden column that's blank (column 65 or, appropriately enough, column BM)? Excel will sort the currentregion of the activecell, so any fully blank columns will cause that kind of disconnect. HTH, Bernie MS Excel MVP "KarenF" wrote in message ... We've recently made a very long spreadsheet with about 80 columns. BAD things happen! When people do a lazy sort - that is, ctrl home, Sort and use the populated defaults, Excel only "selects" 64 columns. Of course this totally pooches the data and creates disconnects. No problems when the user selects the whole spreadsheet (by selecting the upper lefthand corner), sorts and fills in the criteria themselves. My question is, is this for real or am I imagining it? Is there a limit of 64 columns in Excel on that lazy sort? We're dealing with the workaround, I just want to know I'm not crazy. Karen F. |
#4
|
|||
|
|||
It so happens I DO have an empty column 65, with more scattered throughout.
Now it makes perfect sense - Excel doesn't realize there's more data after that. I solved the problem by adding a blank (" ") as the column header. Thank you! "Bernie Deitrick" wrote: Karen, No, there isn't any limit (at least, none that I am aware of.) Are you sure you don't have a hidden column that's blank (column 65 or, appropriately enough, column BM)? Excel will sort the currentregion of the activecell, so any fully blank columns will cause that kind of disconnect. HTH, Bernie MS Excel MVP "KarenF" wrote in message ... We've recently made a very long spreadsheet with about 80 columns. BAD things happen! When people do a lazy sort - that is, ctrl home, Sort and use the populated defaults, Excel only "selects" 64 columns. Of course this totally pooches the data and creates disconnects. No problems when the user selects the whole spreadsheet (by selecting the upper lefthand corner), sorts and fills in the criteria themselves. My question is, is this for real or am I imagining it? Is there a limit of 64 columns in Excel on that lazy sort? We're dealing with the workaround, I just want to know I'm not crazy. Karen F. |
#5
|
|||
|
|||
I'd select the whole range and do the sort. I never let excel guess.
It scares me too much. KarenF wrote: It so happens I DO have an empty column 65, with more scattered throughout. Now it makes perfect sense - Excel doesn't realize there's more data after that. I solved the problem by adding a blank (" ") as the column header. Thank you! "Bernie Deitrick" wrote: Karen, No, there isn't any limit (at least, none that I am aware of.) Are you sure you don't have a hidden column that's blank (column 65 or, appropriately enough, column BM)? Excel will sort the currentregion of the activecell, so any fully blank columns will cause that kind of disconnect. HTH, Bernie MS Excel MVP "KarenF" wrote in message ... We've recently made a very long spreadsheet with about 80 columns. BAD things happen! When people do a lazy sort - that is, ctrl home, Sort and use the populated defaults, Excel only "selects" 64 columns. Of course this totally pooches the data and creates disconnects. No problems when the user selects the whole spreadsheet (by selecting the upper lefthand corner), sorts and fills in the criteria themselves. My question is, is this for real or am I imagining it? Is there a limit of 64 columns in Excel on that lazy sort? We're dealing with the workaround, I just want to know I'm not crazy. Karen F. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort data in columns | Excel Discussion (Misc queries) | |||
worksheet columns changed from letter to number, how change back | New Users to Excel | |||
limited number of columns | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions | |||
Change number of columns mid page? | Excel Worksheet Functions |