Scattered blank cells can break a list, but deleting full rows may destroy related data in other columns. The safest cleanup depends on whether you are cleaning one standalone column or a full table that must stay aligned.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, and Excel 2019 on Windows or Mac when a list has empty cells mixed into it and you want the remaining values grouped together.
Shift cells up in one column
Use this only when the column can be cleaned independently from neighboring columns.
- Select the range in the column that contains blanks.
- Press Ctrl+G on Windows or Cmd+G on Mac, then choose Special.
- Select Blanks and click OK.
- Right-click one highlighted blank cell and choose Delete.
- Choose Shift cells up and click OK.
Excel removes the blank cells inside the selection and pulls lower values upward, leaving a continuous list at the top. Do not use this on one column of a table if other columns must stay row-aligned.
Sort blanks to the bottom
Use this for multi-column data because whole rows move together.
- Select the entire data range, including headers.
- Open Data > Sort.
- Sort by the column that contains blanks.
- Choose either ascending or descending order.
- Click OK.
Rows with blanks in that column move together toward the bottom while the rest of each row stays aligned.
Create a clean copy with a formula
Use this when the original column should remain unchanged.
- Click an empty area for the cleaned list.
- In Microsoft 365, enter a formula such as =FILTER(A2:A100,A2:A100“”).
- Press Enter and let the formula spill the non-blank values.
- In older Excel, use an INDEX/SMALL style array formula if FILTER is not available.
More control
Watch for formulas that look blank
Go To Special > Blanks does not select cells containing formulas that return “”. Convert formulas to values first or use a filter formula that excludes empty text.
Select the exact range
Shift cells up only affects the selected area. Select precisely the cells you intend to clean so neighboring data does not become misaligned.
Check formulas and charts
Most formulas that reference the original range adjust to moved cells, but review totals, charts, and named ranges after cleanup.
Sources
- Microsoft Support – Find and select cells that meet specific conditions (2025)
- Microsoft Support – TEXTJOIN function (2025)
- Microsoft Support – Sort data in a range or table (2025)