Contextures

Contextures News 2021-05-18

Excel Double-Sorted Drop Down List

May 18, 2021

Show inch marks, double-sorted drop down list, 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.

<== Previous Newsletter          Next Newsletter ==>

Thank you for reading the news, and you'll get the next email on June 1st.

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

Double-Sorted List

In this data validation drop down list, the 3 most recently ordered products are at the top. The rest of the product names are below those, in alphabetical order.

This technique uses the new functions, SORTBY and MAXIFS, which are available in Excel 365. See more Spill Function Examples on my Contextures site.

drop down list with latest items at top

The source list for the data validation drop down uses this formula, which sorts the top items first, then sorts the remain products alphabetically, by product name:

=SORTBY(tblProd[Product], tblProd[Sort],1, tblProd[Product],1)

You can get the sample file, and all the setup details, on the Latest Used Items at Top page on my Contextures site.

Custom Number Format

Here's an obscure Excel problem that I ran into last week. I tried to create a custom number format (link to Microsoft site) for inches, using the double quote mark: 0"

Excel wouldn't allow that, so I used two single quote marks instead. You can see the results in column A, below.

Another option is to use a different Unicode character (Wikipedia). In column C, I used the Modifier Letter Double Prime character (02BA) from the Arial font.

Which custom format do you prefer?

There are more custom number format examples on the Excel Rounding Functions page of my Contextures site.

And if you want a deep dive into ASCII and Unicode quotation marks, take a look at this article by Dr. Markus Kuhn, from the University of Cambridge

inch marks in custom number format

Excel Articles

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

Dashboard: Chandoo started a new series on his blog - How to create a fully interactive Project Dashboard with Excel. Part 1 shows how to set up a Project Gantt Chart, and more lessons are coming.(Level - All)

Excel Weekend: Sign up for the free online sessions at the upcoming Excel Weekend 7. There are English, Portuguese and Spanish sessions, and the event runs from June 11th - 13th. (Level - All)

Also see: Previous Newsletter Issues

Garden Plans

Just in case the garden centres don't open this spring, I ordered an "Easy to Grow Perennial Garden" kit from Costco. It arrived last week, with details on the 8 different plants that are in the kit.

To plan where everything should go, I made a garden map in Excel, and that's why I wanted to use the inch mark! Only the kids know how to cook and garden in metric. ;-)

Planning was the fun part -- the planting took a lot more effort!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210518ctx.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
ctxdebra @gmail.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.

<== Previous Newsletter          Next Newsletter ==>

contextures newsletter info

 

Last updated: June 10, 2021 3:34 PM