Excel Tips
UNIQUE From Non-Adjacent Columns »
November 15, 2018
The other day, I was about to create a unique combination of two non-adjacent columns in Excel. I usually do this with Remove Duplicates or with Advanced Filter, but I thought I would try to do it with the new UNIQUE function coming to Office 365 in 2019. I tried several ideas and none would work. So, I went to the master of Dynamic Arrays, Joe McDaid, for assistance. The answer is pretty cool, and I am sure I will forget it, so I am documenting it for you and for me. I am sure, two years from now, I will Google how to do this and realize "Oh, look! I am the one who wrote the article about this!"
Find Latitude and Longitude for Each City in Excel »
November 9, 2018
Say that you have a list of cities in Excel and need to know the latitude and longitude for each city. A new Geography Data Type feature coming to Office 365 will make this easy. Check the Data tab in the Excel ribbon. Do you have a new Data Type category with Stocks and Geography?
November 7, 2018
Microsoft is adding Stock and Geography data types to Excel. While I covered geography data types on Monday, today is a discussion of stock data types. This feature is only available to people with an Office 365 subscription - it will never be included if you purchase Excel 2019, or Excel 2016 or Excel 2013.
Geography Data Types in Excel »
November 5, 2018
Office 365 customers should have a new gallery on the Data tab of the Ribbon with icons for Stocks and Geography. These are new data types in Excel and are hopefully the first of many such data types.
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? »
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 »
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 »
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 »
October 3, 2018
Last week at Ignite, the Excel team introduced dynamic arrays. Today, a closer look at the RANDARRAY function.