Removing blank rows

One of the earlier “recipes” was about how to use the Go To Special option  to remove empty rows from the table. Now it is possible to do this in a much easier way, by using the TRIMRANGE function in combination with some other functions which are returning dynamic arrays. The result is a dynamic table that will be automatically updated if you decide to remove any more rows.

Let’s start with a simple table on which the data of the employees is recorded. In the table, by random selection, a few rows were deleted. How do you remove them so that they are no longer visible, that is, extract only those rows which have data from the table?

In cell A14, we enter the formula:

=UNIQUE(A3:H12)

We’ll get the result as the one shown by the image.

If we want to edit the values so that they are displayed in ascending order, we will correct the formula:

=SORT(UNIQUE(A3:H12))

The result of this operation is shown below.

If we further “frame” this expression by using the TRIMRANGE function, we have achieved the desired result. Here’s what the formula should look like:

=TRIMRANGE(SORT(UNIQUE(A3:H12)))

And in the image below you can see the final appearance of the table from which the blank rows have been removed.

If we try to remove another row, the table will be updated automatically. Finally, to get the dates and salary values shown in a correct way, it is necessary to use appropriate formatting for the proper columns in the area where the new table will be placed.