SOLVED

# Numbering Only Visible Rows in Excel

Copper Contributor

# 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."

5 Replies

# Re: Numbering Only Visible Rows in Excel

As variant

Enter formula in D4 and drag it down

best response confirmed by SAIFON1090 (Copper Contributor)
Solution

# Re: Numbering Only Visible Rows in Excel

@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...

Okay

# Re: Numbering Only Visible Rows in Excel

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
```
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.

Best regards,

# Re: Numbering Only Visible Rows in Excel

@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.

1 best response

Accepted Solutions
best response confirmed by SAIFON1090 (Copper Contributor)
Solution

# Re: Numbering Only Visible Rows in Excel

@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...