Say I have a spreadsheet containing names, dates and a rank.

The spreadsheet is by default sorted by date but I want to create a filter view that sorts the spreadsheet by the rank column, i.e., "General" at the top and "Private" at the bottom.

How would I do this with a custom filter view formula?


This is such a common need that the generalized answer needs to be stated, despite the age of this particular question. Whenever sorting by something Sheets cannot natively understand, one should provide a mapping between the values and some equivalent ordinal numbering.

For neatness perhaps do this on another Sheet "tab" in the same file. Create your mapping from the real-world values and their numerical equivalent—in this case, a list of ranks. A tiny subset, for example:
Column A containing four US Army ranks and Column B with those rank's numbers, all on a new Sheet named Ranks

Then back in your main list's sheet, you can just add a column where each person's rank will be automatically looked up and shown via a formula. Here, I did so in the new Tier column:

Sheet1, containing four populated columns: Rank, First, Last, and Tier. The tier column is populated with rank numbers matching each row's rank from column A as looked up from the Ranks sheet.

Then a formula such as the following can check each row's rank, match it up with the equivalent order number from the other sheet, and show that number here. This column may then be used for sorting, and either hidden or deleted.


Several formulas could be used, but in that example, in D2 I used VLOOKUP to take column A's text "Captian", find it in the Ranks sheet, and show Captian's corresponding order number, 21. After dragging that formula down the column, each Rank will be converted into it's matching order number, making the entire sheet ready for easy sorting.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.