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.
Dim lMax As Double
Dim rCell As Range
gclsAppEvents.AddLog "^+w", "MatchColumnWidths"
If TypeName(Selection) = "Range" Then
If Selection.Cells.Count > 1 Then
‘if the first cell is active, set all columns to the biggest column
If ActiveCell.Address = Selection.Cells(1).Address Then
For Each rCell In Selection.Cells
If rCell.ColumnWidth > lMax Then lMax = rCell.ColumnWidth
Next rCell
For Each rCell In Selection.Cells
rCell.EntireColumn.ColumnWidth = lMax
Next rCell
‘if the user selected a particular cell (not the first one), set
‘all columns to the selected column
Else
For Each rCell In Selection.Cells
rCell.EntireColumn.ColumnWidth = ActiveCell.ColumnWidth
Next rCell
End If
End If
End If
End Sub
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.