Contextures

Contextures News 20180703

Excel Flexible Formula Trick

July 3, 2018

Formula trick, spreadsheet history, 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: You'll get the next newsletter on July 17th -- we're on the summer schedule now.

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

Flexible Formula Trick

Instead of typing an argument number into a formula, use a cell reference. For example, in the RANK function, type a zero in cell E1, and the items are ranked in descending order. Or, type a 1, to show the rank in ascending order.

=RANK(B2,$B$2:$B$6,$E$1)

By linking to a cell, you can quickly see different results, without changing the formula.

Order for RANK function

For more examples, see how to use drop downs for the CONVERT function units, and choose options for SUBTOTAL and AGGREGATE functions.

Multiple Dependent Drop Downs

Recently, I showed you how to create fancy drop down lists, where you select from one list, and the next list only has related items. Here's an even fancier version of that technique, so you can set up multiple dependent drop down lists. You'll just need 2 tables and 3 named ranges - no macros.

There are written instructions and a sample file on my website, thanks to Roger Govier. And if you'd rather watch the steps, I've just made a new video to show all the setup steps. I also explain how the named range formulas work, but you can skip that part, if you don't need to know!

multiple dependent drop down lists

Excel Articles

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

Formulas - Chandoo posted a list of 100+ Excel formulas, for every situation. Are there examples there that will help with your work? (Level - All)

Charts - On the Flowing Data blog, Nathan Yau explains why people make bad charts, and what you can do about it. (Level - All)

History - See an early spreadsheet in this April 1984 episode from the BBC Computer Literacy Project. There are lots of other episodes there too, and old software programs, like Space Invaders, that run in your browser. There's even an Abacus program, if you're tired of using Excel! (Level - All)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Humanitarian Award

School has ended for the summer, and we're very proud of our 11-year-old grandson, who won his school's Humanitarian Award this year. His school celebrated its 100th anniverary in 2012, and the gym outfits in this old news photo reminded me of the ugly blue bloomers that we had to wear in high school gym class!

weekly photo

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

NOTE: For the online version, paste this URL into your web browser: http://www.contextures.com/newsletter/excelnews2018/20180703ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
dsdalg @ 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.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: June 29, 2018 2:12 PM