How to Delete Columns in Excel
Home >> Excel Tutorials from Compute Expert >> Excel Tips and Trick >> How to Delete Columns in Excel
In this tutorial, you will learn how to delete columns in excel using various methods.
Columns are important parts of our excel worksheet as we usually put our data categories division there. Sometimes, we need to remove the columns we don’t need anymore so our data table can be cleaner.
Want to know how to do this in excel? Read on!
Table of Contents:
How to Delete Columns in Excel 1: Delete Column Menu
The first method to delete excel columns we are going to discuss is the one using the delete column menu.Here are the steps to run the menu for our purpose.
-
Highlight the columns you want to delete by clicking their letter notes on the top. You can highlight adjacent columns by clicking and dragging across those columns. If you want to highlight non-adjacent columns, then hold your Ctrl (Command on Mac) button while clicking on those columns letter notes
-
Go to the Home tab and click Delete
-
Done!
How to Delete Columns in Excel 2: Right-Click
The second method is by using the right-click action. This method is probably the simplest if you compare it with the other two methods discussed in this tutorial.-
Highlight the columns you want to delete by clicking their letter notes on the top. You can highlight adjacent columns by clicking and dragging across those columns. If you want to highlight non-adjacent columns, then hold your Ctrl (Command on Mac) button while clicking on those columns letter notes
-
Right-click on one of those highlighted columns and choose Delete
-
Done!
How to Delete Columns in Excel 3: Shortcut Buttons
There are also shortcut buttons to delete columns in excel if you want to use them. However, this is not a one-go shortcut as there are some steps to do before you can delete your sheets.Note that you also cannot delete non-adjacent columns simultaneously by using this method.
-
Highlight the cells that reside on the columns you want to delete. You can highlight cells in adjacent columns by clicking and dragging across those cells
-
Press Ctrl + Space Bar (Control + Space Bar on Mac) buttons simultaneously
- Press Ctrl + - (Command + - on Mac) buttons simultaneously
-
Done!
How to Delete Blank/Empty Columns in Excel
If your blank/empty columns are only a few, then you can use one of the three methods we discussed previously. Highlight the blank columns you have and then delete those columns!However, if you have many blank columns you want to delete, then highlighting them one-by-one can be troublesome. Here are the steps you can implement so you can do the process quickly.
-
Make sure one row on the top of your data table which blank columns you want to delete is empty. You can do that by inserting one row at the top of your data table (the fastest way is probably by highlighting the most top row in your data table first. Then, right-click on it and select Insert)
-
Type this COUNTA formula with a criterion into the most left cell of that one row above your data table. This formula assumes that the cell is at A1
= COUNTA ( A2:A1048756 ) = 0
The cell range input should be from the top of your data table until the most bottom cell in your worksheet. This is to make sure that the columns you will delete using these steps are completely empty
-
Copy the COUNTA formula you have written until the right edge of your data table in the row. To do that fast, highlight the formula cell and move your pointer to the bottom right of your cell cursor (until your pointer becomes a +)
Then, click and drag until you reach the right edge cell of your data table in the row. Release the drag after that
You will get the TRUE/FALSE of the emptiness checking of the columns where your COUNTAs are. TRUE means the column is empty and FALSE means the column isn’t empty
-
Highlight the cells that contains your formulas and your data table too. Then, click on the Sort & Filter menu in the Home tab. Choose Custom Sort from the dropdown
-
Click the Options button in the dialog box shown and choose Sort Left to Right before clicking OK
-
Choose Row 1 in the “Row” dropdown, Values in the “Sort on” dropdown, and Largest to Smallest in the “Order” dropdown. Then, click OK
-
Your data table has been sorted from left to right with the blank columns are in the most left! Highlight those blank columns, right-click on them, and choose Delete
-
Done! You can now delete the row containing the COUNTA formulas if you don’t need it anymore
How to Delete Infinite Columns in Excel
In our excel worksheet, it can seem that we have infinite columns to work on (although, actually, the number of columns in an excel worksheet is “just” 16,384 columns since excel 2007).We might sometimes want to delete those columns from the end of our data table until the end of the worksheet. This possibly can make the display of the data in our worksheet better depending on the case.
Unfortunately, we cannot do that as the columns in an excel worksheet will always be 16,384 (if you delete a column, then excel will just add a blank column into the right edge of your worksheet). If you need to make it seems like those columns are gone, however, then you can hide them instead.
Here are the steps you can implement to do that!
-
Highlight the column from where you want to “delete” the columns. Do that by clicking the column’s letter note on the top
-
Press Ctrl + Shift + -> (Command + Shift + -> on Mac) buttons simultaneously. This will make you highlight columns from where you currently highlight until the last column in your worksheet
-
Right-click on the highlighted columns and choose Hide
-
Done! Now, the columns on your worksheet other than the ones you didn’t highlight seem gone, isn’t it? If you need them back at some point, then just unhide them!
Exercise
After learning various methods to delete columns in excel, now let’s do this simple exercise to deepen your understanding!Open an excel file, make dummy data, and try to delete columns by using each of the three methods. Download the sample file we use throughout this tutorial here if you want to. Try also the methods to delete blank and infinite columns as well!
How is the result? Can you do it? If you aren’t sure, then just read again the discussions of the columns deletion methods given in this tutorial!
Additional Note
Be careful when you want to remove your columns. If cells in them are references for formulas, then those formulas will produce errors because of the removal of the columns!Related tutorials you should learn:
