Excel Tips
Excel 2020: Replace Ctrl+Shift+Enter with Dynamic Arrays. »
October 5, 2020
Say that you have a friend who is superstitious about Friday the 13th. You want to illustrate how many Friday the 13ths your friend has lived through. Before Dynamic Arrays, you would have to use the formula.
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...