What Is The AutoFit Column Width in Excel?
The Excel column auto width adjustment feature, AutoFit Column Width, enables one to resize a column width according to its largest-sized data automatically. We can use the option to adjust the width of one or more columns in a worksheet in one go.
Users can use the Excel column auto width adjustment feature to improve the data readability, reduce errors, and help make the data more organized and presentable.
For example, the following image shows a dataset containing a list of fruits, their grades and the invoice numbers.
However, the invoice data is not readable as it is partially visible. Thus, we can use the AutoFit Column Width Excel 2019 option to make the above data readable.
While the VBA Excel column auto width option helps adjust the required column width, utilizing the AutoFit Column Width feature from the Home tab is more straightforward.
First, click the column C header to select the whole column C and choose the AutoFit Column Width Excel 2019 option under the Format function in the Home tab.
The above action will automatically adjust the chosen column’s width according to the data with the largest size in the column. Thus, now we can view the invoice data better.
Table of contents
Key Takeaways
- The Excel column auto width adjustment option, AutoFit Column Width, helps us auto-adjust the chosen column’s width based on the largest data in the column.
- Users can autofit one or more columns’ width to improve the Excel data consistency and quickly make massive datasets appear more professional.
- Users can use the mouse cursor (column width resizing pointer), the AutoFit Column Width option, or the keyboard shortcut Alt + O + C + A to auto-adjust columns’ width. On the other hand, using the appropriate VBA code is also a practical solution.
Default Column Width In Excel
When we open a new workbook, we see the default number of empty worksheets. Further, the sheets contain columns with the same default width, which we can adjust according to our needs using the Excel column auto width adjustment feature.
The steps to find the default column width in an Excel file are as follows:
- Place the mouse cursor on the required column header’s separator on the right side.
- The column resizing pointer appears, after which we must press the mouse’s left key to view the current column width, which is the required default value.
- Release the mouse’s left key.
[Alternatively, click a column header to choose the entire column and choose the Home tab – Format function down arrow – Column Width option.
Otherwise, click a column header to choose the entire column and right-click the chosen column to select the Column Width option from the contextual menu.
The Column Width window will open, showing the current width of the chosen column in the Column width field, which is the required default value. Click OK to close the window.]
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Basic Example
Consider we open a new workbook. Thus, we will see a blank worksheet containing rows and columns.
Here is how to check the default column width in our Excel file.
Step 1: Hover the cursor over the column A header’s separator on the right. The cursor will appear as the column resizing pointer.
Step 2: Hold the mouse’s left key to view the current column width in points and pixels in a box, which is the default column width.
The above image shows that the default column width in our Excel file is 8.09 points or 96 pixels.
[Alternatively, click a column header to select the entire column.
Next, choose the Home – Format – Column Width.
Otherwise, right-click the chosen column and choose Column Width in the contextual menu.
The Column Width window will open, showing the current width of the chosen column in the Column width field.
The above image shows the same default column width we saw earlier, 8.09 points. Click OK to close the window.]
While we can use Excel column auto width code to adjust the current column width, we shall use more straightforward methods to achieve the desired outcome.
Column Width Is Not Auto Adjusted To The Largest Value In The Column
Typically, when we work with massive Excel datasets, we will have columns of data of varying lengths. In such cases, we may not be able to view the entire information in those cells which contain a greater number of characters than the specific cells’ column widths can show.
Thus, here is what we can do when the column width is not auto-adjusted to the largest value in the column:
- Place the cursor on the required column header’s separator on the right to view the cursor as a column width resizing pointer.
- Double-click the mouse on the required column header’s separator on the right side. The action will auto-adjust the column’s width according to the largest or longest value in the column.
[ Alternatively, we can use the VBA Excel column auto width option. But a simpler method is to click the required column header to select the entire column. Next, choose the Home tab – Format option down arrow – AutoFit Column Width option.]
Basic Example
The following image shows a dataset containing an employee’s information in column B.
However, column B contains data of varying size or length, and the default column width has made the data less readable. So, here is how to make all the data in column B visible.
Step 1: Hover the cursor over the column B header’s separator on the right side to get the column width resizing pointer. Also, pressing the mouse’s left key shows the current column B width in points and pixels.
Step 2: Double-click the mouse on the column B header’s separator on the right to auto-adjust the column width according to the largest value in the column, which is the cell B3 data.
[Alternatively, click the column B header to choose the entire column and select Home – Format – AutoFit Column Width to auto adjust column width to fit text in column B.
Once we complete the Excel column auto width action, the chosen column adjusts automatically based on the largest data in the specific column, i.e., the cell B3 value.]
AutoFit Column Width Using Excel Shortcut Key
While using an Excel column auto width code may appear simpler to auto-adjust a column’s width, Excel offers shortcut keys to achieve the desired outcome.
The steps to use the Excel column auto width adjustment option, AutoFit Column Width, using Excel shortcut keys to adjust the column width automatically are as follows:
- Click the required column header to select the entire column.
- Press Alt + O + C + A to auto-adjust the chosen column’s width. Otherwise, press Alt + H + O + I to choose the AutoFit Column Width option for performing the column width autofitting action.
Basic Example
The following image shows a dataset containing a list of products and their order data.
However, some columns in the given source dataset do not show the complete data due to the inadequate column widths. Thus, here is how to use the AutoFit Column Width shortcut to auto adjust column width to fit text in each column.
Step 1: Place the cursor on the first column header, and while pressing the mouse’s left key, drag the mouse to the last column header in the dataset to select the entire columns.
[Alternatively, click the first column header to select the entire column in the concerned set. Next, press Ctrl and click on the required column headers to select the remaining columns entirely.]
Step 2: Press the Excel column auto width keyboard shortcut Alt + O + C + A.
Once we press A, the column widths of all the chosen columns get auto-adjusted.
[Alternatively, choose the required columns, as explained earlier, and press the excel keyboard shortcut Alt + H + O + I.
The keyboard shortcut will select the Excel column auto width adjustment option, AutoFit Column Width, from the Home tab. The option adjusts the chosen columns’ widths according to the largest values in each column.]
Important Things To Note
- Auto-adjusting one or more columns’ width using the Excel column auto width fitting feature, AutoFit Column Width, requires us to select the column or columns entirely. Otherwise, only the chosen cells’ width will get auto-adjusted.
- The keyboard shortcut to access the AutoFit Column Width option is Alt + H + O + I.
- We must double-click the column header’s separator on the right side to auto-adjust the column’s width.
Frequently Asked Questions (FAQs)
We can auto adjust column width in Excel VBA using the following steps, explained with an example.
The following dataset lists the students in three teams participating in a quiz competition.
However, the data in all the columns is not entirely visible. So, here is how to use VBA coding to make the dataset information readable.
Step 1: Keep the worksheet containing the source dataset as the active sheet, and press Alt + F11 to open the VBA Editor.
Step 2: Choose the applicable VBAProject and choose the Module option from the Insert tab in the top menu to open a new module window.
Step 3: Enter the VBA code to auto-adjust the width of columns containing data in the active worksheet.
Step 4: Select the play icon in the top menu to execute the VBA code.
Finally, open the active worksheet to view the column widths in the source dataset auto-fitted according to the largest value in the respective columns.
Thus, we can now view the entire source data more clearly.
We can autofit column width in Excel for Mac using the following steps:
1. Click and drag across the column headers for the columns we aim to auto-adjust the widths. But if we must auto-adjust the width of a single column, we do not have to choose it.
2. Point to the column separator on the right side of any chosen column header. The cursor shows as a column width resizing pointer.
3. Double-click the column separator to auto-adjust the chosen column or columns’ width.
You can stop Excel from auto adjusting column width by following the below steps:
1. Click on a column heading to choose the entire column. But if we must choose non-contiguous multiple columns, press Ctrl and click the remaining column headings to select them. However, if we must select a set of adjacent columns, hover the cursor over the first column heading, and while pressing the mouse’s left key, drag the mouse to the last column. The action will select the required adjacent columns.
2. Choose Home – Format – Column Width to open the Column Width window.
3. Set the required column width for the chosen column or columns in the Column width field and click OK in the Column Width window.
Download Template
This article must be helpful to understand the Excel Column Auto Width, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Excel Column Auto Width. We learn to set the default column width, auto-adjust column width to the largest value & shortcut. You can learn more from the following articles –
Leave a Reply