If it’s blank, the IF formula would return a blank and if it isn’t, it will count all the filled rows in the specified range and give that number. In the above formula, the IF function checks whether the adjacent cell is blank or not (using the ISBLANK function). The below formula can do this: =IF(ISBLANK(B2),"",COUNTA($B$2:B2)) Suppose you have a dataset as shown below and you only want to assign a row number (serial number) to a row if that row is filled. The COUNTA function will count the number of non-empty cells adjacent cells and return a serial number only when the row is filled (unlike the ROW function method, which would still give a serial number if the row is empty). Using the COUNTA FunctionĪnother way you can number rows in Google Sheets is by using the COUNTA function. The good thing about using the ROW function is that in case you delete a row from this dataset, the function would adjust to give you the correct row number. In case there is no text, it returns a blank, else it returns the row number. The above uses an IF function to check whether there is any text in the adjacent cell or not. As soon as you add a new record, the number would automatically appear. You can use this formula on an expanded number of cells in the column (even if there are no records in it for now). In case you only want to show the serial number for the records that are filled, you can use the below formula: = IF ( B2 "", ROW ( ) - 1, "" ) In case you’re starting from the first row, you can simply use the ROW function.
The above formula uses the ROW function to get the current row number and then subtracts 1 from it as we are starting from the second row onwards.
You can then format this column to make it look better. The above steps would give you serial numbers in the additional column you inserted. Double-click using the mouse (or left-click and drag till you want the numbers).The cursor would change into a plus icon (this is called the Fill Handle) Place the cursor at the bottom-right corner of the selection.Give the new column a heading and format it like other columns.To do this, right-click on any cell in column A and select ‘Insert Column’ Insert a column to the left the Name column.Suppose you have a dataset as shown below and you want to add serial numbers in column A.īelow are the steps to use the ‘Fill handle’ to fill down the serial numbers:
If you have a dataset without any blank rows, this is the easiest way to add serial numbers. The method you choose to numbers rows will depend on how your data is structured or how you want this to work.įor example, if there are blank rows in between and you only want to add a serial number to records that are not blank, you can not use all the methods (example covered later in this tutorial). In this tutorial, I will show you four ways to add serial numbers in Google Sheets:
0 kommentar(er)
