How to change filter formula so it works in Excel 2024

Question

How to change filter formula so it works in Excel 2024

KkSssssssss 20

Heya!

I am a teacher who is desperately attempting to create a dynamic dashboard that will show all of the student data I need to evaluate progress for a student when I paste in a student number. I am not an expert, clearly. It has taken a long time to figure out how to do this. Anyways, I started in Google sheets and it was working great, but I cannot keep student data in Google. So I am attempting to recreate the same thing in Microsoft Office 2024 [and will reside on OneDrive in the end].

Here is the formula from Google sheets:

=filter(DOWNLOADS!A:M,P12(DOWNLOADS!$A:$A=$A$2)*(DOWNLOADS!$B:$B=$B$2))

I am sure you can see that I am attempting to use the filter function to return all of the information [all courses] for the student when the student is listed one or more times because they have more than one course.

I have tried it in Excel on my desktop [Office 2024] and the [program on the OneDrive. Excel changed the formula to:

=FILTER(DOWNLOADS!A:M, ((DOWNLOADS!$A:$A = $A$2) * (DOWNLOADS!$B:$B = $B$2)))

which returns a spill error.

There is nothing in the cells. I tried formatting the cells. I have deleted them and I have pasted new cells in... I have changed workbooks and created new tabs. Nothing is working and I am not sure what to do next.

The data is in a named table as I hoped to be able to just keep adding data without needing to adjust formulas.

How can I change the Google sheets formula so it actually works in Excel? Thank you so much for help!

**I also used the AI suggestions. There is no data there. I removed formatting with no change. Also, the formula showing on my sheet is exactly what the AI said it should be. I tried changing the ranges to ensure they all held data... I tried using the name of the table. I tried creating tables for the response. Ugh. Finally, there was no message about running out of memory. The data is 12-24 cells--just reflecting data from another sheet. Not sure what else to try.

Thanks for any help!

Kristan Shabanov 0 Reputation points

2025-07-23T12:20:04.8966667+00:00

I tried the named tables again and here is a screenshot of what I am seeing.

I am somewhat a beginner in formulas, so I am not sure about the brown highlighted area--except it shows an error!

This formula also encompasses cells in the table that are empty.

Thanks again!
Kristan Shabanov 0 Reputation points

2025-07-23T12:47:52.8266667+00:00

Hello again and THANK YOU. All along you were saying the formula could not be in a table, but I didn't create a table... so I didn't think there was one. Apparently I DID create a table and forgot. =(

I deleted and the formula came out as needed.

I am so amazed at how you all kept helping. Thank you so much!

Answer accepted by question author

Vivian-HT 17,910 Microsoft External Staff Moderator

Dear @KkSssssssss,

Thank you for posting your question in the Microsoft Q&A forum.

According to my research, the issue you're seeing in Excel is a common one when transitioning from Google Sheets. In Excel, the FILTER function doesn’t support full column references like A:A inside array operations (especially when combining multiple conditions with *). This often causes a #SPILL! error.

Therefore, I recommend you use a Named Table

If your data is in a table named StudentData, and the columns are StudentID and Course, use: =FILTER(StudentData, (StudentData[StudentID]=$A$2)*(StudentData[Course]=$B$2))

For reference: Using structured references with Excel tables

Make sure there’s enough empty space around the cell with the formula, specifically, enough blank cells to the right and below it, so the results can display properly. Also, keep in mind that formulas like FILTER that return multiple values (called “spilling”) won’t work if they’re placed inside a table cell. These types of formulas need to be in regular cells outside of Excel tables.

Moreover, the limitation, where dynamic array formulas like FILTER cannot be placed inside Excel table cells and require sufficient empty space to spill, is a known constraint that affects many users building interactive dashboards.

These formulas are incredibly powerful for educators, analysts, and professionals who rely on dynamic data views, and it's clear that enabling them within structured tables would significantly enhance Excel’s flexibility and usability.

I strongly encourage you to share this feedback directly with Microsoft. To ensure your feedback reaches the right teams, I highly recommend submitting your feedback directly to Microsoft Ideas · Community and through the Help > Feedback option in Excel. where our product development team can know your requirements and ideas for product improvements. Your real-world experience and use case as a teacher make a compelling case for improvement, and community-driven feedback like yours plays a vital role in shaping future updates.

Note: As Microsoft Community moderators, please note that we are not involved in product design decisions and do not have direct access to the development roadmap, we want to assure you that your feedback has been acknowledged and is valued.

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.

I'm looking forward for your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

User's image

Vivian-HT 17,910 Reputation points Microsoft External Staff Moderator

2025-07-21T06:10:39.3133333+00:00

Hello @KkSssssssss,

Just following up to see how things are going, your updates would be greatly appreciated! If you have any difficulties, please do not hesitate to contact us.

Looking forward to hearing from you.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Kristan Shabanov 0 Reputation points

2025-07-23T12:08:52.2933333+00:00

Hello, and thank you so much for replying. I had already tried a named table and it didn't work. However, I tried again with your recommendation. Unfortunately that did not work either.

To note, there is only one table on the Downloads tab. Dashboard tab doesn't have a table, just cells with formulas and headings.
Kristan Shabanov 0 Reputation points

2025-07-23T12:50:10.79+00:00

Thank you! the formula was in a table. Also, I don't see an "accept answer" button. I did mark that your answer was helpful. thank you!
Vivian-HT 17,910 Reputation points Microsoft External Staff Moderator

2025-07-23T23:57:36.0333333+00:00

Good day, @KkSssssssss!

I hope you are doing well.

Thank you for sharing your feedback about your recent interactions with our team.

I'm glad my suggestions helped you found a way to resolve your issue.

Meanwhile, hope you kindly accept it as an answer and vote it up by your original account @KkSssssssss which raised this question (as seen in the screenshot). Only your original account @KkSssssssss can see the "Accept Answer" button.

Once marked, it will automatically pin to top. As other users will also search information in this community, your valuable marked answer will definitely also help other users who have similar queries easily to find the correct channel and useful information more quickly.Looking forward to hearing from you.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

4 additional answers

Your answer

Kristan Shabanov 0 Reputation points

2025-07-23T12:20:04.8966667+00:00

I tried the named tables again and here is a screenshot of what I am seeing.

I am somewhat a beginner in formulas, so I am not sure about the brown highlighted area--except it shows an error!

This formula also encompasses cells in the table that are empty.

Thanks again!
Kristan Shabanov 0 Reputation points

2025-07-23T12:47:52.8266667+00:00

Hello again and THANK YOU. All along you were saying the formula could not be in a table, but I didn't create a table... so I didn't think there was one. Apparently I DID create a table and forgot. =(

I deleted and the formula came out as needed.

I am so amazed at how you all kept helping. Thank you so much!
Vivian-HT 17,910 Reputation points Microsoft External Staff Moderator

2025-07-21T06:10:39.3133333+00:00

Hello @KkSssssssss,

Just following up to see how things are going, your updates would be greatly appreciated! If you have any difficulties, please do not hesitate to contact us.

Looking forward to hearing from you.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Kristan Shabanov 0 Reputation points

2025-07-23T12:08:52.2933333+00:00

Hello, and thank you so much for replying. I had already tried a named table and it didn't work. However, I tried again with your recommendation. Unfortunately that did not work either.

To note, there is only one table on the Downloads tab. Dashboard tab doesn't have a table, just cells with formulas and headings.
Kristan Shabanov 0 Reputation points

2025-07-23T12:50:10.79+00:00

Thank you! the formula was in a table. Also, I don't see an "accept answer" button. I did mark that your answer was helpful. thank you!
Vivian-HT 17,910 Reputation points Microsoft External Staff Moderator

2025-07-23T23:57:36.0333333+00:00

Good day, @KkSssssssss!

I hope you are doing well.

Thank you for sharing your feedback about your recent interactions with our team.

I'm glad my suggestions helped you found a way to resolve your issue.

Meanwhile, hope you kindly accept it as an answer and vote it up by your original account @KkSssssssss which raised this question (as seen in the screenshot). Only your original account @KkSssssssss can see the "Accept Answer" button.

Once marked, it will automatically pin to top. As other users will also search information in this community, your valuable marked answer will definitely also help other users who have similar queries easily to find the correct channel and useful information more quickly.Looking forward to hearing from you.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 1

HansV 462.7K MVP Volunteer Moderator

You cannot use a formula that returns multiple values in a table. Are you sure that the cell with the formula is in a table? If so, convert that table to a range.

When the cell with the formula is in an ordinary range, try

=FILTER(DownloadTable2, (DownloadTable2[SID]=A2)*(DownloadTable2[CURRENT DATA]=B2), 0)

0 comments

Answer 2

HansV 462.7K MVP Volunteer Moderator

If the data are in a named table, I would refer to that. Let's say the table is named StudentData, its first column is named Column1 and its second column is named Column2.

The formula then becomes

=FILTER(StudentData, (StudentData[Column1]=$A$2)*(StudentData!Column2=$B$2), "")

Make sure that there are sufficient empty cells to the right and below the cell with the formula to contain the spill range of the formula.

Also, the formula can not be in a table cell - formulas that return more than one cell do not work in a table.

Kristan Shabanov 0 Reputation points

2025-07-23T12:10:15.82+00:00

Hello, thank you for responding. I tried that, too. It didn't work. =(

Answer 3

Kristan Shabanov 0

Hello again and THANK YOU. All along you were saying the formula could not be in a table, but I didn't create a table... so I didn't think there was one. Apparently I DID create a table and forgot. =(

I deleted and the formula came out as needed.

I am so amazed at how you all kept helping. Thank you so much!

0 comments

Answer 4

To adapt your Google Sheets formula for use in Excel 2024, you need to ensure that the logical conditions in the FILTER function are correctly formatted. In Excel, the FILTER function should use the + operator for logical OR conditions and the * operator for logical AND conditions.

Your original formula in Google Sheets:

=filter(DOWNLOADS!A:M,P12(DOWNLOADS!$A:$A=$A$2)*(DOWNLOADS!$B:$B=$B$2))

In Excel, the modified formula should look like this:

=FILTER(DOWNLOADS!A:M, (DOWNLOADS!$A:$A = $A$2) * (DOWNLOADS!$B:$B = $B$2))

If you are encountering a spill error, it may be due to the output range being blocked by other data. Ensure that the cells where the results will spill into are empty. Additionally, if the formula still does not work, check that the ranges DOWNLOADS!$A:$A and DOWNLOADS!$B:$B contain data and that there are no formatting issues causing the problem.

If you continue to experience issues, consider testing the formula with a smaller range instead of the entire column, like DOWNLOADS!$A1:$A100 and DOWNLOADS!$B1:$B100, to see if that resolves the spill error.

References: