How to sort data by multiple columns in Excel

3 years ago 321

Sorting is 1 of Microsoft Excel's easiest tasks. Click; you're done! At slightest until you request to benignant by aggregate columns. For this benignant task, you'll request a customized sort. Here's how.

Sorts are a elemental task successful Microsoft Excel if each you request is an ascending oregon descending sort. Excel is astute capable that it tin construe the information range. Not each sorts are truthful easy. Fortunately, Excel offers 2 types of customized sorts: by irregular presumption and by aggregate columns. The caller article, How to make a customized benignant erstwhile a regular benignant won't bash successful Excel, shows you however to make a unsocial database of sorting elements, specified arsenic Monday, Tuesday and truthful on, oregon Small, Medium and Large. In this article, I'll amusement you however to benignant by aggregate columns. For instance, you mightiness privation to benignant a income expanse by unit and past region. The extremity effect groups the unit and past further groups each of those groups by region.

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

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.

Please usage whichever you similar passim the article.

I'm utilizing Microsoft 365 connected a Windows 10 64-bit system, but you tin usage older versions. For your convenience, you tin download the objection .xlsx and .xls files. Excel Online supports sorting by aggregate columns.

How to benignant by 2 columns

Grouping is simply a communal database task, and sorting by aggregate columns is fundamentally the aforesaid thing. You person a superior benignant connected a circumstantial file and past a secondary benignant wrong the results of the superior sort. You're not constricted to 2 columns either, but we'll support the examples simple.

Figure A shows a elemental information acceptable of income information for respective radical and the respective regions for each record. Let's accidental you privation to reappraisal the full commissions per unit breached down by the regions. This request volition request a benignant by aggregate columns; the superior benignant volition beryllium connected the Personnel column, and the secondary benignant volition beryllium connected the Region column.

Figure A

You tin benignant by unit oregon region, but utilizing the Sort options alone, you can't benignant by both. In this case, you request to physique a customized benignant arsenic follows:

Click anyplace wrong the information set, preferably successful the Personnel column, but doing truthful isn't necessary.
In the Editing radical (on the Home tab), click the Sort & Filter option, and take Custom Sort from the dropdown list.
If you clicked wrong the Personnel column, the archetypal Sort By mounting successful the effect dialog should beryllium Personnel. If you didn't, take Personnel now. The Sort On and Order options should beryllium acceptable to Cell Values and A To Z, respectively due to the fact that these are the default settings. If necessary, marque changes.
At this point, you've satisfied the superior sort. To physique the secondary sort, click the Add Level option.
From the Then By dropdown, take Region. Again, the Sort On and Order columns should beryllium acceptable to the defaults, Cell Values and A To Z (Figure A).
Now you person some benignant columns set, truthful click OK to execute the sort.

As you tin spot successful Figure B, Excel groups the names successful the Personnel file successful an ascending alphabetic sort. Then, the regions are sorted wrong each sanction group. James has 3 regions, Central Northwest, and Southwest. Then, driblet down to Martha and Rosa. Their portion groups are the same, Northwest and Southwest.

Figure B

This benignant was alternatively easy. There are lone 2 columns and they're contiguous. Let's complicate the requirements conscionable a spot with a 2nd example.

How to adhd a 3rd file to benignant successful Excel

There are precise fewer repeated dates, but determination are a fewer repeated months. Let's make a caller customized benignant connected 3 columns: Date, Personnel and Region. To bash so, we'll archetypal person to adhd a helper column—one that returns the month. To bash so, insert a file betwixt Date and Value, participate the pursuing relation successful C3 and transcript it to the remaining cells:

=Month(B3)

=Month([@Date]) (if you're utilizing a Table object)

Then, acceptable the format to General. Doing truthful volition show values that correspond the month. For instance, 1 is January, 4 is April, 10 is October, and truthful on. Now, let's acceptable up a caller customized sort:

Click anyplace wrong the information set, preferably successful the Date column, but doing truthful isn't necessary.
In the Editing radical (on the Home tab), click the Sort & Filter option, and take Custom Sort from the dropdown list.
If you clicked wrong the Date column, the archetypal Sort By mounting successful the effect dialog should beryllium Date. If you didn't before, take Date now. The Sort On and Order options should beryllium acceptable to Cell Values and Oldest To Newest, respectively. If necessary, marque those changes.
At this point, you've satisfied the superior sort. To physique the secondary sort, click the Add Level option.
From the Then By dropdown, take Personnel. Again, the Sort On and Order columns should beryllium acceptable to the defaults, Cell Values and A To Z.
Click Add Level, take Region from the Then By dropdown (Figure C), and click OK to execute the sort.

Figure C

As you tin spot successful Figure D, January 31 is the archetypal day group. James is the lone idiosyncratic with a merchantability successful January, but helium has 2 and the regions are alphabetically sorted. Luke and Martha are sorted wrong the February 28 group, and some person 1 region, Northwest.

Figure D

The information acceptable is elemental connected intent truthful you tin easy discern the antithetic groups arsenic a effect of the aggregate file sorts. For each group, simply adhd a caller benignant level.