If you’ve ever imported data into Excel and ended up with columns that show ‘###’ or rows that hide your text, you know the pain of manually dragging each boundary. This guide covers the fastest ways to resize all columns and rows at once—from keyboard shortcuts to the hidden ‘AutoFit’ options—so you can clean up any worksheet in seconds. Whether you’re a beginner or a daily Excel user, these methods will save you time and frustration.
Select All and Double-Click
The quickest way to resize every column and row is to select the entire sheet by clicking the triangle at the top-left corner (where the row numbers and column letters meet). Then, double-click the boundary between any two column headers (or any two row numbers) to AutoFit all selected columns (or rows) to their content. This works instantly and is perfect for when you want everything to fit exactly.
Use the Ribbon’s Format Menu
Go to the Home tab, find the Cells group, and click Format. Under Cell Size, you’ll see options like AutoFit Row Height and AutoFit Column Width. After selecting the entire sheet (or a specific range), click these to automatically adjust all selected rows or columns. This method is great if you prefer menu navigation over mouse tricks.
Set a Fixed Width and Height
If you want uniform columns and rows—say, for a printable report—select the whole sheet, then go to Format > Column Width (or Row Height) and type a number. For columns, the unit is characters; for rows, it’s points. This forces every column to the same width and every row to the same height, which is handy for creating a clean, grid-like layout.
Keyboard Shortcut: Alt+H, O, I and Alt+H, O, A
For keyboard lovers, press Ctrl+A to select all, then Alt+H to open the Home tab, O for Format, and I for AutoFit Column Width. For rows, use Alt+H, O, A (A for AutoFit Row Height). This is the fastest way if you’re already typing and don’t want to touch the mouse. It works in all modern versions of Excel.
Create a Macro for One-Click Resizing
If you resize frequently, record a macro: go to View > Macros > Record Macro, perform the AutoFit steps on the whole sheet, then stop recording. Assign the macro to a button on the Quick Access Toolbar. Now, one click resizes everything. This is ideal for repetitive tasks like weekly report formatting, and you can even save the macro in your Personal Macro Workbook so it’s available in all your files.
Use the ‘AutoFit’ from the Right-Click Menu
Select the entire sheet (or a range), right-click on any column header, and choose Column Width—but that only sets a fixed width. To AutoFit, right-click and select AutoFit Column Width (or Row Height). This is a quick alternative to the ribbon, especially if you’re already working with the mouse. It’s less discoverable but just as effective.
Resize Only Visible Cells (After Filtering)
When you filter data, AutoFit on the entire sheet will also resize hidden rows, which can mess up your view. Instead, select only the visible cells by pressing Alt+; (or go to Home > Find & Select > Go To Special > Visible cells only). Then apply AutoFit. This way, only the visible rows adjust, keeping hidden data intact. This is a lifesaver for filtered reports.
Use the ‘Format as Table’ Trick
Convert your data range into an Excel Table by pressing Ctrl+T. Tables automatically adjust column widths when you type new content, and you can quickly select the entire table (click the small arrow in the header) and use the same AutoFit methods. Tables also keep formatting consistent when adding rows. This is more of a preventive measure, but it saves resizing time later.
Copy-Paste Special to Match Width
If you have a well-formatted sheet and want another sheet to match, select the source columns, press Ctrl+C, then go to the target and use Paste Special > Column Widths. This copies the exact widths without copying data. It’s perfect for maintaining consistent layouts across multiple sheets in a workbook.
Use the ‘Merge & Center’ Alternative for Headers
Sometimes resizing isn’t the issue—long headers cause columns to be too wide. Instead of resizing, consider using Wrap Text (Home > Alignment) and then AutoFit the row height. This keeps columns narrow and rows taller, which can make your sheet more readable. Combine it with Center Across Selection (in Format Cells > Alignment) to avoid merging cells, which can cause sorting issues.
How to Choose
The best method depends on your goal. For a one-time cleanup, the double-click AutoFit is fastest. If you need uniform sizing for printing, set fixed widths and heights. For repetitive tasks, a macro or table is worth the setup. Keyboard shortcuts are great if you’re already typing. Consider whether you have filtered data (use visible cells only) or need to match another sheet (use Paste Special). Always test on a copy if you’re unsure, especially when using macros.
Frequently Asked Questions
Q: Why does my column show ‘###’ after resizing?
That means the column is too narrow to display the number. Use AutoFit or manually widen it.
Q: Can I resize all rows at once without affecting columns?
Yes, select the rows (or the whole sheet) and use the row AutoFit option (Alt+H, O, A) or double-click a row boundary.
Q: Does AutoFit work on merged cells?
No, merged cells often don’t AutoFit properly. You may need to manually adjust them.
Q: How do I resize all columns in a specific range, not the whole sheet?
Select the range (e.g., A1:D10) and then use any of the methods—double-click, ribbon, or keyboard.
Q: Will resizing affect my data?
No, resizing only changes the display, not the data itself. It’s safe to do anytime.
Q: Can I set a default column width for new sheets?
Yes, go to Format > Default Width and set a number. This applies to all new sheets in the current workbook.
Final Thoughts
Resizing all columns and rows in Excel doesn’t have to be tedious. Whether you prefer the mouse, keyboard, or automation, there’s a method that fits your workflow. Start with the double-click AutoFit for quick fixes, and graduate to macros or tables if you find yourself doing it often. With these techniques, you’ll spend less time fiddling with boundaries and more time analyzing your data.