Sort by Multiple Columns in Google Sheets: Keep Rows Together and Break Ties

Write the ordering rule before the formula

Use the desktop browser version of Google Sheets with English function names. The sample represents a review list, not a universal ranking policy. Department groups come first; within each department, higher scores appear first; equal scores are ordered by the unique record ID. An ID tie-breaker is an explicit display rule, not evidence that one person performed better.

This rule places every Sales record before every Support record even when a Support score is larger. That is intentional. If your actual requirement is a company-wide leaderboard, score should be the first key. The two expected downloads make this choice visible rather than hiding it in the order of formula arguments.

Select the full record rectangle and leave headers out

Put sort-input.csv in A1:D8. Keep record_id, department, score and owner together in the source range. Copy the headers to F1:I1 and reserve F2:I8 for the formula result. The data-only input is A2:D8; including row 1 would ask the formula to sort the headers as data.

Put =ISNUMBER(C2) in E2 and fill it down through E8. All seven results must be TRUE, including the row containing zero. A numeric-looking text score should be corrected under a clear import rule before relying on the ordering. Keep E as a temporary type check outside the A:D record range; do not change all scores to text just to make the cells look consistent.

Enter all sorting keys in priority order

Enter the formula below in F2. The first key is the second column of A:D, which is department. TRUE means ascending. The next key is the third column, score, with FALSE for descending. The final key is the first column, record_id, ascending. Each direction belongs to the key immediately before it.

These integer key positions are relative to the range passed to SORT. If the source rectangle later moves to B:E, department is still its second column even though it sits in worksheet column C. Recalculate the positions if you change which columns the formula returns. Keeping the source headers visible helps catch that distinction.

F2: =SORT(A2:D8,2,TRUE,3,FALSE,1,TRUE)

Verify ties instead of assuming a stable order

R02 and R03 both belong to Sales and score 90. The final key puts R02 first despite R03 appearing earlier in the original input. R01 and R07 tie in Support at 80, and their IDs settle that order too. These cases verify the third key, which a dataset with no ties would never exercise.

Do not rely on an undocumented original-row order when all your named keys are equal. If preserving original sequence is the requirement, capture a unique sequence value in the source and use that as the final key. Freeze that value before any manual reordering; a live ROW-based formula can change when the source is rearranged.

Change the primary key when the report question changes

For a score-first leaderboard, put the alternative below in K2 with headers in K1:N1. It orders score descending, department ascending and record ID ascending. R05 at 100 now appears before all Sales records. The same source data can support both reports because neither formula sorts the source in place.

Compare K2:N8 with sort-score-first-expected.csv. Keep the formula label explicit so someone does not mistake a grouped list for an overall ranking. Adding another key only resolves ties in earlier keys; it does not override a higher-priority key or compute a weighted score from several columns.

K2: =SORT(A2:D8,3,FALSE,2,TRUE,1,TRUE)

Keep manual notes attached to IDs

The sorted output can move whenever a source score changes. Typing a comment in the next unused column beside the output does not attach it to the record ID. After recalculation, that comment could sit beside a different person. Store notes in the source record or in a separate table keyed by record_id, then look them up.

Likewise, reserve the whole output rectangle for the formula. A note entered inside it can prevent expansion. If you need to hand off a fixed snapshot, copy the verified output to a separate tab and paste values there. Record when the snapshot was taken; it no longer recalculates with the source.

Reconcile records and extend the range carefully

Compare the seven IDs in the source with the seven IDs in each output. Check R03 still belongs to Ada and R02 still belongs to Dee. The total score is 535 before and after sorting, but a matching total alone would not detect owners being detached from their scores. Verify both membership and associations.

When adding rows, extend the complete A:D rectangle and any external key ranges together. Blank department values, duplicate IDs, locale-specific collation and text scores need explicit policies before expanding the example to production. The independent validator checks the stated tuple ordering and record preservation. It does not run the native Sheets engine or establish how unspecified ties behave.

Enter the following checks in P2:P4; the expected values are 7, 535 and 1. P4 confirms that the real zero score survived. Read the complete F2:F8 ID order against sort-expected.csv, then check R02 with Dee and R03 with Ada. These arithmetic checks support the row comparison; they do not replace it or prove that each owner stayed attached to the correct score.

For real data, decide what a missing department or score means before sorting. The practice range contains neither, so it deliberately makes no promise about where blank keys should appear. Keep unresolved records in a visible review list or establish a documented category before expanding the range. If record IDs are not unique, add a unique, preserved sequence as the final key; adding a duplicate ID key cannot settle an otherwise complete tie.

P2: =COUNTA(F2:F8)
P3: =SUM(H2:H8)
P4: =COUNTIF(H2:H8,0)

Verified practice results

This synthetic example was calculated in Google Sheets on October 11, 2026. Both complete-row outputs matched their expected CSVs: department-first began R04, R02, R03, R06; score-first began R05, R04, R02, R03. All seven IDs retained their owners, the total stayed 535, and the zero score remained.

Changing R06 from zero to 110 made it the first record in both reports, raised the total to 645 and removed the zero-count match. Restoring the source restored both full baseline orders. The native calculation engine and a browser view of the score-first report were checked. Blank keys, case-only ID differences, locale-specific collation and unresolved complete ties are outside this fixture; establish those policies for your own data.