Overview
Is there anything more annoying than opening up an Excel file and seeing a ton of ######## characters? Or cut-off text? Or text that takes up so much space you can’t see any other columns?
Resizing Excel columns is a really common task – and there are many ways to accomplish it. In this article, we’ll look at ten different ways you can resize your columns, including some great shortcuts that will add up over time. Make sure to download the example file below to leverage some of the automatic options!
Column Resizing Options
Option 1: Change one column width
To change the column width for any one column, simply place your cursor between two column headers and drag left or right. You’ll see the cursor change to a resize symbol.
Option 2: Change multiple column widths
Need to change the width for multiple columns? Just select the columns first, then drag the cursor for one of them left or right to size all of the selected columns.
Option 3: Set one specific column width
Want to set a very precise column width? Just right-click the column header and click the Column Width option. Then set the width in characters from 0 to 255.
Option 4: Set multiple specific column widths
Need to set very precise widths for multiple columns? Just select the columns first, then right-click a column header and click the Column Width option. Then set the width in characters from 0 to 255.
Option 5: Auto-fit one column width
If you want to automatically size a column width based on the widest cell value in the column, simply double-click the cursor between the column to resize and the column to the right.
Option 6: Auto-fit multiple column widths
Need to auto-fit multiple column widths? Just select the columns first, then double-click the cursor between any selected column to resize and the column to the right of it. Note: you may set some really wide columns if some of them contain a lot of text, such as a description or a URL. Either don’t select those or set them manually (or use option 10 below!).
Option 7: Use keyboard shortcuts
There are several keyboard shortcuts you can use to key expedite resizing column widths:
- Ctrl+Space: Select entire column(s)
- Menu-w / Alt-h-o-w: Set specific width
- Alt-h-o-i: Auto-fit width
- Ctrl-Alt-v / Alt-h-v-s: Paste special
Option 8: Use paste special
If you already have columns with the widths you want to use in another area of your file, you can copy and paste them! Simply copy the cells with the widths you desire, then select the cells you want to apply those widths to, then (via the ribbon, right-clicking, or keyboard shortcut) go to paste special: column widths.
Option 9: Auto-fit using VBA events
If you want the column widths to be auto-expanding, it’s possible with VBA events! This requires using macros and adding some VBA code to the sheet. Right-click the sheet name, click View Code, and insert the code below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = “” Then Exit Sub
Target.EntireColumn.AutoFit
End Sub
Option 10: Use a smart macro
This is the option I use almost every day. It’s a keyboard shortcut on steroids that allows you to auto-fit or specify the column width for the selected cells or all used cells. Even better, it allows you to set a maximum width so that those extra-long cell values don’t get auto-sized too wide. This is one of hundreds of tools in the XLEV8 Excel Add-in (learn more).
Video
Summary
As with many tasks in Excel, there are several options for resizing column widths. Because of how often Excel users tend to resize columns, it’s worth knowing the best ways to resize them quickly.
What’s your favorite way to resize columns? Do you know of other ways that we’ve missed? Let us know in the comments below!
Recent Comments