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

Hi Leila, great video! Question for you. What if you wanted the top 5 salaries, but only from finance? I've been trying this with (TableSalary[Department]="Finance")*(TableSalary[Yearly Salary]>=LARGE(TableSalary[Yearly Salary],G5))), but it seem it only produces the top 3 Finance salaries.

Great tutorial Leila! And if I wanted to add an exra filtering option to the formula? For example I would like to have the option to not only to see the first 5 (or last) but to be able to choose dates or quarter etc ?

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

Mam, if criteria is multiple then how it will work,please guide us.

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

this was pure gold. Thank you.

Perfect solution for a problem encountered of picking the 4 smallest numbers from a list and intentionally ignoring duplicates

The formula on your coffee mug is wrong.
Shows as: =(Energy>Workload,Coffee, MORE_Coffee)
Should be: =(Energy<=Workload,Coffee, MORE_Coffee)

I have a hurdle with the FILTER() function. I have a grid of checkboxes 9x9. Once I finish clicking through one column I would like for the array list to look to the next column on the right and repopulate the array list once the column to the left is either complete or one of the checkboxes in the next column (on the right) is checked and array list displays again to be checked off as I complete the repetitive task in the next adjacent column to the right (or next day). I hope that makes sense. I tried IF() functions with and without (AND(), OR() logic tests and nothing has worked. Any thoughts?

Very helpful thank you for making this video

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

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

in this formula with the same data, if you go for top 2 value that the result will be 3 because of 140,000.
kindly check and advice to restict to 2 result if we go for top 2.

Thanks

Hi i have a question can we sort top 5 products in pivot

Excellent, amazing

Will it work if i want to bring top 25% of value?

Can we solve this question without filter & sort function, because i Don't have office 365 & i am looking for the same question from a long time, I can do this with the help of pivot but i want to solve this through function.

Excellent !!!!

Hi Leila , This is idea really save time of filtering out the TOP N number items. I faced a problem when i used it for TOP N SMALL items when my data sets contains negative and positive values. It only filter the item with negative value. Is there any way to troubleshoot this situation to show both negative value and for 2nd to 5th smallest items with positive value?

Hi Leila, Thanks so much for all your excel tutorials. It really helps me grow my skills on how to using excel more in a smarter way. I am trying to build out a table from this example you show here. However, I encountered some difficulty that I could figure out for days. Is there anyway I can seek for your help?

This is Briliant Formula. One more question, if we want to add one drop down for Top/Bottom to use Large/Small function, how the function change?

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

Hi Leila, as always you are the best, Thanks for sharing all of this, it is very valuable knowledge, I really appreciate it. And I have a question, I am trying to use this with additional filters in a dataset, to get the Top 10 records considering the total quantity produced, and filtered by year and month, but I can't get the full list, could you take a look and give me some advice on what could be wrong with this, please?

=FILTER(DATASET,(DATASET[Year]=YearSelected)*(DATASET[Month]=MonthSelected)*(DATASET[Qty]>=LARGE(DATASET[Qty],10)),"Nodata")

Great video!

Instead of doing this yearly, is it possible to do this weekly?

Hi. How can i select Position holder students from 20 record Table.. ,in the table there are 6 students are position holder..

3 students are same marks like 750 and they are 1st postion ( 3 students at 1st position)

2 student who are 2nd postion

1 student is in 3rd Position


Help me to any dynamic methad?

I can't see FILTER formula in my 2019 excel but only the FILTERXL.

Hm - What if I dont to see all the columns as a result?
If I have a very large table, i am filtering on.

Thanks for teaching the filter formula, it really helps.
Is there a way to choose the columns the array returns, i.e Just Department and Amount, no need for the name column, would I have to edit the array (table itself), or the filter formula can choose what to present...

How can I specify a different Top N for each department? (i.e. I want the top 6 from Finance, the top 3 from Procurement, and the top 2 from Sales dept.) Note that I still want all departments listed, but I want to be able to specify how many from each department. Thanks SO much!!

great video many thanks - is there a way to retrieve top 5 salaries of a specific department using the filter function ? like Top 5 salaries in finance ?

thanks

What would you add to the formula if you just wanted to view top yearly salary for only a single department instead of all departments? I assume you would have to create another dropdown list to select a department, but what would the formula be?

this is a great and useful video, i tried it and it works for my HR list. I had a problem to extract the photo of the employees in the spilled table. please let me know if it is possible to add the photo to your table and show it in the filtered spilled table.

Thank you for this, solved a big problem! Can we sort the data into separate columns by department type? And using VBA?

How to do this without filter function on Excel 2016 ?
Thanks for your video Leila, awesome as usual

Oh my goodness, mind blown!😃

Hi Leila.. thanks for the video. Great solution! Thumbs up!!
PS - In case any viewers don't have Microsoft 365, here is a solution to pull the same Top "x" using INDEX, SMALL and RANK:
=IF(ROWS(G$8:G8)>$G$5,"",INDEX(A$6:A$36,SMALL(IF(RANK($C$6:$C$36,$C$6:$C$36)<=$G$5,ROW($C$6:$C$36)-ROW($C$6)+1),ROWS(G$8:G8))))
It requires CTRL+SHIFT+ENTER in legacy Excel and you must copy down and right. Also, sorting the results by salary then department within the formula is pretty tricky. The solution there is to sort the data first by department and then by salary.. which allows the formula to produce the same result as using the FILTER function with SORT which of course is by far the easier choice, assuming you have modern Excel. I love the new dynamic array functions.. but.. always like to remember the roots of where we came from. Hope it helps someone. Cheers!!

Excellent lesson, very helpful. I have followed your courses on Udemy too.
May I ask a question on the filter formula: is it possible to reduce the number of columns on the output if the source data has many columns ?
Thanks, Sena

Hi leila
I am wondering if i can switch between two columns or rows
I know you have a trick 😏 so could you please help me ?
Thank you

Thanks.the video was pretty helpful.

Can you explain if we need top N based on more than one column.

hello leila, i am your new subscriber, thank you so much for tutorial

Does this function can work with pivot tabel or excubed?

You can use data validation in the number entry box as values between so that you will get an error inside.

Is this correct?

Dear Leila Gharani,

Good Morning
I have fix a problem in calculating our state electricity bill
It calculated in slab rate
Slab rate as follows

Units Price/unit
0-100 3.15
101-200 3.70
201-300 4.80
301-400 6.40
401-500 7.60

For example My Electricity Unit is 220 But the calculation is separated slab
100 x 3.15 = 315
100 x 3.70 = 370
20 x 4.80 = 96
Total 315+370+96= 781

Kindly request give me a formula or upload a video for such types calculation



Sincerely