Ravindra BagaleCourses & study guides Track your progress

Guides

Sort by Several Columns, Custom Sort Order and Advanced Filter

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Rani's manager asks for the September orders sorted by city, and inside each city by the biggest amount first. Then he wants Priority in the order High, Medium, Low (not alphabetical). Finally he wants a separate list of Pune orders above ₹2,000 plus every Nagpur order. Sort handles the first two; Advanced Filter handles the last.

Data > Sort can sort by several columns at once (levels) and by a custom order such as High, Medium, Low. Data > Advanced (Advanced Filter) filters with a small criteria range where conditions on the same row mean AND and conditions on different rows mean OR, and it can copy the result to another place.

Download the practice file (sort-filter-advanced-filter.xlsx)

Excel file, 7 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

  1. Click inside the data and choose Data > Sort.
  2. Sort by City, A to Z. Click Add Level: then by Amount, Largest to Smallest. Click OK.
  3. For High-Medium-Low, set Order > Custom List, type High, Medium, Low, click Add and OK.
  4. For AND/OR conditions, type a criteria range with the same headers (H1:I3) and fill the conditions.
  5. Click Data > Advanced, set List range and Criteria range, choose Copy to another location, and click OK.

Multi-level sort

  1. Click any cell in the list (do not select one column only, or the rows will break apart).
  2. Data > Sort. Tick My data has headers.
  3. Sort by City, Sort On Cell Values, Order A to Z.
  4. Add Level: Then by Amount, Largest to Smallest.
  5. OK. Pune rows sit together, biggest amount first.

Order of levels matters

The first level is the main group. Use Move Up / Move Down arrows in the Sort box to change which column wins.

Custom order: High, Medium, Low

  1. Data > Sort, Sort by Priority.
  2. Under Order choose Custom List...
  3. Type High, Medium, Low in List entries, click Add, then OK.
  4. High rows come first (12 of them in the practice file), then Medium, then Low.

The custom list is saved in your Excel, so next time it is already in the list. Month names (Jan, Feb...) and day names are built-in custom lists.

Advanced Filter with AND and OR

CityAmount
Pune>2000
Nagpur

This criteria range sits in H1:I3. Row 2 means City is Pune AND Amount is more than 2000. Row 3 is a separate OR condition: City is Nagpur (any amount).

  1. Click inside the orders list.
  2. Data > Advanced.
  3. List range: $A$1:$F$25. Criteria range: $H$1:$I$3.
  4. Choose Copy to another location and set Copy to: $K$1.
  5. Click OK.

The practice file expects 5 rows (2 Pune orders above ₹2,000 and 3 Nagpur orders). Cell I6 checks this with =COUNTIFS(C2:C25,"Pune",F2:F25,">2000")+COUNTIF(C2:C25,"Nagpur").

Headers must match

The criteria headers must be spelled exactly like the list headers. A trailing space in City gives zero results.

Ravindra Bagale's Tip

Before any big sort, add a column called Row No with 1, 2, 3... (Fill Series). If the sort goes wrong, sort by Row No and you are back to the original order.

Practice task

  1. Open the Orders sheet and sort by City (A to Z) and then Amount (Largest to Smallest).
  2. Sort by Priority with the custom list High, Medium, Low.
  3. Run Advanced Filter with the criteria in H1:I3 and copy the result to K1.
  4. Count the copied rows. Expect 5, the same as I6.
  5. Add a third criteria row (Kolhapur) and run the filter again.

Learn it properly

Course lessons:

Related guides: FILTER Function · Excel Table vs Normal Range: When to Use Ctrl + T · UNIQUE, SORT and SORTBY: Lists That Update Themselves · Remove Duplicates

Frequently asked questions

Why did my sort mix up the rows?

Only one column was selected, so Excel sorted that column alone. Undo (Ctrl + Z) and sort with one cell selected inside the data, or choose Expand the selection.

Does Advanced Filter update by itself?

No. Run it again after the data changes. In Microsoft 365, the FILTER function updates automatically.

How do I get unique rows with Advanced Filter?

Tick Unique records only in the Advanced Filter box.

Can I sort left to right?

Yes. In Data > Sort > Options, choose Sort left to right.