How to Remove Blank Cells and Shift Values Up Using Excel Formulas
Question details
The user wants to compact a table by extracting all non-blank values and shifting them upward to fill empty cells while preserving the original column structure.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Cleaning up and organizing a dataset that contains scattered blank cells across various rows and columns without manually deleting them.
- Observed behavior
- The table currently contains blank cells, and the goal is to shift all data up to create a contiguous, gap-free table via automated formulas.
Determine whether you are using a modern spreadsheet version that supports dynamic array functions (like TOCOL and WRAPROWS) or a legacy version, as the required formulas differ significantly.
Use Dynamic Array Formulas (Modern Spreadsheets)
The most efficient way to remove blanks and shift values up is by combining the LET, COLUMNS, TOCOL, and WRAPROWS functions.
This method utilizes dynamic arrays to automatically spill the results into adjacent cells without the need for manual dragging. The TOCOL function extracts all values into a single column while ignoring blanks, and WRAPROWS reconstructs the table.
Click on the top-left cell of the new range where you want the compacted table to appear.
Type the formula: =LET(c,COLUMNS(Table2),vector,TOCOL(Table2,1),WRAPROWS(vector,c)) into the formula bar. Replace 'Table2' with your actual data range.
Press Enter. The formula will automatically calculate and spill the compacted data, shifting all non-blank values up while maintaining the column count.
Use an INDEX and SMALL Array Formula (Legacy Excel)
For older versions of Excel that do not support dynamic arrays, you can use a combination of INDEX, SMALL, IF, and ROW.
Manage Data and Remove Blank Cells Easily with WPS Spreadsheet
WPS Office provides robust spreadsheet tools that handle complex formulas, dynamic arrays, and large datasets effortlessly. You can compact your tables and clean up data efficiently without needing expensive software.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx workbook containing the data with blank cells.
- 2. Select your target range: Click on the cell where you want the new, compacted table to be generated.
- 3. Input the extraction formula: Type the appropriate array formula for your data layout directly into the formula bar.
- 4. Execute the formula: Press Enter to instantly execute the dynamic array and shift all non-blank values upward.
Frequently Asked Questions
Can I remove blank cells without using complex formulas?
Yes. You can select your data range, press F5 to open the 'Go To' dialog, click 'Special', select 'Blanks', and click OK. Then, right-click any highlighted blank cell, select 'Delete', and choose 'Shift cells up'. Note that this permanently alters your original data rather than creating a dynamic copy.
Why is my INDEX and SMALL formula returning a #NUM! error?
This error occurs when the formula has successfully extracted all the non-blank values and there is no more data left to return for that specific row. You can wrap your entire formula in an IFERROR function (e.g., =IFERROR(your_formula, "")) to display a blank space instead of the error.
What does the TOCOL function do in the recommended formula?
The TOCOL function transforms a multi-column array or range into a single vertical column (a vector). By setting its second argument to 1, you specifically instruct the spreadsheet to ignore any blank cells during this transformation, which is the key to compacting the data.
