Google Sheets QUERY ORDER BY: Sort Without Breaking Links

Basic structure:

=QUERY('P&L'!A:F, "SELECT A, B, C, D ORDER BY D DESC", 1)

The third argument (1) is the header row count. Column letters (A, B, D) refer to actual spreadsheet columns in the source tab, not positions within the range.

Why ORDER BY Beats a Manual Sort

When you use Data → Sort range on a source tab, rows physically reorder. Any formula on another tab that references 'P&L'!C14 now points at the wrong entity. In an 8-tab model - P&L, Balance Sheet, Cash Flow, FCFF, Assumptions, Returns, Sensitivity, Output - one careless sort cascades into dozens of broken references.

QUERY ORDER BY writes a sorted array to a separate display range. The source stays intact. You can hand the board pack to a CFO who sorts by instinct without worrying upstream data moved.

Syntax: Ascending, Descending, Multi-Column

Default is ascending. For finance, you almost always want descending - biggest first:

=QUERY('Revenue'!A:E, "SELECT A, B, E ORDER BY E DESC", 1)

Multi-column sort works exactly like SQL, with chained keys:

=QUERY('Pipeline'!A:G,
  "SELECT A, B, C, D ORDER BY C DESC, D ASC", 1)

Sort by ARR descending, then close date ascending as a tiebreaker. For a bank syndicate DCF with 40+ deals in the pipeline, this ranks by deal size while keeping same-size deals in chronological order.

Cross-Tab QUERY: The Column Reference Gotcha

For a single contiguous range from one tab, column letters work:

=QUERY('FCFF'!A:H, "SELECT A, B, G, H ORDER BY G DESC", 1)

But when you build an array literal from non-contiguous columns or multiple tabs, QUERY loses track of the original column letters. You switch to positional Col1, Col2 notation:

// This fails
=QUERY({'P&L'!C:C, 'P&L'!F:F}, "SELECT C, F ORDER BY F DESC", 1)

// This works
=QUERY({'P&L'!C:C, 'P&L'!F:F}, "SELECT Col1, Col2 ORDER BY Col2 DESC", 1)

As of June 2026, this distinction is documented in Google's Visualization API Query Language reference, but it's buried. According to that documentation: "Columns are referenced by their column ID, which in a Sheets QUERY is the spreadsheet column letter for direct ranges, but becomes a positional identifier for array literals."

Most analysts hit this error at least once building a multi-source summary view.

To stack rows from multiple tabs (the quarterly consolidation pattern):

=QUERY({'Jan'!A:E; 'Feb'!A:E; 'Mar'!A:E},
  "SELECT Col1, Col2, Col5 ORDER BY Col5 DESC", 1)

Semicolons stack rows. Commas stack columns.

WHERE + ORDER BY Together

You'll usually want both. Filter first, sort the filtered output:

=QUERY('SKU Data'!A:F,
  "SELECT A, B, D, F WHERE F > 0.35 ORDER BY F DESC",
  1)

This pulls SKUs where contribution margin (column F) exceeds 35% and ranks them descending. For a board pack contribution margin by SKU slide, this is the entire formula - no helper columns, no manual filter step.

With a dynamic date threshold from an Assumptions tab:

=QUERY('Transactions'!A:E,
  "SELECT A, B, C, E WHERE B >= date '"&TEXT(Assumptions!$B$3,"yyyy-mm-dd")&"' ORDER BY E DESC",
  1)

The TEXT() wrapper converts your assumption cell to the yyyy-mm-dd format QUERY expects. Without it, the date comparison silently passes all rows or none, depending on data type. No error, just wrong data - the worst kind of bug in a model.

Sorting on a Column You Don't Display

QUERY ORDER BY lets you sort by a column without including it in the output. SORT() can't do this cleanly.

=QUERY('Headcount'!A:G,
  "SELECT A, B, C ORDER BY G DESC",
  1)

Output shows name, department, salary band (columns A-C). Rows sort by hire date (column G) without exposing G. For a board-facing headcount view where you want newest hires ranked first but don't want dates cluttering the slide, this is the right pattern.

Runway Sensitivity: A Real Example

For a new hire pace sensitivity, you want planned hires ranked by monthly cash burn - so if runway drops below 18 months, the CFO can read the cut list top to bottom:

=QUERY('Headcount'!A:J,
  "SELECT A, C, D, J WHERE D = 'Planned' ORDER BY J DESC",
  1)

Column J is monthly fully-loaded cost. With a $4.2M ARR company burning $380K/month, knowing that three planned engineering hires represent $52K/month in incremental burn - and seeing them ranked - is exactly the analysis that gets acted on. Sorted output from a live headcount tab means the CFO always sees current data, not a stale snapshot.

QUERY ORDER BY vs. SORT()

For simple "sort this contiguous range by column 3," SORT() is shorter. For anything involving filter + select + sort in the same operation - which is most board pack work - QUERY does it in one formula.

Debugging Common Failures

Silent wrong sort: The column contains numbers stored as text. QUERY sorts text lexicographically, so "100" sorts before "20". Wrap the source column with VALUE() or check alignment - real numbers right-align, text-formatted numbers left-align.

#VALUE! on cross-tab range: You used column letters inside an array literal {}. Switch to Col1, Col2.

Date filter returning all rows or no rows: Wrap your date cell reference with TEXT(cell, "yyyy-mm-dd") as shown above.

Sort ignored entirely: You have ORDER BY after a GROUP BY or PIVOT. These clauses conflict in certain configurations. Group first, then wrap the grouped output in a second QUERY to sort.

If you're building these sorted views as part of a larger model and want the QUERY syntax generated from a plain description of the sort logic - "show me SKUs above 35% margin sorted by revenue" - ModelMonkey writes and updates the formula from the sidebar.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.