How to Remove Blank Cells and Shift Data Left in Excel
Question details
The user wants to delete blank cells within a specific range and shift the remaining data to the left.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up spreadsheet data by removing gaps and empty cells within a selected range, ensuring data is condensed.
- Observed behavior
- Blank cells are successfully removed from the highlighted selection and the adjacent data is shifted left without misaligning the surrounding rows.
Verify that your data does not rely on strict column alignment before deleting cells, as shifting data to the left will permanently change the structure of the selected range.
Use Go To Special to Delete Blanks and Shift Left
This built-in feature allows you to locate all blank cells in a selected range instantly and delete them while shifting the remaining data to the left.
This is the most efficient method for cleaning up data sets with scattered blank cells. It selectively targets only the empty spaces within your highlighted area, ensuring that data outside the selection is left completely untouched.
Click and drag your mouse to highlight the specific range of cells from which you want to remove the blanks.
Press 'F5' or 'Ctrl+G' on your keyboard to open the Go To dialog box, then click the 'Special' button.
In the Go To Special window, choose the 'Blanks' radio button and click 'OK'. All blank cells within your initially selected range will now be highlighted.
Right-click on any of the currently highlighted blank cells and choose 'Delete' (or press 'Ctrl+-'). In the Delete dialog prompt, select 'Shift cells left' and click 'OK' to confirm.
Clean Up Data Instantly in WPS Spreadsheet
WPS Spreadsheet provides intuitive and powerful data management tools. You can effortlessly locate blank cells and reorganize your data layouts using familiar shortcuts, completely free of charge.
- 1. Open your spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet application.
- 2. Highlight the data: Select the rows or columns containing the blank cells you need to clean up.
- 3. Find blank cells: Press Ctrl+G to open the Go To dialog, click 'Special', select 'Blanks', and hit OK.
- 4. Delete and shift: Right-click the selected blanks, click 'Delete', choose 'Shift cells left', and apply.
Frequently Asked Questions
Will shifting cells left affect data outside my selection?
No. As long as you carefully select only your target range before using the 'Go To Special' method, only the cells within that specific boundary will be deleted and shifted. Data outside the selection will remain perfectly intact.
Why is the 'Shift cells left' option greyed out?
This usually occurs if you are attempting to delete cells within an officially formatted Excel Table, or if the worksheet is protected. You must unprotect the sheet or convert your Table back to a normal range (Right-click Table > Table > Convert to Range) before deleting individual blank cells.
What is the keyboard shortcut to delete cells quickly?
Once your blank cells are highlighted, you can press 'Ctrl' and the minus key ('-') simultaneously to instantly open the Delete dialog box where you can select 'Shift cells left'.
Can I use formulas instead of deleting cells to remove blanks?
Yes. If you are using newer versions of Excel, you can use the FILTER function to extract only non-empty cells into a new location. However, for quick in-place editing without creating new columns, the 'Go To Special' and Delete method is recommended.
