Excel Function: Generate Auto Serial Number in Excel

Problem: If you  want to generate auto Serial Number in Column A starting from A2 depending on having data from Column B from B2. If nothing is inserted in Column B, Column A will remain blank. How can you do it?

Solution: To generate an auto serial number in Column A starting from A2, depending on the presence of data in Column B from B2 onwards, you can use the following formula in Excel:

1. Using a Formula in Column A:
   1. Click on cell A2.
   2. Enter the following formula:

      excel
      =IF(B2<>"", ROW(B2)-1, "")
      

   3. Drag the fill handle down to cover the range you need in Column A.

This formula checks if there is any data in Column B. If there is, it will generate a serial number based on the row number minus 1. If there is no data, it will leave the cell blank.

2. Using VBA: If you prefer to use VBA to automate this process, follow these steps:
   1. Press Alt + F11 to open the VBA editor.
   2. Insert a new module by clicking Insert > Module.
   3. Copy and paste the following code into the module:

      vba
      Sub GenerateSerialNumbers()
          Dim i As Integer
          Dim lastRow As Long
          lastRow = Cells(Rows.Count, 2).End(xlUp).Row ' Find the last row with data in Column B
          
          For i = 2 To lastRow
              If Cells(i, 2).Value <> "" Then
                  Cells(i, 1).Value = i - 1
              Else
                  Cells(i, 1).Value = ""
              End If
          Next i
      End Sub
      

   4. Close the VBA editor.
   5. Press Alt + F8, select GenerateSerialNumbers, and click Run.

These methods will ensure that Column A generates serial numbers only when there is data in the corresponding cells in Column B. If Column B is empty, Column A will remain blank.