MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


Dependent Validation Using Arrays

Dependent Validation Using Arrays »


October 18, 2018

Ever since Data Validation drop-down menus were added to Excel in 1997, people have been trying to work out a way to have the second drop-down menu change based on the selection in the first drop-down.

Can You Return All VLOOKUP Values?

Can You Return All VLOOKUP Values? »


October 16, 2018

VLOOKUP is a powerful function. But I often get a question in one of my Power Excel seminars from someone who wants to know if VLOOKUP can return all of matching values. As you know, the VLOOKUP with False as the fourth argument will always return the first match that it finds. In the following screenshot, cell F2 returns 3623 because it is the first match found for job J1199.

A Look Back At 13 Years of The MrExcel Podcast

A Look Back At 13 Years of The MrExcel Podcast »


October 12, 2018

In the summer of 2005. I was flying to Toronto every month to record two appearances on Leo Laporte’s Call for Help TV Show. On the way home, I was having dinner at the airport bar and the guy sitting next to me said a sentence that was a complete mystery to me: “Did you know that Leo’s TWiT Podcast is the Number One Podcast on iTunes?”

Find One-Hit Wonders with UNIQUE

Find One-Hit Wonders with UNIQUE »


October 4, 2018

UNIQUE is one of the new dynamic array functions in Excel. Using the second or third arguments, you can control if the function returns the UNIQUE or DISTINCT list of items. You can also control if UNIQUE works row-wise or column-wise.

Streamlining the Bennu Model With RandArray

Streamlining the Bennu Model With RandArray »


October 3, 2018

Last week at Ignite, the Excel team introduced dynamic arrays. Today, a closer look at the RANDARRAY function.

Replace a Pivot Table with 3 Dynamic Array Formulas

Replace a Pivot Table with 3 Dynamic Array Formulas »


October 2, 2018

It has been eight days since dynamic array formulas were announced at the Ignite 2018 conference in Orlando. Here is what I've learned:

Easy Picture Transparency in Excel

Easy Picture Transparency in Excel »


October 1, 2018

The Microsoft Ignite Conference was held in Orlando last week. There were several new Excel features announced.

SEQUENCE and RANDARRAY Functions in Excel

SEQUENCE and RANDARRAY Functions in Excel »


September 28, 2018

SEQUENCE and RANDARRAY Functions are the last of the seven new functions introduced this week at the Ignite Conference in Orlando. Here is a recap of the articles from this week:

UNIQUE Function in Excel

UNIQUE Function in Excel »


September 27, 2018

The new UNIQUE function in Excel debuts this week as part of the Dynamic Arrays formula collection. UNIQUE will return the unique values from an array.

FILTER Excel Data with a Function!

FILTER Excel Data with a Function! »


September 26, 2018

FILTER is a brand new Dynamic Array function in Excel. Announced at Ignite 2018, the function is one of several new Excel functions:

Excel Sort With a Formula Using SORT and SORTBY

Excel Sort With a Formula Using SORT and SORTBY »


September 25, 2018

Sorting with a Formula in Excel used to require an insane combination of formulas. Take a look at this data which will be used throughout this article.

Excel Formulas Now Spill Into Adjacent Cells - Major Calc Engine Change - Breaking News

Excel Formulas Now Spill Into Adjacent Cells - Major Calc Engine Change - Breaking News »


September 24, 2018

Today at the Ignite Conference in Orlando Florida, Microsoft debuted a major change to the calculation engine. Every function is now treated as an array formula and there is no more need to press Ctrl+Shift+Enter. This will lead to a rapid increase in the number of functions introduced in Office 365.