Table of Contents

Operating within Microsoft Excel frequently requires managing data of diverse lengths. When text surpasses the width of a cell, it either becomes truncated or overflows into adjacent cells. Likewise, large numerical values might display as hash symbols (#####). These formatting inconsistencies can detract from the professional appearance of your spreadsheet and make it harder to interpret.

This guide will walk you through several approaches to effectively adjust column widths and row heights in Excel. You’ll discover how to make quick changes with your mouse, use the ribbon for granular adjustments, and implement keyboard shortcuts for efficiency. Additionally, we’ll cover how to set consistent sizes for multiple columns and explore an advanced technique to make columns automatically resize as you type.

Requirements

• An active installation of Microsoft Excel on either Windows or macOS.
• Compatible with most modern versions, including Excel for Microsoft 365, Excel 2021, 2019, and 2016.
• No prior expertise needed—these methods are accessible for users at all proficiency levels.

Method 1: Adjust Column Widths and Row Heights Using Your Mouse

Manually Drag to Resize a Column or Row

Place your cursor on the right edge of a column header or the bottom edge of a row header. When the cursor changes to a double-sided arrow, click and hold, then drag the edge to the desired width or height. A tooltip will display the exact size in points as you drag. For finer control, see Method 4 to set a specific numeric value for width.

AutoFit with a Double-Click

To automatically adjust size based on content, position your cursor on the right edge of a column header or the bottom edge of a row header until it changes to a double-sided arrow. Double-click the edge, and Excel will resize the column or row to fit the content. Note: AutoFit may not function correctly with merged cells.

Select and AutoFit Multiple Columns or Rows

To adjust multiple columns or rows simultaneously:

• Select the target columns or rows by clicking and dragging over their headers. For non-contiguous selections, hold Ctrl while clicking individual headers.
• Double-click the right edge of any selected column header or the bottom edge of any selected row header. All chosen columns or rows will resize together.

AutoFit the Entire Worksheet

To adjust all columns and rows in your sheet:

1. Click the Select All button (the gray triangle where the column and row headers intersect).
2. Double-click the edge of any column header to AutoFit all columns, and then double-click the edge of any row header to AutoFit all rows.

Method 2: Adjust Column Widths and Row Heights Using the Ribbon

Select the Target Cells

Highlight the columns or rows you wish to resize. You can select full columns or rows by clicking their headers or highlight specific cell ranges within them.

Navigate to the Home Tab

Ensure the Home tab is active on the Excel ribbon at the top of your screen. The formatting tools are available here.

Open the Format Menu

Within the Cells group on the right side of the Home tab, click the Format button. A dropdown menu will appear with options categorized under sections like Cell Size and Visibility.

Select AutoFit Column Width or Row Height

Under the Cell Size section of the dropdown menu, click either AutoFit Column Width or AutoFit Row Height. The selected columns or rows will adjust accordingly.

Method 3: Use Keyboard Shortcuts for AutoFit

Select Your Columns or Rows

Select the columns or rows you need to resize. Use the mouse to click headers or apply these shortcuts:

• Ctrl + Spacebar: Select the current column.
• Shift + Spacebar: Select the current row.

Use the AutoFit Column Width Shortcut

Once your columns are selected, press the following keys in sequence: Alt, then H, then O, then I. The columns will automatically adjust to fit their content.

Use the AutoFit Row Height Shortcut

With your rows selected, press the following keys sequentially: Alt, then H, then O, then A. The rows will resize to accommodate their tallest content.

Note for macOS Users

The Alt-key shortcuts are specific to Windows. For macOS, use the ribbon menu as described in Method 2 or set up custom shortcuts via System Settings > Keyboard > App Shortcuts.

Method 4: Set Uniform Column Widths and Row Heights

Select Multiple Columns or Rows

Highlight the columns or rows you want to standardize. For non-contiguous headers, hold Ctrl while selecting. For adjacent headers, click and drag.

Open the Width or Height Dialog Box

On the Home tab, click Format in the Cells group. From the dropdown menu, select Column Width... or Row Height.... A dialog box will appear.

Enter a Specific Value

In the dialog box, input a numeric value for the desired size and click OK. Column width values correspond to the number of visible characters in the default font, while row height is measured in points. All selected columns or rows will adjust to the specified size.

Method 5: Use VBA for Automatic Resizing

Security Warning: Understand Macros

⚠️ Important: VBA macros require enabling macros, which can pose security risks if the file originates from an untrusted source. Only use this method in trusted files.

Open the VBA Editor

Right-click the sheet tab where you want auto-resizing to occur (e.g., Sheet1) and select View Code. Alternatively, press Alt + F11 to open the editor.

Enter the AutoFit Code

In the VBA editor, select Worksheet from the left dropdown menu. Copy and paste the following code into the Sub...End Sub block:

Cells.EntireColumn.AutoFit

Save as a Macro-Enabled Workbook

Save your file as an Excel Macro-Enabled Workbook (*.xlsm). This ensures the macro functionality is retained.

FAQ

• Why does AutoFit fail? Merged cells can obstruct AutoFit. Unmerge the cells and try again.
• Can I use specific units like inches or centimeters? Switch to Page Layout view to define sizes using standard measurement units.
• Can AutoFit work with multi-line text? Enable the Wrap Text option for the relevant cells.

Tags

Microsoft Excel, Microsoft 365, Spreadsheets, Tutorials, AutoFit, Column Width, Row Height, VBA, Excel Formatting, Productivity, Windows 11, Microsoft Office