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 sequential and ignore the hidden rows. I would like this numbering to be done in the column titled "Line Number."

Thank you in advance for your help!

Formulas and Functions
  • djclements's avatar
    djclements
    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...

5 Replies

Sort By
  • djclements's avatar
    djclements
    Bronze Contributor
    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...

    • SAIFON1090's avatar
      SAIFON1090
      Copper Contributor
      Jul 22, 2024
      Thank you for your response

      I have several views in my Excel file, but I want the numbering of visible rows to be applied only to the current view. For all other hidden rows and all rows from other views that are not visible in my current view, I would like a blank string to be displayed.

      Here is the formula you provided:
      ```excel
      =IF(SUBTOTAL(103,[@Name]), MAX(Table1[[#Headers],[Line Number]]:INDEX(Table1[[#All],[Line Number]],ROW()-ROW(Table1[#Headers])))+1, "")
      ```
      However, it only works for the current view and does not fully meet my needs. Could you help me adjust this formula or find another solution so that:

      1. Visible rows in the current view are numbered.
      2. Hidden rows and rows from other views that are not visible in the current view display a blank string.

      Thank you in advance for your help!

      Best regards,
      • djclements's avatar
        djclements
        Bronze Contributor
        Jul 22, 2024

        SAIFON1090 Unfortunately I'm not familiar with the new Sheet View features as I am not using OneDrive or Sharepoint to store my files (required). From what I've read, its main purpose seems to be to save personalized sort and filter options, so multiple users collaborating on the same file are not affected by each other. This sounds very similar to the legacy Share Workbook feature of old, which allowed Filter settings to be included in each user's personal view.

         

        I could be wrong, but I suspect there would be no way of modifying the formula to work with anything other than the current view, because it's based on the visible/hidden rows the user is currently seeing (as a result of the filter they've applied or the rows they've manually hidden). It's possible you could base your numbering sequence on something other than visible rows (e.g. whatever criteria was used to filter the data in the primary view); however, if each view is sorted differently, the numbering order would not be consistent across all views.

         

        Since I am unable to test Sheet View options on my system, this is just my "best guess". Kind regards.

Resources