How to add a line in the xl table. Adding a new line in Microsoft Excel

Some of the most commonly used actions when working with tables in Excel are adding and deleting rows in general and empty lines in particular. Let's look at the mechanisms for adding and removing empty rows in tables.

Inserting one blank row into an Excel table

Add a new line using standard using Excel can be done as follows: right click click on the line number above which you want to insert a new line and select “Insert” in the context menu

Moreover, if some data in the table has been formatted (fonts changed, colors changed, cell fills set), then after adding new line The “Add Options” button will appear, by opening it you can select one of three options for the new line: “Format as top”, “Format as bottom” and “Clear format”.

You can also insert an empty line using the buttons on the toolbar. To do this, select the line above which you want to insert a new empty line. After that, go to the “Home” tab, in the “Cells” group and expand the “Insert” button menu, select “Insert rows into sheet”.

Adding multiple blank rows to an Excel table

In order to insert several new lines into a document, you need to select the line above which you want to add new lines, and without releasing the left mouse button, select as many lines as you need to insert.

After the lines are selected, you need to right-click on the selected area of the sheet and select “Insert” from the context menu, or use the buttons in the ribbon tabs, similar to how described in the previous paragraph.

How to insert/add a given number of blank/new lines at specific places?

It is not always possible to solve such a problem using standard Excel tools, but let’s not forget that there is such a thing. powerful tool like VBA, which allows you to automate repetitive actions of the same type. Install according to instructions V Excel application add-on for inserting blank lines, which provides the following features:

1. Selecting the desired value, which can be empty cells, non-empty cells, cells with specified color fills, cells with given number, as well as cells with given text(bulls, symbols);

2. inserting a specified number of empty lines both above and below the lines with the desired value;

3. inserting a different number of empty rows depending on the value specified in a given column;

4. inserting new lines below every second, third, fifth, nth line of the selected range (the step is specified by the user);

5. insert empty rows each time a value changes in a given column;

6. insert empty lines between filled ones (after each filled cell in a column with a given number);

7. selection of ranges for inserting rows, several modes are provided:

a) used sheet range - the range of the active sheet, which includes all cells located between the first filled and last filled cell;

b) used ranges - differs from the previous mode in that data processing is carried out in the used range of each sheet of the workbook;

c) sheet range - the range that the user will select;

d) sheet ranges are ranges of the same name on all sheets of the workbook.

*Carefully select the range to achieve the desired result!

8. choice necessary conditions for cell values.

For numerical values: equal/not equal, more/not more, less/not less.

For text values: matches/does not match, contains/does not contain, begins/does not begin, ends/does not end.

The program checks all cells in the specified range and, if the selected condition is met, inserts required by the user number of empty lines above/below the line (optional) containing the cell with given value. The user can choose from a variety of conditions for cell values, which can be used to determine where to insert new rows, and also provides a choice of ranges for data processing. For greater flexibility, the search for cells with the desired values can be carried out separately, both by text and numeric cell values. There is also a search for both empty and non-empty cells. This macro also allows you to add new rows in a specified number to every nth row of the selected range. If, for example, we insert two empty lines below every second line of the range A2:A24, we get the following result:

Inserting varying numbers of blank lines

The add-in can insert a variable number of blank rows as long as the number of rows to be inserted is specified in a separate column.

If you check the box " Various quantities empty rows to insert", then the field with the number of rows to insert is changed to the number of the column in which numeric values are indicated.

Add and remove row, column in Excel can be anywhere in the table.You can insert several rows and columns at once in the desired place in the table.
There are several ways.TO.
How to add a column to an Excel table
First way.
To insert a column in Excel, you need to place the cursor on the column title line, on the column next to which you want to insert the column. The new column appears to the left of the selected column. For example.
There is a column “A” with numbers 1, there is a column “B” with numbers 2. You need to insert a column between them. We move the mouse over the address of the column before which we need to insert new column
. A black arrow will appear on the address letter of this column. Click on the right mouse button. In the dialog box that appears, select “insert” in the second section and click left button
mice.
To the left of column "B" (with the numbers 2) a new column has appeared.
Note!Column addresses have changed.
Now the new column has become “B”, and the former column “B” (with the numbers 2) has become “C”.
Second way. Insert Column in Excel- this is to call the context menu on any cell of the column, to the left of which we will insert a new column. We also select “paste” in the second section of the window that appears. And here we already select – “column” -> “OK”. .
Read about the context menu
Let's take a look at this menu right away. Here you can select what we want to insert: column, row, cells that will appear to the right or below the selected cell.
If we first select not one, but several cells, columns, rows (as many as we need to insert), then the same number will be inserted.
Third way.
You can configure Excel to insert columns automatically. See the article "How to add a column in Excel automatically"..
How to add a row in Excel
We need to select the cell above which we will insert a new line and call the context menu. Or move the cursor to the row address and continue in the same way as inserting a column.
In this way, you can also select several lines at once and then insert the same number.
For example. We need to insert three lines between lines 3 and 4. New lines will appear above the selected line. Therefore we mark 4,5,6 lines.
Click “insert”. It turned out like this.
New lines took the place of the old lines, and the old ones moved down, changing their address. Add blank lines in Excel after one, two lines
For example, we need to insert empty rows between the rows throughout the table in a filled table, etc. How to do this, see the article "".
How to create a table in Excel, see the article “How to create a table in Excel”.
How to delete a row, column in Excel.
To delete rows and columns, you need to select them and, in the context menu in the second section, select the “Delete” function.Or highlight rows and columns on the lines of their addresses. Right-click on the address number of the highlighted line and select the “Delete” function.
For ways to select rows and columns, see the article “How to select in Excel cells, table, etc."
In a table, you can not delete columns or rows, but hide them. For example, a column with formulas, etc. How to do this, see the article "How to hide columns in Excel".
When creating a questionnaire, price list, or other documents, you need to create a drop-down list in a cell that appears when you hover the mouse over the cell.How to do this, read the article “

When creating various kinds of new tables, reports and price lists, it is impossible to predict in advance the number of required rows and columns. Usage Excel programs– this is in to a large extent creating and customizing tables, which requires inserting and deleting various elements.

First, let's look at ways to insert worksheet rows and columns when creating tables.

Please note in this lesson hotkeys for adding or removing rows and columns are specified. They must be used after selecting an entire row or column. To select the line on which the cursor is located, press the hotkey combination: SHIFT+SPACEBAR. Hotkeys for selecting a column: CTRL+SPACEBAR.

How to insert a column between columns in Excel?

Let's say we have a price list that lacks item numbering:

To insert a column between columns to fill in price list item numbers, you can use one of two methods:

Now you can fill the new column with price list item numbers.

Inserting multiple columns between columns at once

Our price list is still missing two columns: quantity and units of measurement (pcs. kg. l. pack.). To add two columns at the same time, select the range of two cells C1:D1. Next, use the same tool on the main tab “Insert” - “Insert columns into sheet”.

Or select the two column headers C and D, right-click and select the "Insert" option.

Note. Columns are always added to left side. The number of new columns appears as many as they were previously allocated. The order of insertion columns also depends on the order of their selection. For example, after one, etc.

How to insert a row between rows in Excel?

Now let’s add a title and a new product position “New Product” to the price list. To do this, insert two new lines at the same time.

Select a non-adjacent range of two cells A1;A4 (note that instead of the “:” symbol, the “;” symbol is indicated - this means select 2 non-adjacent ranges; to be convincing, enter A1;A4 in the name field and press Enter). You already know how to select non-adjacent ranges from previous lessons.

Now use the “Home” - “Insert” - “Insert Rows on Sheet” tool again. The figure shows how to insert a blank line in Excel between rows.

It's easy to guess the second method. You need to select the headers of rows 1 and 3. Right-click on one of the selected rows and select the “Insert” option.

To add a row or column in Excel, use the hotkeys CTRL+SHIFT+plus after selecting them.

Note. New lines are always added on top of the selected lines.

Removing Rows and Columns

When working with Excel, you have to delete rows and columns of a sheet no less often than insert them. So it's worth practicing.

For clear example Let's remove the numbering of product items and the column of units of measurement from our price list - at the same time.

Select the non-adjacent range of cells A1;D1 and select “Home” - “Delete” - “Remove columns from sheet”. The context menu can also be deleted if you select the headings A1 and D1, and not the cells.

Removing rows occurs in a similar way, only you need to select the corresponding tool menu. And in the context menu - no changes. You just need to highlight them accordingly by line numbers.

To delete a row or column in Excel, use the hotkeys CTRL + “minus” after selecting them.

Note. Inserting new columns and rows is actually a replacement. After all, the number of rows 1,048,576 and columns 16,384 does not change. Just the latest ones replacing the previous ones... This fact should be taken into account when filling the sheet with data more than 50%-80%.

Calling the command:
-group Cells/Ranges -Working with Rows/Columns

It's no secret that Uncle Bill didn't bother to come up with a tool in Excel for adding multiple rows to a table at once. You can resort to a trick and select a certain number of lines and select the insert command. But this is not always convenient - after all, each time you have to count how many lines to select. If you need to add more than 10 lines, this is very inconvenient. The team is correcting this oversight. You only need to click on the button and indicate the number of lines in the form that appears.

But as you can see - this command allows you to do not just insert rows, but insert with certain conditions.
Add by: indicates the number of lines to be added. Default is 1.
Interval/Step: specifies the step at which empty lines will be inserted. For example, by specifying step 2, rows will be inserted every 2 rows. Default is 0. If not specified, rows will be inserted sequentially.
Start insert with: indicates the line number from which you want to start inserting rows. By default, it is equal to the row number of the active cell at the time the command was launched.
Number of inserts: indicates how many times the insertion must be performed.

Usage example: If you specify Add 2 at a time, Interval 3, Start insertion at 2 and Number of insertions 5, then starting from the 2nd line 5 times 2 empty lines will be inserted every 3 filled ones.
If you need to insert a certain number of rows once, then the Interval must be set to 0, the Number of insertions to 1.

Note: if you specify parameters that will result in the number of rows exceeding the maximum possible number for adding (for example, if you specify Add by 100000 ; Interval 5 ; Start insertion from 8 ; Number of inserts 11 ), the following window will appear:

Tables in Excel are a series of rows and columns of related data that you manage independently of each other.

Working with tables in Excel, you can create reports, make calculations, build graphs and charts, sort and filter information.

If your job involves data processing, then knowing how to work with Excel tables will help you save a lot of time and increase efficiency.

How to work with tables in Excel. Step-by-step instruction

Before working with tables in Excel, follow these recommendations for organizing data:

Data should be organized in rows and columns, with each row containing information about one record, such as an order;

The first row of the table should contain short, unique headings;

Each column must contain one type of data, such as numbers, currency, or text;

Each row should contain data for one record, such as an order. If applicable, please indicate unique identificator for each line, for example order number;

The table should not have empty rows and absolutely empty columns.

1. Select an area of cells to create a table

Select the area of cells in which you want to create a table. Cells can be either empty or with information.

2. Click the “Table” button on the Quick Access Toolbar

On the Insert tab, click the Table button.

3. Select a range of cells

In the pop-up, you can adjust the location of the data and also customize the display of headings. When everything is ready, click “OK”.

4. The table is ready. Fill in the data!

Congratulations, your table is ready to be filled out! You will learn about the main features of working with smart tables below.

Pre-configured styles are available to customize the table format in Excel. All of them are located on the “Design” tab in the “Table Styles” section:

If 7 styles are not enough for you to choose from, then by clicking on the button in the lower right corner of table styles, all available styles will open. In addition to preinstalled by the system styles, you can customize your format.

Besides color range, in the table “Designer” menu you can configure:

Show header row – enables or disables headers in the table;
Total line – enables or disables the line with the sum of the values in the columns;
Alternating lines – highlights alternating lines with color;
First column – makes the text in the first column with data “bold”;
Last Column – makes the text in the last column “bold”;
Alternating columns – highlights alternating columns with color;
Filter Button – Adds and removes filter buttons to column headers.

How to add a row or column to an Excel table

Even within an already created table, you can add rows or columns. To do this, click on any cell right key mouse to open the pop-up window:

Select “Insert” and left-click on “Table Columns on the Left” if you want to add a column, or “Table Rows Above” if you want to insert a row.

If you want to delete a row or column in a table, then go down the list in the pop-up window to the “Delete” item and select “Table Columns” if you want to delete a column or “Table Rows” if you want to delete a row.

How to sort a table in Excel

To sort information when working with a table, click the “arrow” to the right of the column heading, after which a pop-up window will appear:

In the window, select the principle by which to sort the data: “ascending”, “descending”, “by color”, “numeric filters”.

To filter information in the table, click the arrow to the right of the column heading, after which a pop-up window will appear:

“Text filter” is displayed when the column data contains text values;
“Filter by color”, just like a text filter, is available when the table has cells colored differently from standard design colors;
“Numerical filter” allows you to select data by parameters: “Equal to...”, “Not equal to...”, “Greater than...”, “Greater than or equal to...”, “Less than...”, “Less than or equal to...”, “Between...”, “Top 10...”, “Above average”, “Below average”, and also set up your own filter.
In the pop-up window, under “Search”, all the data is displayed, by which you can filter, and with one click, select all values or select only empty cells.

If you want to cancel all filtering settings you have created, open the pop-up window above the desired column again and click “Remove filter from column”. After this, the table will return to its original form.

How to calculate the amount in an Excel table

In the window list, select “Table” => “Total Row”:

A subtotal will appear at the bottom of the table. Left-click on the cell with the amount.

Select a principle from the drop-down menu subtotal: this can be the sum of the column values, “average”, “count”, “number of numbers”, “maximum”, “minimum”, etc.

How to fix a table header in Excel

The tables you have to work with are often large and contain dozens of rows. Scrolling down a table makes it difficult to navigate the data if the column headings are not visible. In Excel, you can attach a header to a table so that when you scroll through the data, you will see the column headings.

To fix headers, do the following: