How to Space Columns Evenly in Excel (5 Methods)

When you enter text or number in your worksheet which is quite large, you find it difficult to adjust it in the column width. As an Excel user, it’s quite normal to face such kind of problem and without knowing the exact solution, you spend your valuable on it. Microsoft Excel gives you several methods to space columns evenly in Excel. This article will portray to you all the necessary methods to space columns evenly in Excel.

How to Space Columns Evenly in Excel: 5 Simple Methods

As an Excel regular user, you have already aware of the fact that Excel gives you multiple solutions for a single problem. Here, we discuss the most useful 5 ways to space columns evenly in Excel. To show all the methods we take a dataset that portrays players, age, and countries.

1. Space Columns Evenly Using Format Option

Our first method is based on using the Format option. Through this option, you can easily modify your column width evenly.

Steps

• First, click on the top icon at the left corner of the worksheet. It will select the entire worksheet

• Now, go to the Home tab, and select the Format option from the Cells group.

• In the Format option, click on Column Width where you can modify your worksheet column.

• Enter any column width in the Column Width box and click on ‘OK’.

• There we have a dataset in which columns are evenly spaced.

Read More: How to Space out Cells in Excel

2. Double-Click the Column Heading

Our second approach is mainly by using column headings. When you make a double click on the column headings, it will automatically adjust the column width.

Steps

• To use this method, first select all the columns of our dataset by pressing Shift or Shift uses for contagious cells whereas Ctrl uses for non-contiguous cells. Press Ctrl and select one after another.

• Now, double-click on any of the column headings. It’ll automatically adjust all the column widths.

• Here we have the final result like this.

Read More: How to Space Rows Evenly in Excel

3. AutoFit Option to Space Columns Evenly

Another useful method is by using AutoFit option. This option will modify the column width according to your text size.

Steps

• Like previous methods, select the entire dataset.

• Now, go to the Home tab, and in the Cells group, click on the Format

• From the Format option, click on AutoFit Column Width.

• This will automatically change the column width according to the text of your dataset.

Read More: How to Add Space Between Text in Excel Cell

4. Space Selected Columns Evenly

You can modify your column space evenly for selected cells. This method helps to modify a certain column rather than the whole dataset.

Steps

• Select some specific columns. Here we want to change the column space from the range of cells B4:B12.

• Now, apply AutoFit Column Width option from the Format option or apply the Keyboard shortcut ‘Alt+H+O+I’. It’ll change the column width of this selected column.

5. Embedding VBA Codes

5.1 Space Columns to a Specific Size

We have shown a method in which you can change your column space to a specific size. We can do it easily by using the VBA editor.

Steps

• First, open the Developer tab by pressing Alt+F11. You can open it through the ribbon. If you don’t have that in the ribbon section, then you need to customize it and get the developer tab. A visual basic interface will appear.

• Now click on Microsoft Excel Objects and select the sheet where you want to apply this code. As we want to apply this to the ‘VBA1’ sheet that’s why we select this.

• A code window will appear and copy the following code and paste it. Here, we want our column size to be 16. You can apply your preferred value.

Sub makeequalsize_Column()

Selection.ColumnWidth = 16

 End Sub

• Now, close the code window. Go to the View tab and select Macros.

• A Macro box will appear. Select your applied Macro name and click on Run.

• It’ll give the desired result where all the column widths will be the same as you entered in the code.

5.2 Same Size as a Specific Column

Sometimes, you have a well-defined column width, and you want to use that width for other columns also. Yes, you can do it by changing all the column widths manually but that is a very time-consuming process. This VBA code will help you to solve this problem very easily.

Steps

• First, open the Developer tab by pressing Alt+F11. Just like the previous VBA method, select any sheet where you want to run the code from Microsoft Excel Objects.
• In the Code Window, copy the following code and paste it

Sub makeequalsize()

Selection.ColumnWidth = Columns("B").ColumnWidth

End Sub

• Minimize the visual basic window and go to the View tab in the menu bar. From there, select Macros.

• A Macro box will appear then select your Macro Name and click on Run

• This will make all the column widths similar to the specified column in the code.

5.3 Autofit Selected Columns

You can autofit your dataset column by using VBA codes. This code helps to adjust all the text or numbers according to their size.

Steps

• Open the Developer tab by pressing ‘Alt+F11’. Select the preferred sheet where you want to apply this code.
• Copy the following code and paste it into the code window.

Sub autofitColumns_Selection()

Selection.Columns.AutoFit

End Sub

• Minimize the developer tab and go to the View tab and select the Macros option in the toolbar.

• A Macro box will appear and from there select your Macro name and click on Run

• This will automatically resize the column width according to the text size.

Read More: How to Add Space between Numbers in Excel

Download Practice Workbook

Download this practice workbook

Conclusion

Here, we have discussed 5 important methods to space columns evenly in Excel. I hope you will find it really easy to use it in your day-to-day life. If you have any questions, feel free to ask in the comment section, and we will respond as early as possible.

Related Articles

• Add Blank Space Using Excel Formula
• How to Add Space between Rows in Excel
• How to Insert Tab in Excel Cell

<< Go Back to Space in Excel | Text Formatting | Learn Excel