×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Filtered rows in Structured Table (Excel)

Filtered rows in Structured Table (Excel)

Filtered rows in Structured Table (Excel)

(OP)
Suppose I have a very simple (and entirely hypothetical) structured table in Excel containing data about people.  Its columns might be:  Name, DateOfBirth, Salary, Department.

Outside the table, above it and aligned with the appropriate table columns, I have cells containing correctly structured formulae for:  Earliest date of birth;  Latest date of birth;  and  Average salary.  At this stage I have no filters applied, so all the table's rows are displayed and the formulae I describe above are doing exactly what I would expect.  So far, so good. 

Now I apply some table filtering.  I click on the down-arrow inside the header of the Department column, and restrict the table to displaying only the entries for the "Clothing" department.  The cells for averages etc that are outside the table still reflect the entire table rather than being restricted to only the entries that are currently displayed inside the table.  That's fine, but …

Is there a way that I can have my "averages etc" cells apply only to the table's displayed entries?

Alternatively, is there a way to create an extra column inside the table that indicates whether the row is currently displayed?  If so, then I could use the entry in this extra column to condition my "averages etc" cells to achieve what I am after.

I have tried using everyone's favourite search engine to get an answer to this, but get completely inundated with irrelevant replies.  I am using Excel 2010 so I do not have access to the FILTER() function.

RE: Filtered rows in Structured Table (Excel)

The SUBTOTAL function summarizes visible rows. Formulas for structured table totals can be copied anywhere (with table name added before field).

combo

RE: Filtered rows in Structured Table (Excel)

(OP)
Thanks, Combo.  Reads like SUBTOTAL (previously unknown to me) will do the trick. Cannot test it right now, but should get an opportunity in a few hours.  Will report back then.

RE: Filtered rows in Structured Table (Excel)

(OP)
Got it working.  Thanks again.

I decided I would get more future flexibility if I took the approach of including in my table (be it structured or non-structured) of including in the table a column that contains a boolean entry of True or False according to whether the row is visible or not visible.  It took a bit of exploration to find a way, but I eventually homed in on

=NOT(ISERR(SUBTOTAL(101,CellAddress)))

where "CellAddress" is the address of some other cell in the same row.  For this to work, "CellAddress" cannot be non-numeric, and cannot be empty.  These restrictions will not usually be very onerous, but if (say) your table has all columns being text then you simply create TWO extra columns rather than one.  First a dummy column guaranteed to always be numeric, such as simply the number 1 (or even 0).  Second a column containing an appropriately edited version of the above formula.

This works regardless of whether the row is unshown because it is Hidden or because it has been "filtered" out.

Any suggestions of a slightly "cleaner" approach will be welcome, but not essential.

RE: Filtered rows in Structured Table (Excel)

(OP)
I have now implemented into my spreadsheet the ability to limit "outside-the-table" calculations to those table rows that are visible.  In case anyone else has a similar need, I will describe the method I ended up using.

As per my earlier post (27Jan23@05:50), I decided my table needed to include an extra column whose value would be True or False according to whether the row was visible or hidden.  I named this column "Visible", and the formula in it was
    =NOT(ISERR(SUBTOTAL(101,[@ExtraColumn2])))
which is unchanged from my previous post except that ExtraColumn2 has to be an additional extra column that cannot be used for any other purpose.

What caused me some extra grief on the way through was what to use for ExtraColumn2.  My first few attempts were not robust enough.  I ended up using an approach that required another dedicated column, this one with a one-character title.  The formula for the column was
    =LEN(Table1[[#Headers],[x]])

So far all the operations I have tested can be restricted to rows with Visible=TRUE.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close