Contextures

Contextures News 20200225

Excel Fun with New Functions

February 25, 2020

Dynamic arrays, Excel's new functions, 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: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

New Excel Functions

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 cell F2, select a region (based on dynamic array that starts in D7)
  • In cell F7, dynamic array shows employees for the selected region
  • In cell G2, the drop down shows that list of employees

set up data validation

How It Works

In D7, the new SORT and UNIQUE functions create a dynamic array of region names

=SORT(UNIQUE(A2:A11))

In F7, the new SORT and FILTER functions create a dynamic array of employees for the selected region

=SORT(FILTER(A2:B11, A2:A11=F2),2)

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.

set up data validation

Quick Tip - Empty Cells

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.

T function with IF

Excel Articles

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)

Office App: Microsoft released a new Office app for iOS and Android. The app combines Excel, Word and PowerPoint, with new features added. Does this remind you of Microsoft Works?

Excel Humour: See what people said about Excel recently. Are you in Excel Hell or Pivot Table Purgatory?

Also see: My Excel Products || Previous Issues

Pancake Day

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!

weekly photo

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.

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

contextures newsletter info

 

Related Links

Dependent drop downs
with Dynamic Arrays

Weekly planner template

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: March 2, 2020 9:50 AM