How to Automatically Sort Data in Google Sheets (Formulas & Script)

Sorting datasets in Google Sheets is standard procedure when reviewing sales leaderboards, task priorities, or inventory counts. However, relying on manual sorting (navigating to Data > Sort range) creates an operational bottleneck: every time new rows are added or existing numbers are edited, your carefully ordered list becomes scrambled until you manually re-sort it.

Google Sheets offers two reliable ways to solve this problem: formula-based dynamic sorting (which outputs an automatically updating view in a separate range) and script-based in-place auto-sorting (which automatically reorders your raw data table the instant a cell is edited). This guide covers how to set up both methods step-by-step.

---

Method 1: Formula-Based Dynamic Sorting (SORT & QUERY)

If you want to keep your raw data entry tab untouched while displaying a real-time, self-sorting master view on a dashboard tab, use built-in Google Sheets formulas.

1. Using the SORT Function

The SORT function mirrors an input range and sorts the results dynamically as underlying cells change.

Formula Syntax:

=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])

Example: To sort a sales table in range A2:D100 by Column C (Revenue) from highest to lowest (descending order):

=SORT(A2:D100, 3, FALSE)

2. Combining SORT with FILTER (Excluding Empty Rows)

When referencing an open-ended range like A2:D to accommodate future data entry, empty rows will sort to the top or bottom, creating large blank gaps. Wrap SORT inside FILTER to strip out empty rows automatically:

=SORT(FILTER(A2:D, A2:A <> ""), 3, FALSE)

Pro Tip: You can also use the QUERY function for complex multi-column sorting and filtering:

=QUERY(A1:D, "SELECT * WHERE A IS NOT NULL ORDER BY C DESC", 1)

---

Method 2: In-Place Auto Sorting via Google Apps Script (onEdit Trigger)

Formula outputs require a separate output area. If you want your actual raw data table to re-sort itself in place automatically the second a user types a new entry or updates a status, use a simple Google Apps Script onEdit(e) trigger.

Step 1: Open the Script Editor

1. In your Google Sheet, navigate to Extensions > Apps Script.
2. Clear any placeholder code inside the editor.
3. Paste the following script:

/**
 * Automatically sorts a specified range whenever a cell edit occurs.
 */
function onEdit(e) {
  var sheetName = "Data_Entry"; // Replace with your exact sheet tab name
  var sortColumn = 3;            // Column C (3rd column) to sort by
  var isAscending = false;       // Set true for A-Z / Lowest-First, false for Z-A / Highest-First
  var headerRows = 1;            // Number of top header rows to protect from sorting

  var range = e.range;
  var sheet = range.getSheet();

  // Execute sort only if edit occurs on the target sheet
  if (sheet.getName() === sheetName) {
    var fullRange = sheet.getRange(headerRows + 1, 1, sheet.getLastRow() - headerRows, sheet.getLastColumn());
    fullRange.sort({column: sortColumn, ascending: isAscending});
  }
}

Step 2: Save and Test the Script

1. Click the Save project icon (disk symbol) or press Ctrl + S.
2. Switch back to your Google Sheet tab named Data_Entry.
3. Add a new row at the bottom of your table or edit an existing number in Column C.

The moment you press Enter, the simple onEdit trigger detects the modification and instantly re-orders your table rows in place without requiring you to refresh or run a manual command.

---

Comparing Auto-Sort Methods: Formula vs. Script

Comparison Factor	Formula Method (SORT / QUERY)	Apps Script Method (onEdit)
Data Location	Requires a separate output range/tab.	Sorts raw data in place inside the active table.
Setup Complexity	Very Simple (Single cell formula).	Intermediate (Requires pasting Apps Script code).
Execution Speed	Instant recalculation.	Near-instant (Runs ~1 second after cell edit).
Permission Prompt	None (Native formula).	None (Simple onEdit triggers run automatically).
Editability	Output cells cannot be typed over directly.	All sorted rows remain fully editable.

---

Troubleshooting Common Auto-Sorting Issues

1. Table Headers Get Sorted Into Data Rows:
   In Apps Script, ensure headerRows = 1 is set so the sort boundary begins strictly on row 2 (headerRows + 1). In formulas, make sure your range excludes header row 1 (e.g., A2:D instead of A1:D).
2. Script Triggers on Wrong Tabs:
   Check that sheet.getName() === sheetName matches the capitalization and spelling of your spreadsheet tab name exactly.
3. Formula Output Returns #REF! Error:
   The SORT formula requires empty cells below and to the right to expand its results. Clear any manual text or formatting blocking the spill area.