Changing column widths and row heights in Excel. How to resize cells in Excel and make them the same How to change the width of all columns in Excel

Quite often, the contents of a cell in a table do not fit within the default boundaries. In this case, the issue of expanding them becomes relevant so that all the information fits in and is visible to the user. Let's find out how you can perform this procedure in Excel.

There are several options for cell expansion. Some of them involve the user manually pushing the boundaries, while others can be configured to automatically perform this procedure depending on the length of the content.

Method 1: Simple Drag and Drop Borders

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

Attention! If on the horizontal coordinate scale you place the cursor on the left border of the expandable column, and on the vertical scale - on the top border of the row by performing the drag-and-drop procedure, then the size of the target cells will not increase. They will simply move to the side due to changes in 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 once.

Method 3: Manually entering the size via the context menu

You can also manually enter the cell size, 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 can be changed in a similar way.

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

Method 4: Entering cell size through a button on the ribbon

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

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

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

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: Automatic width selection

This method cannot be called a full increase in the size of the cells, but, nevertheless, it also helps to completely fit the text within the existing boundaries. With its help, text characters 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 into the cell. However, you need to take into account that if there are too many characters in a sheet element, and the user does not expand it using one of the previous methods, then this entry can become very small, even unreadable. Therefore, being content solely with this option in order to fit the data within the boundaries is not acceptable in all cases. Also, it should be said that this method only works with text, not 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 increasing all elements of a sheet or book. Each user can choose the most convenient option for him to perform this procedure in specific conditions. Additionally, there is an additional way to fit content within a cell using auto-width. True, the latter method has a number of limitations.

When you open a new workbook, all cells in it are of the default size. You have the ability to modify cells, insert and delete columns, rows and cells if necessary.

In this tutorial, you'll learn how to change row heights and column widths, insert and delete rows and columns, wrap text in a cell, and merge cells.

Working with Rows, Columns, and Cells

By default, every row and every column of a new workbook has the same height And width. Excel allows you to change column widths and row heights in a variety of ways.

To change the column width:

If you see (#######) characters in a cell, the column is not wide enough to display the contents of the cell. Simply increase the column width to display the cell's contents.

To set an exact column width:

Select AutoFit Column Width from the Format command's drop-down list, and Excel will automatically adjust the width of each selected column to match the text you enter.

To change the line height:

To set the exact line height value:

Select AutoFit Row Height from the Format command's drop-down list, and Excel will automatically adjust the height of each selected row to match the text you enter.

To insert rows:

When you insert new rows, columns, or cells, you may see a button next to the inserted cells Paste options. This button allows you to choose how Excel should format them. By default, Excel formats inserted rows or columns the same as the cells in the row above the inserted one. To access more options, hover over the Paste Options button and click the drop-down arrow.

To insert columns:

By default, Excel formats inserted columns the same as the columns to the left of them. To access more options, hover over the Paste Options button and click the drop-down arrow.

When you insert rows or columns, be sure to select the row or column by clicking on the header, that is, all its cells are selected. If you select only a cell, then only one new cell will be inserted.

To remove rows:

To remove columns:

Wrapping text and merging cells

If a cell contains more text than can be displayed, you can select the Wrap text in cell option or merge the cell with its empty neighbors. Wrap text causes text to appear in a cell in multiple lines. Merge cells connects adjacent cells into one.

To install Text Wrap:

If you change your mind about wrapping text, simply click the Wrap Text command button again.

To merge cells using the Merge and Center command:

If you change your mind about merging cells, simply click on the Merge and Place in Center command again.

To access the merge options:

Click the drop-down arrow next to the “Merge and Center” command on the Home tab. A drop-down menu will appear.

Merge and place in the center: Merges the selected cells and places the text in the center.
Merge by lines: Merges each row of selected cells into larger cells. This command is useful when you combine the contents of many cells in rows but don't want to create one large cell.
Merge cells: Merges selected cells into one.
Unmerge cells: Undoes merging of selected cells.

Practice!

Open an existing Excel 2010 workbook.
Resize the column.
Change the line size to 46 pixels.
Insert a column between columns A and B.
Insert a line between lines 3 and 4.
Delete a column or row.
Try merging multiple cells.

The fastest way to ensure that the contents of cells are displayed in full is to use the mechanism for automatically adjusting the column width/row height to the content.

Let there be a table with cells filled with text values.

Automatic column width

As you can see from the picture above, text values in cells A2 And A3 are not fully displayed, because the text in the column gets in the way B . We need the contents of all cells in the column A was displayed in full. To do this you need to make the column width A sufficient to display the longest text in a column. This is done simply: move the mouse pointer to the columns section A And B (on the gray column headers), the cursor will look like this:

We double-click the mouse and, Voila, the column width has become sufficient to display the values in all cells of the column (including hidden rows).

If you need to align the width to the content of several columns at once, then do the following:

highlight the required columns (by their gray headings);
move the cursor to any section of the selected columns and double-click.

Alternative option:

Select the column or columns whose width you want to change;
On the tab home in Group Cells select team Format;
In Group Cell size select item Automatic column width.

Automatic line height

If the cells contain values with a very large line length (for example, if the length of the text line without breaks is comparable to the width of the visible part of the sheet), then the column width may become too large, and it will not be convenient to work with the data. In this case, you need to select the cells and enable the option Wrap according to words through Cell Format(or through the menu).

The column width will remain the same, but the row height will automatically increase to fully display the cell value.

Sometimes, despite the option being installed Wrap according to words, the line height is not enough to display the entire text line (this could happen if the line height was manually reduced). In this case, you need to do the same as we did in the case of selecting the row width - double-click on the section border, but now not columns, but rows:

After this, the text value will be displayed in full in the cell:

Real example

Now we will apply the considered method in a real situation - we will edit the table created by copying data from MS ACCESS. Let's copy via Clipboard some table from MS ACCESS to an EXCEL sheet.

Please note that the cells in which we inserted values from Clipboards, option enabled Move according to words although, by default, it is disabled (EXCEL itself enabled it when inserting). Additionally, EXCEL did not change the default column widths, only the row heights were changed to fully display all values. This table formatting does not always suit the user. Using the inserted table, we will solve 2 problems.

Task 1. Let's say we need all values to be displayed in a table in one row.

For this:

select the inserted values (to do this, select any table cell and click CTRL+A(highlighted), if the table does not contain empty rows and columns, then all inserted values will be highlighted);
turn off the option Wrap according to words(via menu Home/ Alignment/ Wrapping text);
the height of the cells will be reduced so as to display only one row, as a result, some of the values will become invisible;

highlight the columns A , B And WITH for gray headings;
move your mouse to the columns section A And B (on the gray column headers) and double-click.

Task 2. Now let's say that we want all columns to have a certain user-specified width, and the values to be displayed entirely in the cell. In this case, the line height should be minimal.

For this:

set the required column width A , B And WITH ;
option Wrap according to words do not turn off (turn on);

select all rows containing inserted values, or all sheet cells (all sheet cells can be selected by clicking the button Select all in the upper left corner of the sheet or by double-clicking CTRL+ A);
Move the mouse pointer to the section of any two filled rows (on the gray column headers) and double-click.

The problem is solved: the contents of all cells are fully displayed.

When creating a table in Excel, you usually need to adjust the size of the cells - make them larger or smaller. In order to make them the same in height and width, many users try to move the slider “by eye”. Even though the gap in numbers is shown during setup, this method is not very convenient and takes a lot of time.

In some cases, you need to set exact margin sizes

The column spacing in an Excel table can be any value in the range from 0 to 255 units. It determines the number of characters that will fit into an object that is formatted using a base font. The base value of the vertical range is 8.43 units. If you set the value to zero, the vertical row will be hidden.

The height of the bar in an Excel table can be set in the range from 0 to 409 units. As with the column width, if you set the value to zero, the bar will be hidden, only this time it will be horizontal. The unit of horizontal row height corresponds to approximately 0.035 centimeters or 1/72 of an inch. The base value is 12.75 points.

When working in page layout mode, you can specify the size of the strip of objects in centimeters, millimeters, or inches. To change the measure, click “File” - “Options” and in the “Advanced” category, find the “Screen” section. In the "Units on Ruler" drop-down menu, set the system of measures that suits you best.

The horizontal spacing of a row of objects is adjusted to accommodate longer numbers or sentences. This is done according to several principles:

To change the value of one column, drag its right border left or right, setting the desired indicator.
To set up a custom quantity, select them and drag the border of any. To edit two or more non-adjacent vertical ranges, select the first one, hold Ctrl and click on the others. Adjacent - Select the far left or right, then hold Shift and select the opposite one.
In order to apply editing to all columns, first mark them on the corresponding button (at the intersection of the markings of numbers and Latin letters) or press Ctrl + “A”.

The only drawback of this option is that it is not very accurate.

How to set a column's value via formatting

Algorithm:

Select an arbitrary number of columns;
In Cell Size, click Column Width;
Enter a value in the field.

How to make the distance between column borders equal

If you need to change a value so that it is equal to another:

Mark the object with the desired indicator;
In the “Buffer” group on the main tab, click “Copy”;
There, click on the drop-down menu under “Insert” and select the “Paste Special” link (at the very bottom);
In the Insert area, click Column Widths.

You can also select two or more bars in the table, and then adjust any of them with the mouse pointer. The edit will be applied to everyone.

How to set the default width of all vertical rows

The bar size indicator tells you how many characters of the base font will fit in the element. You can specify your own value for the column, which will be applied by default:

For one sheet - mark it with a left mouse click;
For several pages - select any one, then click on others while holding Ctrl;
For the entire workbook - right-click on any page and click “Select all sheets”.

In the “Home” tab, click “Format”;
In the Cell Size area, select Default Width;
Enter a new value in the field.

How to make a template for creating sheets and books

In order to set the parameters of elements in future Excel sheets and workbooks, you can create templates. Based on them, new tables will have user parameters. To do this you need:

Open the book you want to use as a template;
Click “File” - “Save As”;
Enter the template name in “File name”;
In the “File type” drop-down menu, click “Excel template” or its equivalent “with macro support”, if they are in the book and will be needed in the future;
Click "Save".

The document will be moved to the templates folder, where you need to select it to create a book next time.

How to edit the gap of a horizontal row with the mouse

Here, as in the case of width, you need to mark an arbitrary number of vertical columns, then drag any border. Moving up will make the object larger, and moving down will make it smaller.

How to set horizontal range spacing via formatting

Algorithm:

Select any number of columns;
In the “Home” tab, find the “Cells” area and click “Format”;
In Cell Size, click Row Height;
Enter a value in the field.

Automatic change based on element content

Thanks to this function, the elements themselves will edit the value depending on how much data is recorded in them:

Select an arbitrary number of columns;
In the Cells group on the main tab, click Format;
In Cell Size, click AutoFit Column Width (or Height).

This can also be done by clicking on the right border of the vertical bar and the bottom border of the horizontal bar (any of the marked ones).

Bottom line

Setting the gap of an element allows you to change the value, and accordingly, the amount of data that it contains. To do this, Excel has several editing methods and display mode options.

2017-02-06T17:39:57+00:00

In this lesson I will tell you how you can set and change row height in Excel.

How to change line height with mouse?

To change the height of a line with the mouse, you need to move the cursor to its bottom border (that is, to the border with the line that goes below). The cursor will change to a black double-headed arrow. Press the left mouse button and move the cursor up and down to the desired distance:

If you need to set the exact value of the line height in points, do not first select it with the left button. Move your cursor to the row header, and when it changes to a thick black arrow, right-click. From the drop-down menu, select “Row Height...” and enter a value.

By right-clicking, you save time on selecting a line.

How to change the height of multiple lines?

Excel allows you to change the height of several rows at once. If they are in a row, select them by headings and set the desired height manually (or through the “Line Height” menu item). Working manually, drag the bottom border of the header of any of the selected lines.

You can select several lines that are separated from each other by other lines by using the Ctrl key in the process. First, select the “first” line with the left button, then hold down Ctrl and select the remaining lines. You can also drag the bottom border of any of the selected lines.

Automatic selection of row heights based on their contents

Excel allows you to automatically set row heights based on the contents of the cells. If you need to auto-select the height for one line, move the cursor to the bottom border of its number (that is, to the border with the line that goes below), the cursor will change its appearance to a black double-sided arrow. Double-click with the left mouse button:

Proceed in the same way if you need to auto-select the height of several lines.

It might be useful to read: