Column filter strangeness

Welcome to the Forum webregs!

I have never heard of a problem like this. The unusual part is that the problem does not exist until you delete rows.

But just because the display format is numeric does not mean that the data is numeric. I suspect this is related to your source data. Where are you copying the data from that you are pasting in here?

Can you attach a sample file that shows this problem? Since the problem occurs with 496 or fewer rows, you could just show us one row.

Administrative Note:

Welcome to the forum.

Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you are using?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The four most recent Excel products are Excel 2019, Excel 2021, Excel 2024 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

I stumbled across this issue recently and discovered what appears to be a simple rule controlling the type of Filter option presented based on the data.

If more than 50% of the values are numeric then you get the numeric option.

Try this very simple data set. Check the filter option and then change the value to text and check again.

That is an interesting "feature". It further suggests to me that the OP is pasting in some text and some numeric data.

6StringJazzer. To respond to your comments, I had checked my source data and confirmed it was numeric, and in fact it wasn't exhibiting the same issue. I should have mentioned this.

However, I think I've found what the problem is but both can't understand why nor figure out how to resolve it.

The problem is caused by my use of the =IF(ISBLANK(xy),"",xy) statement in each of the calculated cells. I use this to avoid having columns of 0.00 values in calculated cells where source data doesn't exist. Replacing this with a straight reference to the source data (ie. =xy) causes the filter to work as expected.

It's weird that this problem is resolved by adding a single row to the source data. eg. if I copy/paste to source data in row 497 the filter options change to numeric.

It doesn't seem the same as the anomaly Andy identified since with 406 rows of data, all subsequent cells are empty but obviously something triggers with 497 rows of data (such a strange/random number to trigger on?) And the strangeness keeps on coming. If I recreate one of the calculated columns from scratch (ie. same formula) elsewhere on the sheet, this issue doesn't arise. But if I copy/paste one of the columns exhibiting this problem the issue DOES carry over.

In any case the solution now is to replace =IF(ISBLANK(xy),"",xy) with something else that doesn't cause this problem. Any thoughts welcome.

PS. I was going to attach a much cut down spreadsheet with enough data in it to exhibit the problem how ever I don't have posting permission for attachments.

Personally, I’d go with

Formula:

Please Login or Register  to view this content.

but I don't know if that will help or hinder.

Whatever, a null value ( "" ) will be treated as text.

With cells A1 and B1 empty, compare the results:

PHP Code: 

            TRUE    =IF(A1&B1="","",A1+B1)    =ISTEXT(C1)
        0    FALSE    =IF(ISBLANK(A1&B1)="","",A1+B1)    =ISTEXT(C2)
        0    FALSE    =IF(AND(ISBLANK(A1),ISBLANK(B1))="","",A1+B1)    =ISTEXT(C3) 


Yes, I can see that using the "" was causing the problem but it leaves me with the issue now that replacing
=IF(ISBLANK($A9),"",$C9) with =IF(ISBLANK($A9),,$C9) gives me columns of 0.00 or $ - entries which cause other
filtering issues. For example, if I sort a column with rows of 0.00 into ascending order then I get all those 0.00
rows showing at the top of my list. I have a workaround I can use but it's still very messy.

It also doesn't explain why having more than 496 rows changes the filter from seeing text to seeing numeric.

Anyway, a problem for another day.

Originally Posted by webregs

I don't have posting permission for attachments.

You definitely have permission for attachments--every user does. What are you seeing that says otherwise? There is a size limit.

Filetype	Max File-size
bmp	100.0 KB
csv	1,000.0 KB
doc	1,000.0 KB
docm	1,000.0 KB
docx	1,000.0 KB
gif	100.0 KB
jpe	100.0 KB
jpeg	9.77 MB
jpg	9.77 MB
pdf	9.77 MB
png	9.77 MB
txt	100.0 KB
xls	1,000.0 KB
xlsb	9.77 MB
xlsm	1,000.0 KB
xlsx	1,000.0 KB
zip	9.77 MB
Sheet3

In the FAQ it says "below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments" but I don't have this button. It also says I need to not use Quick Reply but this is the only reply option I get when I click Reply or Reply with Quote so I assumed I don't have permission.

Look at the QUICK REPLY box below and take your eyes to the right of it, then look down. The GO ADVANCED button is there. Click it and than scroll down to MANAGE ATTACHMENTS.

NOBODY does not have permission to post an attachment.

The yellow banner at the top of every page also explains this.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) 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 each of those who offered help.