How to number columns in Excel. 5 Ways to Number Columns in Excel

For more convenient orientation within an Excel spreadsheet, you can use column numbering. In some documents, such as scientific papers, column numbering may be required according to the terms of reference. If you fill in the numbers manually, then it will take a lot of time, and there is also a risk of making a mistake somewhere. It is best to use the toolkit built into Excel for this task.

Numbering columns in an Excel spreadsheet

Entering numbers manually for each column is inefficient, especially when it comes to large tables. For convenient numbering of columns in Excel, several system tools are provided. So that you can evaluate the effectiveness of a particular tool, consider several ways to fill in the numbering of columns.

Autocomplete marker

This method is the most popular with most users, as it directly uses the built-in Excel autocomplete tool.

1. In the existing table, you need to add a new row. Select any cell in the row that will be placed under the row for numbering.
2. Right click on it. A context menu will appear, where the “Insert” item is selected.

3. The Add Cell Wizard opens. Put a marker next to the “Line” item and click “OK” to add it.
4. A new blank line will appear, which will be used for numbering. In the cell where you plan to start numbering, enter the first digit. Enter the next number in the next cell.
5. Select both cells and drag the selection to trigger autofill. As a result, you will get numbered columns.

There is also an alternative to this instruction, which shortens one step:

1. Follow the first three steps from the previous instruction to get a finished line for numbering.
2. In the cell where you plan to start numbering, enter the first digit.
3. Select it and, holding down the “Ctrl” key, drag it to adjacent cells, numbering it. The necessary numbers will be substituted by the program automatically.

There is a second alternative way:

1. Create a column with cells for numbering by analogy with the first steps of the previous instruction.
2. Enter in the first cell the first digit with which the numbering should begin. Then select it and fill with the help of the autocomplete function adjacent ones, stretching a selection on them.
3. All cells that fall under the selection will be “1” (or another number that you entered). Select all these cells and click on the more actions icon that will appear in the lower right part of the frame.
4. Select “Fill” from the context menu. After that, the program will automatically adjust the values ​​in the selected cells.

Fill button

This method involves the use of tools that are located at the top of the program interface in the “Home” tab.

1. Add an additional line for numbering, if required. How to do this was written above.
2. Insert the first digit of the numbering. Select the entire area to be numbered, including the first number.
3. At the top of the interface, pay attention to the “Editing” toolbox. Click on the “Fill” toolbar. A context menu will appear in which you need to select “Progression”.
4. The progression settings window will open. Fill it out as follows:
   1. “Location” – a marker on “By lines”.
   2. “Type” – a marker on the “Arithmetic” position.
   3. Line “Step” – write the number 1.
   4. We leave the filling of the remaining blocks and lines by default.

5. Click “OK” to apply the settings.

This instruction has an alternative that can be used in cases where the exact number of columns to be numbered is known:

1. In the first cell that will be used for numbering, write the number “1” or another number from which the countdown will begin.
2. Launch the arithmetic progression settings window from the top toolbar.
3. Here, the settings can be left by default, but only in the “Limit value” line specify the number of working columns in your table. Click OK. You will get the same result as in the previous instruction.

Column function

You can also perform numbering using ready-made function templates embedded in the program.

1. Select the leftmost cell in the column you plan to use for numbering. Put in it the sign “=” (without quotes).
2. In the function bar, click on the symbol similar to “f”.
3. The function selection window will open. In the “Category” field, set the value to “Full alphabetical list”.
4. In the “Select a function” field, find the “Column” function and click on it. Click the OK button to insert the function.

5. A window with argument settings will open. In the “Reference” field, enter the number of any cell related to the first column of the current table. You can enter it manually or simply click on the desired cell. The number will be inserted automatically.

Attention! In the “Reference” field, you must always enter the cell of the first column, even if your worksheet does not start with it, or if it already has some values.

6. In the first cell of the column for numbering, enter the number from which the numbering will begin.
7. Select the cell with this number and drag the selection to the rest for automatic filling.

Conclusion

There are several ways to number columns in Excel, although each is based on the AutoFill tool. The first method is suitable for small plates, but the Fill tool, which is discussed in the second method, is more convenient to use on large ones. Due to the variety of methods, each user will be able to choose the most suitable and comfortable for him.