How to filter "next 10" and "previous 10" in Excel

Dear Forum,

I'm working on a dashboard where I have a nice pivot table and pivot chart. The data is geographical, I have States, DMAs, Demographic groups and Population in it. I filtered out the pivot table and now it show's the Top 10 DMAs and Top 5 Demo Groups by Population.

I would like to create a 2 buttons under my chart. 1 of them would show the "next 10" DMAs the other would show the "previous 10" DMAs but I can't figure out how to make the pivot table filter "next 10" or "previous 10". There are approximately 200 DMAs in the US.

Can anybody suggest a solution?

How about this:

Sort by DMA's, add a column to the filter, number it 1-200, then have a variable which you can +-10 to and select those 10 rows

OMG so out-of-the box!! Thanks!

@ managy

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Originally Posted by niko79542

How about this:

Sort by DMA's, add a column to the filter, number it 1-200, then have a variable which you can +-10 to and select those 10 rows

So I was experimenting with the solution a little further and it doesn't work for the following reason. I have the States in filter and the top 10 DMAs for different states are in different order. I can't insert a calculated field into my pivot to reference the position of the DMA. Is there a way to somehow write a macro for this?

Or what if I remove the Top 10 filter from the pivot table and show the whole thing. Can I somehow show only the first 10 DMAs then scroll to the next 10 and so on?

Whats a DMA? I don't udnerstand your last post.

DMA = Designated Market Area.

So the data is structured something like this (example data in brackets): STATE (New York)| DMA (Albany et al, NY) | DEMO (Young Influentials)| POPULATION (611,997).

I have state in Report Filter, DMA and DEMO in Row Filter, POPULATION in Values.