How to set a sequence number in Excel. Automatic line numbering in Excel
When working with tables, you often need to number columns. Of course, this can be done manually by separately entering the number for each column from the keyboard. If the table has a lot of columns, this will take a significant amount of time. Excel has special tools that allow you to quickly number. Let's figure out how they work.
Excel has a number of options for automatically numbering columns. Some of them are quite simple and understandable, others are more difficult to understand. Let's take a closer look at each of them in order to conclude which option is more productive to use in a particular case.
Method 1: Fill Marker
The most popular way to automatically number columns is by far using a fill marker.
You can also do it a little differently. Fill the first two cells of the added line with numbers "1" And "2". Select both cells. Place the cursor in the lower right corner of the rightmost one. With the mouse button held down, drag the fill marker to the end of the table, but this time on the key Ctrl no need to press. The result will be similar.
Although the first version of this method seems simpler, many users nevertheless prefer to use the second.
There is another option for using a fill marker.
After this, all cells of the selected range will be filled with numbers in order.
Method 2: Number using the Fill button on the ribbon
Another way to number columns in Microsoft Excel involves using a button "Fill in" on the tape.
Following this, the table columns will be numbered in order.
You don’t even have to select the entire row, but simply put a number in the first cell "1". Then call the progression settings window in the same way as described above. All parameters must match those we talked about earlier, except for the field "Limit value". It should indicate the number of columns in the table. Then click on the button "OK".
The filling will be completed. The last option is good for tables with a very large number of columns, since when using it you do not need to drag the cursor anywhere.
Method 3: COLUMN function
You can also number the columns using a special function called COLUMN.
After completing all the above steps, all columns of the table will be numbered in order.
As you can see, there are several ways to number columns in Excel. The most popular one is using a fill marker. In tables that are too wide, it makes sense to use a button "Fill in" with the transition to the progression settings. This method does not involve manipulating the cursor across the entire plane of the sheet. In addition, there is a specialized function COLUMN. But due to the complexity of use and abstruseness, this option is not popular even among advanced users. And this procedure takes more time than the usual use of a fill marker.
The developers of Word and Excel have provided several simple techniques for quickly and easily automatically numbering cells, even in a very large table. The website office.microsoft.com gives two main ways to number columns and rows in Excel - “Filling cells with a sequence of numbers” and “Using the ROW function” to number rows. There is a third way, a progressive one - using the “FILL” button
Method 1 for numbering cells in Excel (easy and fast)
“Filling cells with a sequence of numbers”
“Filling cells with a sequence of numbers” is a very simple and convenient technique for automatically numbering cells in Excel. This method is ideal for small tables. The entire numbering process comes down to entering the initial numbers of the numbering row in the first two cells and then simply dragging the selection marker to the required number of rows or columns.
To do this you need:
- Enter the initial value for a series of fill numbers in the first cell.
- Enter the subsequent value for a series of fill numbers in the second cell to define the fill pattern. For example, if you need to specify a sequence like 1, 2, 3, 4, 5..., you need to enter 1 and 2 in the first two cells. If you need a sequence like 2, 4, 6, 8..., you need to enter 2 and 4, etc.
- Select both cells into selection block
- Drag the selection marker in the desired direction, onto the required number of cells. This method works in any direction (up, down, left, right). Accordingly, numbering will occur in ascending or descending order.
Note
What is a selection handle and how to drag it
A selection marker is a selection of a cell or a series of cells using a rectangular area (the cursor). To drag a selection handle, you need to hover your mouse over the square in the lower right corner of the selected cell or row of cells. The mouse cursor changes from a white cross to a black one. It looks something like this:
By dragging the selection marker in different directions, you can create any cell numbering for any table in Excel:
Note
Dragging the marker is one of the disadvantages of this numbering method, since in large tables it is a very labor-intensive process.
Method 2 for numbering cells in Excel (very clever)
"Using the ROW function"
A method more suitable for automatically numbering column cells. Fundamentally, everything is almost the same as in the first case, with the only difference that instead of entering the first two numbers, to specify a sample of values for a numbering series, you need to enter a formula using the “ROW” function. To do this, place the cursor in the cell and enter in the formula bar =ROW(A1) and drag our favorite marker down to the required number of cells.
Method 3 for numbering cells in Excel (progressively)
"Using the PROGRESSION function"
The method is very good for large and very large tables, when carrying a marker is tedious.
In this case, you don't need to drag anything. You only need to indicate the starting number in the desired cell, call the “PROGRESSION” function and specify the filling direction (row or column), the filling step (by how much to add the value) and the limit value for filling (the number of future numbered cells). Below, the screenshots show the order of clicking the buttons in the Excel window to achieve the desired result.
- After filling the cells with a progression, you can add cell numbering by simply dragging the marker (method No. 1)
- Types of progressions (arithmetic and geometric) are described in the algebra textbook for the ninth grade.
Method 4 for numbering cells in Excel (very fast and progressive)
"Quick use of the PROGRESSION function"
By default, in Excel, the progression fill settings window is adjusted to the normal cell numbering order. This can be used to quickly number rows or columns.
To quickly number cells of a column of any size, do this:
- Enter the required starting numbering digit in the first cell, for example “1”, “101” or “19” (without quotes)
- Select the first cell in the block with the initial number (“1”, “101” or “19”), and below - the number of cells that need to be numbered.
- Using the above method, call up the “PROGRESSION” window
- Don't look at anything. Just click Ok!
- The result is numbering for the entire selected block of cells and in a given range.
How to quickly select a block of cells in Excel
Move the cursor while holding the Left Mouse Key (LMB).
How to quickly select a large block of cells in Excel
You need to place the cursor in the starting cell, enter the address-number of the final cell into the active cell window, and press the key combination Shift+Enter. The location of the active cell window is shown in the screenshot. When entering the address-number of the final cell, pay attention to the syntax (writing order).
Numbering table cells in Word (cheap and cheerful)
Numbering table cells in Word is much simpler. This is done using a button to create a numbered list. To do this you need:
- Select the required cells into a block
- Click the button to create a numbered list (1. 2. 3. …)
- As a result, we get a table with a block of cells (columns or rows, options), in which the cells will be numbered automatically when they are deleted or added.
Now, our block (column, row, table) works like a regular numbered list, which automatically expands or contracts by the number of list positions (rows or cells). When adding or deleting rows (cells) in such a table, their numbering will be performed automatically.
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. 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.
Alternatively, you can transfer the table from Excel to Word and do everything there. In Microsoft Word this is done much easier, using a numbered list. This approach is not quite the same, but it also allows us to solve the problem.
This is how you can number rows and columns in Excel. Each option for solving this problem is good and convenient in its own way, so choose which one you like best. Write in the comments whether this article helped you, and ask all your questions on the topic discussed.
Row numbering in Excel- what could be simpler? It would seem that this is how it should be, however, practice shows that even the majority of completely “confident users” know almost nothing about it. In this lesson we will talk about several tricks related to automatically assigning row numbers to your tables, and I think you will find many uses for your new knowledge.
Row numbering in Excel in a “semi-automatic” version
This is the method that users use most often. We open the table, left-click in the first cell of the “No.” column, and put a one there. Then, click in the cell below it and put a two there. And then it’s a matter of technique - just select both entered values, move the mouse cursor to the lower right corner of the selection, wait until the cursor changes its appearance to a small black cross, hold down the left mouse button, and then drag the selection to the very bottom of the table, once - and you're done!
We just take it and pull it!
The method is of course very simple, but it is not without its drawbacks. The main one is quite banal: if the table turns out to be larger than we thought, then for each new row we will have to manually increase the numbering, and as a result, draw frames around the table again. If we delete one of the rows in the table, then the numbering will have to be redone again - otherwise there will be spaces in the row numbers.
I deleted line No. 5 and... it turned out what happened.
Automatic row numbering in Excel
The “correct” way of numbering rows in a table does not have all the disadvantages described above (and some undescribed ones), but for some strange reason, it is not known to all MS Excel users. This is doubly strange - because once you have appreciated all the advantages of automatic numbering, you no longer have any desire to go back and work the old fashioned way. Let's compare and make sure?
It's time for REAL tables!
First of all, go to the "Insert" tab, And in the "Tables" group activate Table tool. The “Create Table” window will appear on the screen, where by default the range of data currently present on the sheet will be displayed. You can change the range as you wish.
The Excel table looked noticeably better, and we didn’t even have to adjust the styles.
We click “Ok” and see a wonderful transformation - our data range has turned into a real table. This mode has quite a lot of advantages, but one of the coolest is this:
If a formula is entered into one of the table cells, its value is automatically “stretched” to other cells of the same column.
In other words, if we enter a formula that allows us to count the rows in the table in the first cell of the “No.” column, the numbers of other rows will be automatically calculated! Just paste the following formula into the first cell of the “No.” column and see what happens:
=ROW()-ROW( Table_name)+1
As if by magic, automatic numbering filled all the cells of the “No.” column. Now let's repeat our recent experience and delete one of the table rows... oops, the row was deleted, but the numbering was not affected, and the formula automatically recalculated the remaining values of the “No.” column without disturbing the order.
Table with fully automatic numbering. You just need to insert the formula, and then Excel will work on its own.
Note: Where can I get the name of the table for the formula? You can always see it (and replace it with your own) on the “Design” tab, which will appear on the toolbar as soon as you select at least one of the table cells.
And here is the name of the table. Yes, this is what is inserted into the formula, and not the name of the sheet or the entire document.
If we write text in Excel, for example, as a description for a table or chart, sooner or later we are faced with the question: “How to correctly make a numbered list in Excel?” There are only two options to create a numbered list or a list with markers - inside a cell or 1 line = 1 cell. Numbering rows in Excel is easy
Quite often, a point-by-point description requires a list structure - this greatly simplifies the perception of the text. But if the option is one point, one line is not suitable, i.e. everything must fit into 1 cell (if, for example, the number of cells is limited).
Marker or number in a cell:
The simplest and apparently only way is to insert a marker or number by copying.
Select the text to make a beautiful list
Select the Insert tab - Text section - Symbol
Find the desired character, for example “ ”, copy this character into each line inside the cell. It should look like the title picture.
To quickly call up this particular symbol “ ” (the so-called marker), hold down Alt and type the symbol code on the number keyboard on the right - 0149.
To make a line break in one cell use
2. Numbered list on several lines. Row numbering in Excel
Standard Excel methods will help here.
How to number lines in Excel. Without formulas:
Enter the number 1 and 2 in adjacent list cells and hover over the square in the lower right border of the cell. Hold down the left mouse button and drag down
Automatic row numbering in Excel
To easily determine the row number in a cell, there is a function =ROW(). Accordingly, you can return the row number in the table. It is very comfortable
3. Bullet list on multiple lines
In order for a marker to be automatically placed in a cell, you need to make changes to the custom cell format.
Namely:
Select the required cells
Go to the Format Cell menu (Right click - Format Cells)
In the Number tab, find the item (all formats)
In the type field, enter a marker character, a space, and the dog sign @ (“@”) - now in all cells there will be a marker “ ” in front of the text.
It’s also quite convenient for formatting text in Excel.
In general, it is worth recognizing that working with text is more convenient and correct in Word. But Excel also has several convenient options for how to do this quickly.
Example from this article.
Share our article on your social networks:
