Remove blank cells in a list of values in Excel, retaining the original list.
Use Excel’s Advanced Filter on the Data tab | Sort & Filter section.
The example below copies the values to a new column. This helps if your original list is part of a table or matrix, and you want to retain the original records/rows in the table.
- Column B contains the list to remove blanks from. For this method, the list needs to include a header. The list in column B will be filtered to only include cells that contain a value (i.e. greater than nil/null, defined by: >”” [greater than symbol followed by two speech mark symbols]).
- Column D is the destination column where the values will be copied to. Make sure to leave a blank column in between if your original list is part of a defined table (otherwise the new column will be automatically added to the table, and amend the original).
- Column E holds the ‘criteria’ by which the original values will be filtered.
- Copy the header (‘list of animals’) from the original list (Col B) to the top of the destination range(Col D), and to a second range top cell(Col E).
- Type >”” into the cell under the header in the second column (Col E, in this example cell E4).
- Select the range to be filtered, including its header (B3:B33, click and drag to highlight the cells).
- Click on ‘Advanced filter’ in Data | Sort & Filter.
- In the dialogue box:
- select ‘Copy to another location‘.
- Confirm that the range in the List range field is the range of your list (B3:B33).
- Next to the Criteria range field, click on the up arrow and select the header and criteria cells (E3:E4), click OK.
- Next to the Copy to field, click on the up arrow and select a range of cells in the destination column (Col D), including the header cell and long enough to take the list of filtered values.
- Click OK.
- The filtered list of values with no blanks will appear in the destination column, (Col D).
- You can also use the ‘Unique records only‘ option if your list has duplicates, and you want to just retain the unique values.
- In the example above, the top cells of the columns were being used for explanatory notes. The method works fine for any range – at the top of columns, or further down.
- Microsoft Excel’s error messages in the Advanced Filter dialogue box can be somewhat unhelpful. I found the key to this working was to make sure that each of the three ranges has the same heading at the top.
- This method could be used to filter for other criteria, e.g. wildcards, particular values.