Removing empty rows and columns in data

Empty rows and columns can be a pain in tables in many cases. Standard functions for sorting, filtering, summarizing, creating pivot tables, etc. perceive empty rows and columns as a table break, without picking up the data located further behind them. If there are many such gaps, then manually removing them can be very costly, and it will not work to remove all at once “in bulk” using filtering, because the filter will also “stumble” at breaks.

Let’s look at several ways to solve this problem.

Method 1. Search for empty cells

This may not be the most convenient, but definitely the easiest way is worthy of mention.

Suppose we are dealing with such a table containing many empty rows and columns inside (highlighted for clarity):

Suppose we are sure that the first column of our table (column B) always contains the name of a city. Then empty cells in this column will be a sign of unnecessary empty rows. To quickly remove them all, do the following:

  1. Select range with cities (B2:B26)
  2. Press the key F5 and then press Highlight (Go to Special) or select on the tab Home — Find and Select — Select a group of cells (Home — Find&Select — Go to special).
  3. In the window that opens, select the option Empty cells (Blanks) and press OK – all empty cells in the first column of our table should be selected.
  4. Now select on the tab Home Command Delete – Delete rows from sheet (Delete — Delete rows) or press the keyboard shortcut Ctrl+minus – and our task is solved.

Of course, you can get rid of empty columns in exactly the same way, using the table header as a basis.

Method 2: Search for empty rows

As you may have already figured out, the previous method will only work if our data necessarily contains fully filled rows and columns, which can be hooked on when searching for empty cells. But what if there is no such confidence, and the data may contain empty cells as well?

Take a look at the following table, for example, for just such a case:

Here the approach will be a little trickier:

  1. Enter in cell A2 the function COUNT (COUNTA), which will calculate the number of filled cells in the row to the right and copy this formula down to the entire table:
  2. Select cell A2 and turn on the filter with the command Data – Filter (Data — Filter) or keyboard shortcut Ctrl+Shift+L.
  3. Let’s filter out zeros by the calculated column, i.e. all rows where there is no data.
  4. It remains to select the filtered lines and delete them with the command Home — Delete -‘ Delete rows from sheet (Home — Delete — Delete rows) or keyboard shortcut Ctrl+minus.
  5. We turn off the filter and get our data without empty lines.

Unfortunately, this trick can no longer be done with columns – Excel has not yet learned how to filter by columns.

Method 3. Macro to remove all empty rows and columns on a sheet

You can also use a simple macro to automate this task. Press keyboard shortcut Alt+F11 or select from the tab developer — Visual Basic (Developer — Visual Basic Editor). If tabs developer is not visible, you can enable it through File – Options – Ribbon Setup (File — Options — Customize Ribbon).

In the Visual Basic editor window that opens, select the menu command Insert – Module and in the empty module that appears, copy and paste the following lines:

   Sub DeleteEmpty()      Dim r As Long, rng As Range        'удаляем пустые строки      For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count          If Application.CountA(Rows(r)) = 0 Then              If rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r))          End If      Next r      If Not rng Is Nothing Then rng.Delete            'удаляем пустые столбцы      Set rng = Nothing      For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count          If Application.CountA(Columns(r)) = 0 Then              If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns(r))          End If      Next r      If Not rng Is Nothing Then rng.Delete    End Sub

Close the editor and return to Excel. 

Now hit combination Alt+F8 or button Macros tab developer. The window that opens will list all macros currently available to you to run, including the macro you just created. DeleteEmpty. Select it and click the button Run (run) – all empty rows and columns on the sheet will be instantly deleted.

Method 4: Power Query

Another way to solve our problem and a very common scenario is to remove empty rows and columns in Power Query.

First, let’s load our table into the Power Query Query Editor. You can convert it to a dynamic “smart” one with the keyboard shortcut Ctrl+T or just select our data range and give it a name (for example Data) in the formula bar, converting to named:

Now we use the command Data – Get data – From table / range (Data – Get Data – From table / range) and load everything into Power Query:

Then everything is simple:

  1. We delete empty lines with the command Home – Reduce lines – Delete lines – Delete empty lines (Home – Remove Rows – Remove empty rows).
  2. Right-click on the heading of the first City column and select the Unpivot Other Columns command from the context menu. Our table will be, as it is technically correctly called, normalized – converted into three columns: city, month and value from the intersection of the city and month from the original table. The peculiarity of this operation in Power Query is that it skips empty cells in the source data, which is what we need:
  3. Now we perform the reverse operation – we turn the resulting table back into a two-dimensional one in order to return it to its original form. Select the column with months and on the tab Transformation choose a team Pivot column (Transform — Pivot Column). In the window that opens, as a column of values, select the last (Value), and in the advanced options – the operation Do not aggregate (Don’t aggregate):
  4. It remains to upload the result back to Excel with the command Home — Close and Load — Close and Load in… (Home — Close&Load — Close&Load to…)

  • What is a macro, how does it work, where to copy the text of a macro, how to run a macro?
  • Filling all empty cells in the list with the values ​​of the parent cells
  • Removing all empty cells from a given range
  • Removing all empty rows in a worksheet with the PLEX add-on

Leave a Reply