Forum Discussion

SAIFON1090's avatar
SAIFON1090
Copper Contributor
Jul 15, 2024
Solved

Numbering Only Visible Rows in Excel

Hello everyone, I am looking to number only the visible rows in my Excel table using an Excel function, starting from "1" and leaving a blank string for the hidden rows. The numbering should be sequ...
  • djclements's avatar
    Jul 15, 2024

    SAIFON1090 If there's another column in your table that always contains data (e.g. "Name"), you could try the SUBTOTAL function as follows:

     

    =IF(SUBTOTAL(103,[@Name]),MAX(Table1[[#Headers],[Line Number]]:INDEX(Table1[[#All],[Line Number]],ROW()-ROW(Table1[#Headers])))+1,"")

     

    Adjust the table name and column names as needed.

     

    UPDATE: Alternative Methods

     

    =IF(SUBTOTAL(103,[@Name]),MAX(Table1[[#Headers],[Line Number]]:CellAbove)+1,"")

     

    ...where CellAbove is a relative named range. To set this up, start by selecting any cell in any row other than row 1. For example, select cell A2, then open Name Manager (Ctrl+F3) and define a New Name called CellAbove that refers to =!A1 (do not include the sheet name and do not use $ signs in the cell reference). In doing so, CellAbove can then be used anywhere in the workbook and will always refer to the cell immediately above the formula, even after inserting or deleting table rows.

     

    -OR-

     

    =IF(SUBTOTAL(103,[@Name]),SUBTOTAL(103,Table1[[#Headers],[Name]]:[@Name])-1,"")

     

    ... where this method relies solely on the content of a neighboring column.

     

    All of the above-mentioned formulas will return an empty string ("") for hidden rows, as per your instructions. However, If the value returned for hidden rows is not actually a concern, additional variants include:

     

    =SUBTOTAL(103,Table1[[#Headers],[Name]]:[@Name])-1

     

    -OR-

     

    =SUM(CellAbove,SUBTOTAL(103,[@Name]))

     

    See attached...

Resources