When you're working with filtered lists, you might want to know how many items are in the list, and how many items are currently visible.
In this video, we'll show you how to add a message at the top of a filtered list that displays this information.
Here we have a list of properties. If we enable filtering, and filter the list, Excel will display the current and total record count in the status bar below. However, if we click outside the filtered list, and back again, this information is no longer displayed.
Let's add our own message at the top of the list that stays visible.
The first thing we'll do is convert our list into an Excel table. This will make it easier to count the rows in the list.
Note that Excel automatically names all tables. We'll rename this table "Properties", to make the name more meaningful.
To count total rows, we can use the function ROWS, and simply input =ROWS(Properties). This is a structured reference that refers only to the data rows in the properties table, which is ideal for this use.
Next, we need to count the number of visible rows. To do this, we'll use the SUBTOTAL function. The SUBTOTAL function takes two arguments: a number, specifying the function to use, and one or more references.
SUBTOTAL can perform a variety of operations on data. And it has the interesting feature of being able to include or exclude hidden values. In this case we want to count only non-blank cells that are visible, so we need the function number 103. Any rows hidden by the filter will be ignored.
For the reference, we'll use a structured reference that points to the first column in the table. The first column is named Address, so we enter this as Properties[Address].
Now when we filter the table, we see our formulas in action.
To wrap things up, we need to create a single message that includes the ROW and SUBTOTAL functions we just tested.
First, we'll enter our message as "Showing X of Y properties". Then we'll replace X and Y with the functions, using the ampersand to concatenate, or join the functions to the text.
We can now remove our original formulas and test the message.
You can use this approach with any filtered table. Just make sure to reference a column that always contains data in the SUBTOTAL function.
The Excel SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.