Top 10 with formulas in Excel | Automatically calculate Top 10 as data changes | Excel Off The Grid
HTML-код
- Опубликовано: 15 окт 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegri...
★ Download the example file ★
exceloffthegri...
★ About this video ★
It is easy to create a top 10 list when working with sorted data, Auto Filter, Tables and Pivot Tables. However, when creating a top 10 with formulas on a non-sorted dataset, things become a little bit tricky. In this video, I will show you exactly how to do it.
★ Learn more about the functions used in this video ★
INDEX / MATCH - exceloffthegri...
Dynamic Arrays - exceloffthegri...
FILTER - exceloffthegri...
SORT - exceloffthegri...
SEQUENCE - exceloffthegri...
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegri...
★ Where to find Excel Off The Grid ★
Blog: exceloffthegri...
Twitter: / exceloffthegrid
#MsExcel #ExcelOffTheGrid
![Oscar Maydon x Fuerza Regida - Tu Boda [Official Video]](http://i.ytimg.com/vi/_ymicn0_GYc/mqdefault.jpg)
Thank you very much. I am able to understand as well as use in my excel sheet. Hats Off.🙏😊
Just want to say this is the best video I have watched using the top 10 formula. As you did each step I was able to follow along and understood the process. Thank you.
Thanks Dan - I'm please it was helpful... goal achieved :-)
Your explanation worked wonders, thanks a bunch!
Glad it helped! 😊
Thank you. This was well explained and very helpful
You're very welcome!
This helped immensely! Thanks so much!
Glad it helped :-)
Congrats in getting the RUclips channel going. Happy to be the 36th subscriber!!
Thanks, it’s great to have you on board 😀
Thank you very much! I really enjoyed your video. And it was very useful as well.
Thanks Mihaly - I’m glad it help you out.
Hi. Great video. What if you have multiple revenue lines for multiple customers and you want to be able to add these up within the formula to be considered in the top 10 customers or bottom 10 customers?
If you’ve got Excel 365, it should be possible. But in older versions of Excel I’m not sure it’s possible. If it is, then it would be a crazy long formula.
The easiest option would be to use formulas to get the total of each customer as a first step. Then calculate the top 10.
Brilliant video thank you. 1 Question, in my series of data i have 2 criteria's i would like to work with one Site and one Date. How do i add an additional criteria to the formula?
Are you using a dynamic array or non-dynamic array approach?
Hi - thanks for this video, it definitely helps understanding some stuff. I did use an index match formula before, but I pretty much copied it blindly from some forum without fully understanding what it does exactly. However, now I've watched your video trying to troubleshoot the issue with duplicate values, and while it removed one of them, I still have a duplicate in my top 5. I've triple checked my formula but it is the same as yours (with adjusted ranges to match my own data ofc). Any advice you can give me?
Additional info 1: I tried using the dynamic array, but that only gave me a seemingly random name, and another random name in the cell to the right of it. No correct range, not even a value in the right column.
Additional info 2: my file is basically a large overview. One sheet contains over 600 rows and 11 columns worth of data. The next sheet has a much smaller overview table where everything is sorted per category, showing all kinds of statistics. It's on this table that I want to base my formulas for a top 5 list, but perhaps Excel has issues nesting all those formulas at the same time? I don't know TBH... All I see is that it doesn't work :/ Any help would be much appreciated!
I’m not aware if anything inherent in the calculation which would cause those issues.
Which makes me think it’s a data issue. By which I mean there are spaces or other characters which make the names different even if they look the same to the eye.
Download the example file from the video and test it with a smaller dataset which contains the duplicate values. That might help.
@@ExcelOffTheGrid Thanks for replying. The dataset gets its data from another list which uses data validation, so there is no way that spacing or typos etc. could be causing this issues. I will try to replicate my file with the example data and let you know if it made a difference.
@@jefvl - if it’s not a data issue and the sample file doesn’t help. Send me a message on my the contact page of my site, and I’ll see if I can work out what the issue is.
I wouldn’t put too much faith in Data Validation - somebody can paste special values over the top, and the validation process doesn’t get triggered.
Thank's bro
No problem :-)
Great video, thank you for sharing! Could you please advise, would it be possible to add a slicer instead of the drop down list? I am building a dashboard for financial reporting and I figured it would be more consequent with a slicer.
You would need to use a slicer connected to a PivotTable. Construct it in such a way that when the slicer is clicked a single cell value is shown by PivotTable. Then use that single cell within the formula as the selection criteria.
This post explains the concept of how to do it: www.myonlinetraininghub.com/use-excel-slicer-selection-in-formulas
@@ExcelOffTheGrid Thanks for the quick response! Slicers work only with PivotTables, noted.
My Top10 dashboard started working well with the slicer after loosing the =GETPIVOTDATA.. and referencing the PivotTable cells instead. :)
Thanks again!
I have excel for mac, I tried every possibility do crtl shift enter to make the array formula but nothing worked, any idea or bypass?
Thanks
I think it depends on which version you’ve got, as I believe it changed.
Try:
Ctrl+Shift+Return
Cmd+Shift+Enter
Cmd+Enter
Do any of those work?
Here how we can get the top three values from each location?
The approach is the same whether you want 3, 10 or 100. Just watch the video and follow the same principles.
Very useful and informative it helped me a lot...
I have two side notes for supportting your channel, as I see you are organized well and you work in a good way:
1- Regarding your website page [exceloffthegrid.com/creating-a-top-10-using-formulas]; I couldn't play the vedio there whever I was directed to it through google search(I dont't know if it is done for me only or you need to fix), I just inform you for helping others wo will come to you through google search.
2- You have mentioned the file name in the description for other post, the correct one is [0015-Top-10-using-formulas.zip]
hope for you and your channel all the post, keep posting :)
Hi Ahmed - Thanks for the feedback, I appreciate it.
1) I've tested the video on Chrome, Edge and Firefox, and it worked. So looks like it might be a temporary glitch
2) Ah - yes you're right. Oops! Thanks for letting me know, now fixed :-)
♥
What if I wanted to add the location to my list of top 10
To include the Location in the output:
1) If using traditional formulas: Since the customer name is unique, you could do a INDEX/MATCH or VLOOKUP to calculate the Location for that customer.
2) If using dynamic arrays: change the last argument of the INDEX function to be {1,2,3}, so that it picks up all the 3 columns (or even {1,3,2} if you want the Location as the last column).
what if customer has duplicate names??
Then you'll need to use a unique reference, e.g. Customer Number instead.
👉🤯👈
Awesome video on how to build complex formulas by building them into separate parts and them combining these parts! nice 👌👌
Thanks Carlos. 😀