Excel Fun with New Functions
February 25, 2020
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
The new Excel functions and dynamic arrays are finally available for everyone who's using Excel for Office 365, with monthly updates. Try them out -- they're great!
I used dynamic arrays to make dependent drop downs, and it was really easy. This animated screen shot shows how they work, with lists that change almost magically.
In D7, the new SORT and UNIQUE functions create a dynamic array of region names
In F7, the new SORT and FILTER functions create a dynamic array of employees for the selected region
Data Validation creates the drop downs. Refer to the dynamic array starting cell, followed by the new Spill Operator, #: =$D$7#
NOTE: You can get more details on my Contextures site, where there's a step-by-step video, and written instructions, with lots of screen shots.
Have you ever written a formula to get a value from one cell, unless there's something in another cell?
For example, this formula returns the value from B2, unless there's something entered in cell A2:
=IF(A2="", B2, A2)
But what happens if both cells are empty? The result is a zero, like the one in cell C4.
If you want a cell that looks empty instead, use the T function:
=IF(A2="", T(B2), A2)
NOTE: My weekly planner Excel template also uses T, to avoid zeros in the planner cells.
Here are a few Excel-related articles that you might find useful or interesting.
Copy Paste: Could you get through the day without Excel's copy and paste commands? Those computer commands were created by the late Larry Tesler, when he worked at Apple. Read Luke Dormehl's tribute to this computer pioneer. (Level-All)
Large Files: On the TurboFuture blog, Kevin Languedoc shares 10 tips for working with large files in Excel. Should Power Query be higher in the list?. (Level-Int/Adv)
Excel Humour: See what people said about Excel recently. Are you in Excel Hell or Pivot Table Purgatory?
Happy Pancake Day! Shrove Tuesday is always 47 days before Easter, and there's an Easter date calculation page on my Contextures site, if you want to plan for next year. We enjoy our pancakes with Canadian maple syrup, so let's hope there isn't another maple syrup heist!
In this photo, our grandson was about 4 years old, and a budding food photographer - long before he heard about Instagram!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200225ctx.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.
Last updated: March 2, 2020 9:50 AM