How to set column width and row height for a range in Excel?
In Excel, there are many situations where you need to adjust the column width and row height for a specific range, rather than the entire worksheet. For example, you may be preparing a table for printing, aligning data for better readability, or formatting templates where cell size consistency is important. This article explores several practical ways to set both column width and row height simultaneously for a selected range in Microsoft Excel, allowing you to efficiently fine-tune your worksheet layout.
Set column width and row height for a range with Format commands
Set column width and row height for a range with Kutools for Excel
VBA Code - Automate setting column width and row height for a specified range
Set column width and row height for a range with Format commands
You can use Excel's built-in Format commands to manually set the column width and row height for any range. This method is straightforward and flexible, especially for quick formatting tasks or when you want full control over the exact measurements.
However, note that you need to set the row height and column width separately, and you must manually repeat actions if they need to be identical for both rows and columns. Additionally, this approach always adjusts entire columns and rows that intersect the selected range, which may affect other data in those columns or rows.
1. Select the cells or range where you want to set the column width and row height. If you want only part of the worksheet changed, be careful to select only the necessary area—selecting full rows or columns will apply the change to all cells in those lines.
2. Go to the Home tab, click Format in the Cells group, and then choose Row Height... to set the row height for your selection.
3. In the Row Height dialog box, type the desired height (in points) for the rows included in your selection, then click OK. Excel will instantly apply the new height to all rows in the selected range.
4. Similarly, go to Home > Format > Column Width... to change the width of the columns in your selection.
5. In the Column Width dialog box, enter the desired column width, and then click OK. The new width will apply to all columns in the selected range.
Tip: The maximum row height is 409 points and the maximum column width is 255 characters. If you enter a value outside this range, Excel will display an error or set the measurement to the nearest allowed limit.
Set column width and row height for a range with Kutools for Excel
If you find the standard Format command process repetitive—especially when you want row height and column width to match in size or be set together for a chosen area—the Kutools for Excel's Adjust Cell Size utility offers a more convenient alternative. With this feature, you can set both parameters at once and use the same measurement unit, helping you create perfectly aligned tables or forms with minimal effort. This is particularly useful when preparing templates or ensuring print-friendly layouts.
1. Select the target range you wish to format, then click Kutools > Format > Adjust Cell Size. This will open the Adjust Cell Size dialog box where detailed settings are available.
2. In the Adjust Cell Size dialog, specify whether the measurement unit should be points, centimeters, inches, or pixels. Enter the desired values for Row height and Column width. After confirming your preferences, click OK.
The software will immediately update row height and column width for all cells in your selected range.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
VBA Code - Automate setting column width and row height for a specified range
If you regularly need to set precise column widths and row heights for particular areas or want to automate this task to reduce manual repetition, you can use a short VBA macro to batch-adjust both for any range you select. This approach is suitable for users comfortable with macros, and it makes repeated formatting tasks especially efficient.
1. First, press Alt + F11 to open the Microsoft Visual Basic for Applications editor.
2. In the VBA interface, click Insert > Module, and paste the following macro into the new module window:
Sub SetColWidthRowHeight()
Dim rng As Range
Dim colWidth As Double
Dim rowHeight As Double
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range to adjust:", xTitleId, rng.Address, Type:=8)
colWidth = Application.InputBox("Enter the column width:", xTitleId, "", Type:=1)
rowHeight = Application.InputBox("Enter the row height:", xTitleId, "", Type:=1)
Dim col As Range
Dim rw As Range
' Set column widths
For Each col In rng.Columns
col.ColumnWidth = colWidth
Next
' Set row heights
For Each rw In rng.Rows
rw.RowHeight = rowHeight
Next
End Sub2. Close the VBA editor, then return to Excel. Go to the Developer tab (or press Alt+F8), select SetColWidthRowHeight from the list of macros, and click Run.
You will be prompted to select the target range, then to enter the desired column width and row height. Both settings will be applied to all columns and rows within the selected range in one operation.
In summary, Excel offers multiple approaches to adjust column width and row height for both quick edits and more structured demands. The native Format command is best for simple, one-off changes, but if you want greater control, batch adjustments, or repeated use, Kutools for Excel and the VBA macro method provide significant convenience and flexibility.
Demo
Related articles:
How to set cell size in cm (centimeters) in Excel?
How to square multiple cells in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in