An IMA® (Institute of Management Accountants) member in Wisconsin sent in this question. They routinely download a data set with hundreds of blank columns. Their data has some identifying labels in column A and then 365 columns with daily dates and data. Each column of daily dates is separated by a blank column. They want to quickly delete the blank columns.
One approach is to make use of the F4 key in Excel to repeat the last command. You could start in the first blank column (for example, cell C1). From the Home tab in the ribbon, select Delete, Delete Sheet Columns, as shown in Figure 1.
Figure 1
When you delete column C, the cell pointer will stay in column C and the column for January 2 will be the active cell.
Press the right arrow once to move to the blank column between January 2 and January 3. This is the amazing part. The last command that you performed was Delete Sheet Columns. Most people don’t realize that you can repeat the last command by pressing the F4 key. Press the F4 key, and the blank column between January 2 and January 3 is deleted.
As an aside, some people know that Ctrl+Z is Undo and Ctrl+Y is Redo. In this example, pressing Ctrl+Y and F4 is equivalent. However, since this method involves deleting hundreds of individual columns, it’s easier to use F4 rather than to press Ctrl and Y.
You can continue deleting every other column by using the right arrow to move to the next blank column and then pressing F4 to delete that column. Although the action of pressing the right arrow and then F4 more than 360 times can be a mindless activity, it still will take at least 60 to 120 seconds to delete all of the columns this way. Fortunately, there’s a faster way.
Using Go To Special and Blanks
Start by selecting an entire row. In Figure 2, I’ve selected the heading row by clicking on row 1.
Figure 2
From the Home tab, select Find & Select, Go To Special.
Figure 3
In the Go To Special dialog box, choose Blanks as shown in Figure 4.
Figure 4
There will be a slight delay after you press OK to close the Go To Special dialog box. After a few seconds, you’ll see that you have successfully selected all of the blank columns as shown in Figure 5.
Figure 5
With those blank cells selected, repeat the command from Figure 1: Home, Delete, Delete Sheet Columns. This command will actually cause the blue spinning circle to appear for 20 seconds while Excel deletes 365 individual columns and performs a recalculation in between each deletion. Don’t be alarmed if the Title bar reports that Excel isn’t responding. It will eventually finish deleting the columns.
When the command is complete, you’ll still have every other column selected, as shown in Figure 6.
Figure 6
Comparing the two methods, the first method would require about 725 keystrokes and 120 seconds. The second method only requires nine keystrokes and perhaps 20 seconds, depending on the speed of your computer. There’s also less chance for a keying error using the second method.
Sorting Columns Left to Right
The original problem from the IMA member in Wisconsin didn’t have actual dates in the heading row. The original data set included text dates as headings. If the headings are text dates, the following method wouldn’t work.
Your goal is to select everything from cell B1 to the end of the data. Select B1. To do this, press Ctrl+Shift+End, and you’ve selected all of the data from the first date column to the last date column.
On the Data tab, choose the Sort icon to open the Sort dialog box. Near the top of the dialog box, on the right side, click the Options… button. In Sort Options, choose Sort left to right. Click OK to close Sort Options. Then in the Sort By drop-down menu, choose Row 1.
Figure 7
In Excel, blanks always sort to the end of the data. With actual dates in row 1, the 365 date cells sort to be the first columns and then the other blank columns sort to the right. If the blank columns were narrower than the data columns, you would still need to select Home, Format, AutoFit Column Width to adjust the column widths.
Even though there are more steps involved in sorting data left to right, Excel is able to sort the data faster than Excel can remove 364 individual columns, and this method might finish more quickly than using Go To Special, Blanks.
This method will only work if the headings naturally sort into the proper sequence. If you have text dates in row 1, then it’s safer to use the Go To Special method described above.
