Function Problems

Automatically Move Formula-Based Blank Rows to the Bottom in Excel

Anum MushtaqAnum Mushtaq Nov 22, 2026 868 views

Question details

Automatically place rows that appear blank because of formulas below rows containing data.

How to Automatically Move Formula-Based Blank Rows to the Bottom in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet receives linked data from another sheet, and the user wants its blank-looking rows to move to the bottom whenever the data changes.
Observed behavior
A helper column and manual sort work, but the row order does not update automatically when linked data changes.
Before you start

Identify the column whose displayed values determine whether a row is blank, and include all related columns in the range you sort. Check that Excel calculation is set to Automatic under Formulas > Calculation Options.

Solution 1Recommended

Create an automatically updating sorted view with SORTBY

Use this option in an Excel version that supports dynamic arrays when a separate sorted view is acceptable.

SORTBY returns a reordered view; it does not move the original worksheet rows. The view updates when its source values recalculate.

The example checks column A and displays columns A through C. It treats formulas returning an empty string as blank. Truly empty source cells may appear as zeros in the spilled result.

1
Choose an output area

Select an empty cell outside the source range, such as E3, and leave enough empty space below and to the right for the results.

2
Enter the sorting formula

In E3, enter =SORTBY(A3:C100,--(A3:A100=""),1,ROW(A3:A100),1). Change the ranges to match your data, keeping the same number of rows in each range.

3
Test the view

Change data on the linked source worksheet and return to the output area. Rows with a blank result in column A should appear below rows with values.

Create an automatically updating sorted view with SORTBY
Keep the spill area clear: If Excel displays #SPILL!, remove obstructing content from the output area. SORTBY requires a supported Excel version.
Free Microsoft Office alternative

Sort spreadsheet rows with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks and use a helper column and sort controls; WPS does not fix Excel's VBA or automatic-sorting behavior.

Use helper columns and sorting tools to organize blank-looking rowsCompatible with Microsoft Excel formats such as XLSXFamiliar spreadsheet interface for an easier transition from ExcelFree and lightweight for everyday document work
microsoft office alternative - wps office

Frequently Asked Questions

Can a formula automatically rearrange the original rows in Excel?

No. A formula such as SORTBY can produce an automatically updated sorted view, but it does not move the source rows. Use an automation method such as VBA to reorder the original range.

Why does my helper-column sort not update when linked data changes?

The helper formulas recalculate, but a normal Data > Sort operation does not rerun automatically. Apply the sort again, use a SORTBY view, or set up a suitable VBA event.

How do I identify a formula that returns a blank?

Use =A3="" to return TRUE when A3 is blank-looking, or =--(A3="") to return 1 for blank and 0 for nonblank. Sort the numeric helper from Smallest to Largest to put blank-looking rows last.

Can I put two formulas in one Excel cell?

A cell can contain one formula, but that formula can combine multiple calculations or functions. For example, =IF(A3="","",A3*2) uses IF and a multiplication in the same formula.

Why is the SORTBY result showing a spill error?

The result needs an empty output area. Clear cells blocking the spill range, or move the SORTBY formula to an area with enough space for all rows and columns.