How to Automatically Number Rows in Excel 2010

When you are first creating a set of data in Microsoft Excel 2010, there is probably a lot of data entry that is occurring. While this is, by its very nature, a tedious activity, it can be made even more so if you are just typing slightly different values on each row. For example, “Product 1, Product 2, Product 3,” etc.

Not only is this annoying, but it can also lend itself to certain errors, depending upon your skills as a typist and the complexity of the word that you are repeatedly typing. That’s why it can be so useful to know how to automatically number values in a sequence in Excel 2010.

Excel 2010 includes a feature called Autofill, however, which you can exploit to automatically number rows. By simply typing two values from your sequence, you can call upon Autofill to complete the sequence, no matter how many values you require.

How to Do Automatic Numbering in Excel 2010

  1. Open your Excel file.
  2. Enter the first value into a cell in a column.
  3. Put the second value in the cell underneath it.
  4. Click and hold on the top cell, then drag down to select the second as well.
  5. Click and hold on the fill handle at the bottom-right of the selected cells.
  6. Drag down to select the cells that you wish to number automatically.
  7. Let go of your mouse button.

Our article continues below with additional information on numbering automatically in Excel, including pictures of these steps.

How to Automatically Number Rows in Excel 2010 (Guide with Pictures)

This is the perfect solution if you are inputting sequential values that follow a pattern. Simply enter the number of values necessary to establish the pattern, then activate Autofill to complete the number of values you require. However, Autofill cannot read minds. If there is no discernible pattern or sequence in the set of values that you have highlighted, Excel will not be able to automatically complete your values.

Begin by opening the Excel file that you want to automatically number.

Step 1: Enter the first and second values of your sequence into a column.

If your sequence requires more than two values to establish itself, enter the number of necessary values.

enter the first two values in your sequence

Step 2: Click on the top value, then drag your mouse to select the rest of the values that you just entered.

select both sequence values

Step 3: Click the fill handle at the bottom-right corner of the cell.

Your mouse pointer will switch to a + symbol when you have correctly positioned the mouse.

click the fill handle

Step 4: Drag the selection box down until the preview box indicates the last number of the sequence that you want to create, then release the mouse button.

For example, I want to populate cells in this column until the sequence displays the value Product 10.

how to automatically number rows in excel 2010

You can extend your sequence by simply clicking the last few values and repeating this process. If Excel cannot determine your sequence, it will simply start repeating your selection in the cells following your selection.

How to Use the Row Function for Automatic Numbering in Excel 2010

Another option available to you involves a formula that looks like this:


If you replace the “XX” part of that formula with a cell number then Excel will display that number in the cell containing the formula. This is a handy way to number rows, as you can then copy and paste that formula into other cells and the number will update automatically with the relative row number.

You can also take advantage of this functionality to incorporate the Row formula into other formulas and use that data as part of larger or more complex numbering systems.

While using the row function as a means to perform auto numbering in Excel can be a superior alternative in some instances, if you want to fill rows with simple sequential serial numbers then it’s worthwhile to try the fill handle method first.

Why Would I Want to Use Auto Numbering in Excel?

Have you ever manually typed a large series of numbers into cells in a column? While fast typists that don’t make a lot of errors may be able to fill row numbers on their own by typing them into a column in Excel, many of us can make some typing mistakes.

I find it much simpler and more accurate to type my first value into the first cell in a column, for example, cell A1, then type a second number into cell A2. Then I simply make some movements and selections with my mouse and I can let Excel automatically fill the rest of the cells that I want to number. As long as my first two entries were correct then the number in cell A3 should be correct, as well as everything else that I selected.

How to Turn the Fill Handle On or Off in Excel 2010

My favorite way to number in Excel is with the fill handle, which we mentioned above is found at the bottom-right corner of a selected cell.

But if that fill handle isn’t there, or if you would like to hide it, then you have the ability to do so.

  1. Open Excel.
  2. Click the File tab.
  3. Select the Options button.
  4. Choose the Advanced tab.
  5. Check the box next to Enable fill handle and cell drag-and-drop to turn the option on or off.

Note that this setting applies to the Excel application, not just the current spreadsheet. If you need to use the fill handle again for a different spreadsheet then you will need to re-enable or disable this option again.

More Information on How to Automatically Number in Excel

The Excel Options window that opens when you click the Options button at the bottom-left of the File menu includes a lot of other ways to customize your Excel experience. For example, you could change the default file type for saving spreadsheets, you could change the default font, you can set a different option for how calculations are performed, and more.

While this article focuses primarily on automatically numbering rows in Excel 2010, you can use a similar method to number columns as well.

This method of using the fill handle to number Excel rows or columns will also work in other versions of Microsoft Excel, such as Excel 2007, Excel 2013, or Excel for Office 365.

If you are looking for some additional ways to fill series in Excel, then you may want to try an Excel table. By selecting a range of cells and converting it into a table via the Insert tab you gain some additional options for sorting numbers in Excel.

If you click and drag the fill handle either down or to the right, then Excel will increase its numbering. If you drag up or to the left then Excel will decrease its numbering.

Join Our Free Newsletter

Featured guides and deals

You may opt out at any time. Read our Privacy Policy

Leave a Comment