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

Рет қаралды 114,326

4 жыл бұрын

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
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.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/top-values-...
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.
🎓 Get access to the full course about the new Excel functions here 👉 www.xelplus.com/course/excel-...
Check out the complete Excel for Office 365 playlist: • Excel for Office 365 &...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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

Пікірлер: 242

@LeilaGharani 4 ай бұрын

Grab the file I used in the video from here 👉 pages.xelplus.com/top-values-file

@srvithal38 2 жыл бұрын

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

@drsteele4749 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

They are really great!

@excelemployeeleavetracker1274 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

Wow, that's great. Good luck with your journey!

@Amirazagee 4 жыл бұрын

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

@cheesewedges9100 Жыл бұрын

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

@AI-ec2qb 4 жыл бұрын

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 🖤

@peteringvorsen890 4 жыл бұрын

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 😊

@LeilaGharani 4 жыл бұрын

Thank you Peter! 😃

@excelisfun 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

Thanks Teammate 😄

@manadrama7705 2 жыл бұрын

@@LeilaGharani please alternative solution for those who not having Excel 365 mam

@malikaouenza945 Жыл бұрын

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

@farzanmoha8935 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

That's great! I'm glad you liked the course.

@muhammedmm1407 4 жыл бұрын

@@LeilaGharaniThankyou thankyou so much

@chh8860 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

Glad it was helpful!

@kangtai00 2 жыл бұрын

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?

@misterwill3625 Жыл бұрын

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

@LeilaGharani Жыл бұрын

You are so welcome!

@florincopaci6821 4 жыл бұрын

Hello Leila, First of all, thousands of thanks for what you do and for all the information you share with us.I learn i lot from you. I want to ask you- is there any possibility to manage to make those wonderful charts of your course on udmy without error bars? I have a newer version of excel that has no error bars and I don't know what to replace it with. Thank you Leila

@amedeoonofri8701 Жыл бұрын

this was pure gold. Thank you.

@marktheirl1842 4 жыл бұрын

I really enjoyed your course on VBA! I also enjoyed your course on dynamic arrays! Oh, and I really enjoyed your course on dashboards!

@LeilaGharani 4 жыл бұрын

Awesome! Thank you for the support of my courses! 😘

@vida1719 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

Glad you enjoyed it!

@garys2187 4 жыл бұрын

I am learning so much from your courses that I purchased my first course from you: Excel Dynamic Arrays: Beginner to Expert (Office 365). You are an incredible instructor!!

@LeilaGharani 4 жыл бұрын

Great to hear Gary! I hope you'll enjoy the course.

@tha2irtalib343 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

My pleasure 😊

@sandeepm2980 4 жыл бұрын

Thanks.the video was pretty helpful. Can you explain if we need top N based on more than one column.

@17aig 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

My pleasure! Glad you liked it.

@ritvikkulkarni4922 3 жыл бұрын

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

@ok4todd 4 жыл бұрын

Making the complex seem simple. Thanks!

@LeilaGharani 4 жыл бұрын

Glad it was helpful Todd!

@darrylmorgan 4 жыл бұрын

Hi Leila!The New Dynamic Functions Certainly Make What Could Be Complex Formulas So Much Easier..Great Tutorial Thank You :)

@LeilaGharani 4 жыл бұрын

So true Darryl!

@ahmedelectric83 4 жыл бұрын

Thank you Leila for the clear and nice illustration. I have a question which you may help me and others to know how to do it in a simple way. My question is that how can I make an Interactive Dashboard related to the Status Report to facilitate easily our assigned tasks in a way that could save time and effort since we do this update in a frequent basis.

@zanzibar6156 3 жыл бұрын

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

@xaquison 8 ай бұрын

Excellent and powerful video!

@asgarahmed Жыл бұрын

Very helpful thank you for making this video

@peterkomives1993 Жыл бұрын

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 ?

@OzduSoleilDATA 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

Thanks Oz! No wild stuff allowed :)

@farzanmoha8935 4 жыл бұрын

Excel On Fire, it’s all real OZ, look how finance person is the first in the top 5 😉

@excelisfun 4 жыл бұрын

@@LeilaGharani and Excel On Fire, no wild stuff allows ; )

@ifemideoluwafemi4434 3 жыл бұрын

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...

@davidferrick Жыл бұрын

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

@subhashsemwal2000 4 жыл бұрын

U r real gem mam. Amazing skills I also wanted to learn advance excel,word and PowerPoint

@LeilaGharani 4 жыл бұрын

Thanks a lot 😊

@martinargimon730 4 жыл бұрын

excellent Leila ( as always ) many thanks

@robertakwasiadjei8223 4 жыл бұрын

Leila, indeed you're amazing. Stay Blessed

@LeilaGharani 4 жыл бұрын

Thank you so much Robert!

@keishrich 4 жыл бұрын

Another good one. Thanks Leila!

@LeilaGharani 4 жыл бұрын

Glad you liked it Keisha!

@maghazahmad3307 4 жыл бұрын

I already commented you’re absolutely amazing.

@LeilaGharani 4 жыл бұрын

😘

@tonyboss5026 4 жыл бұрын

Thanks Leila always very useful the knowledge you share! To the 7 people who clicked dislike... if you know a better way to do this, share it or be humble and learn!

@LeilaGharani 4 жыл бұрын

Thanks for that Tony!

@DougHExcel 4 жыл бұрын

FILTER & SORT are such useful new functions!

@LeilaGharani 4 жыл бұрын

Yes they are!

@HusseinKorish 4 жыл бұрын

Amazing technique ... thanks Liela

@LeilaGharani 4 жыл бұрын

Thank you! Cheers!

@gokulakrishnan7148 4 жыл бұрын

Wow,that's great. Very useful one.

@LeilaGharani 4 жыл бұрын

Glad you think so!

@jhindson88 Жыл бұрын

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

@krn14242 4 жыл бұрын

Thanks Leila. Great video

@LeilaGharani 4 жыл бұрын

You’re welcome 😊

@anushkaprabhath3591 4 жыл бұрын

Thank you... These are some amazing stuffs

@LeilaGharani 4 жыл бұрын

Glad you think so!

@TH3LMITA 2 жыл бұрын

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")

@sandipgumtya130 4 жыл бұрын

Hi Leila. Nice tools. Can you please explain how can it be done with other versions of excel?

@samuellux2962 3 жыл бұрын

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 ?

@savioseona 2 жыл бұрын

Great video!

@joesatriani2189 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

Thanks for subbing!

@guitari89 8 ай бұрын

This is great. I have a question about summing values within the array. Example: The total sales value being summed over multiple transactions to put them into the Top N list. Would I need a helper cell to obtain this?

@mattschoular8844 4 жыл бұрын

Very helpful. Thanks Leila....

@LeilaGharani 4 жыл бұрын

You’re welcome Matt😊

@WaelGuirguis 4 жыл бұрын

Laila, really amazing as usual from you

@Road2Euphoria 4 жыл бұрын

Wonderfully done! Informative

@LeilaGharani 4 жыл бұрын

Thank you!

@Road2Euphoria 4 жыл бұрын

Leila Gharani You are welcome. As a Chartered Accountant and Financial Reporting Professional your videos been life saver. Do you have a course for financial reporting professionals?

@farzanmoha8935 4 жыл бұрын

Oh, I kept my eyes on KZfaq to be the first who hit the like but still I am late. I am 2nd it’s 2am 🙃 Thank you Leila.

@LeilaGharani 4 жыл бұрын

Wow, thank you for the like! Now, get some rest 😄

@ngegnoerick6303 5 ай бұрын

Hello Leila, A big thanks to you for this wonderful video. Am wondering. How can i return top N" values based on selection of a single date and 2,selection of date range? I have tried but would only get errors back.

@matshahanajaktar959 2 жыл бұрын

Excellent, amazing

@sunilbs2455 4 жыл бұрын

Wow you are awesome as always ❤️

@LeilaGharani 4 жыл бұрын

Thank you so much 😀

@chrism9037 4 жыл бұрын

Very useful, thanks!

@LeilaGharani 4 жыл бұрын

Thank you Chris!

@indryfitriani 4 жыл бұрын

Cool. Thanks Leila... In this pandemic COVID-19, would you share how to make sales assumption for, let's say 6-months? Thanks

@elhadoui 4 жыл бұрын

Good video and great excel skills...impressive

@LeilaGharani 4 жыл бұрын

Glad you liked it!

@elhadoui 4 жыл бұрын

Leila Gharani based on your vids i can see that you’ve got an extended experience both financially as well as systems/ tools, so i want to ask you have you ever seen/ created a good tracker in excel in order to track dividends?

@farazchughtai 7 ай бұрын

you are @Ms Leila simplly always brilant!

@jeremykong1657 2 жыл бұрын

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?

@Excelambda 4 жыл бұрын

Great video, came out with a formula using RANK only, after all, RANK is supposed to rank 😊 and can do stuff that other funct. can not do , RANK.AVG can eliminate ties for example. =LET(tbl,TableSalary,sal,INDEX(tbl,,3),rnk,RANK(sal,sal),SORT(FILTER(tbl,rnk

@henryg5735 4 жыл бұрын

Looks interesting; I'll have a play. Wish I could get my head around the LET() function :-{

@mraln26 4 жыл бұрын

Thank you Leila! Are there any workarounds for excel 2010?

@hassantaha3116 2 жыл бұрын

Excellent !!!!

@TEKNISHA 4 жыл бұрын

Great video again as usual. I love the way you are always on point... Btw I am also passionate about excel and do have my own KZfaq channel where I do try to make some videos that hopefully will help people.. Cheers

@LeilaGharani 4 жыл бұрын

That is awesome! Good luck with your channel!

@TEKNISHA 4 жыл бұрын

@@LeilaGharani Thank you

@mshi5826 2 жыл бұрын

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?

@alix743 4 жыл бұрын

Thank you .

@chillihi 3 жыл бұрын

Oh my goodness, mind blown!😃

@LeilaGharani 3 жыл бұрын

😁

@ursvenky6394 4 жыл бұрын

I always love your videos. How to get 5 selected columns out of 10 columns using dynamic arrays.

@laurinuoska794 Жыл бұрын

kzfaq.info/get/bejne/hdSll86onqqxnIk.html

@antoniskouroupis5030 4 жыл бұрын

Excellent.

@sanjayaais8495 3 жыл бұрын

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

@deepaliambekar362 2 жыл бұрын

See this video of Leila: kzfaq.info/get/bejne/hdSll86onqqxnIk.html

@MuhammadAbdullah-el5wz 4 жыл бұрын

For search box, we can use data validation with max or count function, to prevent user entering large number

@indryfitriani 4 жыл бұрын

I thought if we use validation in the cell that entering the formula, it only notif its cel but will still show error in the table below.

@MuhammadAbdullah-el5wz 4 жыл бұрын

@@indryfitriani I guess no, because validation will allow to enter specific number,else it will give you error

@cherubicchild5 3 ай бұрын

Why I need this for 2016...ughhh

@eduardomunoz2764 4 жыл бұрын

Wonderful.

@enriquealtamirano9408 4 жыл бұрын

Thanks!

@Naseer.syed01 4 жыл бұрын

U changed a lot

@noushadaboobakar6492 8 ай бұрын

Great 👍

@mcd22630 4 жыл бұрын

I'm not sure which I long for the most after watching this video. To sign up for your Excel Dynamic Arrays - Beginner to Advance. Or a return to videos that have more to the background than a white background. ;-)

@LeilaGharani 4 жыл бұрын

Well, I'm looking forward to seeing you inside the course. Full disclosure: It only has white backgrounds though 😁

@garyjack1960 3 жыл бұрын

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!!

@mageshkannashanmuganathan594 4 жыл бұрын

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

@lhsdline 3 жыл бұрын

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?

@flossybum 4 жыл бұрын

Once again, great video. My problem is, when I did it I sent it to someone (to show off) and it didn't work on their system when they changed the the reference number (like the [top] 5 to a 6).

@LeilaGharani 4 жыл бұрын

Do they have 365 with dynamic arrays too? If not, it will not work because they do not have the new functions.

@lianduan9584 4 жыл бұрын

those six dislikes are for people who just dont get it!! master work Leila!!!!!

@LeilaGharani 4 жыл бұрын

Thank you! Glad you liked it.

@focused313 7 ай бұрын

Thank you for this! I tried several formulas, but I couldn't figure out how to account for repeated values.

@LeilaGharani 7 ай бұрын

Glad it helped!

@hosseinhosseinpoor4845 3 жыл бұрын

thanks

@girlystuff1998 4 жыл бұрын

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

@halloffame_y5369 4 жыл бұрын

I need to get this office 365

@OneLife_5051 4 жыл бұрын

Does this function can work with pivot tabel or excubed?

@AjaySingh-ll5qw 4 жыл бұрын

Nice

@vijaysahal4556 4 жыл бұрын

wow its so amazing 👍👍👍👍

@LeilaGharani 4 жыл бұрын

Thank you! Cheers!

@vijaysahal4556 4 жыл бұрын

I want to learn excel vba with you bigners to advance label how mch complete course fee I saw unacademy yours cours fees rs 360 it is true ya not plz tell me about all plz

@mujahidulislam2243 Ай бұрын

@LeilaGharani This formula doesn't work when I upload to google sheet, can you do something like this that will work on google sheets too.

@Koreafut02 2 жыл бұрын

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

@SF-bm8ns 3 жыл бұрын

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.

@LeilaGharani 3 жыл бұрын

It's not easily done with Excel. If you're in Google Sheets, you can use the filter function or VLOOKUP to get pictures as well. For Excel you currently need to use a method like this: kzfaq.info/get/bejne/rdKHZbh80tKdc40.html

@SF-bm8ns 3 жыл бұрын

You are rite it is not easily done. I could not get yet. I will be trying again but it will be helpful to all HR professional if you kindly do a small video.

@petersmith8891 Жыл бұрын

Hello, Is it possible to filter the table so it pulls the top 5 for the selected filter? For example. At 1:10s in on this video there is the "Department" Column (A:5). If you clicked on the arrow and filtered it down to only show say "sales", is it then possible to show the top 5 salaries for the Sales Department? and then if you changed the filter to "Finance" it would show the top 5 salaries in finance. Then if you removed the filter it would show the top 5 salaries for all departments? I cannot get it to work. Every time i try it just filters the top 5 even more so there might be 1 or 2 in each department that all add up to 5. I cant seem to find the solution anywhere and this is the closet I can find. Any help would be much appreciated

@user-uz3zt4ne2z 6 ай бұрын

I used the same formula as you mentioned and made just a few minor tweaks to get everyone who has the lowest values. But my formula is returning only a few of the lowest values and missing the others even though their values are the same. Couldn't understand the problem. Hoping you can help me with this. Please tell me how I may share my workbook with you so that you may assist me