Contextures News 20150707

Pivot Table Rank in Excel

July 07, 2015

Use Rank in a Pivot Table, Free Excel Add-ins, 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.

Summer Schedule

Now that it's summer here in Canada, I'll publish the newsletter once every two weeks, so you will get the next issue on July 21st.

Use Rank in a Pivot Table

When you add values to a pivot table, you can leave the results in their default format, showing a simple sum or count. However, in some cases, it helps to change how the numbers are shown, so they are easier to understand at a glance.

I've added a new video to my website that shows how to make quantity and revenue comparisons easier, in a sales summary pivot table, by using the Rank calculation. (NOTE: The Rank function is available in pivot tables in Excel 2010 and later versions.)

In the screen shot below, you can quickly spot the numbers that are out of order in the Rank column, but it takes more work to spot them in the Cases column.

pivot table ranking

Free Excel Add-Ins

On its own, Excel can do amazing things, but it can't do everything that people want. To fix that, many Excel add-ins have been created, and some of them are free, including some of my favourites, like Name Manager. It has features that extend beyond what the built-in Name Manager can do.

I've updated the list of my favourite free Excel add-ins on my website, and if you know of any others, please let me know, so I can check them out. There are some real gems in that list -- for example, the FindLink add-in, by Bill Manville, has saved me hours of time, when trying to find a hidden link in an Excel file. I wrote about it a couple of years ago, and highly recommend it, if you ever have a problem with finding external links in a file.

link finder

Excel News

Here are a couple of Excel articles I read recently, that you might find interesting and useful.

Slicers Intro -- Chandoo wrote a great introduction to Slicers, so if you haven't used that feature yet, or aren't familiar with all the things that Slicers can do, be sure to read it, and download the sample file. (Level - Basic)

Bullet Charts -- Jon Peltier has updated and simplified his instructions for creating bullet charts in Excel. If you make other types of complex charts, you can save time with Jon's Excel Charting Utility. (Level - Intermediate)

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog.

Excel Announcements

Ends Friday, July 10th -- Jan Karel Pieterse is having a summer sale -- get 25% off his Excel utilities – RefTreeAnalyser (easy auditing of formulas) and Excel File Remediation Utility (helps you salvage Excel models with problematic content accumulated during a long editing history)
To get your 25% discount, use this coupon code: JKPADS-Summer2015

Limited Seats -- Power Query experts Ken Puls and Miguel Escobar have opened registration for their next online Power Query training. On August 12th and 13th, you'll have live online training (4 hours per day). Then attend a live online follow-up Q&A session, a week later (Aug. 20). The class size is limited, so register early!

Photo of the Week

To celebrate Canada Day last week, we went for an overnight visit to Niagara Falls. It was a cloudy day, but the view from our hotel room was fabulous, as you can see in the photo below. There were fireworks that night, so we had a great view of those too! If you celebrated Canada Day, or Independence Day, I hope you enjoyed some time with family and friends.

weekly photo

And Finally -- For a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish

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.


Last updated: December 29, 2019 4:00 PM