Excel How To Quickly Delete All Blank Columns In Excel 3 Ways - Episode 2641

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Apr 16, 2024.
Microsoft Excel Tutorial: Three ways to delete hundreds of tiny blank columns in Excel.

Welcome back to another MrExcel tutorial! In this video, we tackle a common Excel dilemma: deleting 365 blank columns swiftly. Hailing from Green Bay, Wisconsin, our viewer shared their data download challenge, and we're here to provide not one, not two, but three lightning-fast solutions.

First up is the Ctrl+Y or F4 trick, a nifty shortcut that repeats the last command. Watch as we demonstrate how to breeze through column deletion with just a few keystrokes, saving you valuable time.

Next, we delve into the power of Excel's Go To Special feature. Learn how to select all blank cells in a flash, followed by a quick deletion process. Despite a brief wait, this method proves significantly faster than manual deletion.

But wait, there's more! Our third method involves a clever sorting technique, perfect for datasets with chronological data. Witness how a simple left-to-right sort eliminates blank columns efficiently, leaving your spreadsheet tidy and optimized.

Which method is your favorite? Do you have an even faster solution? Share your thoughts and techniques in the comments below! Let's exchange Excel wisdom and explore the possibilities, whether it's VBA magic or Power Query prowess.

Don't forget to Like, Subscribe, and Ring the Bell to stay updated on more Excel tips and tricks. Have questions or suggestions for future tutorials? Drop them in the comments section—we love hearing from you!

Thank you for tuning in, and until next time, happy Excel-ing from MrExcel! 📊✨

To download the workbook from today: Excel How To Quickly Delete All Blank Columns In Excel 3 Ways Sample Files - MrExcel Publishing

Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel

You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel

#excelhacks #excelshortcut #excelchallenge #shortcutkey #excelsort #excelgotospecial #excelsequence #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

Table of Contents
(0:00) Problem Statement: How to delete 365 blank columns
(0:24) Deleting columns one at a time would be tedious
(0:34) Using F4 key in Excel to repeat last command
(0:53) Go To Special Blanks and then Delete Column
(1:46) Excel Sort Left to Right
(2:30) Repeating the Sort method and adjusting column width
(3:10) Would not work with text dates
(3:20) Have something faster?
(3:32) Like, Subscribe, and Ring the Bell

This video answers these common search terms:
how to quickly delete blank columns in excel
excel tips for removing empty columns fast
mrexcel tutorial: delete 365 blank columns in seconds
fast excel tricks: removing empty columns effortlessly
efficient ways to delete blank columns in excel
excel column deletion hacks: save time with mrexcel
quick methods to remove blank columns in excel
excel tutorial: deleting empty columns made easy
speed up your workflow: delete blank columns in excel
excel column cleanup: fast solutions with mrexcel
maxresdefault.jpg


Transcript of the video:
One Problem. Three Solutions.
Each One Faster than the Previous. But I bet YOU have something even faster.
Three ways to quickly delete 365 blank columns between data.
This question from Green Bay, Wisconsin. Check out this data they're downloading.
So we have every day of the year going across. These are actual dates up here in row one.
But between each of those there is a blank column. It would take forever to choose the column.
Home, Delete, Delete Sheet Columns. And then repeat that.
Luckily, my first method is a great trick here.
That's either Ctrl+YY or F4, which repeats the last command.
So here from column D, I press F4 and then right arrow. F4, right arrow.
F4, right arrow F4.
At least it's a little bit faster. Two keystrokes per column.
Still over 700 keystrokes you're going to need for that method.
Here's a faster way to go. Choose one row like this.
From the Home tab, Find and Select, Go To Special. Choose Blanks.
Click Okay. See it took about a second to do that.
Now I have all the blanks selected. Now we go to Delete, Delete Sheet Columns.
And it's going to take a good 18 to 20 seconds on this computer.
You get the blue spinning circle. And you get Excel Not Responding.
But just wait it out. It's deleting those columns one at a time.
And it eventually works.
Kind of strange - it still has every other column selected.
No big deal there. Just select something else.
Now this third trick will only work if you really have something up there like Dates.
In this particular case, I'm going to use a Left-to-Right sort.
I start from the very first date. I'm going to hold down three keys.
Control, Shift, and Ctrl+End.
Ctrl+End select out to the last column and last row with data.
By adding Shift in, I select from that first date over to the last cell. On the Data tab, go into the Sort dialog box.
We're going to go into Options.
This beautiful option called Sort Left to Right - Click.
Okay, I'm going to sort based on row one, oldest to newest.
The sort happens much faster than the deleting every other column.
Now in this particular case, these columns had a width of three and these columns had a width of one.
Let me Undo. We'll redo that Sort.
Sort, Options, Left to Right, Okay. Sort by row one.
Click okay. And then right here.
Let's go to Home, Format, Autofit Column Width and everything is fixed.
Any of these three methods are faster than going through and hitting Home, Delete, Delete Sheet Columns 365 times. I think the sort is probably my favorite.
If you have real data up here that can be sorted.
If that had been text dates like apostrophe Jan, space 21, then it's not going to work.
My gut says that someone watching this has a faster way to do this.
So down in the YouTube comments below, let me know how you would do it. Maybe a little VBA?
I don't know - Power Query? Could Power Query do this?
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
 

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top