How to Change Column Width and AutoFit Columns in Excel

- Written by Puneet

excelchamps-free-courses-puneet-gogia

In Excel, a sheet has multiple cells of the same size, each containing 8.43 characters.

Especially while preparing reports, creating data tables, or doing other calculations, you often need to alter (increase -decrease) the width of a column.

And, by increasing your columns wider, you can easily get the visibility of the whole data on your sheet.

In this tutorial, we will learn several effective ways to adjust the width of the columns.

Key Takeaways

  • Try not to make columns narrow enough, as it can make your data hard to read and take up unnecessary space.
  • If you have many columns to adjust, selecting all of them may be more efficient for adjusting the width.
  • Autofit is quick and easy, but if your cells contain a lot of text, there may be better choices.

Adjust Column Width with Mouse

Adjusting the width of the column by clicking and dragging is very easy and understandable. Let’s do this step by step.

  1. To change the width of “Single or multiple columns”, first, select that specific column on the spreadsheet.
    select-the-column
  2. After that, move the pointer of the mouse to the border of the selected column in the column header.
    mouse-pointer-on-column-border
  3. Once it looks like a four-sided arrow, press and hold the left button of the mouse and drag it towards the right side to increase its width size.
    drag-towards-with-mouse
  4. For changing the width of “All Columns”, press Ctrl + A first to select all columns from the sheet and now drag on any column header to adjust it as you required.
    select-the-sheet-to-expand-all-columns

You can also view the current width of the column by clicking on the border of the column in the column header with its pixels.

current-width-of-column

Set Column Width to a Specific Number

  1. Again, you need to select the column first to change its width to a specific number.
    set-column-width-to-specific-number
  2. Then, in the Home tab, go to the Cells group and now click on “Format”.
    home-tab-cells-icon
  3. Next, choose “Column Width” from the list of the drop-down.
    choose-column-width
  4. Once you click, it opens the dialog box of column width. Now, enter the exact value of the width that you want in the box and click “OK” to apply.
    column-width-dialog-box

Change the Column Width by Right-Click in Excel

To change the column width in Excel, right-click on the column header and select the column you want to adjust.

After selecting the column, right-click on the column header. A menu will appear.

column-width-by-right-click

In the drop-down menu, look for the option labeled ‘Column Width’. Click on this option.

A dialog box will appear, asking you to enter a new width for the column.

Type in the desired width and then click ‘OK’. The width of the column will be adjusted accordingly.

column-width-dialog-box

Repeat these steps for any other columns you want to adjust. When you’re done, remember to save your changes.

Adjust Column Width by AutoFit Option

If the data in the columns are not visible, you can use the autofit option to adjust the width of the columns using double click.

The following are the steps to do:

  1. For this, you must first select the single, multiple, or all columns to set the width.
    column-width-autofit
  2. Next, go to “Format” in the cells group under the Home tab. Select the “Autofit Column Width” option from the list of options.
    select-autofit-column-width
  3. Then, it autofits the width of all selected columns automatically so that you can see all the data easily, as shown in the following picture.
    columns-width-adjusted

If you have other columns that you want to adjust, simply repeat these steps for each of those columns.

With the AutoFit option, you can easily ensure that your columns are the perfect width for their content, enhancing the readability and appearance of your Excel worksheets.

Setting the Default Width of Columns

With default column width, you can simultaneously change the width of complete worksheet columns and set the width for all worksheets in a workbook.

  1. Select the single or multiple spreadsheets where you would like to adjust the default width. Here, we are selecting the single worksheet as an example.
    default-width-of-the-columns
  2. Now, go to the “format” and click on the “Default Width” option.
    default-width-option
  3. Then, it will open the “Standard Width” dialog box. There, you can see the current width of the blank columns, and it’s 20.
    standard-width-dialog-box
  4. Last, you need to enter the “Standard Column Width” in the box and click “OK” to apply.
    standard-column-width-dialog-box

Points to Remember

  • The standard default width applies to all blank cells in the worksheet instead of those with data already.
  • The autofit column width doesn’t apply if the column width is sufficient for the data in the column.

Understanding the Width used in Excel Columns

In Microsoft Excel, the width of a column is determined not by pixels but by the number of characters that can fit within it.

This measurement is based on the default font size and the character ‘0’ width. It means that one unit of column width is equivalent to the width of one ‘0’ character in the default font size.

For example, if you set a column’s width to 10, you could fit ten ‘0’ characters side by side in that column.

This character-based measurement method is unique to Excel and some other spreadsheet software. It helps maintain consistency when viewing the workbook.

It’s always a good idea to check how your data displays on different screens and under different view settings to ensure it remains legible and well-formatted.

Last Updated: March 22, 2024