Matching Column Widths

Here’s a report for a high volume, low margin product. Because the profit is so much smaller than sales and costs, column D is narrower than columns B and C.

Another example is the following table with names across the top. When the column widths are set to autofit, they all become different widths. Of course that simply won’t do.

Drag Multiple Columns

The first technique, and likely the most common, is to select all the columns and change the width of one of them. That will change the width of all of them. In the below figure, I select the entire columns B through K. It appears that column D is the largest so I select the column divider between D and E and drag it a few pixels to the right, then drag it back.

This changes all the selected columns to the width set for column D.

Well, that’s all I have to say about setting column widths. Of course I’m kidding. Let’s look at some keyboard only methods.

Format Column Widths

Select any cell in column D and click the Column Widths button on Home – Cells – Format (Alt + H + O + W). That will tell you the width of column D.

Make a note of the width and dismiss the dialog box (Esc). Now select cells in every column you want to change. For example, I selected B2:K2. It doesn’t have to be row 2. In fact, it could be multiple rows. All that matters is that every column that you want to change is included in the selection. Because the column widths aren’t the same, the Column Width dialog is empty.

I can type 8.43 in that box (the width of column D that I looked up earlier) and all the columns will be set to that width.

Paste Special

To use this method, select D2 and copy it (Ctrl+C). Next, select B2:K2 and choose Paste Special from the Ribbon (Alt + H + V + S). Choose the Column widths radio button (Alt+w) and click OK (Enter).

Build Your Own

You knew this was coming, didn’t you? Didn’t you? I wrote a macro and assigned it to Ctrl+Shift+W.

Now I can select a range, press Ctrl+Shift+W, and my column widths are set. From the examples above, I select B2:K2, press Ctrl+Shift+W, and all the columns match the largest column (D). If you simply select a range, it will make all the columns the same size as the largest column. If you want to choose a different column, first select the range, then use the Tab key to move to the column you want to mimic.

If you want to mimic the first column, and it’s not the largest, you have to select more than one row and press Enter to move to first the column in the second row.

7 thoughts on “Matching Column Widths

  1. For the formatting column widths manual method, if you click OK to dismiss the Column Width dialog box, you can then press F4 after selecting all the columns to set all columns to the width of column D.

  2. Way long ago, before you could protect a sheet and specify that users could resize columns, I made a little form to let users change columns widths. It had a textbox for entering the width and a slider with an accelerator key for really fast sizing :-). I loved that form, and was kind of sad when the next version of Office made it irrelevant. The reason it’s relevant here is that it also had a “Size to Largest” button.

    The one change I’d make to your code is to have it cycle only through the cells in one row of the selection. Otherwise a user could select entire columns (maybe already selected from doing the mass column resize) and then run it. I just did that and, five minutes later, wishing I’d saved my work.

  3. Ooh, sorry about that. Fixed to replace

    with

  4. No problem. I went into it with my eyes open :-). It’s a rare day that I don’t crash Excel, and it’s mostly just data connections anyway.

  5. Thanks, great post! There is often a need to adjust many columns at once when e.g. working with large reporting forms that I often encounter.
    /Mats

  6. This might be an equivalent:

  7. Of course it should be:

Leave a Reply

Your email address will not be published. Required fields are marked *