HomeHow ToHow to Remove Extra or Unwanted Spaces in Microsoft Excel Worksheet

How to Remove Extra or Unwanted Spaces in Microsoft Excel Worksheet



Using Microsoft Excel is one way to create a more organized presentation of your data for school, work, or personal business. You can arrange them properly, so each detail looks clean and orderly. It is also an excellent tool to submit reports or share data collections with other users.

The good thing about the Microsoft Excel worksheet is that you can export and save the file using other formats like PDF to retain its order and arrangement when you want to send it to someone.

Image credit: Microsoft

Unfortunately, importing an Excel worksheet will give you extra blank spaces that are not necessary on the file. Some trailing character spaces creep in before number or in between lines. If you want to remove unwanted leading or trailing spaces on your spreadsheet document cells, there are several ways you can do it.

Methods to Delete Unwanted Trailing or Leading Blank Spaces in Microsoft Excel Worksheet Cells

Unwanted extra blank spaces on your Microsoft Excel worksheets will only clutter your file. You can keep it clean by deleting all the leadingcharacter spaces that you think are not needed in the worksheet. Here are some methods and workarounds on how you can efficiently and easily do it.

Method #1 – Enter the TRIM Formula on your Worksheet

You can easily remove an unwanted space on MS Excel by entering the TRIM formula. Here’s how to use the function.

  • On your Microsoft Excel worksheet, select the last column of your data.
  • Click the Home button.
  • Select Insert.
  • Choose Insert Sheet Columns.
  • On the first cell of the new column, enter the following formula: =TRIM(A1), where A1 is the cell with the unwanted space.
  • Copy and paste the formula on the cells of the new column adjacent to where you want to delete the extra spaces.
  • Now, select all the cells from the new column and copy them.
  • Select the first cell from the new column and press Shift+F10. 
  • Paste it back by pressing Ctrl+V.
  • Once done, delete the new column.

Method #2 – Use the TRIM Button

If the TRIM formula sounds complicated for you or the function is not working for your requirements, there is a TRIM button on the ribbon that you can use on your Excel sheet cells. However, please note that you need to use a third-party Excel add-in like Ablebits to make this function easier for you.

  • First, you will need to install Ultimate Suite for Excel.
  • Once done, open your Excel worksheet.
  • Select all the cells.
  • From the menu bar, go to the Ablebits Data tab.
  • Select Trim Spaces.
  • Now, choose how you want to remove the extra trailing spaces on your worksheet.

RECOMMENDED: Click here to get the Ablebits add-in suite for your MS office Excel app.

Method #3 – Do a Find and Replace Workaround

  • Select the columns with the extra leading spaces.
  • Press Ctrl+H to open the Find & Replace window.
  • On the Find What option, press the space bar two times. 
  • Go to the Replace With option and press the spacebar once.
  • Click the Replace All button.
  • Click OK.
  • Do the steps several times until there is nothing else to replace.

What method above works better for you in removing an extra blank space on your Microsoft Excel worksheet? You can share your thoughts with us in the comment section below.



Please enter your comment!
Please enter your name here

Recent Articles