When Filter is applied, Excel jumps down multiple rows to (Blanks). How can Filter ignore blanks?

SL37

New Member
Joined
Oct 15, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
My subject line may be a poor description, as I'm struggling to explain this problem and behavior. I hope the screenshots will assist.

I have a workbook that will contain Names, Accounts, Effective Dates, and then extract a specific Month from the aforementioned effective date. A filter will be applied to these headers so users can sort, such as for a specific person's name or a particular month.

I'm using an IF statement to extract the Month. It's programmed to return a blank value if no data (Name, Account, Effective Date) have been input. The formula for the month column will go on for 1000 rows, as this workbook is intended to be used for a full calendar year, with data being added all throughout the year.

The problem I'm encountering is that when a Filter is applied for Name (or any other field), the results "jump" down to the next empty row.

So in my screenshot, I have the Month field programmed until row 15.
If the user sorts for "John", the results jump down to display Row 7 and then Row 20 as the next available line for data entry.

I know the user could sort for "John" + "(Blanks)", but they never do this. If the users don't clear the filters, the result is data jumping around on the workbook. It gets keyed in on random rows, rather than consecutive rows throughout the year.

I also know that Excel may count my "blank" month field as a "1" value, even if it's not displayed. It's kind of a binary yes/no, 1/0, situation behind the scenes. But I don't want that to happen. I've tried TEXT, MONTH, etc. to solve the problem. I've even given up and had users manually key in the month, which solves the sort problem (since Excel doesn't detect my formula in rows later in the workbook), but I would love to automate this functionality. There has been too much data garbage if it's not automatic (users may input "10" vs. "Oct" vs "October"...ugh).

Is there a way to change the filter to work better?

Or a way to change how the Month is extracted, so it doesn't mess up the filters?

Thank you in advance!
 

Attachments

  • Spreadsheet 1 - Unfiltered.JPG
    Spreadsheet 1 - Unfiltered.JPG
    94.3 KB · Views: 14
  • Spreadsheet 1 - Filtered.JPG
    Spreadsheet 1 - Filtered.JPG
    46.6 KB · Views: 14

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Welcome to the MrExcel board!

I suggest that you try this with a copy of your workbook.
  1. Remove all the formulas from the month column but leave the header. (My dates are in d/m/y format)

    1729057587397.png


  2. With your cursor in one of the cells with data and press Ctrl+T to create a formal table. Select "My table has headers" & click "OK"

  3. Now in D2 enter the formula =MONTH([@[Effective Date]])
    The formula should then automatically fill down the column

    SL37.xlsm
    ABCD
    1NameAccountEffective DateMonth
    2JohnJ11/01/20241
    3SusanS11/01/20241
    4AmyA11/01/20241
    5SusanS21/02/20242
    6DavidD11/02/20242
    7JohnJ21/03/20243
    8AmyA21/04/20244
    9AmyA31/04/20244
    Sheet1
    Cell Formulas
    RangeFormula
    D2:D9D2=MONTH([@[Effective Date]])


  4. Now filter column A for John

    SL37.xlsm
    ABCD
    1NameAccountEffective DateMonth
    2JohnJ11/01/20241
    7JohnJ21/03/20243
    10
    11
    Sheet1
    Cell Formulas
    RangeFormula
    D2,D7D2=MONTH([@[Effective Date]])



  5. Row 10 is there ready for data entry and when an entry is made the table automatically extends, including the formula in the Month column.
 

Attachments

  • 1729057370264.png
    1729057370264.png
    8.2 KB · Views: 6
Upvote 0
Solution
Peter, thanks for your tip about making these into tables. It seems to be working correctly! I'm going to mark your input as the solution. Thanks again!
 
Upvote 0

Forum statistics

Threads
1,224,594
Messages
6,179,795
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top