How to Remove Space between Rows in Excel: 5 Methods

Method 1 – Remove Space between Rows Using Wrap Text

Select the cells that have space between rows; you see the Wrap Text option is enabled from the Alignment section in the Home tab.

Unselect the Wrap Text option from Home > Click on Wrap Text.

In a moment, Excel removes all the space between rows, as depicted in the below image.

Method 2 – Using TRIM Function to Remove Space Between Rows

We know the TRIM function removes all kinds of excessive spaces. The syntax of the TRIM function is

TRIM (text)

The text argument refers to the cell reference from where you want to remove the space.

Use the formula below in any blank cell to get space-removed entries, as shown in the following image.

=TRIM (B5)

To apply the formula in other cells, use the Fill Handle first in the below cell.

Use the Fill Handle to the right-side cell to remove space between rows.

Method 3 – Replacing Cell Format with Existing Ones Using Find and Replace Feature

Earlier we said that typical link breaks appear as space between rows. We can use CTRL+J to find Link Break or CHAR(10) within the worksheet.

Go to Home > Select Find & Select (from the Editing section) > Click on Replace.

You can bring out the Find and Replace window using Keyboard Shortcuts CTRL+H.

Excel fetches the Find and Replace window. In the Find and Replace window,

⧫ Insert CTRL+J in the Find What command box. CTRL+J is used for inserting Link Break Character in the Find and Replace window.

⧫ Keep the Replace with command box empty.

⧫ First- Click on Find All.

⧫ Second- Click on Replace All.

Executing Replace All removes space between rows, similar to the below picture.

You can assign Cell Formats to Find what and Replace with the command box by clicking the down arrow beside Format > Selecting Choose Format From Cell Format option.

Method 4 – Using Excel SUBSTITUTE Function to Remove Space between Rows

The Link Break is a character that can be easily replaced by the SUBSTITUTE function. The SUBSTITUTE function takes text as cell reference and specifically removes given old_text with new ones. The syntax of the SUBSTITUTE function is

=SUBSTITUTE (text, old_text, new_text, [instance])

The arguments define

text; The cell reference users want to change.

old_text; The characters or text have to be replaced within the text.

new_text; The new text users want to replace with.

instance; The number of replacements users want to execute. In case of not mentioning, replace all the instances. [Optional]

Step 1: Write the below formula in any blank cell (i.e., F5).

=SUBSTITUTE(B5,CHAR(10),"")

Comparing the formula to the syntax, B5 = text, CHAT(10) = old_text,“” = new_text. Here, CHAR(10) represents the Link Break.

Step 2: Drag the Fill Handle downward to remove space between rows.

Step 3: Use the Fill Handle to fetch the entries in other cells without Line Break or Space between rows.

Method 5 – Using CLEAN Function to Remove Space Between Rows

One of the alternative ways to remove any kind of extra space and link break is the CLEAN function. The syntax of the function is

CLEAN (text)

Text is the cell reference from where you want to remove the space between rows.

Step 1: To clean the space between rows, write the following formula in any blank cell.

=CLEAN (B5)

Step 2: Move the Fill Handle to fetch the space removed entries in new cells as depicted in the picture below.

Issues with Removing Space

Certain functions such as SUBSTITUTE, return entries in different formats after their execution. If we use those resultant outcomes, we encounter calculation errors (like getting a product value of 0), as shown below.

To overcome the caveat, use the VALUE function. The VALUE function converts any cell data type (mostly text) into calculatable entries. Insert the formula below to transform texts into values, as shown in the image below.

=VALUE(C5)