Create an Interactive Top N Report in Excel (includes duplicate values) with Filter Function

Add this interactive report to your Excel dashboard if you want to make an impression! It's INTERACTIVE so the user can decide which TOP N records of a data-set they'd like to see. It will also NOT have a problem with showing MULTIPLE MATCH results either! This way you get to quickly rank the data and categories automatically and be able to show duplicate values with their correct categories.

Instead of a complex formula with the LARGE function in a lookup-formula like INDEX & MATCH, VLOOKUP, or XLOOKUP we will use my new favorite Excel function: The FILTER function! Together with the SORT function we will create a simple formula to extract the data automatically. Both functions are available exclusively in Excel for Office 365 with the new Excel Dynamic Arrays features.

We will even improve the report and make sure the user cannot input a number that's exceeding the available options in the data set. This will help avoid the #Spill error. To do that we'll use a combination of the COUNT formula and the SEQUENCE function.

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[Amirazagee]

Hi! Miss Leila Gharani, You will never forget a person who came to you with a torch in the dark.

[srvithal]

Your way, style of imparting the know how is superb. Very helpful. The simplicity in teaching is awesome. Thank you very much . Your teachings are very helpful

[drsteele]

The dynamic array functions are so terrific I sometimes just sit and marvel at their performance. Good vid, Leila.

[excelemployeeleavetracker]

Hi Leila... Just want to say after watching your channel and Mr Excel for over the past year (and sharing with former co-workers), I finally started my own business and channel during my state's "Stay-At-Home". Always love watching your videos and saving them on my phone to refer to them when not at home. Thanks for all the great work!! WJA

[cheesewedges]

Thanks a lot! The tutorial about FILTER function really helps me a lot in my assignment.

[peteringvorsen]

Nice Excel lesson as always, Leila 👍
You are still the best Excel teacher at the Internet. I hope you soon will visit Denmark for an exclusive Excel event. If this happened I will surely be there 😊

[AI-ecqb]

Hello Mi Amor 💋🤣
the other day I made an incredible excel project,
It is a Credit Application that analyzes
customer data and submits information through a custom internet tunnel in VB code,

and I remembered you, so I just came to give you thumbs up again!

You're a Queen 🖤

[malikaouenza]

I really appreciate what you do to help us become excel masters

[aig]

Thanks a lot, Leila for an amazing tutorial again!!!!

[chh]

Perfect timing … I think I will be able to use this concept but in a different situation. Thank you … excellent presentation … no surprise (again).

[excelisfun]

Thanks for the cool formulas, Teammate Leila !!!!

[misterwill]

That was fun to watch. I can think of times to use these formulas in this way. Thank you 👏🏾

[thairtalib]

That's so great, I like it .. amazing flexable filter .
Thanks for sharing Leila .

[oktodd]

Making the complex seem simple. Thanks!

[farzanmoha]

It was great Leila, thank you. I like dashboards and graphs the most. I finished your dashboard course, and learned a lot from it.

[martinargimon]

excellent Leila ( as always ) many thanks

[Innovlrnr]

Very helpful thank you for making this video

[vida]

Great example and great explanation! I liked how you used arrays {} in 2 arguments

[davidferrick]

Simply lovely, now i need to tweak this to sum a value by a group name.

[OzduSoleilDATA]

Beautiful! Good addition to control the input to prevent wild stuff.