Create an Interactive Top N Report in Excel (includes duplicate values) with Filter Function
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
Пікірлер: 245
Grab the file I used in the video from here 👉 pages.xelplus.com/top-values-file
Hi! Miss Leila Gharani, You will never forget a person who came to you with a torch in the dark.
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!
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... 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!
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
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! 😃
How to do this without filter function on Excel 2016 ? Thanks for your video Leila, awesome as usual
Thanks a lot! The tutorial about FILTER function really helps me a lot in my assignment.
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 🖤
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
I really appreciate what you do to help us become excel masters
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!
Thanks a lot, Leila for an amazing tutorial again!!!!
@LeilaGharani
4 жыл бұрын
My pleasure! Glad you liked it.
awesome tutorial, hats off
this was pure gold. Thank you.
That was fun to watch. I can think of times to use these formulas in this way. Thank you 👏🏾
@LeilaGharani
Жыл бұрын
You are so welcome!
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! 😘
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 ; )
Making the complex seem simple. Thanks!
@LeilaGharani
4 жыл бұрын
Glad it was helpful Todd!
Great example and great explanation! I liked how you used arrays {} in 2 arguments
@LeilaGharani
4 жыл бұрын
Glad you enjoyed it!
That's so great , I like it .. amazing flexable filter . Thanks for sharing Leila .
@LeilaGharani
4 жыл бұрын
My pleasure 😊
Excellent and powerful video!
excellent Leila ( as always ) many thanks
Very helpful thank you for making this video
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!
Perfect solution for a problem encountered of picking the 4 smallest numbers from a list and intentionally ignoring duplicates
Great video!
Another good one. Thanks Leila!
@LeilaGharani
4 жыл бұрын
Glad you liked it Keisha!
Simply lovely, now i need to tweak this to sum a value by a group name.
U r real gem mam. Amazing skills I also wanted to learn advance excel,word and PowerPoint
@LeilaGharani
4 жыл бұрын
Thanks a lot 😊
Thanks.the video was pretty helpful. Can you explain if we need top N based on more than one column.
Thanks Leila. Great video
@LeilaGharani
4 жыл бұрын
You’re welcome 😊
Excellent, amazing
Why I need this for 2016...ughhh
I already commented you’re absolutely amazing.
@LeilaGharani
4 жыл бұрын
😘
FILTER & SORT are such useful new functions!
@LeilaGharani
4 жыл бұрын
Yes they are!
Amazing technique ... thanks Liela
@LeilaGharani
4 жыл бұрын
Thank you! Cheers!
Excellent
Excellent !!!!
Thank you... These are some amazing stuffs
@LeilaGharani
4 жыл бұрын
Glad you think so!
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?
Wow,that's great. Very useful one.
@LeilaGharani
4 жыл бұрын
Glad you think so!
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 :-{
Thank you for this, solved a big problem! Can we sort the data into separate columns by department type? And using VBA?
Leila, indeed you're amazing. Stay Blessed
@LeilaGharani
4 жыл бұрын
Thank you so much Robert!
Laila, really amazing as usual from you
Thank you .
Excellent.
Very useful, thanks!
@LeilaGharani
4 жыл бұрын
Thank you Chris!
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
Very helpful. Thanks Leila....
@LeilaGharani
4 жыл бұрын
You’re welcome Matt😊
Wow you are awesome as always ❤️
@LeilaGharani
4 жыл бұрын
Thank you so much 😀
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?
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.
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!
Thanks!
Wonderful.
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...
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 ?
Oh my goodness, mind blown!😃
@LeilaGharani
3 жыл бұрын
😁
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.
hello leila, i am your new subscriber, thank you so much for tutorial
@LeilaGharani
4 жыл бұрын
Thanks for subbing!
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
Great 👍
Hi Leila. Nice tools. Can you please explain how can it be done with other versions of excel?
you are @Ms Leila simplly always brilant!
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?
Thank you Leila! Are there any workarounds for excel 2010?
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
U changed a lot
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 again as usual. I love the way you are always on point... Btw I am also passionate about excel and do have my own KZread 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
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?
Nice
I always love your videos. How to get 5 selected columns out of 10 columns using dynamic arrays.
@laurinuoska794
Жыл бұрын
kzread.info/dash/bejne/gaKpxs2waaeznLg.html
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.
Oh, I kept my eyes on KZread 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 😄
Thanks
@LeilaGharani
2 жыл бұрын
Thank you!
@hampshireoak
2 жыл бұрын
Your videos are always my first go to place, for I find that you help me though some quite complex functions. Thank You.
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?
Cool. Thanks Leila... In this pandemic COVID-19, would you share how to make sales assumption for, let's say 6-months? Thanks
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
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 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?
I need to get this office 365
Thank you for this! I tried several formulas, but I couldn't figure out how to account for repeated values.
@LeilaGharani
8 ай бұрын
Glad it helped!
You can use data validation in the number entry box as values between so that you will get an error inside. Is this correct?
those six dislikes are for people who just dont get it!! master work Leila!!!!!
@LeilaGharani
4 жыл бұрын
Thank you! Glad you liked it.
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!!
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 😁
Does this function can work with pivot tabel or excubed?
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
just wow .....
In filter function how can i get headings of each columns
How to more filter to this table to show top salaries for only finance section not all sections?
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
@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.
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: kzread.info/dash/bejne/gaKpxs2waaeznLg.html
secondary axis not find out in macbook air focan you pls help me