In a table when I put in a filter and I want a number filter EXCEL automatically puts in a text filter and I want a number filter. EXCEL Does not give me a choice. There are blank cells in the column because I have written logic to return a number or,"",. I know that EXCEL recognizes the ,"", as text. I know that if I put in 0s it will give me a number filter but then if I want averages they will be wrong because of adding 0s so I then get a number filter. WHY doesn't EXCEL allow me to make the choice? Is there another way to do it?

skylanetk I can't answer the question why it doesn't give you a choice, because when I went and looked for an example in one of my own spreadsheets, it DID give me a choice. The formula that I have for that column has exactly the same logic as yours, returning either "" or a number. And this image shows what I get in the Dialog box if I ask to filter. So could you give a more detailed picture (literally or figuratively) of what happens in your situation?

It is my understanding the if more numbers show up in the column then ,"", text responses EXCEL automatically makes the number filter show. The problem comes if the "" text response is the larger then EXCEL automatically chooses the text filter and you don't have a choice. Then you must make your answer in all of the cells in the column numeric. That creates other problems when you say average the numbers in the column.

Above is my example, Column BA shows the daily price changes of the index. Then I used these formulas to separate the up days and the down days into their own columns.Up days: =IF($BA2>=0,$BA2,"")Down Days: =IF($BA2<0,$BA2,"") So column BB gives me a number filter and I can then filter for different values.Column BC only gives me a Text Filter and no choice to make it a number filter. The only way I can use it to filter different number values is to make more columns getting those values out of the column or making the entire column numeric.

You could change your formula to return a "0", and then us an averageIF formula that ignore's zeros.Not sure if there are other zero's in your data though.

skylanetk You wrote:It is my understanding the if more numbers show up in the column then ,"", text responses EXCEL automatically makes the number filter show. The problem comes if the "" text response is the larger then EXCEL automatically chooses the text filter and you don't have a choice. Then you must make your answer in all of the cells in the column numeric. That creates other problems when you say average the numbers in the column. But my column--the one from which my image of the filter dialog box was taken--has more cells with the "" value than with a numeric value. Yet it's still showing as it is pictured. It's possible we're working with different versions of Excel, I suppose. Mine is the most recent version.

Filter no choice between number and text

mathetes
Silver Contributor
Mar 08, 2022
skylanetk

I can't answer the question why it doesn't give you a choice, because when I went and looked for an example in one of my own spreadsheets, it DID give me a choice. The formula that I have for that column has exactly the same logic as yours, returning either "" or a number. And this image shows what I get in the Dialog box if I ask to filter.
So could you give a more detailed picture (literally or figuratively) of what happens in your situation?
- skylanetk
  Copper Contributor
  Mar 09, 2022
  It is my understanding the if more numbers show up in the column then ,"", text responses EXCEL automatically makes the number filter show. The problem comes if the "" text response is the larger then EXCEL automatically chooses the text filter and you don't have a choice. Then you must make your answer in all of the cells in the column numeric. That creates other problems when you say average the numbers in the column.
  - skylanetk
    Copper Contributor
    Mar 09, 2022
    Above is my example, Column BA shows the daily price changes of the index. Then I used these formulas to separate the up days and the down days into their own columns.
    Up days: =IF($BA2>=0,$BA2,"")
    Down Days: =IF($BA2<0,$BA2,"")
    
    So column BB gives me a number filter and I can then filter for different values.
    Column BC only gives me a Text Filter and no choice to make it a number filter. The only way I can use it to filter different number values is to make more columns getting those values out of the column or making the entire column numeric.
- skylanetk
  Copper Contributor
  Mar 10, 2022
  mathetes
  This is what I am talking about. Sometimes I want a number filter and I do not get a choice. thank you for your response.
  - mathetes
    Silver Contributor
    Mar 10, 2022
    skylanetk
    
    That's a very different Filter dialog box than the one I see. What version of Excel are you using? Mine is the most current version.
    
    Do you have access to the FILTER function? A new "Dynamic Array" function...it also requires the most current version of Excel....given the kind of thing you're doing, I think you'd find it worth your while to have it.
    https://www.youtube.com/watch?v=9I9DtFOVPIg
BK_Bangkok
Copper Contributor
Mar 09, 2022
You could change your formula to return a "0", and then us an averageIF formula that ignore's zeros.

Not sure if there are other zero's in your data though.
- skylanetk
  Copper Contributor
  Mar 09, 2022
  It should not be so difficult it should be my choice. And adding a 0 also means I will have to add more steps in other places. There are also other advantages to seeing empty cells.
  - xsmplfy
    Copper Contributor
    Nov 06, 2023
    You could add conditional formatting to the column to highlight blank cells, then filter by color; or create a second column next to the column where you want to continue to see empty cells that adds a 0 so you can use the secondary column to use a number filter. Otherwise, you'll have to live by Excel's limitations.

Forum Discussion

Filter no choice between number and text

Resources