7 Quick & Easy Ways to Number Rows in Excel – Trump Excel

Watch Video – 7 Quick and Easy Ways to Number Rows in Excel When working with Excel, there are some small tasks that need to be done quite much. Knowing the ‘ right way ’ can save you a great deal of time. One such childlike ( even much needed ) tax is to number the rows of a dataset in Excel ( besides called the serial numbers in a dataset ).

How to Number Rows in Excel - Example dataset nowadays if you ’ re thinking that one of the ways is to plainly enter these serial number manually, well – you ’ re right ! But that ’ s not the best way to do it. Imagine having hundreds or thousands of rows for which you need to enter the row count. It would be boring – and completely unnecessary. There are many ways to number rows in Excel, and in this tutorial, I am going to plowshare some of the ways that I recommend and often use. Of course, there would be more, and I will be waiting – with a coffee – in the comments area to hear from you about it.

How to Number Rows in Excel

The best way to phone number the rows in Excel would depend on the kind of data set that you have. For model, you may have a continuous datum set that starts from row 1, or a dataset that start from a different quarrel. Or, you might have a dataset that has a few space rows in it, and you only want to number the rows that are filled. You can choose any one of the methods that work based on your dataset.

1] Using Fill Handle

Fill handle identifies a convention from a few satiate cells and can easily be used to cursorily fill the stallion column. Suppose you have a dataset as shown below : How to Number Rows in Excel - Dataset here are the steps to quickly act the rows using the meet handle :

  • Enter 1 in cell A2 and 2 in cell A3.
  • Select both the cells (A2 and A3).
  • Note that there would be a small square at the bottom-right of the selection.How to Number Rows in Excel - Fill Handle square
  • Hover the cursor over this square, and you will notice that the cursor changes to a plus icon.How to Number Rows in Excel - cursor changes to plus sign
  • Double-click on the fill handle square (while the cursor is in the plus icon form) and it will automatically fill all the cells until the end of the dataset.

note that Fill Handle automatically identifies the traffic pattern and fill the remaining cells with that pattern. In this shell, the radiation pattern was that the numbers were getting incrementing by 1. In case you have a blank rowing in the dataset, fill handle would only work till the final conterminous non-blank row. besides, eminence that in case you don ’ t have data in the adjacent column, double-clicking the occupy handle would not work. You can, however, place the cursor on the meet handle, hold the right mouse samara and drag down. It will fill the cells covered by the cursor scuff.

2] Using Fill Series

While Fill Handle is a quick direction to number rows in Excel, Fill Series gives you a lot more master over how the numbers are entered. Suppose you have a dataset as shown below : How to Number Rows in Excel - Dataset here are the steps to use Fill Series to number rows in excel :

  • Enter 1 in cell A2.
  • Go to the Home tab.Home Tab in the ribbon
  • In the Editing Group, click on the Fill drop-down.Fill Series Option in the editing group
  • From the drop-down, select ‘Series..’.Fill Series option to number rows Columns in Excel
  • In the ‘Series’ dialog box, select ‘Columns’ in the ‘Series in’ options.Number rows in Excel - selected columns in Fill series
  • Specify the Stop value. In this case, since we have 26 records, we can enter 26. If you don’t enter any value, Fill Series will not work.Specify the Stop Value in Fill Series
  • Click OK.

This will immediately number the rows from 1 to 26. Using ‘ Fill Series ’ can be utilitarian when you ’ re starting by entering the row numbers. Unlike Fill Handle, it doesn ’ deoxythymidine monophosphate require the adjacent column to be filled already. even if you have nothing on the worksheet, Fill Series would still work. note : In sheath you have lacuna rows in the center of the dataset, Fill Series would still fill the count for that rowing.

3] Using the ROW Function

You can besides use Excel functions to number the rows in Excel. In the Fill Handle and Fill Series methods above, the serial phone number inserted is a static measure. This means that if you move the course ( or cut and paste it somewhere else in the dataset ), the row total will not change accordingly. This defect can be tackled using formulas in Excel. You can use the ROW function to get the row number in Excel. To get the row numbering using the ROW function, enter the play along convention in the inaugural cell and copy for all the other cells :

=ROW()-1

The ROW ( ) function gives the row number of the stream quarrel. so I have subtracted 1 from it as I started from the irregular row onwards. If your data starts from the 5th row, you need to use the convention =ROW ( ) -4. ROW formula to enter row numbers in Excel The best depart about using the ROW function is that it will not screw up the numberings if you delete a row in your dataset. Since the ROW function is not referencing any cell, it will mechanically ( or should I say AutoMagically ) adjust to give you the correct row number. Something as shown below : Automatically number rows in Excel when a row is deleted note that equally soon as I delete a row, the row numbers mechanically update. again, this would not take into account any blank records in the dataset. In case you have blank rows, it will silent show the row number. You can use the following convention to hide the row number for blank rows, but it would calm not adjust the row numbers ( such that the adjacent row count is assigned to the future satiate rowing ).

IF(ISBLANK(B2),"",ROW()-1)

4] Using the COUNTA Function

If you want to number rows in a way that only the ones that are filled get a serial number, then this method acting is the way to go. It uses the COUNTA routine that counts the number of cells in a roll that are not empty. Suppose you have a dataset as shown below : Number rows in Excel - Using COUNTA function to insert serial numbers bill that there are blank rows in the above-shown dataset. here is the formula that will number the rows without numbering the blank rows.

=IF(ISBLANK(B2),"",COUNTA($B$2:B2))

The IF serve checks whether the adjacent cell in column B is empty or not. If it ’ south vacate, it returns a blank, but if it ’ s not, it returns the count of all the fill cells till that cell.

5] Using SUBTOTAL For Filtered Data

sometimes, you may have a huge dataset, where you want to filter the data and then copy and spread the filtered data into a disjoined sheet. If you use any of the methods shown above sol far, you will notice that the row numbers remain the same. This means that when you copy the percolate data, you will have to update the course enumeration. In such cases, the SUBTOTAL serve can automatically update the quarrel numbers. even when you filter the datum set, the quarrel numbers will remain intact. Let me show you precisely how it works with an exemplar. Suppose you have a dataset as shown below : Data set for SUBTOTAL functon - insert serial numbers in Excel If I filter this data based on Product A sales, you will get something as shown below : Filtered Data row numbers note that the series numbers in Column A are besides filtered. so now, you entirely see the numbers for the rows that are visible. While this is the have a bun in the oven behavior, in case you want to get a serial row numbering – so that you can plainly copy and paste this datum somewhere else – you can use the SUBTOTAL serve. here is the SUBTOTAL routine that will make certain that evening the filtered datum has continuous row count.

 =SUBTOTAL(3,$B$2:B2)

The 3 in the SUBTOTAL function specifies using the COUNTA serve. The second argument is the stove on which COUNTA function is applied. The benefit of the SUBTOTAL function is that it dynamically updates when you filter the data ( as shown below ) : Subtotal automatically numbers rows in Excel when filtered note that even when the datum is filtered, the row numbering update and remains continuous.

6] Creating an Excel Table

Excel Table is a great creature that you must use when working with tabular data. It makes wangle and using data a lot easier. This is besides my favored method acting among all the techniques shown in this tutorial. Let me first show you the proper way to count the rows using an Excel board :

  • Select the entire dataset.
  • Go to the Insert Tab.insert Tab in the ribbon
  • Click on the Table icon (you can also use the keyboard shortcut Control + T).create a Table from the Ribbon Icon
  • In the Create Table dialog box, make sure the range is correct.Check Table Range in the dialog box
  • Click OK. This will convert your tabular data into an Excel Table.
  • In cell A2, enter the following formula. Note that as soon as you enter the formula, it will automatically fill it in all the cells in that column (you can read more about calculated columns here).
     =ROW()-ROW(Table2[#Headers])

Number Rows in Excel - Calculated Column in Excel table note that in the formula above, I have used Table2, as that is the name of my Excel table. You can replace Table2 with the name of the mesa you have. There are some add benefits of using an Excel Table while numbering rows in excel :

  1. Since Excel Table automatically inserts the formula in the entire column, it works when you insert a new row in the Table. This means that when you insert/delete rows in an Excel Table, the row numbering would automatically update (as shown below).Inserting Row Numbers in an Excel Table
  2. If you add more rows to the data, Excel Table would automatically expand to include this data as a part of the table. And since the formulas automatically update in the calculated columns, it would insert the row number for the newly inserted row (as shown below).Excel Table - Automatically Add row Number on adding a row

7] Adding 1 to the Previous Row Number

This is a simple method acting that works. The theme is to add 1 to the former row number ( the number in the cell above ). This will make certain that subsequent rows get a phone number that is incremented by 1. Suppose you have a dataset as shown below : How to Number Rows in Excel - Dataset here are the steps to enter rowing numbers using this method :

  • In the cell in the first row, enter 1 manually. In this case, it’s in cell A2.
  • In cell A3, enter the formula, =A2+1
  • Copy and paste the formula for all the cells in the column.

Add 1 to insert row numbers in Excel The above steps would enter serial numbers in all the cells in the column. In case there are any blank rows, this would calm insert the course number for it. besides note that in case you insert a new row, the quarrel number would not update. In case you delete a rowing, all the cells below the edit row would show a mention erroneousness.

These are some quick ways you can use to insert serial numbers in tabular data in Excel. In case you are using any other method, do share it with me in the comments department. You May Also Like the Following Excel Tutorials:

source : https://enrolldetroit.org
Category : Knowledge

Trả lời

Email của bạn sẽ không được hiển thị công khai.