Contextures

Contextures News 20191119

Show Rank in Excel

November 19, 2019

Printing tips, show rank, change a Happy Face, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Rank Numbers

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:

=RANK(B2,$B$2:$B$11, 1)

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.

  • After you add a field to a pivot table's Values area, right-click on any number in that field.
  • Then, point to the Show Values As command, and click on one of the Rank options
    • Rank Largest to Smallest, OR
    • Rank Smallest to Largest
  • Excel will ask you to select a Base Field for the ranking, so choose one of the Row Fields from the drop down list.

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.

show rank in a pivot table

New on Contextures

Here are a couple of new items on my Contextures site and blog.

Printing Tips: Save time with these Excel printing tips -- videos show the steps. Or, if you prefer written instructions, go to my Contextures website.

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

happy face gauge

Excel Articles

Here are a few Excel-related articles that you might find useful or interesting.

Online: Sign up for the free Excel Online Summit. This 5-day event starts Dec 9th, with sessions by Bill Jelen, chart expert Jon Peltier, and many more!

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?

Also see: My Excel Products || Previous Issues

Not the Jack Pine

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.)

weekly photo

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.

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Related Links

RANK function examples

Pivot Table Ranking

Excel printing tips

Excel Sample Files - Smiley Face

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: November 18, 2019 4:14 PM