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.
Go to the target worksheet, highlight any blank cell, and press Ctrl + A to select all the cells of the spreadsheet.
Now, press Alt + H + O + I to invoke the AutoFit Column Width command from the Home > Cells > Format drop-down menu.
This should adjust the width of all the worksheet cells according to the existing dataset.
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.
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.
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.
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.
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.
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.
Excel shall now set this cell width for all the cells in the worksheet.
Follow the same steps to adjust the cell height for all the spreadsheet cells by clicking and dragging the row height.
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.
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.
Enter a desired value in the Column Width dialog.
Once done setting the cell width, go to any of the row headers, right-click, and choose the Row Height option.
You can type in the desired value in the Row Height dialog box.
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.
Open your worksheet and locate the cell for source cell width. Now, copy the cell and its formatting by pressing Ctrl + C keys.
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.
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.
Highlight your dataset for which you must change the cell width and height so all cells contain a uniform dimension.
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.
Now, you can enter a cell height in pixels, like 20. Hit OK to resize all the cells in the selected range.
Keep the target cell range highlighted and this time choose the Column Height option from the Home > Cells > Format drop-down menu.
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.
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:
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."
' Prompt user to enter row height
rowHeight = InputBox("Enter the row height:")
If rowHeight <= 0 Then
MsgBox "Invalid row height. Exiting script."
' Prompt user to enter column width
colWidth = InputBox("Enter the column width:")
If colWidth <= 0 Then
MsgBox "Invalid column width. Exiting script."
' Set row height and column width for selected range
selectedRange.RowHeight = rowHeight
selectedRange.ColumnWidth = colWidth
MsgBox "Row height and column width set successfully."
Click on the Save button of the Excel VBA Editor.
The Microsoft Excel dialog will pop open. There, click the No button.
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.
On your worksheet, press Alt + F8 and select SetCellDimensions macro. Hit the run button to execute the macro.
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.
The VBA script should resize the cell dimensions based on your inputs.
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