Durjoy Paul
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.
Download Practice Workbook
Download this practice workbook
5 Methods to Space Columns Evenly in Excel
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 Easy Approaches)
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.
Keyboard Shortcut
You can do this method easily by keyboard shortcut. To do this, select all the columns of your dataset.
Press ‘Alt +H+O+I’.
Read More: How to Space Rows Evenly in Excel (5 Methods)
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 Easy Ways)
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 (3 Ways)
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 don’t forget to visit ExcelDemy to gain more Excel knowledge.
