The general Rank function only can rank correctly while the values are numerous, if there are some text needed to rank by alphabetical order, it will get the errors. Do you have any ways to quickly rank data alphabetically in Excel?
Here I introduce a formula which does you a favor.
Select a cell where you will place the result, type this formula =COUNTIF($B$2:$B$10,"<="&B2) and drag fill handle down to the cells which need to fill with this formula.
In the formula, B2:B10 is the range you want to rank by alphabetical, B2 is the first cell you want to rank.
Tip.If you want to sort names by the last name, please try to use the Kutools for Excel’s Advanced Sort as shown in the following screenshot. It’s full function without limitation in 30 days, please download and have a free trial now.
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
This comment was minimized by the moderator on the site
One tip for this method, if you are sorting data which includes text Excel might recognize as a date - make sure to modify the data before sorting!
For example, if you have the following data
ABC01
CCC07
MAR02
FEB05
DEL78
The above method won't work, and will ignore MAR02 and FEB05. Try adding a helper column which concatenates the data with something harmless first, before applying the sort.