6 Ways to Make All Cells the Same Size in Microsoft Excel

Read this quick Excel tutorial to learn how to make all cells the same size in Excel!

Maintaining uniformity across the cells in your worksheet according to the dataset is indispensable. It decides whether your worksheet is a professional-looking document or a chaotic jumble of mismatched elements. Hence, one common challenge you face is to make all cells in Excel the same size.

While Excel’s default settings might lead to cells of varying dimensions, it has also provided various native tools and methods to customize the cell width and height the way you want.

Join me as I explain those techniques below with easy steps and illustrative images.

Using Keyboard Shortcuts

Suppose you’ve imported a large dataset in your Excel worksheet. For the imported database, you know that the characters of the dataset are consistent. For example, all the cells contain 8 characters long data. However, you’re seeing inconsistent sizes for rows and columns in the worksheet.

In this scenario, you can use a quick hotkey-based method to make all cells in Excel the same size.

Select all cells
Select all cells

Go to the target worksheet, highlight any blank cell, and press Ctrl + A to select all the cells of the spreadsheet.

AutoFit Column Width
AutoFit Column Width

Now, press Alt + H + O + I to invoke the AutoFit Column Width command from the Home > Cells > Format drop-down menu.

Same cell length in Excel
Same cell length in Excel

This should adjust the width of all the worksheet cells according to the existing dataset.

AutoFit Row Height
AutoFit Row Height

Then, use the hotkey for AutoFit Row Height, which is Alt + H + O + A, to resize the height of all the cells to the same pixel in your worksheet.

Same cell height in Excel
Same cell height in Excel

Be mindful that this method will resize all cells to the same dimension only when the content of the cells results in the same dimension as of other cells. For example, each cell of the worksheet contains 8 As or 10 1s, and so on.

Select All Cells & Drag Using Mouse

This is the manual method you can follow to resize all cells to the same dimension when you’re doing it occasionally.

First, you need to find out the column width and row height of the target cell.

Cell width
Cell width

So, go to your worksheet, locate the source cell for dimension discovery, and click on the right edge of the column header of the corresponding cell. You should see the width of the cell in pixels. The value will disappear soon. So, to see the values properly, long click on the edge to keep the dialog box open for longer.

Cell height
Cell height

Repeat the same steps to get the cell height value. But this time, you need to click on the bottom edge of the row header of the corresponding cell.

Highlight all dataset
Highlight all dataset

Now that you know the cell height and weight you need in all the cells of the spreadsheet, highlight all the cells by clicking on any blank cell and pressing Ctrl + A keys together.

Adjusting cell width
Adjusting cell width

Now, long click on any of the column header’s right-side edges and adjust the pixel size by dragging the mouse to the left slowly. As you move the cursor to the left, the pixel value of the cells will change. When you see the required pixel value, let go of the mouse click.

Make all cell same width
Make all cell same width

Excel shall now set this cell width for all the cells in the worksheet.

Adjust row height same for all cells
Adjust row height same for all cells

Follow the same steps to adjust the cell height for all the spreadsheet cells by clicking and dragging the row height.

Made cell height same for all cells
Made cell height same for all cells

Using the Right-Click Context Menu

If the drag-and-drop method isn’t suitable for you, you can use the following method to make all cells the same size in Excel which involves the right-click menu.

Column width on right click
Column width on right-click

Go to the target worksheet and highlight all the cells to which you want to change the cell height and width. Right-click on the column header and choose the Column Width option from the context menu that pops up.

Column width
Column width

Enter a desired value in the Column Width dialog.

Row height on right click
Row height on right-click

Once done setting the cell width, go to any of the row headers, right-click, and choose the Row Height option.

Row height dialog
Row height dialog

You can type in the desired value in the Row Height dialog box.

Make cell size same using right-click
Make cell size the same using right-click

Excel should be able to set the new cell dimension for all the cells of the worksheet instantly.

Using the Paste Special Command

You can set uniform cell width by using this technique. This method won’t work if you’re planning to also configure the cell height of the worksheet to a similar value.

Copy source formatting cell
Copy source formatting cell

Open your worksheet and locate the cell for source cell width. Now, copy the cell and its formatting by pressing Ctrl + C keys.

Pasting column width
Pasting column width

Go to the cell range where you want to implement the source cell’s cell width. Select the whole cell range and hit Ctrl + Alt + V to call the Paste Special dialog.

On Paste Special, click on the Column widths selection and hit OK to paste column width values to all the selected cells of the worksheet.

Same cell width in Excel
Same cell width in Excel

Excel shall import the cell width to the highlighted cell range.

Using the Cell Size Tool on the Excel Ribbon

This is the universal Excel ribbon menu tool to change cell size in Excel or make all cells the same size.

Select the dataset
Select the dataset

Highlight your dataset for which you must change the cell width and height so all cells contain a uniform dimension.

Change row height
Change row height

Go to the Format menu in the Cells command block of the Home tab. Expand the Format menu and click on the Row Height option.

Setting a row height value
Setting a row height value

Now, you can enter a cell height in pixels, like 20. Hit OK to resize all the cells in the selected range.

Column height menu
Column height menu

Keep the target cell range highlighted and this time choose the Column Height option from the Home > Cells > Format drop-down menu.

Colum width dialog
Colum width dialog

Enter the desired value in the Column Height dialog.

Using Excel VBA Macro

All the methods explained so far require you to perform some steps to make all cells in Excel the same size. If you don’t want to perform all the steps or want to automate the process, you must use Excel VBA.

You need to write a VBA script and package that into a VBA macro which you can execute from your workbook on any worksheet.

Creating a VBA macro
Creating a VBA macro

Firstly, go to the target worksheet and press Alt + F11 to bring up the Excel VBA Editor tool.

On the VBA Editor, click on the Insert button and choose Module from the context menu.

In the blank module, enter the following Excel VBA script:

Sub SetCellDimensions()
    Dim selectedRange As Range
    Dim rowHeight As Double
    Dim colWidth As Double
    
    ' Prompt user to select a range of cells
    On Error Resume Next
    Set selectedRange = Application.InputBox("Select a range of cells", Type:=8)
    On Error GoTo 0
    
    If selectedRange Is Nothing Then
        MsgBox "No range selected. Exiting script."
        Exit Sub
    End If
    
    ' Prompt user to enter row height
    rowHeight = InputBox("Enter the row height:")
    If rowHeight <= 0 Then
        MsgBox "Invalid row height. Exiting script."
        Exit Sub
    End If
    
    ' Prompt user to enter column width
    colWidth = InputBox("Enter the column width:")
    If colWidth <= 0 Then
        MsgBox "Invalid column width. Exiting script."
        Exit Sub
    End If
    
    ' Set row height and column width for selected range
    selectedRange.RowHeight = rowHeight
    selectedRange.ColumnWidth = colWidth
    
    MsgBox "Row height and column width set successfully."
End Sub

Click on the Save button of the Excel VBA Editor.

Microsoft Excel dialog
Microsoft Excel dialog

The Microsoft Excel dialog will pop open. There, click the No button.

Save as XLSM
Save as XLSM

Now, the Save As dialog will open. There, click on the Save as type drop-down menu and choose the Excel Macro-Enabled Workbook (XLSM) option.

Click Save on Save As to save and close the dialog box.

Now, close the Excel VBA Editor.

Running a macro
Running a macro

On your worksheet, press Alt + F8 and select SetCellDimensions macro. Hit the run button to execute the macro.

Select cell range
Select cell range

Excel will prompt you to select a cell range from your worksheet. Do as prompted.

Now, enter the values for the required row height and column width in successive dialog boxes.

Make all cells same dimension using VBA
Make all cells the same size using VBA

The VBA script should resize the cell dimensions based on your inputs.

Conclusions

So far, you’ve gone through multiple methods to acquire the Excel skill to apply the same cell dimension to all the cells of the worksheet.

I’ve divided the methods into two categories. In the first category, you find several user interfaces and menu based methods to resize all cells in the worksheet to the same height and width. The second group is for automated methods, like Excel VBA.

If you like the techniques mentioned above comment below

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃