Create an Interactive Top N Report in Excel (includes duplicate values) with Filter Function
Vložit
- čas přidán 6. 05. 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
The dynamic array functions are so terrific I sometimes just sit and marvel at their performance. Good vid, Leila.
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
Wow, that's great. Good luck with your journey!
Hi! Miss Leila Gharani, You will never forget a person who came to you with a torch in the dark.
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! 😃
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 🖤
Thanks a lot! The tutorial about FILTER function really helps me a lot in my assignment.
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
awesome tutorial, hats off
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.
this was pure gold. Thank you.
I really appreciate what you do to help us become excel masters
Thanks for the cool formulas, Teammate Leila !!!!
Thanks Teammate 😄
@@LeilaGharani please alternative solution for those who not having Excel 365 mam
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! 😘
That was fun to watch. I can think of times to use these formulas in this way. Thank you 👏🏾
You are so welcome!
That's so great , I like it .. amazing flexable filter .
Thanks for sharing Leila .
My pleasure 😊
Great example and great explanation! I liked how you used arrays {} in 2 arguments
Glad you enjoyed it!
Excellent and powerful video!
excellent Leila ( as always ) many thanks
Making the complex seem simple. Thanks!
Glad it was helpful Todd!
Great video!
Very helpful thank you for making this video
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!
Another good one. Thanks Leila!
Glad you liked it Keisha!
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 ; )
Simply lovely, now i need to tweak this to sum a value by a group name.
Leila, indeed you're amazing. Stay Blessed
Thank you so much Robert!
U r real gem mam.
Amazing skills
I also wanted to learn advance excel,word and PowerPoint
Thanks a lot 😊
Thanks Leila. Great video
You’re welcome 😊
Excellent, amazing
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.
Laila, really amazing as usual from you
Wow you are awesome as always ❤️
Thank you so much 😀
Amazing technique ... thanks Liela
Thank you! Cheers!
Thank you... These are some amazing stuffs
Glad you think so!
Excellent !!!!
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
Wow,that's great. Very useful one.
Glad you think so!
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.
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?
Thank you for this, solved a big problem! Can we sort the data into separate columns by department type? And using VBA?
FILTER & SORT are such useful new functions!
Yes they are!
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!
hello leila, i am your new subscriber, thank you so much for tutorial
Thanks for subbing!
Very useful, thanks!
Thank you Chris!
Very helpful. Thanks Leila....
You’re welcome Matt😊
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?
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...
Perfect solution for a problem encountered of picking the 4 smallest numbers from a list and intentionally ignoring duplicates
Excellent.
Thank you .
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!😃
😁
Why I need this for 2016...ughhh
you are @Ms Leila simplly always brilant!
Oh, I kept my eyes on CZcams 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 😄
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.
Thanks!
Wonderful.
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?
Hi Leila. Nice tools. Can you please explain how can it be done with other versions of excel?
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 :-{
Great 👍
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
U changed a lot
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")
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 CZcams 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
Thank you Leila! Are there any workarounds for excel 2010?
I always love your videos. How to get 5 selected columns out of 10 columns using dynamic arrays.
czcams.com/video/Onudkw9DMlU/video.html
thanks
those six dislikes are for people who just dont get it!! master work Leila!!!!!
Thank you! Glad you liked it.
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
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: czcams.com/video/Onudkw9DMlU/video.html
Nice
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?
Cool. Thanks Leila... In this pandemic COVID-19, would you share how to make sales assumption for, let's say 6-months? Thanks
I need to get this office 365
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 😁
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.
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?
Thank you for this! I tried several formulas, but I couldn't figure out how to account for repeated values.
Glad it helped!
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.
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.
just wow .....
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?
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 Vid! BTW, it's now called Microsoft 365. :)
Oops. Still getting used to the new names 😊
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.
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?
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.
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: czcams.com/video/wlW2UKml9CY/video.html
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.
In filter function how can i get headings of each columns
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