How to Expand All Columns in Excel with 8 Powerful Techniques

The pain is real when you have an Excel workbook in your hand where you have to expand all columns in all the sheets, but you only know how to expand only one column at a time. In moments like this, you realize that knowing a few techniques on how to expand all columns in Excel would have proved useful.

We understand this inconvenience and are determined to change this situation for you. That’s why we come before you with 8 excellent ways you can use to expand all columns in your Excel sheet. We will even share with you tricks for expanding all columns in multiple sheets in under five seconds within a workbook! Sounds too good to be real? You are in for a ride then!

We are also sharing the practice file for this guide with you. You can download the file and follow each method step-by-step. Doing this will strengthen your learning and make it impossible to forget these methods that we are going to discuss with you in this visual guide.

Let’s get cracking!

How to Expand All Columns in Excel: 8 Easy Methods

For expanding columns in Excel, there are two systems for determining the column width: manual and automatic. In manual systems, you are going to have to show how much width the columns are going to be expanded to. For automatic width calculation, Excel determines the width of the to-be expanded columns based on the content a column has.

The methods we are going to demonstrate in this guide will have both systems. As always, each method is explained step-by-step with corresponding pictures for those steps. We understand that the pictures help with the learning process massively. And if you consider our recommendation and download the practice file to follow along with the instructions, you will find this guide to be even easier!

Let’s begin with our first method!

Method 1: Copying a Column Width

For the first technique, we are going to show you how you can copy a column’s width and paste it across the columns. It is extremely useful when you want to expand all columns in Excel and you want to use a certain width. For this, you can simply resize the width of a column to your preference, copy the column, then select all the columns, and apply that width of the column to expand all of them.

See the picture below.

Here, only column A is expanded to a good width. If you feel like this is the width you want to maintain to expand all the other columns, this is the method for you.  If you want, you can resize it more to your preference before copying the width.

Let’s see how to do it successfully in a few steps!

1. Select the column you want the width from to apply it across. In this case, it is column A we are going with. Click on the column tab to select the column.

After selecting, right-click on the top of the selection and click Copy. Alternatively, you can simply press the familiar button combination Ctrl+C to copy the selection.

2. Now, look at the top-left corner beside the first column tab. Click on that little triangle to select all the columns. Alternatively, you can press the Ctrl+A buttons to select all the columns in your sheet.

 

3. Once you have selected the columns, right-click your mouse button to bring up the context menu. From there, click Paste Special.

Alternatively, you can click the little down arrow right below the Paste option in the Clipboard group under the Home ribbon. It will open a drop-down menu from where you can click the Paste Special… option.

4. After you have clicked the Paste Special option, you will see a mini Paste Special window. From there, click on the Column widths radio button to select it. Finally, click OK to finish the process.

 

5. There you have it! All your columns are now expanded to the width you have selected.

Pretty easy, right?

Additional Tip: You can do the same to expand your columns even if you select only some of them. The procedure is completely the same. For selecting some particular columns, left-click the first column tab for your provisional selection, hold the click, and then drag the mouse pointer toward the last column. Release the held mouse button at the column you want your selection to end. Then you can apply the column width in the same manner as explained in this method.

Method 2: Formatting to Specific Width

There are times when you feel the need to expand all columns in Excel to a specific width. It helps with not only how good all the columns will look in the same size, but also the way it makes going through data a lot easier. This method proves to be more useful when different-sized columns make everything look messy.

You can manually expand the columns and maintain a width that you would like. But you are working in Excel. Doing one thing at a time when you can do multiple things at once is how you use Excel to its full potential.

Take a look at the dataset below.

Some columns are showing cell data properly while others are too narrow. Let’s expand all the columns to the same size, and we are showing how you can do it to your preferred width! Follow the steps below.

1. Select all columns by clicking the mini-triangle below the Name Box. Alternatively, press the Ctrl+A buttons to select all the columns. When done, click on Format in the Cells group under the Home ribbon. From the drop-down list, click on the Column Width… option.

2. Now, a tiny Column Width box will appear.

3. In the Column width: box, write down your value. The value here refers to the amount of character width. For instance, we are going to put 16 as the value here since we want the columns to expand to the length of 16 characters. You may choose your own value according to your needs. In Excel, the default column width value is set at 8.43 characters.

Now, when you are finished writing the desired value in the box, click OK to complete the process.

4. That’s it! Now you will find all the columns expanded to the width of the number of characters you have put as the column width value.

How cool is that?

Method 3: Using the Column Resize Cursor in Excel

Using the Column Resize Cursor to expand all columns in Excel is a great way to save time. It lets you change the width of all the selected columns based on how wide you want them to be; be it character-wise or pixel-wise. Let’s observe the image below to understand the magic of this method.

As you can see, different columns have different widths. Few are on point, few others are narrow, and the rest are way too bad. One can always change the width of each column one by one. But you are here for something fast and effective.

Now, you might think that if you select all the columns and try to adjust them by using the Resize Cursor, it may only add width to the existing size and still make it look untidy. However, that is not the case. When you select all the columns and adjust them by dragging the Resize Cursor, the final width of the column that you are adjusting will apply to all of the columns irrespective of their current width.

Let us show you a step-by-step demonstration.

1. First, we show how you can select multiple columns and adjust their widths. For a better understanding, we are leaving out column A and selecting only column B through column J. Here, we clicked on column B’s tab and held the click, and dragged it towards the right till column J to make the selection.

2. After completing the selection, bring your mouse pointer over any column separator. In this demonstration, we placed it on the separator of columns C and D. It then turns into a Resize Cursor as marked in the picture below.

3. Now, while the mouse pointer is placed on top of the column separator, left-click your mouse button, hold the press, and start dragging it towards the right to expand the column. Let go of the mouse button when you are content with the width of your column.

You will see that your selected columns have been expanded, all in the same size!

4. You can do the same with all the columns in your sheet. Select all columns by clicking the small triangle right below the Name Box as marked in the picture below. You can also select all by pressing the Ctrl+A buttons. Then, take the mouse pointer over any column separator to turn it into the columns resize cursor.

5. Now click and hold the Column Resize Cursor and drag it towards the right just like before till you are happy with the column width.

6. Let go of your mouse’s pressed left-click. Now you will see that all columns have been resized and expanded to your adjusted width!

Those chaotic columns now look so tidy and organized, don’t they?

Method 4: Formatting with AutoFit Width

For our fourth technique, we are going to show the use of the AutoFit option to expand all columns in Excel. This method requires only three clicks to expand all the columns in your worksheet. In Excel, that is an excellent option!

We are using the dataset shown in the picture below.

We will show you both ways how to expand all columns in Excel as well as how to expand only selected columns for this method. So, let’s begin!

1. You will use the first click to select all the columns. To do so, click the little triangular gray shape under the Name Box as marked in the picture below.

2. Now, click on Format in the Cells group under the Home ribbon. It will open a drop-down menu. From there, click the AutoFit Column Width option. These are your second and third clicks!

3. And now you will see that all your columns have been AutoFitted based on the width of the cell which has the most characters in respective columns.

4. Similarly, if you want to AutoFit only some selected columns, you can achieve the same result. For that, select the columns you need to expand by using the AutoFit Column Width option.

5. Now go to the Format option in the Cells group under the Home ribbon. Click on the AutoFit Column Width option as shown in Step 2 of this method. Instantly, you will see the selected columns getting expanded and AutoFitted to the contents according to the width of each column’s biggest cells.

And that’s it! Super easy!

Important Tip: As the columns with narrow widths got expanded, columns with more width and space got trimmed. Unlike the first three methods in this guide where all the columns were expanded to a particular width regardless of the length of the content in a cell, this Method 4 will produce a result of various-sized columns depending on the cells’ contents.

For example, if a cell from a column has the longest content consisting of 4 characters while another cell in a different column has 16 characters, the AutoFit option will adjust to the widths of those cells in their respective columns. See the image below for further clarification.

Observe columns A, C, and D. And notice the areas marked in green. Those are the cells with the longest contents in their respective columns. When we selected all those columns and clicked AutoFit Column Width, the widths maintained in those columns came from those green-marked contents.

Method 5: AutoFitting with Double-Click Adjustment

Now this one is a fun method! In the previous one, we demonstrated how to expand all columns in Excel using only three clicks. This time, it is also the same. But we can bring down the number of clicks to two if you would like. In that case, you would only need a double-click. That is how fast it is!

And we are using the Column Resize Cursor from Method 3 for this one here. In a way, you can say that this is a combination of Method 3 and Method 4. And the outcome it brings is something extraordinary!

Let’s learn the magic in two easy steps.

1. Select all the columns by clicking on the little triangle under the Name Box. Alternatively, press the buttons Ctrl+A to select all the columns. Then, move your mouse pointer to a column separator. When you see the Column Resize Cursor shape, stop moving your mouse pointer. And now, double-click with your mouse’s left button.

2. As soon as you double-click, you will see that all the columns have been AutoFitted!

As you can tell, this is a super fast method to expand all your columns in Excel.

3. Like always, you can do the same with selected columns instead of all columns. For that, select the columns that you want to expand. Then bring the mouse pointer over a column separator between two of the selected columns. Then double-click the left button on your mouse.

4. There you go. Your selected columns are now expanded!

Quick and easy!

Method 6: Combination of Alt H O I Buttons for AutoFit Width

Using Excel without taking your hands off your keyboard brings more productivity. The less you have to move your hand to operate the mouse, the better and faster your workflow becomes. This is why shortcuts are so popular among Excel users.

And the Alt button holds the key to the superpower in Excel! It opens the door to hundreds of button combinations that show an Excel user ways to the highest level. The Alt button creates many access keys in Excel that make life much easier.

For instance, when you hit Alt in Excel, it opens access to the menu bar through which ribbons could also be accessed with a combination of buttons. In the default state, the menu bar in Excel looks like the picture below.

When you hit the Alt button, notice the difference. All the menu buttons now have different letters accompanying them.

After that, when you press the corresponding buttons on your keyboard, the ribbon gets activated and shows further letters for each option. And that is what we are going to use today to show you how you can use four button presses on your keyboard in a unique sequence to expand all columns in Excel by using the AutoFit Column Width option!

1. Select all columns. To keep the experience mouse-free, use the Ctrl+A button combination to select all the columns. Then press the Alt button and then press the H button for the Home ribbon.

The goal is to open access to the Home ribbon first and access the Format option in the Cells group next.

2. After pressing the Alt and then H button, you can see the corresponding button for the Format option showing underneath the Format option which is the O button. Therefore, now press the O button on your keyboard to access the drop-down menu for this Format option.

3. Right after pressing the O button, you will be shown the drop-down menu where you will find that the corresponding button for the AutoFit Column Width option is the I button (the 9th letter in the English alphabet). So now, press the I button on your keyboard to apply this option.

4. And now, you will see that all the columns in your sheet have been Auto-fitted!

How smooth is that?

Important Tip: Be sure to press the Alt H O I button combinations maintaining the sequence without much delay. If you wait too long after pressing a button, the access may get reset due to some unknown trigger in Excel and you may have to restart the sequence all over from pressing the Alt button. Therefore, make sure that you are pressing the buttons fast in a proper sequence.

But if you want to take a bit of time to input the sequence, you can press and hold the Alt button and then press the three sequential H O I buttons with as much time as you need. When you get familiar with using them in that sequence several times, you will begin completing the whole sequence in under a second. Literally.

Method 7: Specific Width via the Column Tab

We showed in Method 2 how to expand all columns in Excel to a specific width. However, there is another way to do it without going to the Format option. That is what we are going to show you in this method. Despite the similarities, sometimes an Excel user finds one option easier than the other and that is why we are also showing this to you. A different first step always creates a new pathway.

Follow the instructions below to do it in moments.

1. As always, select all the columns by either clicking on the triangle in the corner of the sheet or simply pressing the Ctrl+A buttons.

2. Now, hover your mouse pointer over any column tab. Then click the right mouse button to bring up the right-click menu. Next, click on Column Width…

3. This will bring up the mini Column Width box where the value for column width has to be put in. As we explained in Method 2, the column width is calculated in character length. For this demonstration, we are putting 18 for the width.

To finish up the process, now click the OK button.

4. Now you will see that all the columns have been expanded to the character length used for column widths.

So simple!

Method 8: By Using the Visual Basic for Applications (VBA)

MACROS was all the rage when it was introduced in Excel during the early 90s. It worked through the Visual Basic for Applications (VBA) programming language under the hood. But now, VBA has also taken a front face. And being able to use the VBA in Excel separates a superuser of Excel from an occasional one.

With just a line of code in VBA, work that requires twenty seconds to hours can be done in under five seconds. And when you save many seconds in every single task, they build up to be a lifetime in a hectic data analysis work! Even if you don’t know how to code in VBA, you can save time by simply copy-pasting VBA codes. And as it has been known for years, a little bit of VBA goes a long way!

Now, we are going to show you how you can use VBA to expand columns in Excel. In Particular, we will explain the steps to expanding columns for three different situations: Expanding all columns in a sheet, expanding a specific range of columns in all the sheets in a workbook, and expanding all columns in all the sheets in a workbook.

The second and third situations are the real deal here. When you have several sheets in a workbook where each sheet has hundreds of columns, going to every sheet and then expanding needed columns or all the columns will take a huge amount of time. This VBA method will save you time and will complete the task for you in under five seconds!

So, let’s continue and witness the VBA magic!

8.1 For Active Sheet

This option gives you a way to instantly expand all columns in a sheet that you are in. You don’t have to select the columns and go through the options and then click options here and there to perform this task. A line of VBA code will do the job for you.

We are going to work with the same dataset as shown below.

Now, how to expand all these columns using VBA? Simply follow the steps shown below.

1. Go to the Developer tab. From there, in the Code group, click on the Visual Basic option. Alternatively, you can simply press the Alt + F11 buttons to open the Visual Basic for Applications Editor. If you cannot find the Developer tab in your menu bar, follow the instructions given right after this method on how to enable the Developer option in Excel.

2. When the VBA editor opens up, click on the Insert menu. From the drop-down list, click Module.

3. Now, the module window for writing the code will open.

4. Copy the code from below and paste it into the Module box.

Sub Autofit_Expand() Cells.Columns.AutoFit End Sub

Afterward, click on the green Run button. In another way, you can simply press the shortcut F5 for the Run button.

Close the window after hitting F5 or clicking the Run button.

5. You will see that all the columns in the sheet you are in are now expanded to AutoFit!

Groovy, isn’t it?

Important Tip: If you have to expand all columns in some specific sheets but not in all the sheets in your workbook, you can follow the process of this method and apply it in multiple sheets one by one. You have to insert the code in VBA for your workbook once and then switch through the sheets where you want to apply it. Then open the VBA from the Developer ribbon or by hitting Alt + F11. You will find the code still residing in the module window there. Simply hit the Run button or press F5, close the window, and come back to see that the columns in your sheet have been expanded.

But if you would like to expand all columns in all the sheets in a workbook, we are going to discuss it soon. Keep on reading to find out how VBA makes your life smoother!

8.2 For Specific Columns in All Sheets in a Workbook

At first, you may think that there is nothing new in this method. Because you can expand specific columns in Excel pretty easily by following any of the methods from Method 1 through Method 7 in this article. But let us assure you, this one here is different.

Let’s say you have to expand a specific range of columns in all 15-20 or more sheets in your workbook. Are you going to select the columns and expand them all manually by going to every sheet? Well, you can, but that defeats the purpose of using Excel to make your life easier.

In this method, we are going to show how you can expand a specific range of columns for all the sheets in your workbook with just a snippet of the VBA code. For this demonstration, we are going to expand from column B through column G in all the sheets in the workbook.

Let’s see how to do it.

1. Open the Visual Basic editor from the Developer ribbon in the Code group. You can press the Alt + F11 buttons to open it as well.

2. When the VBA window opens, click Insert to bring up the drop-down menu. From there, click the Module option. It will open the module window for code.

3. At this point, you will find the Module window to write or copy the code into.

4. Now, copy the code from below and paste it inside the Module box.

Sub Autofit_Expand() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Columns(“column_range”).AutoFit Next ws End Sub

In the fourth line of the code, change the “column_range” to the range of columns you want to expand. For instance, we mentioned that we are going to expand columns B through G. For this, we are going to put “B:G” in the column range. Be sure to write the column range within closed quotation marks.

When done, click the Run button or press the F5 button. Then close the Visual Basic for Applications editor window.

5. After you close the window, you will see that the range of columns that you mentioned in your code are now expanded to AutoFit.

You can go to the other sheets within the same workbook. You will see that all those ranges of columns have been expanded to AutoFit there as well!

Could it get any easier than this?

Important Tip: If you want to expand a range of columns in some sheets manually one by one instead of doing it for all the sheets, you may use the code below instead.

Sub Autofit_Expand() Columns(“column_range”).EntireColumn.AutoFit End Sub

Don’t forget to substitute the “column_range” for your range of columns.

8.3 For All Sheets in a Workbook

The magic of Visual Basic for Applications (VBA) in Excel gets to shine fully here! When you have a dozen sheets in a workbook where each sheet has hundreds of columns in a messy situation needing to be expanded, is there a way to expand all the columns in all the sheets in under five minutes? How about under two minutes?

VBA in Excel allows you to do it in under five seconds! That’s right. We are not exaggerating at all. VBA helps you cut down your manual work massively and become more efficient in your work.

We are now going to show you the process step-by-step. Follow all the steps and be amazed by VBA like nothing else!

1. As always, in the Excel window, go to the Developer ribbon and click Visual Basic from the Code group. You can press Alt + F11 as well to open the VBA window. In this window, click the Insert menu and then click Module.

2. Copy the VBA code from below and paste it into the editor box inside the Module window.

Sub Autofit_Expand() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Columns.AutoFit Next ws End Sub

When you are done pasting the code into the box, click the green Run button from the bar or simply press the F5 button to run the code module. Then close the VBA editor window altogether.

3. Now you will be taken back to your Excel workbook. In there, you will see that all columns in all the sheets in your workbook have been expanded through AutoFit!

Can’t believe how easy it was? Try it out for yourself and appreciate the VBA magic!

Enabling the Developer Option in Excel:

If you could not find the Developer tab in your menu bar, it is because Excel keeps it hidden by default. You need to check it to add this Developer tab to the menu bar through ribbon customization. You can do it in two easy steps.

1. Go to the Options from the File menu in Excel.

2. When the Excel Options window pops open, first, click on the Customize Ribbon tab. Next, put a check on the Developer option under Customize the Ribbon: list on the right. Finally, click OK to complete the process.

Now go into Excel and you will find the Developer tab in the menu bar.

Last Words

One by one, we have shared 8 different techniques on how to expand all columns in Excel. Also, there were three different ways to use Visual Basic for Applications (VBA) for expanding columns. Hence, we can say that we shared 11 ways you can expand all columns in Excel. Either way, all of them are useful given the situation you are in.

Method 1, Method 2, Method 3, and Method 7 lets you expand columns to a width that you would like. Excel does not determine the column sizes in these methods, it’s all up to you. For the rest of the four methods in Method 4, Method 5, Method 6, and Method 8, the AutoFit option is used in various ways. Excel fits the width of columns to the content they have and so, these four methods are faster than the other four methods.

The fastest way to expand all columns in Excel is, hands down, Method 6 with Alt H O I buttons sequencing. You don’t even need to use a mouse for this method and can do the task within 3-4 seconds in total. Method 5 comes next in terms of expanding columns fast. However, when you have some sheets in a workbook with literally hundreds of thousands of cells carrying data in columns and you need to expand all those columns, say hi to VBA in Method 8!

We recommend that you learn and practice all the methods and apply them according to the situation. For a smoother and faster workflow in Excel, it is necessary to know more than one technique to do a task and that is what we are sharing with you here. This way, you can present yourself as a more efficient and perfect user of Excel!

Read Also:

• How to Remove Filter in Excel: The Fastest Ways
• How to Open Text File in Excel: Making Things Easy
• How to Stop Excel from Changing Numbers to Dates: A Few Easy Ways