Autofit Excel Column Widths using the Mouse

 In Excel Tips

Excel Shortcuts with Launch ExcelExcel provides a neat way to autofit the width of a column using either a mouse double-click or using the built in menus. In this post I will show you how to use the mouse to automatically resize the width of a column so that the contents of the column can be seen in full…

What happens when your Excel Cell Content is too Wide to Fit in the Column?

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:

Picture showing two problems with data table

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

  • Problem 1: You can’t read the values in columns “Company” and “Email” as they are cut off by the values in the cells on their right.
  • Problem 2: You can’t see any of the date values in the birthday column because that column is not wide enough – instead all you see is a row of #########

You could try to resize the columns one at a time by clicking and dragging on the column borders between column headers for all 6 columns, but there is an alternative way.

Mouse double click trick for resizing column widths

Download File from Launch Excel websiteLet me introduce you to a mouse double-click trick that can save you some time. If you want to download the sample .xlsx file with the same final contacts list so you can follow along as you read the post, click on the download icon or click here. The file is very small (12 kb) and also zipped so when you download it you will need to unzip it first.

Animated gif showing double click trick 1

Demo of mouse double click trick on one selected column

  • Move the mouse pointer to the column border between columns D and E
  • The white cross will change to a black cross with arrows pointing left and right
  • Double click the left mouse button
  • Presto – column D has been autofitted!

You can either repeat this process for each of columns C, D, E, F and G, or you can select all of columns C to G and use the double click trick on any of the columns in your selection.

Animated gif showing double click trick 2

Demo of mouse double click trick on multiple selected columns

If you have followed along, you might wonder why I didn’t double click on column B. What happens if you do? The column width gets adjusted to the longest value, which is in row 3. This looks bad as there is too much empty space in column B.

PNG showing column that is too wide

Using autofit in the first column has created too much blank space next to each name!

Autofit column width using Excel’s Built-In Menus

Now let’s go back to the built in menus and use the autofit column width feature. The key to using this feature is to 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.

Animated picture showing the autofit feature in Excel

Using the autofit feature in Excel 2010 (Home > Cells > Format > Autofit Column Width)

  • Select cell B5
  • Expand the selection to the current region (using Ctrl + Shift + 8)
  • Select the Home tab on the Excel ribbon
  • Find the Cells sub-area
  • Click on Format
  • Click on Autofit Column Width

Read More: Resize & autofit column widths in Excel

Click on one of the following post links to read more on this topic.

Victor Chan
Victor has been using Excel intensively since 2002. He is a Chartered Accountant (Fellow of the ICAEW) and has an MEng in Manufacturing Engineering from the University of Cambridge. He is on a mission to help you learn Excel and VBA with practical video tutorials and awesome online courses.
Join over 9,000 subscribers


Recommended Posts
pingbacks / trackbacks
Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.