Excel Tips
Excel 2020: Use # "The Spiller" to Refer to All Array Results »
September 23, 2020
Using # "The Spiller" to refer to all array.
Excel 2020: Understanding Array Constants »
September 17, 2020
There are several dynamic array that get better with an array constant. Here is a simple way to understand them. A comma inside an array constant means to move to the next column. A semi-colon means to move to the next row. How do you remember which is which?
Excel 2020: Use Data, Refresh All to Update Stock Data »
September 9, 2020
The other data type available is Stock data.
Excel 2020: Match the Parentheses »
August 31, 2020
Excel cycles through a variety of colors for each new level of parentheses. While Excel reuses the colors, it uses black only for the opening parenthesis and for the matching closing parenthesis.
Excel 2020: Lookup to the Left with INDEX/MATCH »
August 20, 2020
What if your lookup value is to the right of the information that you want VLOOKUP to return? Conventional wisdom says VLOOKUP cannot handle a negative column number in order to go left of the key.
Excel 2020: Use the Fuzzy Lookup Tool from Microsoft Labs »
August 19, 2020
When you use VLOOKUP, HLOOKUP, or INDEX/MATCH, Excel is expecting an exact match. But in real life, data is messy. Several years ago, the research team at Microsoft Labs released a free Fuzzy Lookup add-in.
Excel 2020: Replace Columns of VLOOKUP with a Single MATCH »
August 17, 2020
VLOOKUP is powerful, but it takes a lot of time to do calculations. Plus, the formula has to be edited in each cell as you copy across.
Excel 2020: VLOOKUP to Two Tables »
August 13, 2020
I met a person who had two price lists. The price list for the top 100 products is updated weekly. The price list for the other products is updated twice a year. They needed a logic...
Excel 2020: Use Boolean Logic »
August 6, 2020
I always cover IF in my seminars. And I always ask how people would solve the two-conditions problem. The results are often the same: 70–80% of people use nested IF, and 20–30% use AND. Just one time, in Virginia, a woman from Price Waterhouse offered the formula shown in this article.