How to remove blank cells in Excel (2024)

1. Delete blank rows and columns instead of cells

If your data is organized in a table where columns and rows contain related information, deleting empty cells will mess up the data. In this case, you should only remove blank rows and blank columns. The linked tutorials explain how to do this quickly and safely.

2. Does not work for Excel tables

It is not possible to delete any individual cells in an Excel table (vs. a range), you are only allowed to remove entire table rows. Or you can convert table to range first, and then remove blank cells.

3. May damage formulas and named ranges

Excel formulas can adjust to many changes made to the referenced data. Many, but not all. In some situations, the formulas that referred to the deleted cells may get broken. So, after removing blank spaces, take a quick look at the related formulas and/or named ranges to make sure they work normally.

How the formula works

Tricky at first sight, upon a closer look the formula's logic is easy to follow. In plain English, the formula in C2 reads as follows: return the first value in the range A2:A11 if that cell is not blank. In case of an error, return an empty string ("").

For thoughtful Excel users, who are curious to know nuts and bolts of every new formula, here's the detailed break-down:

You have the INDEX function return a value from $A$2:$A$11 based on the specified row number (not a real row number, a relative row number in the range). In a simpler scenario, we could put INDEX($A$2:$A$11, 1) in C2, and it would fetch us a value in A2. The problem is that we need to cater for 2 more things:

• Make sure A2 is not blank
• Return the 2^nd non-blank value in C3, the 3^rd non-blank value in C4, and so on.

Both these tasks are handled by the SMALL(array,k) function:

SMALL(IF(NOT(ISBLANK($A$2:$A$11)), ROW($A$1:$A$10),""), ROW(A1))

In our case, the array argument is generated dynamically in the following way:

• NOT(ISBLANK($A$2:$A$11)) identifies which cells in the target range are not blank and returns TRUE for them, otherwise FALSE. The resulting array of TRUE and FALSE goes to the logical test of the IF function.
• IF evaluates each element of the TRUE/FALSE array and returns a corresponding number for TRUE, an empty string for FALSE:

  IF({TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}, ROW($A$1:$A$10),"")

ROW($A$1:$A$10) is only needed to return an array of numbers 1 through 10 (because there are 10 cells in our range) from which IF can pick a number for TRUE values.

As the result, we get the array {1;"";3;"";5;6;"";8;"";10} and our complex SMALL function transforms into this simple one:

SMALL({1;"";3;"";5;6;"";8;"";10}, ROW(A1))

As you see, the array argument contains only the numbers of non-empty cells (mind you, these are relative positions of the elements in the array, i.e. A2 is element 1, A3 is element 2, and so on).

In the k argument, we put ROW(A1) which instructs the SMALL function to return the 1's smallest number. Due to the use of relative cell reference the row number goes up in increments of 1 as you copy the formula down. So, in C3, k will change to ROW(A2) and the formula will return the number of the 2^nd non-blank cell, and so on.

However, we do not actually need the non-empty cell numbers, we need their values. So, we move forward and nest the SMALL function into the row_num argument of INDEX forcing it to return a value from the corresponding row in the range.

As a finishing touch, we enclose the whole construction in the IFERROR function to replace errors with empty strings. Errors are inevitable because you cannot know how many non-blank cells are in the target range, therefore you copy the formula to a bigger number of cells.

Given the above, we can build this generic formula to extract values ignoring blanks:

{=IFERROR(INDEX(range, SMALL(IF(NOT(ISBLANK(range)), ROW($A$1:$A$10),""), ROW(A1))),"")}

Where "range" is the range with your original data. Please pay attention that ROW($A$1:$A$10) and ROW(A1) are constant parts and never change no matter where your data starts and how many cells it includes.

How to locate the last used cell on the sheet

To move to the last cell on the sheet that contains either data or formatting, click on any cell and press Ctrl + End.

If the above shortcut has selected the last cell with your data, it means the remaining rows and columns are really blank and no further manipulations are needed. But if it has taken you to a visually empty cell, know that Excel does not consider that cell blank. It could be a mere space character produced by an accidental key stroke, a custom number format set for that cell, or a non-printable character imported from an external database. Whichever the reason, that cell is not empty.

Delete cells after the last cell with data

To clear all content and formatting after the last cell with data, do the following:

1. Click the heading of the first blank column to the right of your data and press Ctrl + Shift + End. This will select a range of cells between your data and the last used cell on the sheet.
2. On the Home tab, in the Editing group, click Clear > Clear All. Or right-click the selection and click Delete… > Entire column:
   
3. Click the heading of the first blank row below your data and press Ctrl + Shift + End.
4. Click Clear > Clear All on the Home tab or right-click the selection and choose Delete… > Entire row.
5. Press Ctrl + S to save the workbook.

Check the used range to make sure it now contains only cells with data and no blanks. If the Ctrl + End shortcut selects a blank cell again, save the workbook and close it. When you open the worksheet again, the last used cell should be the last cell with data.

Tip. Given that Microsoft Excel 2007 and higher contains over 1,000,000 rows and more than 16,000 columns, you may want to reduce the workspace size to prevent your users from unintentionally entering data into wrong cells. For this, you can simply remove empty cells from their view as explained in How to hide unused (blank) rows and columns.