Forum Discussion
Excel Custom Sort Beyond Default Range
There are several columns in an Excel spreadsheet. From the Home page when clicking on Sort & Filter, then selecting Custom Sort, then going to the Sort by option it goes up to column SI, then gives a confusing format on how to select a column beyond SI. In this case the sort is on column SP. What needs to be done to sort by column SP?
- Thanks, Detlef, that's the example I was looking for! When I click on the "(More columns...)" and put SP in the box, then click OK, I get an error message. However, when I type in SP:SP it works perfectly. That's not intuitively obvious from the message, and I was unable to find that answer in any of the Help sections. Thanks again!
9 Replies
I am working on a spreadsheet that has columns A-S however, when I attempt to sort the data the AZ Sort and filter icon is grayed and will not allow me to select the option. What is going on?
There are several columns in an Excel spreadsheet. From the Home page when clicking on Sort & Filter, then selecting Custom Sort, then going to the Sort by option it goes up to column SI, then gives a confusing format on how to select a column beyond SI. In this case the sort is on column SP.
"...SEVERAL columns" !!??!! Running beyond SI to (and beyond?) SP ???!!!
I can't even conceive of a table of data that has that many columns, and it's certainly more than "several," unless the first column is something like column SE. But even then, it boggles the mind to attempt to picture what sort of spreadsheet this is.
So I hope it's not asking too much to request that you give us an explanation of the overall structure of that Excel spreadsheet you're working with. Are columns A through column SP (and possible beyond) actually full of data? Does the table in question begin somewhere to the right of column A, and if so, where?
That aside--and I (and I suspect others) really would appreciate a clear description of what we're working with here--that aside, there is a SORT function, separate from the options available across the tool bar, and perhaps you could use it instead, but if we were sitting down face-to-face and I were a consultant you'd brought in, I'd want to understand your situation more fully before pointing you to any solution.
- I am working on a trust fund that has multiple accounts and several beneficiaries so I have to track the data for each bank and each individual.
Thanks for the reply and appreciate the comments.
We provide a marketing research service called PBRMarketTools. One of those services is ThePBR1000, which is tracking 1,000 of the largest publicly held corporations in the US. The spreadsheet has basic info like name & address info. In addition we provide 5 years of info on revenues, profits, earning per share, share outstanding, dividends, assets, equity, 5 years of many related ratios, number of employees, 10 years of monthend stock prices, several analytics on those stocks, 5 years of price earnings ratios, several years of monthend market capitalization values, number of equivalent shares, key information on the board, management, and the CEO, CEO pay ratios info, and much more. The spreadsheet starts in Column A, currently ends in Column XT.
The spreadsheet is also divided into 3 sections, all with the same format. The first third are the 1,000 rows of current The PBR1000 companies, there is a section of around 500 Prospect companies, and another section made of of around 500 companies that are no longer on The PBR1000 through acquisition, going private, going bankrupt, etc. In all there are about 2,500 rows.
Column SP is the date we last updated the CEO info. Periodically we sort from oldest to newest and update the info. We only do that for The PBR1000.
When we select the rows 8-1007(The PBR1000 companies), go to the Sort & Filter, Click on the Custom Sort, it only shows through Column SF, then has an option for more columns, but displays a format that is a little cryptic.
Hopefully that explains the structure of the spreadsheet for you and why it has so many columns.
Thanks again, hope this helps you understand the situation.
It seems you reached one of many restrictions in Excel.
It is very clear what the message says:
Click on the button on the right, select the column, click on the button on the right again, click OK.
Alternatively you can type the column reference into the box: TS:TS
