Excel Flexible Formula Trick
July 3, 2018
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!
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.
By linking to a cell, you can quickly see different results, without changing the formula.
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!
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)
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!
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.
dsdalg @ gmail.com
Last updated: June 29, 2018 2:12 PM