Create an Interactive Top N Report in Excel (includes duplicate values) with Filter Function
HTML-код
- Опубликовано: 6 май 2020
- 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
Grab the file I used in the video from here 👉 pages.xelplus.com/top-values-file
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
The dynamic array functions are so terrific I sometimes just sit and marvel at their performance. Good vid, Leila.
They are really great!
Hi! Miss Leila Gharani, You will never forget a person who came to you with a torch in the dark.
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
Wow, that's great. Good luck with your journey!
Thanks a lot! The tutorial about FILTER function really helps me a lot in my assignment.
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 😊
Thank you Peter! 😃
It was great Leila, thank you. I like dashboards and graphs the most. I finished your dashboard course, and learned a lot from it.
That's great! I'm glad you liked the course.
@@LeilaGharaniThankyou thankyou so much
I really appreciate what you do to help us become excel masters
awesome tutorial, hats off
this was pure gold. Thank you.
Perfect timing … I think I will be able to use this concept but in a different situation. Thank you … excellent presentation … no surprise (again).
Glad it was helpful!
Thanks a lot, Leila for an amazing tutorial again!!!!
My pleasure! Glad you liked it.
Thanks for the cool formulas, Teammate Leila !!!!
Thanks Teammate 😄
@@LeilaGharani please alternative solution for those who not having Excel 365 mam
Great example and great explanation! I liked how you used arrays {} in 2 arguments
Glad you enjoyed it!
That was fun to watch. I can think of times to use these formulas in this way. Thank you 👏🏾
You are so welcome!
Excellent and powerful video!
Making the complex seem simple. Thanks!
Glad it was helpful Todd!
Great video!
That's so great , I like it .. amazing flexable filter .
Thanks for sharing Leila .
My pleasure 😊
excellent Leila ( as always ) many thanks
Very helpful thank you for making this video
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 🖤
I really enjoyed your course on VBA! I also enjoyed your course on dynamic arrays! Oh, and I really enjoyed your course on dashboards!
Awesome! Thank you for the support of my courses! 😘
Excellent, amazing
Another good one. Thanks Leila!
Glad you liked it Keisha!
Thanks Leila. Great video
You’re welcome 😊
How to do this without filter function on Excel 2016 ?
Thanks for your video Leila, awesome as usual
Hi Leila!The New Dynamic Functions Certainly Make What Could Be Complex Formulas So Much Easier..Great Tutorial Thank You :)
So true Darryl!
Beautiful! Good addition to control the input to prevent wild stuff.
Thanks Oz! No wild stuff allowed :)
Excel On Fire, it’s all real OZ, look how finance person is the first in the top 5 😉
@@LeilaGharani and Excel On Fire, no wild stuff allows ; )
Excellent !!!!
Excellent
I already commented you’re absolutely amazing.
😘
Thanks.the video was pretty helpful.
Can you explain if we need top N based on more than one column.
Simply lovely, now i need to tweak this to sum a value by a group name.
Amazing technique ... thanks Liela
Thank you! Cheers!
Wow,that's great. Very useful one.
Glad you think so!
Thank you... These are some amazing stuffs
Glad you think so!
U r real gem mam.
Amazing skills
I also wanted to learn advance excel,word and PowerPoint
Thanks a lot 😊
Wow you are awesome as always ❤️
Thank you so much 😀
Excellent.
Leila, indeed you're amazing. Stay Blessed
Thank you so much Robert!
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
Thank you for this, solved a big problem! Can we sort the data into separate columns by department type? And using VBA?
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!!
Great to hear Gary! I hope you'll enjoy the course.
FILTER & SORT are such useful new functions!
Yes they are!
Wonderfully done! Informative
Thank you!
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?
Very useful, thanks!
Thank you Chris!
Laila, really amazing as usual from you
Thank you .
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?
Very helpful. Thanks Leila....
You’re welcome Matt😊
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...
Thanks!
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 ?
hello leila, i am your new subscriber, thank you so much for tutorial
Thanks for subbing!
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!
Thanks for that Tony!
Good video and great excel skills...impressive
Glad you liked it!
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?
Oh my goodness, mind blown!😃
😁
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.
Great 👍
Perfect solution for a problem encountered of picking the 4 smallest numbers from a list and intentionally ignoring duplicates
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!
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 ?
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")
Oh, I kept my eyes on RUclips to be the first who hit the like but still I am late. I am 2nd it’s 2am 🙃
Thank you Leila.
Wow, thank you for the like! Now, get some rest 😄
U changed a lot
Why I need this for 2016...ughhh
wow its so amazing 👍👍👍👍
Thank you! Cheers!
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
thanks
Cool. Thanks Leila... In this pandemic COVID-19, would you share how to make sales assumption for, let's say 6-months? Thanks
Nice
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
Looks interesting; I'll have a play. Wish I could get my head around the LET() function :-{
I always love your videos. How to get 5 selected columns out of 10 columns using dynamic arrays.
ruclips.net/video/Onudkw9DMlU/видео.html
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 RUclips channel where I do try to make some videos that hopefully will help people..
Cheers
That is awesome! Good luck with your channel!
@@LeilaGharani Thank you
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.
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?
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?
For search box, we can use data validation with max or count function, to prevent user entering large number
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.
@@indryfitriani I guess no, because validation will allow to enter specific number,else it will give you error
Thanks
Thank you!
Your videos are always my first go to place, for I find that you help me though some quite complex functions. Thank You.
Thank you for this! I tried several formulas, but I couldn't figure out how to account for repeated values.
Glad it helped!
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!!
those six dislikes are for people who just dont get it!! master work Leila!!!!!
Thank you! Glad you liked it.
Does this function can work with pivot tabel or excubed?
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
See this video of Leila: ruclips.net/video/Onudkw9DMlU/видео.html
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?
You can use data validation in the number entry box as values between so that you will get an error inside.
Is this correct?
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).
Do they have 365 with dynamic arrays too? If not, it will not work because they do not have the new functions.
I need to get this office 365
In filter function how can i get headings of each columns
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'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. ;-)
Well, I'm looking forward to seeing you inside the course. Full disclosure: It only has white backgrounds though 😁
Which office version this option is avaliable ??
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
Perfect for my requirements
But as a 365 corporate account holder, not sure when we will be getting these functions.
According to Microsoft it should be in July. So, just 2 more months.
just wow .....
How to more filter to this table to show top salaries for only finance section not all sections?