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
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
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 🖤
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 😊
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!
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 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
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 TH-cam channel where I do try to make some videos that hopefully will help people.. Cheers
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?
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...
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?
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, 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
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
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?
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.
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 , 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?
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.
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
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. ;-)
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: th-cam.com/video/wlW2UKml9CY/w-d-xo.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.
Hello Leila, I wanted to get, besides top 5 salary from the table, also the top 5 salary in each department. For this I added AND condition with the reference to a cell (I5) with data validation list with the departments. But it didn't work! I used this formula: =IFERROR(SORT(FILTER(TableSalary,(TableSalary[Yearly Salary]>=LARGE(TableSalary[Yearly Salary],G5))*(TableSalary[Department]=I5)),3,-1),"Type a smaller number") Can you tell me what I did wrong? Thank you!
With how crazy the stock market is you should consider sheet tips for tracking and evaluating stocks. Might be popular. Trending, sorting, profit, loss, cost.
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.
I figured it out Leila. I start with using the Filter function to grab all the Finance rows, then I put that inside a Sort to Sort the array from highest to lowest salary, then I wrap all of that inside a Take function to take the top 5 results
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
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).
Miracle....But, Microsoft team must combine this dynamic array functions in Office 365 with Excel ordinary versions like 2019. Otherwise, good presentations of dashboards can not be useful or visible for excel users whose using old versions. ✌
The way you explain make it too easy to understand. I have a question: I didn´t not use your values, so my top 5 appears six names because Top 5 and 6 are the same value. But if I use Function Sequence it only shows numbers 1 to 5 on Column F, no number 6. Is it possible to "repair this bug"? Cheers from Brazil!
@@Excelambda it works really fine. Do you know where I can find a explanation when i can use # or not? Eg, my table starts in a1, but if I use Rows(a1#) in a cell, returns a error, but the way you teached me works. Thanks again.
@@carlosborba3453 ROWS(A1#) works only if in respective cell you have a spilled array already there, a table with top left corner in A1 does not hosts an array. For a table excel provides those arrows that appear on top to select them, entirely, the diagonal arrow that appears when you hover the top left corner, or at the top of any column, left side for rows, etc.. Hope that helps. You're welcome👍
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.
I feel that too.
The dynamic array functions are so terrific I sometimes just sit and marvel at their performance. Good vid, Leila.
They are really great!
This is exactly what I'd been looking for until I came across your video. Thanks a lot!
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!
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
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 🖤
You solved yet another dilemma of mine with another clear and perfect video. Thank you so so so much!
Our pleasure!
Thanks a lot! The tutorial about FILTER function really helps me a lot in my assignment.
I learnt so much, so quickly - THANK YOU
Glad it was helpful!
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
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 ; )
Why I need this for 2016...ughhh
Leila, you are the best!
That was fun to watch. I can think of times to use these formulas in this way. Thank you 👏🏾
You are so welcome!
Perfect solution for a problem encountered of picking the 4 smallest numbers from a list and intentionally ignoring duplicates
FILTER & SORT are such useful new functions!
Yes they are!
Great example and great explanation! I liked how you used arrays {} in 2 arguments
Glad you enjoyed it!
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! 😘
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.
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).
Glad it was helpful!
How to do this without filter function on Excel 2016 ?
Thanks for your video Leila, awesome as usual
Thanks a lot, Leila for an amazing tutorial again!!!!
My pleasure! Glad you liked it.
Making the complex seem simple. Thanks!
Glad it was helpful Todd!
this was pure gold. Thank you.
Hi Leila!The New Dynamic Functions Certainly Make What Could Be Complex Formulas So Much Easier..Great Tutorial Thank You :)
So true Darryl!
awesome tutorial, hats off
Simply lovely, now i need to tweak this to sum a value by a group name.
That's so great , I like it .. amazing flexable filter .
Thanks for sharing Leila .
My pleasure 😊
U r real gem mam.
Amazing skills
I also wanted to learn advance excel,word and PowerPoint
Thanks a lot 😊
Excellent and powerful video!
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!
those six dislikes are for people who just dont get it!! master work Leila!!!!!
Thank you! Glad you liked it.
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.
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 :-{
Thanks.the video was pretty helpful.
Can you explain if we need top N based on more than one column.
Amazing technique ... thanks Liela
Thank you! Cheers!
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
hello leila, i am your new subscriber, thank you so much for tutorial
Thanks for subbing!
Another good one. Thanks Leila!
Glad you liked it Keisha!
excellent Leila ( as always ) many thanks
Very helpful thank you for making this video
Thanks Leila. Great video
You’re welcome 😊
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 TH-cam 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
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?
Wow,that's great. Very useful one.
Glad you think so!
Excellent
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...
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?
Great video!
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,
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
Very helpful. Thanks Leila....
You’re welcome Matt😊
Wow you are awesome as always ❤️
Thank you so much 😀
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
Leila, indeed you're amazing. Stay Blessed
Thank you so much Robert!
you are @Ms Leila simplly always brilant!
Laila, really amazing as usual from 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?
Oh, I kept my eyes on TH-cam 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 😄
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.
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 ?
Thank you for this! I tried several formulas, but I couldn't figure out how to account for repeated values.
Glad it helped!
Hi Leila. Nice tools. Can you please explain how can it be done with other versions of excel?
Oh my goodness, mind blown!😃
😁
U changed a lot
Thank you... These are some amazing stuffs
Glad you think so!
Very useful, thanks!
Thank you Chris!
I always love your videos. How to get 5 selected columns out of 10 columns using dynamic arrays.
th-cam.com/video/Onudkw9DMlU/w-d-xo.html
Excellent.
Thank you .
I already commented you’re absolutely amazing.
😘
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?
Excellent !!!!
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.
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?
Great 👍
Wonderful.
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
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 😁
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: th-cam.com/video/wlW2UKml9CY/w-d-xo.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.
Thank you for this, solved a big problem! Can we sort the data into separate columns by department type? And using VBA?
Cool. Thanks Leila... In this pandemic COVID-19, would you share how to make sales assumption for, let's say 6-months? Thanks
Thanks!
Hello Leila, I wanted to get, besides top 5 salary from the table, also the top 5 salary in each department. For this I added AND condition with the reference to a cell (I5) with data validation list with the departments. But it didn't work! I used this formula:
=IFERROR(SORT(FILTER(TableSalary,(TableSalary[Yearly Salary]>=LARGE(TableSalary[Yearly Salary],G5))*(TableSalary[Department]=I5)),3,-1),"Type a smaller number")
Can you tell me what I did wrong?
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.
With how crazy the stock market is you should consider sheet tips for tracking and evaluating stocks. Might be popular. Trending, sorting, profit, loss, cost.
Good idea. I'll add it to my list. Thanks for the suggestion!
Thank you Leila! Are there any workarounds for excel 2010?
Good evening there a error with 2 person with the same values in the function of the =sequence
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.
I figured it out Leila. I start with using the Filter function to grab all the Finance rows, then I put that inside a Sort to Sort the array from highest to lowest salary, then I wrap all of that inside a Take function to take the top 5 results
👍
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
Instead of doing this yearly, is it possible to do this weekly?
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.
Miracle....But, Microsoft team must combine this dynamic array functions in Office 365 with Excel ordinary versions like 2019. Otherwise, good presentations of dashboards can not be useful or visible for excel users whose using old versions. ✌
True Emre, different versions will remain an issue for some time.
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.
The way you explain make it too easy to understand. I have a question: I didn´t not use your values, so my top 5 appears six names because Top 5 and 6 are the same value. But if I use Function Sequence it only shows numbers 1 to 5 on Column F, no number 6. Is it possible to "repair this bug"? Cheers from Brazil!
Hi, you can use instead of SEQUENCE(I5) , SEQUENCE(ROWS(I8#)) (replacing in seq function top value with the nr. of rows of the result array itself)
@@Excelambda you are really GOOOOOOD!!! Thank you!
Hi, this is how to fix it, =IF(G5>COUNT(TableSalary[Yearly Salary]),"Too large",SEQUENCE(ROWS(G8#)))
@@Excelambda it works really fine. Do you know where I can find a explanation when i can use # or not? Eg, my table starts in a1, but if I use Rows(a1#) in a cell, returns a error, but the way you teached me works. Thanks again.
@@carlosborba3453 ROWS(A1#) works only if in respective cell you have a spilled array already there, a table with top left corner in A1 does not hosts an array.
For a table excel provides those arrows that appear on top to select them, entirely, the diagonal arrow that appears when you hover the top left corner, or at the top of any column, left side for rows, etc.. Hope that helps. You're welcome👍