Continue numbering in excel. How to make continuous row numbering in excel

Autocomplete- a function that allows you not to enter duplicate data arrays (simply copy them in columns and rows) or data that can be filled in using a certain algorithm (for example, numbering rows and columns). You can simply stretch the cell down and fill it with the same data automatically.

Using autofill you can do the following:

• number lines automatically
• quickly copy duplicate data as you type
• copy formulas

How to number rows in an Excel table automatically?

To number lines you need:

• enter data in the first two cells so that Excel understands which algorithm should be used for numbering
• select these cells
• hover the cursor over the lower right corner of the selected range so that the cursor looks like a cross, hold and drag down with the left mouse button, as you drag you will see the numbers that Excel shows as a preview of the resulting numbering

Or you can simply enter the number 1, move the cursor over the lower right corner so that a cross sign appears, hold down and drag down with the left mouse button while holding down the Ctrl key. If the key is not held down, the number 1 will be copied.

Copying formulas into Excel

Autofill is also often used to copy formulas; it is very convenient; the formula is entered once for each column and is simply “stretched” to the remaining rows. When copying you need to take into account, i.e. where the data will change, and where the value should remain the same for all resulting formulas.

In order to copy a formula in Excel using autocomplete, you must:

• enter the formula in the first cell of the column
• select cell with formula
• hover the cursor over the lower right corner of the cell so that the + sign appears, hold down the left mouse button and drag down (or in the desired direction)

Formulas can also be copied from one cell to another, for example, to another sheet or table; to do this, just select the cell with the formula and copy it, and when pasting it into the desired place, select the option in the context menu - paste special - paste formula.

Autofill is also convenient for:

• copying the same data into cells (for example, assigning all employees the same city of residence or the same positions, if you need to enter them in a row)
• use for entering standard or independently entered lists (for example, enter “Monday” or “January” in the first cell and drag to get a complete list; such lists can be created independently in the parameters)

To copy data, simply select the cell with the desired value, move the mouse cursor over the lower right corner until the + sign appears, hold down the left button and drag down.

To use standard lists (months and days of the week), enter the first element and drag it as well.

To create a custom list (for example, employee names), click the Office button in the upper left corner of the screen, go to Excel Options, and under General, select Edit Lists. There are two ways to create a list:

1. Manual entry in the creation window
2. Import from an existing plate

Every user who regularly works in Microsoft Excel has been faced with the task of numbering rows and columns. Fortunately, Microsoft developers have implemented several tools for quick and easy numbering in tables of any size. In this article we will take a closer look at how to number rows or columns in Excel. Let's figure it out. Let's go!

For quick data analysis, table elements must be numbered

There are three ways to solve this problem. The first is to fill cells with a sequence of numbers, the second is to use the “string” function, and the third is based on working with the “Fill” button. Below we will consider each of the methods in more detail.

The first approach is very simple and ideal for working with small objects. This method works as follows:

• In the first field, you must specify the initial numeric value from which filling will continue (for example, “1”);
• In the second you need to set the following value (for example, “2”);
• Select both areas;
• Now, using a selection marker, mark the desired area of ​​the table in which you need to number.

This approach is very simple and convenient, but only for small tables, since when working with huge amounts of data it will take a lot of time and effort.

The second method is similar in principle to the first, the only difference is that the first numbers in the series are entered not in the cells themselves, but in the formula field. In this field you need to enter the following: =ROW(A1)

Next, as in the first option, simply drag the selection marker down. Depending on which cell you start from, instead of “A1” indicate the desired one. In general, this approach does not provide any advantages over the first, so do what is most convenient for you.

The third method is perfect for working with large tables, since here you don’t have to drag the selection marker across the entire page. It all starts the same way as in the previous versions. Select the desired area and specify the first number in it from which filling will continue. After that, on the “Home” tab, in the “Cells” toolbar block, click on the “Fill” icon. In the list that appears, select “Progression”. Next, set the direction (columns or rows), step and number of cells that will be numbered; in the “Type” section, mark the “Arithmetic” item with a dot. Once all the options are set, click OK and Excel will do everything for you. This approach is the most preferable, as it allows you to solve the problem, regardless of the amount of data you are working with.

There is also a faster way to use Fill. To begin, enter the number from which numbering will continue, similar to the first two methods. After that, select the remaining areas in which you want to put numbers. Now click the “Fill” button and click on the “Progression” item in the list. There is no need to change anything in the window that opens. Just click "OK" and you'll be done.

Quite often, users ask the question: how to number rows in Excel? This is what we will look at in this article.

There are several ways in Excel to help you quickly arrange numbers. Using the program, you can do everything automatically, or arrange the numbers yourself. Even despite the name of the second method, both of them will greatly facilitate your work with tables: you will not need to enter numbers in order in each row separately.

Manually

First, let's manually number the lines. To do this, select the cell that should be the first and write “1” in it, in the cell below we write “2”. Select them with the mouse. Now move the cursor to the lower right corner of the selected blocks - an autofill marker will appear in the form of a black plus sign. Hold down the left mouse button and drag it down the required amount. So we numbered the rows using a simple sequence of numbers.

Automatically

It can be done in various ways.

Using progression

Select the desired cell on the sheet and put in it the number from which the count will begin, for example, “1”. On the “Home” tab, click on the “Fill” button, and select “Progression” from the list.

In the next dialog box, put a marker in the “by columns” field, select the desired “Step” and "Limit value". Then click OK.

If you put “2” in the “Step” field, then the cells will be arranged in order: 1, 3, 5, 7. This can be useful when you need to number only odd or even numbers in the table. In the second case, initially put the number “2” and select “Step” - “2”, then you get: 2, 4, 6, 8.

As a result, the required rows or columns will be numbered.

Using the function

Select the desired block and write the following: =ROW(B3), where B3 is the address of the current cell.

Now move the cursor to the lower right corner of the block, it will look like a black plus sign. Then press the left mouse button and drag down to do automatic numbering.

If in the block from which the numbering should begin, for the “ROW” function you specify the current address as an argument - B3, or leave the brackets empty - “ROW()”, then the function will return the row number in the cell in which it was entered. In the example, the line number is “3”, so the count starts from three.

In order for the row numbering in Excel to start from one, count the number of rows that are above the one you need. In the example there are two of them. Then select the desired cell and write the function: =ROW()-2 . Here “-2” is the amount you calculated.

Now block B3 contains “1”. Pull the number down as far as needed.

This method is also convenient because it is fully automated. If you need to add or delete a row from an Excel sheet, the calculation in the table will not be lost.

For example, let’s delete the fifth row of the sheet; in the table it is numbered “3”. The "vvvvv" text was removed and the rest was moved up one row. At the same time, the counting of rows in the table was not disrupted.

Now, you can definitely number the rows in the desired order in Excel. To do this, you just need to use the manual or automatic method described in the article.

The Excel spreadsheet editor is present in the standard Microsoft Office package and is widely used for creating tables, constructing charts, carrying out calculations, calculating functions, and storing data. The main purpose of this program is to automate calculations and process data sets.

Experienced Excel users know many tricks that allow them to perform fairly mundane tasks with a few clicks. In particular, this applies to the cell numbering process. A beginner can spend a lot of time writing each number by hand. It's good if the table is small. What if you need to number several thousand lines? In this case, the need to automate the process is not even questioned.

So, you need to number the cells in a row or column. You can do this in several ways. You can choose the one you like the most.

Filling cells with a sequence of numbers

This method is convenient to use if the table is small.

To number rows in Excel 2010 using this method, you need to follow these steps:

• enter the initial value in the first cell;

Photo: Numbering cells in the vertical direction

• the following value should be entered in the second cell;

Photo: Numbering cells in the horizontal direction

• Next you need to select both cells.

The selection marker should be dragged to the required number of cells (to do this, the square at the bottom of the selected area must be moved in the desired direction).

Please note: if you enter the number 2 in the first cell, and the number 4 in the second, then the numbering will be done in geometric progression: 2,4,6,8,10... If you move the selection marker from bottom to top or from right to left, then numbering will be in the negative direction.

Photo: Line numbering in a negative direction

This method is not suitable for large tables, since dragging the cursor will be quite inconvenient. If you need to number a large number of lines, use the alternative methods below.

Line numbering

Microsoft Excel allows you to perform numbering using two functions. The term “function” itself may scare a person who is far from mathematical sciences, but this method does not carry anything overly complicated. All you need to do is follow a few simple steps.

Video: Perspective line numbering

Function "ROW"

This method involves entering a formula using a function called "String":

Photo: Selecting the required number of cells

Progression function with limit value indication

This method is advisable to use if the table is large and dragging the cursor is inconvenient.

Please note: When the cells are filled, further numbering can be done by dragging the cursor (similar to how described in the first method).

Progression function with highlighting a range of cells

This method is similar to the one described above, but it involves using the default values ​​of the Progression function. These values ​​are set to the usual, most common numbering order.

Photo: Line numbering using progression

So, if you need to number the cells in a column from 1 to 10, you would do the following:

1. indicate the initial value in the cell (in our case it is 1);
2. select an area with the number of cells that need to be numbered (in our case, 10);
3. Call the “Progression” function and press the “OK” button.

Now you know how to number rows in Excel 2010 in four ways. Perhaps in the future the developers will offer several more options for solving this frequently encountered problem.

The more ways you know to automate various operations in Excel, the more time you can save when working with data. You will free yourself from routine actions, be able to solve problems faster and increase your work efficiency.

Often, when working in Excel, especially when creating a large database, the user needs to number all the lines to make it easier to visually search for the one they need. The program has special tools for this, which will be discussed now. To be more precise, in the article we will talk about how to automatically number lines in Excel in three different ways.

First method: stretching method

The first method presented, how to number lines in Excel, is rightfully considered the simplest. This is what is used most often. Let's get straight to how to do this.

In the table, you need to number the first two rows, for example, by entering the numbers 1 and 2 in them.

Then you need to select these two cells by holding the left mouse button on the first and dragging the cursor to the second.

Now you need to click on the lower right corner of the selection; it is usually displayed as a special square.

By holding down the left mouse button, all that remains is to drag the selection to the cell in which the numbering should end.

By releasing the left mouse button, you will see that all lines are numbered. This method of how to number lines in Excel is very simple and will not cause any difficulties for anyone, but it is not very suitable in cases where you need to number more than a hundred lines, because dragging them all out with the mouse will be quite long and inconvenient.

Second way: using a function

In this method, we will use the function, as you can guess from the name of the subtitle. An inexperienced user often bypasses them, since it seems to him that using the functions requires a lot of knowledge, but this is not at all the case. Moreover, using simple instructions, you will definitely be able to number lines in Excel the first time.

So, speaking of how to number deadlines in Excel using a function, you need to do the following:

Place the cursor in the cell where line numbering should begin.

Double click on the cell.

Enter the following expression: "=ROW(A1)".

Press ENTER.

Now, as in the previous method, you need to move the cursor to the lower right corner of the cell selection and, holding down the left mouse button, drag the cursor to the cell in which the list should end.

As a result, you will get numbered lines. However, it is easy to see that this method is not much different from the first one, the difference is that you need to select not two cells, but only one, but you need to enter the formula itself longer. In some situations, this method may still be useful, so you should not deprive it of attention.

Third method: using progression

It was said earlier that methods with stretching the selection are good only in cases where you need to number a few rows, and they are not suitable for long tables. So, now we will look at how to number lines in Excel using progression.

So, for this you will need:

Select the first cell and enter the number 1 there.

Click on the "Fill" button, which is located on the toolbar in the "Home" tab.

From the list that appears, select "Progression".

In the window that appears, you need to set all the necessary parameters. Set the location to “columns”, select the type “arithmetic”, step – “1”, and specify the limit value equal to the rows that need to be numbered.

Click OK.

After this, the lines you specified will be numbered. As you can see, this method works best in cases where you need to enter line numbers into a huge number of cells.

Conclusion

Now you know all three ways to number lines in Excel. The proposed methods differ from each other to some extent, and this is undoubtedly good, because there is plenty to choose from.