Contextures News

Excel Sorting Gone Wrong

Mar 1, 2016

See how to count unique items, chart tips, and more, in this week's Excel news.

 -  Debra -  ddalgleish @
     Visit my Excel website for many more tutorials and videos.

Formula Sorting Problems

Be careful how you click, when building a formula, or you could end up selling things for the wrong price. To show you what happened, I made a sample order form, and used VLOOKUP to get the product prces. At first, all the data was correct. But then I sorted the list, and suddenly all the prices were wrong. Yikes!

What had caused things to go so horribly wrong? Well, while I was creating the formula, Excel had "helpfully" added unnecessary sheet names to the cell references, and that was causing the problem. Watch this short video to see what went wrong, and how to make the simple fix.

Quick Date Calculations

Do you ever use the EOMONTH (End of Month) function? It's perfect for calculating the first or last day of a month. For example, get the first day of next month: =EOMONTH(TODAY(),0)+1

Just for fun, I used EOMONTH to create a list of future Leap Year Days. The starting date is 2/29/2016 in cell B1. In cell B2, the EOMONTH formula refers to that date, and adds 4 years (48 months): =EOMONTH(B1,48)

I copied my formula down a few rows, and formatted the dates to show the weekday. You can see the results in the screen shot below, and there are more EOMONTH examples on my website.

Excel Articles

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

Unique Count -- Roger Govier explains how to set up a pivot table in Excel 2013 or later, so you can show a Distinct Count. For example, if there were 100 orders, how many distinct (unique) products were sold?. (Level - Intermediate)

Chart Tips -- Sometimes the data needs a bit of help. Ann K. Emery shows four examples of how to use a storytelling approach in a chart. (Level - Intermediate)

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog. And for a bit of humour, read my weekly collection of Excel tweets.

Excel Training

  • Macros for Beginners: Recently I learned that Allen Wyatt, a long-time Excel trainer and publisher, is offering a macro course for absolute beginners. It's online video training, and I was impressed with the content. The course is designed for absolute beginners, so don't worry, you won't be learning any advanced programming skills. You'll focus on the basics, and learn how to use macros to get your Excel work done faster and more efficiently.
    Registration is only open for a couple of weeks, and there is a 20% discount if you register by March 10th.

The Good Old Days

Thanks for your well wishes, and I'm feeling much better this week. But, in a strange coincidence, I found a yearbook that my Grade 5 class made (we won't say how long ago). Apparently there was an outbreak of mumps that February, and 11 of my classmates were out sick. Not me though -- I only got sick during the holidays. Sigh.

Two things I noticed -- the purple ink of the ditto machine copy (do you remember that smell?), and the large class size. It was a split grade 4/5, so that teacher had her hands full!

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:

Recommended Excel Products


Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.




Last updated: May 24, 2021 7:30 PM