Deleting blank columns and rows is a tedious task when you are working with a large set of data. And manual deletion is not even an option. In this article, you’ll learn to delete the blank columns in excel in some simple steps using Excel built-in tool named Go To Special. It is a quick and easy way to remove the blank columns. While this makes it a simple alternative to implement, be aware that it may cause your document to become misaligned. Always save a backup copy of your document before you start deleting cells to be safe.
There are two ways to delete the blank columns in excel:
- Manual selection and deletion
- Deleting columns using Go To in-built tool from excel
Note: The tutorial is for Microsoft Excel 2013.You may find the same or different steps in other Microsoft Excel versions.
Deleting blank columns in Excel using manual selection and deletion
We can delete blank columns in Excel using manual selection and deletion. This method works with all types of data but it is time taking, here I would suggest to use this method only when your data is less. If you have a large number of columns to delete then move on to the second method. Now we understand this method with the help of an example. So consider the example
Step 1: Select the blank columns, to select the blank columns press Shift and press the down arrow to the row upto you want to select the column.
Step 2: Then right-click on the selected column. A drop-down will appear (as shown below). Select the Delete button. The selected column will be deleted.
Repeat the same steps for all the blank columns left in the required worksheet and delete them.
Deleting blank columns in Excel using Go To
We can also be deleting blank columns in MS Excel using Go To. So follow the following steps:
Step 1: Open the required Excel where you want to delete the blank columns.
Step 2: Select all the data by selecting the required rows and columns and press F5 from the keyboard. A dialogue box will appear(as shown below) and select Special. The special feature allows users to select all that has matching criteria as selected such as blanks, numbers, formulas.
Step 3: After selecting special, a window (as shown below) will appear. Click on Blanks (as shown) and then select OK. This operation will select all the blank columns from the selected data.
Step 4: Then go to the Home tab>Cells Group>Delete i.e go to the Home tab and in the Cells group click on Delete(as shown below). The delete will help to delete the selected row, column, cell, and entire sheet.
Step 5: After selecting Delete a drop-down will appear (as shown in the image). Select Delete Cells.
Step 6: After selecting Delete Cells, a pop-up window will appear(as shown below). Select Shift cells left. This option will shift all the leftover cells left after deleting the blank columns. Click OK.
All the blank columns from the selected range will be deleted.
My Personal Notesarrow_drop_up
FAQs
Select all blank columns - click on the first column letter, press Shift, and then click the letter of the last blank column. Right-click the selected columns and choose Delete from the pop-up menu.
How do I delete blank columns in Excel that go on forever? ›
Step 1: Select the blank columns, to select the blank columns press Shift and press the down arrow to the row upto you want to select the column. Step 2: Then right-click on the selected column. A drop-down will appear (as shown below). Select the Delete button.
How do I delete all blank rows and columns in Excel? ›
I. Removing Blank Rows with Find & Select
- Click Find & Select.
- Click to Go to Special.
- Choose Blanks.
- Click OK and then all the blank rows/cells will be highlighted.
- Choose the Delete under Cells section on the Home Tab.
- Click Delete Sheet Rows.
Delete cells, rows, or columns
- Select the cells, rows, or columns that you want to delete.
- Right-click, and then select the appropriate delete option, for example, Delete Cells & Shift Up, Delete Cells & Shift Left, Delete Rows, or Delete Columns.
To do this, select the row or column you want to delete, then press Ctrl+-. If you want to delete multiple rows or columns at once, you can select them all by holding down the Shift key while you click on the row or column headers. Then, you can press the Delete key or right-click and click on Delete, just like before.
What is the shortcut to remove blank columns and rows in Excel? ›
You can also use this shortcut to delete all blank columns in your spreadsheet. Simply press the Ctrl + A keys, and then press the Ctrl + - keys. This will delete all blank columns in your spreadsheet. If you want to delete all blank cells in your spreadsheet, you can do so by using the Ctrl + A keys.
Can you delete all empty cells in Excel? ›
Click the filter icon from any column. In the dropdown menu, click Clear, then check the (Blanks) option. This will sort all the blank cells in the range you chose.
How do I quickly delete blank rows in Excel? ›
Easy Ways to Remove Blank or Empty Rows in Excel
- Select the row. Click its heading or select a cell in the row and press Shift + spacebar.
- Right-click the selected row heading. A drop-down menu appears.
- Select Delete.
To remove several columns, select the columns by using Ctrl + Click or Shift + Click. The columns can be contiguous or discontiguous. To remove all columns except the selected column, select one or more columns, and then select Remove Other Columns.
What is the shortcut key to delete the blank columns? ›
Delete a Row/Column
To delete a row or column using keyboard shortcuts, move your cursor to the row or column you want to delete. Click 'Shift' plus the 'Spacebar' to select the row, or 'Ctrl' plus the 'Spacebar' to select the column, then click 'Ctrl' plus the 'Minus' sign found in your number pad.
Method A: Remove blank rows with the Go To Special command
- Select the range you want to remove blank rows, click Home tab, then in Editing group, click Find & Select > Go To Special.
- In the Go To Special dialog, check Blanks option.
- Click OK, now all blank cells in the selection have been selected.
On the Home tab, in the Editing group, click Find & Select > Go to Special. Or press F5 and click Special… . In the Go To Special dialog box, select Blanks and click OK. This will select all empty cells in the range.
How do I delete blank rows in Excel 2022? ›
How to delete blank rows if 1 or more cells are blank
- In the Home tab, click the 'Find & Select' button on the right side of the Ribbon, so you can start to find blank rows.
- Select 'Go To Special'.
- Select Blanks and click OK. ...
- From the Home tab, click the arrow below the Delete button and choose 'Delete Sheet Rows'.
Hold the shift key and then click on the last empty row in your data set. This will select all the empty rows in the dataset. Right-click on any of the select column number. Click on Delete Rows 6- 14 (in your case it will show the number of your rows).
Why isn't Excel letting me delete columns? ›
to delete a column in a spreadsheet, right-click the column heading, where you see the column letter. This should open a context menu where you see the "Delete" command. If the delete command is grayed out, the worksheet may be protected and you need to unprotect it first via Review ribbon > Unprotect Sheet.
How do I get rid of unused GREY cells in Excel? ›
One of the easiest ways to grey out unused cells in Excel is by using the conditional formatting feature. This one allows you to apply formatting based on certain conditions that you specify.
How do you get rid of unwanted columns? ›
To remove a single column, select the column you want to remove, and then select Home > Remove Columns > Remove Columns. To remove several columns, select the columns by using Ctrl + Click or Shift + Click. The columns can be contiguous or discontiguous.
How do I remove columns from a table in Excel? ›
Delete a row or column
Select a row or column that you want to delete. Press Backspace, or select the Table Tools Layout tab >Delete, and then select an option. Note: In Excel, select a row or column that you want to delete, right-click and select Delete , and choose the option you want.
How do I get rid of non empty cells? ›
If you do not have data in cells that can be shifted off of the worksheet you can reset which cells Excel considers nonblank. To do this, press CTRL+End to locate the last nonblank cell on the worksheet. Delete this cell and all cells between it and the last row and column of your data and then save.
How do you clear formatting in Excel from unused cells? ›
To remove the excess formatting in the current worksheet, do the following:
- On the Inquire tab, click Clean Excess Cell Formatting.
- Choose whether to clean only the active worksheet or all worksheets. After excess formatting has been cleared, click Yes to save changes to the sheets or No to cancel.
In the Cells group, click Format. A drop-down menu appears. Click Visibility, select Hide & Unhide and then Unhide Rows.
...
To unhide a row or rows by double-clicking:
- Select the row headings above and below the hidden row(s). ...
- Hover the mouse over the hidden row headings.
