Contextures News 20181002

Excel New Functions

October 2, 2018

New functions, put your macros on the Excel Ribbon, 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.

Your Macros

Even if you don't do much Excel programming, you might have a collection of macros that you recorded. To make it easy to use your macros, I've created a free "My Macros" workbook that you can download.

When you open the workbook, it shows a new tab on Excel's Ribbon -- MY MACROS. That tab has 10 buttons and 2 drop down lists that run the sample macros that I stored in that file.

my macros tab

You can copy your own macros into that workbook, and change the buttons and lists so they run your macros. All you have to do is edit the information on a worksheet -- change my macro names to yours, or add your macro names to one of the macro lists.

See the instructions, and get the workbook on the Add Your Macros to Ribbon Tab page on my website.

change button settings

Dynamic Arrays

At the Microsoft Ignite conference last week, announce the release of Office 2019 (commercial users), and the consumer version will be available "in the coming weeks".

This is the non-subscription Office, and Excel has new functions -- CONCAT, TEXTJOIN, IFS, and SWITCH -- that were only available in Office 365 subscriptions before.

Even more exciting is the new type of formula that was announced -- Dynamic Arrays. For example, SORT, UNIQUE and FILTER functions will replace the complicated, old-style, array formulas that we use with multiple functions and Ctrl+Shift+Enter.

To learn more about Dynamic Arrays, read this preview from Microsoft's Joe McDaid, and download a free ebook from Bill Jelen, with many examples.

Currently, Dynamic Arrays are only available in preview for Office 365 Insiders, and no date has been announced for General Availability.

Excel Articles

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

Solver - Kevin Lehrbass shows how to use Excel's Solver feature to solve a puzzle, with simple formulas. This is a great introduction to Solver, if you've ever thought about trying it, but weren't sure where to start. (Level - Intermediate)

VLOOKUP - If you're using Excel in Office 365, you should notice significant improvements in Lookup function speed, after the latest update. For other versions of Excel, here's a comparison of VLOOKUP speed compared to INDEX/MATCH. (Level - Intermediate)

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

Keep It Simple

Our old clothes dryer finally died, after many years of use, so we went shopping for a new one. The appliance section was full of shiny new dryers with control panels that look like they could fly an airplane.

We stuck with a plain and simple model though, because simple gadgets are less likely to break, right? (Do you remember when Excel introduced "workbooks", with multiple sheets?)

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:
I'll also post any article updates or corrections there.

Debra Dalgleish

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: September 29, 2018 3:10 PM