In the previous post we discussed how Excel provides a neat way to autofit the width of a column using either the mouse double-click trick or using the built-in Excel menus.
In this post I will show you how to use a keyboard shortcut to accomplish the same task. If you want to learn more keyboard shortcuts you can download my free PDF with over 100 popular Excel keyboard shortcuts by clicking here.
Here is the scenario. You have a table of contacts that lists names, e-mails and birthdays. You have either imported this list from your e-mail program or perhaps just typed the list into Excel. But as it stands there are two problems:
The list of contacts in the screenshot above has two problems
(1) we can’t read all the text, (2) we can’t see any of the dates
In the previous post we looked at the mouse double-click trick, where you select the columns you want to resize then double click on a column border to automatically resize the columns to fit the widest contents of that column. Then we looked at the autofit column width feature, accessed via clicking on the built in menus.
Now we are now going to use keyboard shortcuts.
(Excel 2007 & 2010)
As in the previous post we will use the built in menus and use the autofit column width feature, this time using keyboard shortcuts to access Excel’s cool autofit feature.
First select the cells you want to autofit. Here we don’t want Excel to resize column B to fit the text in row 2 and row 3, because we only want the column to be just wide enough for the first names in our contact list. So we first select the current region of cell B5 by pressing (Control + Shift + 8).
Next type the keyboard shortcut ALT + H + O + I to automatically resize the columns. If you want to learn more keyboard shortcuts I recommend you download my free PDF with over 100 Excel keyboard shortcuts by clicking here.
Autofit columns using keyboard shortcuts in Excel 2007 & 2010
Step by step instructions:
The keyboard shortcut ALT + H + O + I is my favorite method for automatically resizing column widths. I find this method is most useful when I am already using the keyboard to enter data, and don’t want to move my hand to the mouse.
The good thing about memorising this keyboard shortcut is that you don’t need to hunt for each menu item, as these might look different on different computers or if you have resized the Excel window they might not even appear where you expect. Whereas the keyboard combination is fixed and does not depend on who’s computer you use (as long as you have a standard keyboard layout!) or whether you have a resized window.
To illustrate this, have a look at the screenshot below. The window has been reduced in size, and the Format Cells button is not visible. You need to click on the “Cells” dropdown first, then this will show you the Format button. In fullscreen mode, Excel normally displays the Format button so that it is clickable in the Cells sub-area, rather than being hidden in a dropdown. If you use the ALT + H + O + I shortcut then you won’t need to go hunting for the Format cells button.
Screenshot showing that Format Cells button is hidden (normally visible
on the Home ribbon when Excel is full screen)
For those of you who use Excel 2003 or earlier, the process is similar but the keys are different. You use ALT + O + C + A.
If you want our popular reference guide with over 100 Handy Excel Keyboard Shortcuts, please check out our Excel Shortcuts PDF page
Click on one of the following post links to read more on this topic.
Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.