Cell expansion in Microsoft Excel. Excel for Dummies - Changing Columns, Rows and Cells How to set the desired column width

For any, not only electronic, table, the height and width of a cell is one of the basic questions. What are, for example, various unified forms, which are often implemented using Excel. In this article, you will learn about ways to size spreadsheet elements (rows, columns, cells).

Let's consider several options:

manual setting of borders;
auto-selection depending on what is entered in the cell;
precise setting of dimensions in the selected units of measurement;
using the clipboard;
merging cells as a way to resize.

The first case is the most common. When placing the mouse cursor between column names or row numbers, it changes shape to a double-headed arrow, the tips of which indicate in which directions the element can be resized.

The second option is best suited for a situation where you want to make the sizes of rows or columns as small as possible. User action algorithm:

select an element or several elements of the table for which sizes are set;
tab home expand the list at the command Format and select the appropriate command auto-match.

You can see the result of autofitting the column widths in the figure.

For various standardized tables, it is important to set the dimensions of the elements accurately. In this case, you first need to decide on the units of measurement. By default, Excel sheet sizes are in inches. You can change these settings under Additionally teams Options tab File.

Changes are more clearly displayed in the mode Page layout(on tab View find the appropriate command). Rulers with markings in the selected units, such as centimeters, become visible. Next, select the custom element (column or row) and set the size of the element (in the example, this is a column) using the appropriate command in the button menu Format.

If you need two elements of the same size, you can use Paste Special. To do this, you first need to copy the sample element. Then select the element to which you want to apply formatting settings (including dimensions) and select special insert in command options Insert, then in the dialog box - Column Widths.

Often there is a task to change the size of only one cell. This is possible in Word, but not in Excel, so you have to resort to workarounds, namely merging cells. This can be illustrated by the example of a unified form. First, a width of 1 mm is set for all columns, and then a certain number of cells are selected in the desired row and combined using the button Merge and center.

The combination of all the resizing capabilities allows you to create the most complex tables, while retaining all the advantages of automating calculations in Excel.

Quite often, when working with tables, users need to resize cells. Sometimes the data won't fit in the elements of the current size and you have to expand them. The reverse situation is also often encountered, when in order to save a workplace on a sheet and ensure compact placement of information, it is required to reduce the size of the cells. Let's define actions with which you can change the size of cells in Excel.

It should be noted right away that for natural reasons, changing the value of only one cell will not work. By changing the height of one sheet element, we thereby change the height of the entire row where it is located. Changing its width - we change the width of the column where it is located. By and large, there are not so many options for resizing a cell in Excel. This can be done either manually by dragging the borders, or by specifying a specific size in numerical terms using a special form. Let's learn about each of these options in more detail.

Method 1: dragging borders

Changing the cell size by dragging the borders is the easiest and most intuitive option.

Changing the width of sheet elements by dragging the borders follows the same principle.

If you want to resize several objects at the same time, then in this case you must first select the sectors corresponding to them on the vertical or horizontal coordinate bar, depending on what you want to change in a particular case: width or height.

Method 2: change the value in numerical terms

Now let's find out how you can change the size of the elements of the sheet by setting it with a specific numeric expression in a field specially designed for this purpose.

In Excel, by default, the size of sheet elements is set in special units. One such unit is equal to one character. The default cell width is 8.43. That is, in the visible part of one sheet element, if it is not expanded, you can enter a little more than 8 characters. The maximum width is 255. You cannot enter more characters in a cell. The minimum width is zero. An element with this size is hidden.

The default line height is 15 points. Its size can vary from 0 to 409 points.

You can also change the width of a column in much the same way.

There is another option to change the size of the elements of the sheet by setting the specified value in a numeric expression.

However, some users are still not satisfied with the Excel system for specifying the size of sheet elements in points, expressed in the number of characters. For these users, it is possible to switch to another measurement value.

Now you can adjust the change in the size of the cells using the options that are indicated above, operating with the selected unit of measurement.

Method 3: Automatic resizing

But, you must admit that it is not very convenient to always manually resize cells, adjusting them to specific content. Fortunately, Excel provides the ability to automatically resize sheet items to fit the size of the data they contain.

As you can see, there are several ways to change the size of cells. They can be divided into two large groups: dragging borders and entering a numerical size in a special field. In addition, you can set the autofit height or width of rows and columns.

Working in Microsoft Excel, you can freely change the height of the rows and the width of the columns and set them to the desired value. Excel offers many tools to do this, including autofit height and width. In this tutorial, we will look at the most common ways to resize rows and columns.

When you create a new workbook, all rows and columns are set to the same height and width by default. Excel allows you to change column widths and row heights in a variety of ways, including word wrapping and cell merging.

Change column width in Excel

In the example below, some of the text in the column A is not displayed because the cell width is too small. We can make all text visible by simply changing the column width A.

If you suddenly see that instead of the expected result, the entire cell is filled with pound signs (#####), this means that the width is not enough to display the contents of the cell. Just increase the column width so that all the content will fit completely.

AutoFit Column Width and Row Height in Excel

Tool Auto selection allows you to set the column width or row height according to its content.

You can use the tool Auto selection to change the width of several columns at once. To do this, select the columns you want to change, and then on the tab home from command dropdown menu Format select item AutoFit Column Width. The same method can be used to autofit the line height.

Change row height in Excel

Change the height of all rows in an Excel sheet

Instead of resizing rows and columns separately, you can also resize them at the same time. This method allows you to set the same size for all rows and columns on a worksheet. In the following example, we will set the same height for each row.

You can select all cells in an excel sheet by simply pressing the combination Ctrl+A on keyboard.

Quite often, the content of a cell in a table does not fit within the default borders. In this case, the issue of their expansion becomes relevant so that all the information fits and is visible to the user. Let's find out in what ways you can perform this procedure in Excel.

There are several options for expanding cells. Some of them allow the user to push the borders manually, while others can be configured to automatically perform this procedure depending on the length of the content.

Method 1: simple border dragging

The easiest and most intuitive way to increase the size of a cell is to manually drag the borders. This can be done on the vertical and horizontal coordinate scale of rows and columns.

Attention! If you set the cursor on the horizontal coordinate scale to the left border of the expandable column, and on the vertical coordinate scale to the top border of the row by dragging, then the size of the target cells will not increase. They will simply move to the side by changing the size of other elements of the sheet.

Method 2: Expanding Multiple Columns and Rows

There is also an option to expand multiple columns or rows at the same time.

Method 3: manually entering the size through the context menu

You can also manually enter the size of the cells, measured in numerical values. By default, the height is 12.75 units and the width is 8.43 units. You can increase the height to a maximum of 409 points and the width to 255.

The row height is changed in a similar way.

The above manipulations allow you to increase the width and height of the cells in units of measurement.

Method 4: entering the size of the cells through the button on the ribbon

In addition, it is possible to set the specified cell size via the button on the ribbon.

Method 5: Increase the size of all cells in a worksheet or workbook

There are situations when you need to increase absolutely all the cells of a sheet or even a book. Let's figure out how to do it.

We perform similar actions to increase the size of the cells of the entire book. Only to select all sheets we use a different technique.

Method 6: autowidth

This method cannot be called a full-fledged increase in the size of the cells, but, nevertheless, it also helps to completely fit the text into the existing borders. With its help, the characters of the text are automatically reduced so that it fits in the cell. Thus, we can say that its size relative to the text increases.

After these steps, no matter how long the entry is, it will fit in the cell. True, it must be taken into account that if there are too many characters in the sheet element, and the user does not expand it in one of the previous ways, then this entry can become very small, up to unreadable. Therefore, to be content only with this option in order to fit the data within the boundaries is not acceptable in all cases. In addition, it should be said that this method only works with text, not with numeric values.

As you can see, there are a number of ways to increase the size of both individual cells and entire groups, up to an increase in all elements of a sheet or book. Each user can choose the most convenient option for him to perform this procedure in specific conditions. In addition, there is an additional way to fit content within a cell using autofit. However, the latter method has a number of limitations.

Excel columns are often not wide enough to accommodate long headings and large numbers. In these cases, the following situations may occur. Text records "crawl out" to the cells adjacent to the right, if they are free; otherwise, the text on the screen is truncated. Numbers with a long "tail" after the decimal point may appear shortened with the loss of some significant digits on the right. Large numbers are often represented on the screen as ######, symbolizing overflow. In order for all the contents of the cell to be displayed correctly on the screen, the column must be expanded. Using the same methods, you can shrink columns to a size that is no wider than required to display their content on the screen.

Note
Even if an entry on the screen looks truncated, Excel "remembers" the entire contents of that cell, playing it on the screen in the formula bar, when that cell is referenced in some formula or function.

Advice
Double-clicking on the line to the right of the column heading changes the column width so that the largest entry fits entirely in the cell.

In the column you want to expand or contract, hover over the line on the right border of the column heading. Pay attention to the shape that the mouse pointer will take ()
While holding down the left mouse button, move the pointer to the right (the column will expand) or to the left (the column will shrink). A vertical line will appear showing how wide the column will be if the mouse button is currently released. As you drag, an on-screen tooltip appears showing the current column width.
Release the mouse button and check if the column has reached the desired width. Is the data still truncated? Or maybe the column came out too wide and takes up extra space? Here's an example of an employee name column that seems to have been given the optimal width. If necessary, repeat steps 1-3 to further adjust the width of this or other columns.
If you want to change the width of several columns at the same time, first select them (by clicking on the corresponding column letters) and then drag the mouse to adjust the width of one of the columns. The last action will expand or shrink all selected columns by setting them to the same width.
Row heights can be adjusted in the same way as column widths. To do this, position the mouse pointer at the bottom of the row header (it will look like ), and then drag the bottom border of the row up or down. You can select multiple rows and drag the headings of one of those rows to adjust the height of all of them.

Advice
Excel can independently choose the optimal width for a column or group of columns. To do this, select a group of columns and in the menu Format select a team Column › AutoFit. The width of each column from the selected group will be adjusted so that the largest entry fits entirely in the cell. There is a command for automatic selection of line heights Format › String › Autofit Height .

Note
Sometimes Excel expands columns for convenience. If you enter a number and then set a number format for it that causes the number to grow larger, Excel adjusts the column width accordingly.