Filter Function on computer without 365

Question

I have a spreadsheet that I would like to use the Filter function on but I have purchased Excel using the Microsoft Office Home and Student 2019. I originally created this spreadsheet on Google Sheets and I am able to filter the data I need using the Filter function.

Are there any other functions or Macros I could use to do the filter ability? Since I just purchased the Microsoft package 3 weeks ago I am not wanting to spend more money.

Answer 1

Hello, I warmly welcome you to the Community. I'm Israel and it is a pleasure to be able to support you today.

This functionality isn't available in Office 2019.

you can see What's the difference between Microsoft 365 and Office 2019

https://support.microsoft.com/en-us/office/what...

I hope that you can solve the problem, please let me know about it. I'm at your disposal to continue supporting you.

Virtual Greetings and have a nice day!

Answer 2

Hi,

You should show us what your data looks like and what you want as a result of the filter, then maybe someone can come up with a solution. There are filters available in PivotTables, in Power Query and of course in the basic spreadsheet.

Thanks,

Shane

Answer 3

Hi Shane,

Good idea! I have included a screenshot of my spreadsheet. I am tracking my expenses and I have created categories for each item, like Food, Car or Bills. What I would like is to have every item listed "Food" be put on the Food tab, with all of information with it. That way I can see how much I am spending on Food. The only way I have figured out to do this is to using Filter and have the condition be the Category word.

Answer 4

Formula in cell F2 to be copied down and to the right:

=IFERROR(INDEX(A$2:A$15,AGGREGATE(15,6,(ROW($D$2:$D$15)-ROW($D$2)+ 1)/($D$2:$D$15 =$F$1), ROW($A1))), "")

You may use Index-Aggregate like above or else Index-Small-Match as an alternate to Filter.

Regards

Amit Tandon

www.excelanytime.com

Answer 5

Hi Nicole,

I don't have the Home/Student version but I would expect it to support the FILTER function. However, here is another approach:

Define your range as a Table (Insert, Table). Filters will be placed at the top of each column. On the row directly after the last table entry put your cursor in the cell under Amount and press Alt+= . (Alt =) Excel will insert the SUBTOTAL function. Now open the filter in the Category column and choose Food.

You can also type the formula in. In either case Excel extends the table with a row for calculations. A total row can also be added by choosing Table Design, Total Row while in the table. If you don't like the table formatting, you can change that. To add new rows to the table you can put your cursor in the last data cell (D12 in your example) and press Tab.

Shane