xtraasebo.blogg.se

Usually, I add a dummy row at the end of the list, with just the SUBTOTAL function, or other dummy data. So, if you want your last row hidden when using the SUBTOTAL function in a filtered list, you can use one of the following workarounds. If there’s a SUBTOTAL function in the last row of that database, Excel decides that it’s a special row for the list’s totals, and it’s not included in the named range. Using Jan Karel Pieterse’s Name Manager utility, I can see the definition for Orders!_FilterDatabase.Įven though the list ends in row 9, the named range stops at row 8: =Orders!$B$1:$H$8 When you use an AutoFilter, Excel creates a hidden named range for the database. Also, its row number isn’t blue, like the row numbers for rows 2, 3, 5 and 8.

Can you spot the problem?Īlthough the list is filtered for Paper or Staplers, the File Folders record in row 9 is also visible. In the screenshot below, the list is filtered for the products Paper or Staplers, as you can see in the AutoFilter tooltip. The SUBTOTAL function works well, and renumbers our rows as expected, but there’s something wrong. Now, when you apply an Excel AutoFilter, or show all the records, your record numbers in column B will change. We want the count to end at the row the formula is in.

The second cell, C2, has a relative row reference, so when we copy the formula down, that part of the formula WILL change.

We always want the count to start in row 1.

The first cell, C$1, has an absolute row reference, so when we copy the formula down, that part of the formula won’t change.

Copy the Formula DownĪfter you create the formula to number the visible rows in Excel AutoFilter, copy it down to the last row with data. The second argument, C$1:C2 is the range of cells that we want to count. If you want to count text entries instead, use 3 (COUNTA) as the first argument. We’re counting dates, so the first argument, 2, tells Excel to count the numbers in the range. In our list, there will always be a date in column C, so we can use the SUBTOTAL function to count the visible dates. WARNING: Don’t use this technique if you plan to use Excel’s Subtotal feature (Data>Subtotals) - it may delete your table when you remove the Subtotals. It is designed to ignore rows that are hidden by a filter, so its result will change if rows in our list are hidden. Instead of using the simple formula shown above, if you want to show the record numbers for the visible records, you can use the SUBTOTAL function.

In the screenshot below, the second record is hidden by the filter, and the record count shows as 1, 3, 4, 5. In column B of our list, if we just type the numbers, or use a simple formula, such as =B2+1 the numbers won’t change if the list is filtered. To make things easier for them, you can add record numbers that will print for each row.įor example, in the screenshot below, the visible rows are 2, 4, 5 and 6, and the numbers in column B are 1, 2, 3 and 4. That’s helpful if you’re looking at the results on the screen, but not much help if you print the filtered list and give it to someone else. Note: If the Status Bar shows Filter Mode, instead of the record count, you can use one of the workarounds shown here – Status Bar shows Filter Mode. When you use an Excel AutoFilter to filter a list, usually the count of visible records appears in the Status Bar, at the bottom left of the Excel window. If you filter the list, here is a way to number the visible rows in Excel AutoFilter, so they’re in consecutive order. When you create a list in Excel, do you start with a column that numbers the rows? I usually create an ID column and type the number, or use a function to automatically number them.