In this short tutorial, you will learn a few efficient ways to change the column width manually and have it adjusted automatically to fit the contents (AutoFit).
Changing the width of a column in Excel is one of the most common tasks that you perform daily when designing your reports, summary tables or dashboards, and even when using worksheets only to store or calculate data.
Microsoft Excel provides a variety of ways to manipulate the column width - you can resize columns using the mouse, set the width to a specific number or have it adjusted automatically to accommodate the data. Further on in this tutorial, you will find the detailed information about all these methods.
On an Excel spreadsheet, you can set a column width of 0 to 255, with one unit equal to the width of one character that can be displayed in a cell formatted with the standard font. On a new worksheet, the default width of all columns is 8.43 characters, which corresponds to 64 pixels. If a column's width is set to zero (0), the column is hidden.
To view the current width of a column, click on the right boundary of the column header, and Excel will display the width for you:
Columns in Excel do not resize automatically as you input data in them. If the value in a certain cell is too large to fit in the column, it extends over the column's border and overlaps the next cell. If the column to the right contains data, then a text string is cut off at the cell border and a numerical value (number or date) is replaced with a sequence of hash symbols (######) like shown in the screenshot below:
If you want the information in all cells to be readable, you can either wrap text or adjust column width.
I believe everyone knows the most common way to make a column wider or narrower by dragging the border of the column header to the right or to the left. What you might not know is that using this method you can adjust the width of several columns or all columns on the sheet at a time. Here's how:
As explained at the beginning of this tutorial, the Excel column width value represents the number of characters that can be accommodated in a cell formatted with the standard font. To resize columns numerically, i.e. specify an average number of characters to be displayed in a cell, do the following:
In your Excel worksheets, you can also auto fit columns so that they get wider or narrower to fit the largest value in the column.
Another way to autofit columns in Excel is by using the ribbon: select one or more columns, go to the Home tab > Cells group, and click Format > AutoFit Column Width.
When preparing a worksheet for printing, you may want to fix the column width in inches, centimeters or millimeters.
To have it done, switch to the Page Layout view by going to the View tab > Workbook Views group and clicking the Page Layout button:
Select one, several or all columns on the sheet, and drag the right boundary of any of the selected column headings until you set the required width. As you drag the boundary, Excel will display the column width in inches like shown in the screenshot below:
With the width fixed, you can exit the Page Layout view by clicking the Normal button on the View tab, in the Workbook Views group.
You already know how to make several or all columns on the sheet the same width by dragging the column border. If you have already resized one column the way you want, then you can simply copy that width to other columns. To have it done, please follow the steps outlined below.
Alternatively, you can select some cells in the target columns, press the Paste Special shortcut Ctrl + Alt + V, and then press W.
The same technique can be used when you create a new sheet and want to make its column widths the same as those in an existing worksheet.
To change the default width for all columns on a worksheet or the entire workbook, just do the following:
As you see, there exist a handful of different ways to change column width in Excel. Which one to use depends on your preferred work style and situation. I thank you for reading and hope to see you on our blog next week!
6 Responses to "How to change column width and AutoFit columns in Excel"
Thanks!!! Svetlana Cheusheva!!! Very nice Article. Can you please publish Article on Form control and Basic VBA lessions
Hi Rohan,
Thank you for your kind feedback! I think we have a few articles on the subject of interest:
How to insert calendar in Excel (Date Picker control)
How to insert and run VBA code in Excel (step-by-step guide)
Tutorial with Excel examples about Macros
What will 80 pixels convert to in characters? And how can I calculate this in the future?
Hello, Jimmy,
unfortunately, we can’t tell you exactly how many characters 80 px will take, because it depends on the font family, font size, Excel and Windows versions installed, and your monitor resolution. The simplest way to see how many characters fits into your cell is to right-click and hold between two column headings, and Excel will instantly show you the result.
I think Jimmy was looking for a default calculation. The article states, "On a new worksheet, the default width of all columns is 8.43 characters, which corresponds to 64 pixels." It makes sense that the character width to pixel ratio is dependent on the standard font (although I haven't tested to see if changing the standard font affects this ratio). However, I suspect the default ratio of 8.43 characters to 64 pixels is pretty standard. Or perhaps the article should not have stated the default column width and the corresponding pixel count?
Jimmy, on my version of Excel, which has the default ratio above, each character past the first one takes up 7 pixels. For some reason, Excel divides the first character into 12 divisions. You can hover over each column divider to see the width in both characters and pixels. Test by changing the column width, then hover the mouse to confirm that ratio is correct on your version also.
So the formula to convert column width to pixels (for the default ratio above, which has valid widths from 0 to 255) is this:
=ROUND(IF(ColWidth<1, ColWidth*12, 12+((ColWidth-1)*7)), 0)
To convert back to ColumnWidth, use this formula:
=ROUND(IF(Pixels<12, Pixels/12, 1+((Pixels-12)/7)), 2)
Thanks Svetlana
This information helped a lot