Summary
Keywords
Full Transcript
🔥 Join 500,000+ professionals in our courses here 👉 https://link.xelplus.com/yt-d-all-courses Master Excel ranking formulas to create perfect reports, even with duplicate values. In this tutorial, we move beyond the standard RANK function to show you how to perform dense ranking—ranking values without skipping numbers in the sequence. You’ll also learn how to use TEXTJOIN and SUMPRODUCT to list all names in a "Top 3" report, even when there are ties for 2nd or 3rd place. ⬇️ DOWNLOAD the workbook here: https://www.xelplus.com/excel-rank-without-skipping-numbers/#download This video covers the RANK function in Excel, providing clear guidance on ranking values in both ascending and descending order, including handling duplicate values without skipping numbers in the sequence. 🔑 Key Points: - Ranking: Learn how to rank sales managers based on their sales numbers, handling scenarios where two managers have the same sales figure. - Understanding RANK Function: Get to grips with the RANK and RANK.EQ functions, exploring their use for maintaining the original order of data while ranking in a separate column. - Handling Duplicates: Find out how to rank duplicate values without skipping numbers, ensuring a continuous sequence in your ranking. - Complex Formula for Ranking: Discover a more intricate formula involving SUMPRODUCT and COUNTIF, ideal for ranking without skipping numbers in the sequence. - Creating a Top 3 Report: Learn how to generate a report showing the top three sales managers, including all those tied for a position, using the TEXTJOIN function. - Detailed Explanation: Benefit from a thorough walkthrough of the formulas used, providing clarity on each step of the ranking process. 0:00 How to use the Excel RANK function 0:51 RANK Function & RANK.EQ 3:30 RANK duplicates but don't skip numbers in between 6:51 Top 3 Report 8:51 SUMPRODUCT & COUNTIF Excel Array formula explained You might need to create a top 10 or top 3 report in Excel. For example you'd like to get the top 3 values but there are two categories that have the exact same value and both are considered number 2. How can you show both categories as number 2 and not just the first one? VLOOKUP will not help here, because it will return the first match. You'd like ALL matches returned. The solution uses the SUMPRODUCT function together with the Excel COUNTIF function to get the ranking. We then use the TEXTJOIN and IF functions together as an array to get the category names ranked in ascending order. LINKS to related videos: Excel TextJoin Function - https://youtu.be/TMZEUlFGp1U Excel Lookup Formulas Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxpMnh_KKfAgp5DF9ydawmi ★ My Online Excel Courses https://www.xelplus.com/courses/ ➡️ Join this channel to get access to perks: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join 👕☕ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/ 🎓 Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/ 🎥 RESOURCES I recommend: https://www.xelplus.com/resources/ 🚩Let’s connect on social: Instagram: https://www.instagram.com/lgharani LinkedIn: https://www.linkedin.com/company/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
Continue this lesson in the app
Install CourseHive on Android or iOS to keep learning while you move.
