Sorting and Filtering in Excel: The One-Column Sort That Moved a 31,725.00 Invoice to Another Customer

The aged receivables list below is twelve rows long and adds up to 118,331.50. Someone wants to see the biggest debts first, so they click the header of the Amount column, drag down to the last number, and press the A–Z button with the arrow pointing the other way.

Excel asks a question. They read the first half of it, pick the option that sounds like just do what I asked, and press Sort.

The amounts are now in perfect descending order. The total still says 118,331.50. And every single amount is now sitting next to somebody else's invoice number. The 31,725.00 that Vantage Rail owes has moved to the top row and become a debt of Halvorsen Marine, who actually owe 12,480.00 on that line. Halvorsen's file goes from 13,100.00 to 44,205.00. Vantage Rail's goes from 37,255.00 to 1,915.50. The over-60-days exposure the credit committee looks at goes from 49,860.75 to 104,605.00.

Nothing on the screen is red. Nothing says #VALUE!. The column is beautifully sorted, and the grand total — the one number people check to see whether a sheet is still intact — is exactly what it was before.

This article is about the two operations everyone thinks they already know. Sorting rearranges what is stored. Filtering hides what is shown. Almost everything that goes wrong with either one comes from forgetting which of those two sentences applies.

Tip: Copy the table below into a blank sheet starting at A1 to follow along. Everything here works in any desktop version of Excel; the three functions in section 8 need Microsoft 365 or Excel 2021, and I have marked them where they appear.

1) Sorting Moves Rows. Filtering Moves Nothing.

That is the whole distinction, and it decides which one you should be reaching for.

A sort is an edit. It physically reorders the cells in your worksheet. Save the file and the old order is gone; there is no "unsort", only Ctrl+Z, and only until you close the workbook. If the order your data arrived in carries information — entry order, the order the system exported them, the order the auditor saw them in — then that information lives in nothing but the row positions, and a sort destroys it silently.

A filter is a view. It hides rows. The data is untouched, the row numbers on the left go blue and skip (12, 15, 16, 21), and clearing the filter brings everything back exactly as it was. Nothing you do to a filter can lose data — with one exception, in section 9, that costs people whole tables.

Two consequences worth having in your hands before anything else:

• If you only need to look, filter. Sorting to answer a question and then sorting back is two edits where zero were needed.
• Before your first sort of a new sheet, add a column of 1, 2, 3, … and fill it down. It costs ten seconds and it is the only thing that can put a scrambled sheet back the way it was. Sort by that column and the original order returns from anywhere.

2) The Sort Warning, and the 31,725.00

Here is exactly what happened at the top of this article.

The user selected G1:G13 — one column, the Amount column, on its own — and asked for a descending sort. Excel noticed live data sitting immediately to the left and put up the Sort Warning:

Microsoft Excel found data next to your selection. Since you have not
selected this data, it will not be sorted.

  ( • ) Expand the selection
  (   ) Continue with the current selection

"Expand the selection" is the correct answer and it is selected by default. "Continue with the current selection" means sort these numbers among themselves and leave every other column exactly where it is, which is a real thing people occasionally want and almost never want here.

Take the top four rows of the table and watch what one click does:

The invoice numbers did not move. The customers did not move. The dates, the regions and the days-overdue did not move. Only the twelve numbers in column G were re-dealt, largest first, onto whatever rows happened to be in those positions.

Why nobody catches it. Every check people habitually run still passes:

• The grand total is unchanged — 118,331.50 before, 118,331.50 after. A sort of a column against itself is a permutation, and a permutation cannot change a sum. The number most likely to be used as a sanity check is the one number mathematically guaranteed not to notice.
• The row count is unchanged. Twelve rows before, twelve rows after.
• Every individual amount still exists somewhere in the column. Search for 31,725.00 and you will find it.
• The column is, as requested, perfectly sorted.

What has changed is every relationship, and relationships are what the sheet was for:

🎯 Scenario: You have just done this, and you do not know how long ago.

If the workbook has not been closed, Ctrl+Z, immediately, before anything else — a sort is a single undo step no matter how many rows it moved. If it has been closed, and you kept no index column, the honest answer is that the file must be recovered from a backup, a previous version, or the source system; there is no formula that can reconstruct which amount belonged to which invoice, because the sheet no longer contains that information. This is why the index column in section 1 is not fussiness.

How to stop it happening. Two habits, in order of how much work they save:

1. Select one cell, not one column, before sorting. With a single cell selected inside a contiguous block, Excel works out the whole range itself and moves entire rows. No warning appears because there is nothing ambiguous to warn about.
2. Convert the range to a Table (Ctrl+T). In a Table, sorting from a header dropdown always moves whole records. There is no "continue with the current selection" — the option does not exist, because a Table knows it is a set of records rather than a rectangle of cells. This one keystroke removes the entire failure mode.

And the case the warning cannot help with: a blank column inside your data. Excel decides what counts as "next to your selection" by looking for contiguous non-empty cells. Put an empty spacer column between two blocks and Excel will sort one block without ever mentioning the other, no dialog, no warning. Same for a blank row: everything below it is a different range as far as sorting is concerned.

3) Multi-Level Sorts, and Ordering Things That Are Not Alphabetical

The A–Z buttons handle one column. Data → Sort opens the dialog that handles up to 64 levels, applied top to bottom: the first level orders the whole list, the second orders ties within the first, and so on.

For the receivables table, the sort the collections team actually wants is three levels deep:

That produces a worklist: each region as a block, oldest debt first inside it, biggest first where two are the same age.

Sort On is a dropdown too, and it is the half of the dialog people miss. Next to each level, Sort On offers Cell Values, Cell Color, Font Color and Conditional Formatting Icon. Sorting on colour brings one colour to the top per level, so a three-colour scheme needs three levels — one per colour — which is tedious enough to be a decent argument for putting the meaning in a real column instead of in a fill.

Custom lists are the answer to the regular complaint that Excel sorts things stupidly. Alphabetically, Iberia comes before UK&I; by fiscal quarter, Q4 comes after Q3 but by text so does Q10. Build the order once — File → Options → Advanced → General → Edit Custom Lists — and it becomes available in the Order dropdown of every sort, in every workbook on that machine, forever. Excel ships with the day and month lists already there, which is why Jan, Feb, Mar sorts correctly out of the box and Small, Medium, Large does not.

Two more switches live behind the Options button in the Sort dialog:

• Case sensitive. Off by default: "aslan" and "Aslan" are the same key. Turn it on and lowercase sorts before uppercase in an ascending sort.
• Sort left to right. Changes the orientation so Excel reorders columns by the values in a row. This is how you put twelve month columns back in calendar order after someone inserted August in the wrong place. Note that it sorts columns as blocks and does not touch your header column, so select the data columns only.

🎯 Scenario: A sort that has to hold for a report someone else will refresh next month.

Do not sort at all. A manual sort is a one-time edit that says nothing about what should happen to next month's rows, and nobody remembers to redo it. Either use a Table plus a saved view, or produce the ordered copy with the SORT formula in section 8, which re-sorts itself whenever the source changes.

4) The Order Excel Actually Uses

Ascending order is not simply "smallest first". Excel sorts mixed content in this sequence:

1. Numbers (including dates and times, which are numbers)
2. Text, character by character
3. Logical values, FALSE before TRUE
4. Error values, in the order they occur
5. Blanks — always last, in ascending and descending order

That last one surprises people every time. Reversing the sort does not bring the empty cells to the top; blanks sit at the bottom in both directions, by design, so that missing data does not bury your real data.

The trap that costs the most time is the second line. Text sorts character by character, which means a column of numbers stored as text sorts like this:

1000
1500
9         ← "9" is greater than "1" at the first character
980

If a sort produces an order that is obviously wrong for numbers, the sort is fine and the data is text. The tell is that the values sit left-aligned by default and that =ISNUMBER(G2) returns FALSE. Fix the data — Data → Text to Columns → Finish is the fastest brute-force conversion for a single column — and sort again. In a mixed column, real numbers sort ahead of every text-number, so you get two sorted groups instead of one, which is a good diagnostic in its own right.

Also worth knowing before you sort anything real:

• Hidden rows are not moved by a sort. Rows you have hidden by hand stay physically where they are while everything around them reorders, which detaches them from their neighbours in exactly the way section 2 describes. Unhide everything first (Ctrl+A, then Format → Hide & Unhide → Unhide Rows).
• Leading spaces sort first, because a space is a low-value character. A column where " Rivera y Cia" and "Rivera y Cia" both appear will produce two entries far apart in the list. TRIM before sorting.
• Merged cells refuse to sort unless every merged cell in the range is the same size, with the message "This operation requires the merged cells to be identically sized." This is a good reason to stop merging cells inside data ranges; Center Across Selection gives the same look with none of the consequences.

5) AutoFilter: What the Dropdowns Actually Do

Select any cell in the data and press Ctrl+Shift+L (or Data → Filter). Every header gets a dropdown, and each dropdown hides rows based on that column. Multiple filters combine with AND: filter Region to UK&I and Owner to M. Ferrer and you see the rows that satisfy both.

The dropdown holds more than the checkbox list:

• The search box takes a partial string and checks everything matching. Type mar and it offers Halvorsen Marine. Important detail: searching then ticking Add current selection to filter is how you build a filter out of two searches, and forgetting it is why the second search seems to throw away the first.
• Number Filters → Top 10 is misnamed — it does top or bottom, any count, and items or percent. Top 3 Items on Amount is the fastest answer to "who are the big ones".
• Date Filters get their own hierarchy: the checkbox list groups by year → month → day, and the menu has relative options (Last Month, This Quarter, Year to Date) that recompute against today's date rather than freezing a range.
• Text Filters → Contains / Begins With / Does Not Contain, and the two wildcards work here as everywhere else: ? for one character, * for any run of them.
• Filter by Color appears whenever the column has fill colour, font colour or conditional-formatting icons.
• Blanks is always the last entry in the checkbox list when the column has any, and (Select All) plus unticking Blanks is the quickest "show me only the rows somebody finished".

Three behaviours that cause support tickets:

The filter covers less than you think. AutoFilter grabs the contiguous block around the active cell. One completely empty row in the middle of the data and the filter applies to the top half only — while the dropdown arrows sit in the header looking entirely normal. The check is quick: turn the filter on and look at what Excel highlighted, or convert to a Table, where the range is explicit and grows with the data.

Filters do not refresh themselves. Change a value so that a row no longer matches the current filter and it stays visible until you re-run the filter. Data → Reapply (Ctrl+Alt+L) does that. A filtered sheet that has been edited since the filter was applied is showing you a stale answer, and nothing on the screen says so.

SUM does not care about filters. A filter is a view; SUM adds the cells in the range whether or not you can see them. That is what SUBTOTAL and AGGREGATE are for, and there is a whole article on this site about the difference — the short version is =SUBTOTAL(109,G2:G13), which ignores rows the filter has hidden, and which is what Excel writes for you when you press AutoSum at the bottom of a filtered list.

6) The Status Bar Is a Free Filter Readout

Bottom right of the Excel window, and almost nobody looks at it.

With a filter applied, the left of the status bar reads "7 of 12 records found". That is your row count, free, permanently, without a formula. Select the visible cells of a column and the right-hand side gives Average, Count and Sum of the visible cells only — right-click the status bar to switch any of those on, and add Numerical Count and Minimum and Maximum while you are there.

For a five-second answer to "what does UK&I over 30 days come to", that is faster than writing SUMIFS. For anything that must survive being reopened tomorrow, write the SUMIFS.

🎯 Scenario: Copying filtered rows somewhere else and getting all twelve.

Selecting a filtered range and pressing Ctrl+C copies only visible cells in modern Excel, which is what you want. The failure is the other direction — pasting into a filtered range writes into the hidden rows too. If you need to be explicit, select the range, press Alt+; (Go To Special → Visible cells only), and then copy; the marching ants break into separate blocks, which is how you know it worked.

7) Advanced Filter: OR, and Criteria That Are Formulas

AutoFilter can do AND across columns. It cannot do OR across columns — "UK&I rows over 20,000 or any row more than 80 days overdue" is not expressible in the dropdowns at all. Data → Advanced is.

Advanced Filter takes a criteria range: a small block somewhere else on the sheet whose first row holds column headings copied exactly from your data, and whose rows underneath hold the conditions.

The layout rule is the entire feature:

• Conditions on the same row are joined by AND.
• Conditions on different rows are joined by OR.

So this criteria range in J1:L3 says (Region is UK&I and Amount > 20000) or (Days Overdue > 80):

J1: Region    K1: Amount    L1: Days Overdue
J2: UK&I      K2: >20000    L2:
J3:           K3:           L3: >80

Then Data → Advanced, List range A1:G13, Criteria range J1:L3, and either filter the list in place or Copy to another location, which writes the matching rows out as a new block and leaves the original untouched. Two options worth knowing: Unique records only deduplicates the output, which makes Advanced Filter the pre-dynamic-array way to get a distinct list; and copy-to-another-location wants the destination on the active sheet, so if you want the results on a different sheet, start the command from that sheet and point the list range back at the data.

Criteria text follows the same rules as COUNTIF: >20000, <=1000, Nordic, <>Nordic, wildcards like Br*. Bare text is a begins with match, not an exact one, so Delta catches Delta Print Works, and an exact match needs the ugly but reliable ="=Delta Print Works".

Formula criteria are where Advanced Filter goes past anything the dropdowns can do. Two rules, both non-obvious, both mandatory:

1. The heading cell above a formula criterion must be blank, or a word that is not one of your column headings.
2. The formula refers to the first data row with a relative reference, and returns TRUE or FALSE.

🎯 Scenario: Show only invoices bigger than the average invoice.

J1: (leave blank)
J2: =G2>AVERAGE($G$2:$G$13)

G2 is relative and walks down the rows; $G$2:$G$13 is absolute and stays put. On the table above that returns the four invoices over 9,860.96. Any formula that evaluates to TRUE or FALSE works here — =AND(G2>10000,F2>60), =D2<>D1, =WEEKDAY(B2,2)>5 — which makes this the escape hatch for the filter you cannot build any other way.

Advanced Filter's real weakness is that it is a one-shot command, not a live view: change the data and the output does not move until you run it again. Which brings us to the version that does.

8) Doing It With Formulas Instead (Microsoft 365 / Excel 2021)

Three functions turn both operations into results that update themselves.

SORT returns a sorted copy of a range without touching the original:

=SORT(A2:G13, 7, -1)

Sort that range by its 7th column (Amount), descending. It spills into as many rows and columns as it needs, and because it is a formula it cannot detach a column from its row — the whole point of section 2's disaster is structurally impossible here.

SORTBY sorts by something that need not appear in the output, and takes several keys in priority order:

=SORTBY(A2:C13, D2:D13, 1, F2:F13, -1)

Region ascending, then Days Overdue descending, returning only columns A:C. The multi-level sort of section 3, as a live formula.

FILTER is AutoFilter as a function, and it does the OR that AutoFilter cannot:

=FILTER(A2:G13, (D2:D13="UK&I")*(G2:G13>20000) + (F2:F13>80), "None")

Multiply conditions for AND, add them for OR. The last argument is what to show instead of #CALC! when nothing matches — always supply it, because a bare #CALC! in a report reads like a broken sheet rather than an empty answer.

And the two that give you order without any rearranging at all:

=LARGE($G$2:$G$13, 1)     → 31,725.00, the biggest, with nothing moved
=RANK.EQ(G2, $G$2:$G$13)  → this row's position in the ranking

If what you needed was "the top three" or "where does this one place", these answer it in place, and the sheet still holds its rows in the order the export produced them. There are separate articles here on dynamic arrays and on statistical functions that go further into both.

🎯 Scenario: A dashboard sheet that must always show the ten oldest unpaid invoices.

=SORT(FILTER(A2:G13, G2:G13>0), 6, -1) on a dashboard tab, and nobody sorts anything ever again. The source keeps its own order, the dashboard keeps its own, and next month's rows appear in the right place on their own.

9) Six Ways These Go Wrong

1. Deleting rows while a filter is on. This is the one filter operation that destroys data, and it does it correctly. Filter to the rows you want gone, select them, right-click → Delete Row: Excel deletes only the visible ones, which is exactly right. Now do the same thing after clearing the filter, or with a filter that covers less of the sheet than you assumed (see section 5), and you have deleted rows you never looked at. Check the status-bar record count before deleting, always.

2. Row-relative formulas inside a sorted range. A formula like =H1+G2 — a running total that reaches up a row — is meaningless the moment the rows move, because it will keep reaching one row up and that row is now a different invoice. Same-row formulas (=E2*F2) survive sorting perfectly; anything that references a different row inside the sorted range does not. Rewrite those as SUMIFS or a running-total formula anchored with an absolute start (=SUM($G$2:G2)) before you sort.

3. Sorting only part of a Table. Tables protect you from the column-detach disaster, but not from selecting rows 5 to 9 inside one and sorting just those. Excel will do it. Click a single cell, use the header dropdown.

4. A "No." column that renumbers itself. If your row-number column is =ROW()-1, it recalculates after a sort and gives you 1, 2, 3 again in the new order, so it records nothing. The insurance column from section 1 must hold typed constants — or paste-special the formula results as values before the first sort.

5. Assuming a filtered sheet is a filtered file. Send someone a workbook with a filter applied and they see your filtered view, complete with the rows you did not intend them to see, one click away. Filtering is not redaction. If rows must not travel, copy the visible cells to a new workbook (Alt+; then copy) and send that instead.

6. Sorting a range that a chart or a formula elsewhere points at by position. =Sheet1!G5 follows the cell, not the record. Sort the sheet and that reference is now quoting a different invoice while looking completely unchanged. Anything that must survive a re-sort should look the record up by key — XLOOKUP, INDEX/MATCH, SUMIFS — rather than by address.

Conclusion

The two-sentence version of this whole article: sorting is an edit and filtering is a view. Reach for the view unless you specifically need the edit, because a view cannot be wrong for long and an edit can be wrong forever.

If you take one habit from here, take Ctrl+T. A Table makes the sort that started this article impossible, gives every column a filter dropdown without a keystroke, keeps its range correct as rows are added, and lets your formulas refer to [Amount] instead of to G2:G13. The single most expensive failure mode in this article is prevented, entirely, by two keys pressed before anything else.

And if you take a second one: type 1, 2, 3 down a spare column before the first sort of any sheet you did not create. It is the cheapest insurance in Excel, and the only version of it that works is the one you added before you needed it.

Want to practise the formula side of this? Several exercises in the app are built on exactly these shapes — ranking without reordering, conditional totals over a subset, and a lookup that has to keep finding the right record after the rows have moved.