You are viewing the article How to Add Numbers Automatically in Excel at Tnhelearning.edu.vn you can quickly access the necessary information in the table of contents of the article below.
X
This article is co-authored by a team of editors and trained researchers who confirm the accuracy and completeness of the article.
The wikiHow Content Management team carefully monitors the work of editors to ensure that every article is up to a high standard of quality.
This article has been viewed 4,031 times.
There are two different ways to add numbers to columns automatically. The surest way to add automatic numbers to Excel is to use the ROW function. This method will ensure that the cells display the correct sequence number even when rows are added or removed. The second way (without typing the formula) is to use the Fill feature, this method is easier but when you delete the line, the number sequence will be missing. Let’s learn how to automatically number rows, or fill in consecutive numbers in columns.
Steps
Automatic line numbering
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/90/Add-Autonumber-in-Excel-Step-1-Version-7.jpg/v4-728px-Add-Autonumber- in-Excel-Step-1-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/9/90/Add-Autonumber-in-Excel-Step-1- Version-7.jpg/v4-728px-Add-Autonumber-in-Excel-Step-1-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click on the first cell where the sequence of numbers will begin. This method instructs how to have each cell in the column display the corresponding row number. [1] X Research Source You should use this method if rows are frequently added or removed from the worksheet.
- To create a basic row of consecutive numbers (or other data like the day of the week or month of the year), see How to Fill a Column with Consecutive Numbers.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/1a/Add-Autonumber-in-Excel-Step-2-Version-7.jpg/v4-728px-Add-Autonumber- in-Excel-Step-2-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/1a/Add-Autonumber-in-Excel-Step-2- Version-7.jpg/v4-728px-Add-Autonumber-in-Excel-Step-2-Version-7.jpg”,”smallWidth”:460,”smallHeight”:344,”bigWidth”:728,”bigHeight” :545,”licensing”:”<div class=”mw-parser-output”></div>”}
Type =ROW(A1) in the cell (if this is cell A1). If it is another cell, you need to replace it accordingly.
- For example, if you are typing in cell B5, type =ROW(B5) .
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/b8/Add-Autonumber-in-Excel-Step-3-Version-5.jpg/v4-728px-Add-Autonumber- in-Excel-Step-3-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/b8/Add-Autonumber-in-Excel-Step-3- Version-5.jpg/v4-728px-Add-Autonumber-in-Excel-Step-3-Version-5.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Press ↵ Enter . The cell line number will appear. If you entered =ROW(A1) , cell A1 will display the number 1 . If you enter =ROW(B5) , cell B5 will show the number 5 . [2] X Research Source
- To start from 1 at any line where you want to start the range, count the number of lines above the current cell, then subtract the result in the function.
- For example, if you enter =ROW(B5) and want to display the number 1 in this cell, change the function to =ROW(B5)-4 , because B4 counts back 4 lines as B1. [3] X Research Sources
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/20/Add-Autonumber-in-Excel-Step-4-Version-7.jpg/v4-728px-Add-Autonumber- in-Excel-Step-4-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/20/Add-Autonumber-in-Excel-Step-4- Version-7.jpg/v4-728px-Add-Autonumber-in-Excel-Step-4-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Select the cell containing the first number in the sequence.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/62/Add-Autonumber-in-Excel-Step-5-Version-7.jpg/v4-728px-Add-Autonumber- in-Excel-Step-5-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/62/Add-Autonumber-in-Excel-Step-5- Version-7.jpg/v4-728px-Add-Autonumber-in-Excel-Step-5-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Hover the mouse pointer over the frame located in the lower right corner of the selected cell. This black note is called the Fill Handle. The mouse pointer will turn into a scissors icon when it is just above the Fill Handle.
- If you don’t find the Fill Handle, navigate to the File tab > Options > Advanced and check the “Enable fill handle and cell drag-and-drop” box. cell drag – drop feature)
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/65/Add-Autonumber-in-Excel-Step-6-Version-6.jpg/v4-728px-Add-Autonumber- in-Excel-Step-6-Version-6.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/65/Add-Autonumber-in-Excel-Step-6- Version-6.jpg/v4-728px-Add-Autonumber-in-Excel-Step-6-Version-6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Drag the Fill Handle down to the last cell in the range. Each cell in the column will display the corresponding row number.
- If you delete a row in this range, the cell number will be automatically corrected based on the new line number.
Enter consecutive numbers in the column
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/12/Add-Autonumber-in-Excel-Step-7-Version-4.jpg/v4-728px-Add-Autonumber- in-Excel-Step-7-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/12/Add-Autonumber-in-Excel-Step-7- Version-4.jpg/v4-728px-Add-Autonumber-in-Excel-Step-7-Version-4.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click on the first cell where the sequence of numbers will begin. This method will show you how to add consecutive numbers to the cells in the column.
- If you do this but then delete certain rows, you’ll need to go through the steps again to renumber the entire column. If you think you’ll be changing the data lines often, then the Automatic Line Numbering method above should be used.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/a/ab/Add-Autonumber-in-Excel-Step-8-Version-4.jpg/v4-728px-Add-Autonumber- in-Excel-Step-8-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/a/ab/Add-Autonumber-in-Excel-Step-8- Version-4.jpg/v4-728px-Add-Autonumber-in-Excel-Step-8-Version-4.jpg”,”smallWidth”:460,”smallHeight”:344,”bigWidth”:728,”bigHeight” :545,”licensing”:”<div class=”mw-parser-output”></div>”}
Enter the first number in the range into a cell. For example, if you are numbering from top to bottom in a column, type 1 in the first cell.
- You don’t have to start at 1. Your sequence can start from any ordinal number, or even a different format (such as even, multiples of 5, etc.) .
- Excel also supports other “numbering” styles, including dates, seasons, and days of the week. For example, when you enter the days of the week in the column, the first cell will display “Monday”.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/df/Add-Autonumber-in-Excel-Step-9-Version-3.jpg/v4-728px-Add-Autonumber- in-Excel-Step-9-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/df/Add-Autonumber-in-Excel-Step-9- Version-3.jpg/v4-728px-Add-Autonumber-in-Excel-Step-9-Version-3.jpg”,”smallWidth”:460,”smallHeight”:346,”bigWidth”:728,”bigHeight” :547,”licensing”:”<div class=”mw-parser-output”></div>”}
Click the next cell in the template. This is the cell directly below the current cell.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/0/0e/Add-Autonumber-in-Excel-Step-10-Version-4.jpg/v4-728px-Add-Autonumber- in-Excel-Step-10-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/0/0e/Add-Autonumber-in-Excel-Step-10- Version-4.jpg/v4-728px-Add-Autonumber-in-Excel-Step-10-Version-4.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Enter the second number of the sequence to create the pattern. To number consecutively (like 1, 2, 3, etc.), enter 2 here. [4] X Research Sources
- If you want the next numbers to be 10, 20, 30, 40, and so on, then the next two numbers in the sequence that you need to enter are 10 and 20 .
- If you are typing by day of the week, type the next day of the week in the box.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/18/Add-Autonumber-in-Excel-Step-11-Version-3.jpg/v4-728px-Add-Autonumber- in-Excel-Step-11-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/18/Add-Autonumber-in-Excel-Step-11- Version-3.jpg/v4-728px-Add-Autonumber-in-Excel-Step-11-Version-3.jpg”,”smallWidth”:460,”smallHeight”:344,”bigWidth”:728,”bigHeight” :544,”licensing”:”<div class=”mw-parser-output”></div>”}
Click and drag to select all cells. When you release the mouse button, the entered cells will be highlighted.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/22/Add-Autonumber-in-Excel-Step-12-Version-3.jpg/v4-728px-Add-Autonumber- in-Excel-Step-12-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/22/Add-Autonumber-in-Excel-Step-12- Version-3.jpg/v4-728px-Add-Autonumber-in-Excel-Step-12-Version-3.jpg”,”smallWidth”:460,”smallHeight”:344,”bigWidth”:728,”bigHeight” :545,”licensing”:”<div class=”mw-parser-output”></div>”}
Hover the mouse pointer over the small frame located in the lower right corner of the selected area. This frame is called the Fill Handle. The mouse pointer will turn into a scissors icon when it is just above the Fill Handle.
- If you can’t find the Fill Handle, go to the File > Options > Advanced tab and check the box for “Enable fill handle and cell drag-and-drop.”
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/c6/Add-Autonumber-in-Excel-Step-13-Version-2.jpg/v4-728px-Add-Autonumber- in-Excel-Step-13-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/c/c6/Add-Autonumber-in-Excel-Step-13- Version-2.jpg/v4-728px-Add-Autonumber-in-Excel-Step-13-Version-2.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click and drag the Fill Handle down to the last cell in the range of numbers. After you release the mouse button, the cells in the column will be numbered according to the pattern you set up in the first two cells.
Advice
- Microsoft offers a free online version of Excel as part of Microsoft Office Online.
- You can also open and edit spreadsheets on Google Sheets.
Warning
- Make sure that the “Alert before overwriting cells” option is checked in the Advanced tab of Excel Options. This will help prevent data entry errors and limit the need to recreate functions or other data.
X
This article is co-authored by a team of editors and trained researchers who confirm the accuracy and completeness of the article.
The wikiHow Content Management team carefully monitors the work of editors to ensure that every article is up to a high standard of quality.
This article has been viewed 4,031 times.
There are two different ways to add numbers to columns automatically. The surest way to add automatic numbers to Excel is to use the ROW function. This method will ensure that the cells display the correct sequence number even when rows are added or removed. The second way (without typing the formula) is to use the Fill feature, this method is easier but when you delete the line, the number sequence will be missing. Let’s learn how to automatically number rows, or fill in consecutive numbers in columns.
Thank you for reading this post How to Add Numbers Automatically in Excel at Tnhelearning.edu.vn You can comment, see more related articles below and hope to help you with interesting information.
Related Search: