Forum Discussion
Fill series with a Filter Applied
Attempting, to drag and fill a series (1,2,3,4) down a column with a filter applied.
I filtered out all the female cast, then attempted to enter "1" into E2, then "2" into E3, then drag down all the rows in "E" become a "1".
So how can we drag and fill when rows are filtered out?
9 Replies
I think Fill Series does not work directly on filtered data. Try this approach then,
Original Table:Use this formula for your 'Rank' column(D2) then drag the formula to bottom:
=SUBTOTAL(3,$A$2:A2)When you filter your table based on Gender (Let's pick "Female) and Sort the episodes from Highest to lowest.
Using a formula, it will account the visible cells only. so SUBTOTAL function creates a sequence that updates correctly when filters are applied.
If you want to save those Ranks filtered data, you need to copy paste it to another cell or sheet.- Sort it by Sex & Rank and then fill the series as needed. Once done, put it back to whatever order you need. Hope it helps
- What I may have to do to manually get around this. But it will be manual PitA as the real sheet I have has thousands of rows I have to contend with.
What I may have to do to manually get around this. But it will be manual PitA as the real sheet I have has thousands of rows I have to contend with
There are probably many ways to get what you expect but without the complete picture not sure how to help you with this. If this can help.... In attached file you'll see:
The Blue Table contains data to be ranked (your wording). The Rank column contains formula for Excel 2021/365. The Excel Legacy column contains formula for previous versions
The Black Table is referenced by the above formulas to indicate the Starting number of the Ranking by Sex
If you run Excel >/= 2016 on Windows or 365 on Mac, there's probably a way to get things done with Power Query....
Sharing a sample file + the actual expected result would help to help you 🙂
Select E2 and E3, then drag it down.
source> How to Auto Generate Number Sequence in Excel (9 Examples)
- This was the method I used to create my original screenshots. Simply does not work. Had multiple from different systems / configurations try. Using O365 version of Office.
This was the method I used to create my original screenshots. Simply does not work
This would mean that nobody (but you) is clear with regard to what you have - in front of you / the actual data - and what you actually expect
As suggested earlier share something (a sample workbook) to clarify things
- Thanks Method 1 and 2 is what I am used to doing. Never had to use a formula like methods 3-9 in the link you gave. There is no mention of if there are filters applied. I am thinking something was changed or a potential bug.
