How to create a custom sort when a regular sort won't do in Excel

3 years ago 384

Not each sorts successful Excel are alphabetical. Sometimes, you request to benignant by an irregular acceptable of terms, and erstwhile that happens, you request to make a customized sort. Here's how.

Sorting is casual successful Excel; you virtually click an enactment and Excel does the rest. Occasionally, if Excel's not definite however galore columns to see successful the sort, it volition ask, but different than that, sorting is 1 of the simplest tasks you'll execute successful Excel. I'm talking astir ascending and descending sorts, which won't ever beryllium adequate. For instance, what if you privation to benignant by the days of the week: Monday, Tuesday, Wednesday, and truthful on? A elemental benignant won't get the occupation done. That's wherefore Excel includes a customized benignant feature. You tin make a unsocial benignant order, specified arsenic the days of the week. This nonfiction volition amusement you however to make a customized benignant erstwhile you person an irregular benignant order.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

I'm utilizing Microsoft 365 desktop connected a Windows 10 64-bit system, but you tin usage earlier versions. Excel Online volition tally an existing customized sort, but you can't make them online. For your convenience, you tin download the objection .xlsx and .xls files. This nonfiction assumes you person basal Excel skills, but adjacent a beginner should beryllium capable to travel the instructions to success.

How to benignant by presumption successful Excel

A customized benignant is 1 you define. For instance, you mightiness request to benignant T-shirts by small, medium, ample and extra-large. Or you mightiness benignant temperatures by cold, lukewarm and hot. A regular benignant can't grip these types of requirements. In this case, I'll notation to these benignant elements arsenic "terms." Fortunately, utilizing the Sort feature, you tin easy specify a customized benignant that handles terms.

There are 2 ways to initiate a customized sort:

On the Home tab, click Sort successful the Editing radical and click Custom Sort.
On the Data tab, click Sort successful the Sort & Filter group.

Now, let's instrumentality connected a contrived example—I'm doing truthful due to the fact that it is truthful unique, and galore of you volition person peculiar sorting requirements that marque nary consciousness to anyone other but you. Figure A shows a elemental information acceptable of regions. You could benignant alphabetically, some ascending and descending. But let's suppose, instead, that you privation a circumstantial benignant order: Southwest, Central, Northwest. Neither an ascending nor descending benignant volition work. Southwest, Central, and Northwest are the benignant terms.

Figure A

To make this sort, bash the following:

Click anyplace wrong the information set.
Click the Data tab and past click Sort successful the Sort & Filter group. (This way requires 1 little click than the Home tab.)
In the resulting dialog, take Region from the Sort by dropdown due to the fact that you privation to benignant by the Region values.
Leave Sort On with Cell Values, the default.
From the Order dropdown, take Custom Sort, which volition unfastened a caller dialog, wherever you tin make the customized benignant (list).
In the List Entries control, participate Southwest, Central, Northwest—the benignant presumption successful the bid by which you privation them sorted (Figure A).
Click Add to determination the caller database to the Custom Lists power connected the near (Figure B).
Click OK to instrumentality to the Sort dialog, which volition present show the caller database successful the Order power (Figure C).
Click OK to benignant the information set.

Figure B

Figure C

As you tin spot successful Figure D, the information acceptable present sorts by the customized list. Southwest sorts first, followed by Central, and past Northwest. Custom sorts don't ever marque sense, but it's large to cognize however this works.

Figure D

To reuse the sort, click Sort and take Custom Sort from the Order dropdown. Choose the database successful the Custom Lists power and click OK.

You mightiness beryllium wondering if a information acceptable tin person much than 1 customized sort. Yes, it can. Let's make a 2nd customized benignant that sorts by the Personnel values successful the pursuing order: Rosa, June, James, Martha, Luke, Mark. To bash so, repetition the instructions supra until measurement 3 and take Personnel from the Sort By dropdown. At measurement 6, participate Rosa, June, James, Martha, Luke, Mark, arsenic shown successful Figure E. Click Add, and past click OK twice. Figure F shows the results. You tin adhd arsenic galore customized sorts arsenic you need.

Figure E

Figure F

Admittedly, the examples are a spot contrived and mightiness not marque sense—unless you're the idiosyncratic asked to benignant records this way. Then, knowing however to usage this diagnostic volition marque you look great!

Stay tuned

Excel supports a 2nd customized support: sorting by aggregate columns. In a aboriginal article, I'll amusement you however to usage this aforesaid diagnostic to benignant by aggregate columns.