Filtering Data Permanently

[chefcheema]

Greetings all,

I have a column in Excel from which I only want values under a certain number. I know how to achieve this by using filtering. However my issue is, when I try to use the count function with this filtered data, it keeps counting values without the filter. Is there any way for me to perhaps change this column permanently or any other way for me to apply functions to this new filtered data? thanks

[Gregb11]

You should be able to use the SUBTOTAL function.
=SUBTOTAL(103,Range)

[chefcheema]

You should be able to use the SUBTOTAL function.
=SUBTOTAL(103,Range)

Ok thanks, so I should use the SUBTOTAL function after applying the filter. I tested it and this works.
Now, if I want to create a pivot table with that filtered data, it removes the filter. Is there any way for me to create a pivot table with the filter intact? thanks

[JeteMc]

Hello chefcheema and Welcome to Excel Forum.
I imagine that the easiest way to produce a pivot table based on only the filtered data is to produce a second table using either formulas or Get & Transform.
I suggest uploading a sample of your data as described in the banner at the top of the page.
Let us know if you have any questions.