Show Rank in Excel
November 19, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Excel has a RANK function that lets you show where each value ranks, in a list of numbers. For example, use this formula to rank numbers from smallest to largest:
There are more RANK function examples and videos on my Contextures site, including ways to break ties in RANK, if necessary.
Did you know that you can rank numbers without using a formula too? Just use a pivot table instead.
In the screen shot below, the Sales amounts are sorted in descending order. The Cases field is in the Values area twice, showing the number of cases sold, and the Rank of each number.
The Rank column makes it easy to see where the Cases are in a different order from the the Sales amounts.
There are detailed instructions and a video for Pivot Table Ranking on my Contextures website.
Here are a couple of new items on my Contextures site and blog.
Happy Face With Colour Change: Enter a number between 1 and 100 on the worksheet (data validation limits what can be entered). Based on your number, an event macro adjusts the smile's curve, and the colour of the face. Get this sample file on my Excel Sample Files page - UF0050
Here are a few Excel-related articles that you might find useful or interesting.
Data Viz: Ideas from chart expert, Alberto Cairo on how to prepare and interpret charts. Good tips at the end of the article. (Level-All)
Tips: On GitHub, MaryJo Webster has a Data Journalism page, with a long list of training resources. Do a search for "Excel" on that page, to find Excel tip sheets and other Excel resources. Who could resist "Torturing Excel into doing statistics"? (Level - All)
Excel Tweets: See what people tweeted about Excel this week. Does Excel teach you patience?
I don't have much luck with indoor plants, but this little succulent has survived for almost a year. It's probably time to repot it, because the plant topples over when it's watered now. I put a rock in its pot, to hold it down, and now it looks like that iconic painting, The Jack Pine, by Canadian artist, Tom Thomson. That painting hangs on the wall in every school across the country, as far as I know! (Or maybe that was just in the old days.)
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20191119ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: November 18, 2019 4:14 PM