Automatically Move Formula-Based Blank Rows to the Bottom in Excel
Question details
Automatically place rows that appear blank because of formulas below rows containing data.

- 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.
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.
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.
Select an empty cell outside the source range, such as E3, and leave enough empty space below and to the right for the results.
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.
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.

Sort the worksheet automatically with a VBA calculation event
Use a macro when the original worksheet rows must be reordered after linked formulas recalculate.
Sort blank-looking rows manually with a helper column
Use this method when you do not need the order to refresh automatically or cannot use macros.
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.

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.




