0

I have an Excel worksheet that should contain a summary of my expenses in different trips (say).

excel

I want to apply a filter to rows 3 and 18 (i.e. the headers for my different trips), so I can quickly filter out all my food expenses in London (Base), for example. If I do this, I do not want the table for the second trip to get affected.

However, when I try (naively) to apply a filter to row 3, I can't apply it to row 18. How can I simultaneously add a filter to multiple rows, for the same column, within the same Excel worksheet.

I'm using Microsoft Excel 365 btw.

Improve this question
2
  • 1
    Only way I can think of is to put each trip on a seperate sheet .. :( which doesn't really answer your question .. but that's about the only way I can think. The only other way would be to reformat your layout .. and put another column left of A .. for "Trip" ... and then just list them together in 1 list ... maybe groups ??
    – Ditto
    Jun 9, 2017 at 14:03
  • @Ditto I tried playing around with groups, but it doesn't seem to work.. And you're right, I want the trips to be all within the same sheet.. I'm not too sure about what you mean for the 'list' thing. (I'm new to Excel..)
    – ksgj1
    Jun 9, 2017 at 14:08

2 Answers 2

Reset to default
1

Highlight the area for the London (Base) and click on Format as Table. It will add the filters to only that area.

Then highlight the area of London to xxx and click on Format as a Table again. It will add the filters to only that area. And now the two filters will act independently of each other.

Improve this answer
-1

Ok .. try this - groups may or may not give you much .. but here's how you could use them ... or you could just leave them out completely. Up to you.

Raw data entered slightly differently ... rather than seperate headings for each trip, put it all together as a SINGLE list .. but use an additional column to id the different trips.

enter image description here

Next, setup Groups to be Summary before .. it defaults to summary after, which might confuse things ;) enter image description here enter image description here

Now, Group the data .. note the highlighted lines (highlight the children), and click Group. enter image description here

and repeat for next section: enter image description here

You end up with this:

enter image description here

And you can then choose your filter .. to get:

enter image description here enter image description here

That's about the only way I can think of to get that effect on a single sheet.

Improve this answer
2
  • A really detailed answer, and I appreciate it! One thing though, the filters of one 'group' shouldn't affect another. e.g. I want to see 'food' expenses from London(base), so I select with filter, only food. Then what I should see is all the London(base) food rows, but London to xxx rows should not be affected. I should still see Leisure, food, accommodation etc. for the second trip.
    – ksgj1
    Jun 9, 2017 at 14:34
  • Yeah, then you're back to 2 seperate sheets .. you either want the filters to work in conjuction (same sheet), or separately (seperate sheet) ... I'm not aware of any other to do it .. sorry
    – Ditto
    Jun 9, 2017 at 14:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .