Technology

Simple and Quick Guide to Deleting Columns in Excel

Deleting columns in Excel is a useful technique for removing empty and unnecessary columns from your data table. If you're looking for a step-by-step guide on how to delete columns in Excel, then look no further. Here is a detailed tutorial to help you with the process.

Administrator

26 December, 2023

157

For office work today, Excel has become an essential tool and while working with Excel on the computer, it is necessary to identify empty columns, columns without data or columns with unimportant data, so it is necessary to delete columns in Excel.

That’s why in today’s article, FPT Shop will show you how to delete columns in Excel with various solutions, from deleting columns in Excel with just a simple mouse click to save time and effort, to deleting columns in Excel with VBA code which is very interesting.

Why Delete Columns in Excel?

When deleting columns in Excel, you can eliminate unnecessary columns or change the structure of the spreadsheet to create a more suitable layout for your work requirements. It can also help make your spreadsheet easier to read and minimize confusion when working with data. In addition, deleting columns in Excel also optimizes the performance of the spreadsheet by minimizing the amount of data displayed on the screen and increasing calculation speed.

In some cases, deleting columns in Excel can also be used to prepare data for other operations such as data analysis or chart creation. When working with large data sets, removing unnecessary columns can help you create a more focused data set, making data analysis and visualization easier.

In addition, deleting columns in Excel can also be used to save storage space and optimize the speed of opening spreadsheets on low-configured computers. When working with large spreadsheets with a lot of data, deleting unnecessary columns for your work can save storage space and increase data access speed.

Some Notes When Deleting Columns in Excel

When you delete a column in Excel, please note the following to avoid losing important data or affecting other formulas and features of the spreadsheet:

Make sure you have selected the correct column to delete. If you delete the wrong column, the data in that column will be lost.
If the column contains a formula, check if the formula contains references to cells in that column. If so, you need to update the formula before deleting the column to avoid losing calculation results.
If the column contains data used in other worksheets, you need to check if deleting the column will affect other features of the spreadsheet. If so, you need to adjust those formulas and features to accommodate the change.
If you have copied that column to another worksheet, check if you need to keep the original column in the current worksheet before deleting the column.
If you want to keep the data in that column but do not want to display it on the spreadsheet, you can hide the column instead of deleting it.

Therefore, before deleting columns in Excel, make sure you have backed up important data and cross-reference it with documents, and only perform the deletion when you are sure it will not affect the integrity and accuracy of the data in the spreadsheet. If you accidentally delete a column, that action can lead to data loss and damage to your important work.

6 ways to delete columns in Excel on a computer

Knowing how to delete columns in Excel is one of the basic skills for Excel users. But few people know that there are many different ways to delete columns in Excel to choose from and use. So let’s find out all the ways to delete columns in Excel with FPT Shop.

1. Delete columns in Excel from the right-click menu

Deleting columns in Excel from the right-click menu is one of the most popular methods that most users choose to use. The right-click menu in Excel is where commonly used commands are pre-installed, including the Delete command to delete columns in Excel.

Step 1: Open the Excel file, then select the column to delete by clicking the left mouse button on the column’s name. This step will help you select the entire column.

Step 2: Right-click anywhere in the selected column.

Step 3: From the pop-up dialog, choose Delete to delete the column you want.

2. Delete columns in Excel using shortcut keys

One of the fastest ways to delete columns in Excel is to use shortcut keys in Excel. For users who frequently use keyboard shortcuts, this will be a very quick and easy solution.

Step 1: Select the column to delete by clicking the left mouse button on the column’s name.

Step 2: Press the shortcut keys Ctrl (for Macbook it’s the Command key) and – on the keyboard. The selected column will be deleted.

This method is fast but requires you to remember the key combination to execute. Expanding on this, the corresponding key combination is Ctrl and + will also help you add new columns or rows to Excel.

3. Delete columns in Excel using the command button on the ribbon

Using the command button on the ribbon toolbar is one of the simplest and most visual ways to delete columns in Excel without using the keyboard. The steps to perform are as follows:

Step 1: Select the column or any cell in the column to delete.

Step 2: Access the Home tab, find the Cells group.

Step 3: At this step, there are 2 ways to delete columns in Excel to choose from depending on how you selected the column in step 1. If you selected the header of the column you want to delete, simply click the Delete button.

For the case where you select any cell in the column you want to delete, left-click on the arrow to the right of the Delete button. From the drop-down menu, select the Delete Sheet Columns option. This is the easiest to remember method and helps you delete columns without having to select the entire column.

4. Delete columns in Excel using the Go-To Special feature

When working with large Excel data tables, you may encounter cases where your data contains many completely empty columns. If you choose to delete columns in Excel using the above methods, it will take a long time. To learn how to delete columns in Excel in batches, you can use the Go-To Special feature.

Note: The Go-To Special feature is only suitable for cases where the columns to be deleted are empty cells. If there are empty cells in the columns that you want to keep, this method will also delete these columns.

Step 1: Select the entire Excel data set you want to process.

Step 2: On the Home tab, find the Editing group and click the Find & Select button to open the Find and Replace dialog box.

Step 3: In the Find and Replace dialog, select the Go to Special… option.

Step 4: When the Go to Special dialog box appears, select the Blanks option.

Step 5: Click the OK button and the empty cells in your data file will be selected.

Step 6: After completing these steps, the empty columns will be selected as follows:

Right-click within the selected column area, from the popped up dialog, choose Delete and then choose Entire Column and press the OK button.
From the Home tab, find the Cells group, left-click on the arrow to the right of the Delete button. From the drop-down menu, select the Delete Sheet Columns option.

5. Delete columns in Excel using VBA

VBA stands for Visual Basic for Applications, a programming language commonly used in office applications such as Word, Excel, or PowerPoint. Therefore, using VBA will provide you with another way to delete columns in Excel. Of course, this solution is only suitable for those who love VBA or those who like to explore new things more than usual.

Below is the VBA code snippet that will show you how to delete columns in Excel.

‘Code Developed by Sumit Bansal from https://fptshop.com.vn/
Sub DeleteBlankColumns()
Dim EntireColumn As Range
On Error Resume Next
Application.ScreenUpdating = False
For i = Selection.Columns.Count To 1 Step -1
Set EntireColumn = Selection.Cells(1, i).EntireColumn
If Application.WorksheetFunction.CountA(EntireColumn) = 0 Then
EntireColumn.Delete
End If
Next
Application.ScreenUpdating = True
End Sub

The above VBA code uses a simple For-Next loop to check through each column in the selected range and checks if the COUNTA function value for all cells in that column equals 0.

In the case that the COUNTA function value is 0, which means the column is empty, the VBA macro code will delete that column. And in the case that the COUNTA function value is greater than 0, which means the column is not empty, the column won’t be deleted.

How to use the above VBA macro code to delete columns in Excel

Here are the steps to use the above VBA macro code to delete columns in Excel.

Step 1: Select the data range that contains the empty column(s).

Step 2: Click the Developer tab on the ribbon toolbar.

Step 3: Click the Visual Basic button in the Code feature group on the left top corner. This action will open the Visual Basic editor window in Excel.

Step 4: In the Microsoft Visual Basic for Applications window displayed, click the Insert menu and choose the Module option from the drop-down menu. This will insert a new module appearing in the Project Explorer pane (in case you don’t see the Project Explorer pane, go to the View menu then choose the Project Explorer option).

Step 5: Copy and paste the above VBA macro code into the module window in the right pane.

Step 3: Enter the following formula into the first cell of the inserted row and copy it to all cells in the row:

=IF(COUNTA(A2:A1048576)=0,”Blank”,”Not Blank”)

Step 4: Press the Ctrl+F shortcut key or go to the Home tab, find the Editing group, and click the Find & Select button to open the Find and Replace dialog box.

Step 5: In the Find and Replace dialog box, enter Blank in the Find what box, and click the Options button.

Step 6: In the drop-down menu, in the Look in box, select Values, and check the Match entire cell contents box.

Step 7: Click the Find all button, the search result will return the column with the text Blank and display all the search results in the result pane below.

Step 8: Now your task is to select one search result, then press the Ctrl+A shortcut key to select all the other results. Then right-click on any selected cell (or any cell with the displayed content Blank), and select the Delete option from the displayed right-click menu.

Step 9: In the Delete dialog box, select the Entire Column option and click the OK button to apply the change.

Notes when using the COUNTA function

The COUNTA function will only return 0 if all cells in the column are empty, in the case where the column has a title and doesn’t contain any cells that need to be removed, you need to adjust the formula to exclude the header row. For example, you can use the formula =IF(COUNTA(A3:A1048576)=0,”Blank”,”Not blank”) if even empty columns contain headers.
In order for this formula to work, the columns must be truly empty. For example, if there are white spaces in the cells in empty columns, although they may appear empty, the COUNTA function will not consider them empty.

How to delete columns in Excel on a phone

In case you often use Excel on mobile devices such as tablets or phones, to delete columns in Excel, you can follow these steps:

Step 1: Open the Excel file you want to delete columns on your phone. You can open an Excel file using many programs like Google Sheets, Office 365…

Step 2: Select the column you want to delete by tapping on the column’s header. In this example, I am selecting column G.

Step 3: From the list of options appearing on the screen, choose the Delete option.

Immediately, you will see that the entire column has been deleted from the data of the spreadsheet. If you know that you have accidentally deleted columns, click the Undo button to restore the data, then repeat the above steps with other columns.

You need to note that deleting this column will also delete all the data in that column, so make sure you have backed up important data before performing this operation.

Conclusion

Mastering the various ways to delete columns in Excel not only helps you organize Excel data tables neatly, avoid unnecessary data disruptions but also increases your work efficiency. Deleting columns in Excel using keyboard shortcuts, right-click menus, or any solutions above will save you a lot of time and effort, and depending on your needs and habits, you can choose a suitable way to delete columns in Excel to use.