Forum Discussion
Filter no choice between number and text
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?
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.
skylanetk So what I have had to do sometimes is change the query setting. For whatever reason sometimes the Columns will be set as Text type. If this is the case then when you query the data into your Excel spreadsheet go to the Query Tools tab on the toolbar.
1. Click Edit
2. The Power Query Editor should pop up
3. Select the columns you need to change to Number type
4. Under the Transform section on the toolbar there is a dropdown for Data Type
5. Select the type you need
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
