Excel Roundup 20181025

Excel Roundup 20181025

We're well into autumn, here in Canada, so it's time for another Excel roundup. To get Excel links and articles every week, sign up for my weekly Excel newsletter.

Contextures Articles

Here are a few of the Excel articles that I've published recently.

Drop Downs – Sometimes data validation arrows disappear, for no apparent reason. See some of the causes for that behaviour, and how to fix or avoid the problems

Macros – Put your macros on a custom Excel Ribbon tab, by making simple changes on a worksheet, in my free sample workbook.

Pivot Tables – Use macros to automatically name drilldown sheets, and delete some or all of them when closing the workbook

Comments – Use these macros to move or resize Excel comments that have wandered away from their original locations

Microsoft Events

Watch the recordings from a couple of recent Microsoft events.

Power BI - If you missed Microsoft's Business Applications Summit this summer, watch the session videos online. Type "Excel" in the Search box, to see a list of those sessions.

Microsoft Ignite – You can also watch sessions from September's Microsoft Ignite conference. Here is the list of Excel sessions, and they all look interesting.

Excel Tips

Shortcuts - Harvard Business Review has a list of 10 Excel features and shortcuts that everyone should know. Does it really take 15 minutes to learn Freeze Panes?

Errors - Jon Peltier wrote a tongue-in-cheek article about Excel error messages. How many of those gems have you seen?


Solver - Kevin Lehrbass shows how to use Excel's Solver feature to solve a puzzle, with simple formulas.

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 and INDEX/MATCH.

Advanced Excel

Data Model - Rick de Groot has a good lesson on how to use the Data Model in Excel. There are lots of screen shots, and clear explanations.

Prediction - Dave Smith, from Excel with ML, shows how to build a recommender system in Excel, similar to the Netflix system.

ModelOff - The Excel questions and answers have been posted for last year's ModelOff competition. You might find a new formula or two, to use in your own Excel projects.

Models - Brett Whysel, a former investment banker, shares 8 ways to make more powerful Excel models -- "begin with your end in mind".

Excel Automation

Macros - Jon Acampora shows two ways to assign keyboard shortcuts to your macros, and lists the pros and cons of both methods.

Passwords - Doug Glancy shows how to make a Password form in Excel, with a Cap Lock warning, and a "Show Text" button.

Automation – You can create macros to save time in Excel, but are you automating yourself out of a job?


Excel Roundup 20181025


3 thoughts on “Excel Roundup 20181025”

  1. Regarding your entry about VLOOKUP and particularly the link to the speed comparison discussion…

    Should we not first acknowledge that even if those results are irrefutably conclusive, then the average difference was never more than 40 milliseconds? That’s only 0.04 seconds across 16,000 lookups -- or 0.0000025 seconds (2.5 microseconds) per lookup -- against a list of 100,000 records! Scientifically speaking, the blink of an eye is about 0.3 to 0.4 seconds. In other words, all 16,000 lookups could have been performed 10 times using either method in approximately the time it takes to blink an eye. And in just a few eye blinks, we could easily search the internet and find a handful of blog/discussion posts, several from Microsoft MVPs, who will claim equally large, robust, and repeated tests that bring INDEX/MATCH out on top…but again by the narrowest of margins.

    In other words, this is a long running debate over fractions of a second. Can’t we all just agree that there is virtually no difference in calculation time between them…at least for a single lookup? But what if we need multiple returns for the same lookup value -- i.e. if we need to return values from columns B, G, M, and X with column A as the common lookup reference for all of them. Finding the matching value is the most difficult (time consuming) part of any lookup operation, while returning the value from the same position of a neighboring column is so fast as to require nanoseconds, maybe even picoseconds, in order to measure.

    As Mark notes over at Excel Off The Grid, “…nobody ever said you had to use INDEX and MATCH together within the same cell.”

    VLOOKUP has no way of separating the lookup from the return, meaning every subsequent lookup is just as hard as the one before it. Whereas MATCH could be used in one cell/column to locate the matches with multiple INDEXes across other cells/columns taking full advantage of that result by reusing it multiple times. To reference the discussion at the link you provided, if the binary search with constant index took VLOOKUP 165 ms to return something to the first result column, then it’s going to take another 165 ms to return the second column and another 165 ms for the third and so on. To the contrary, using a separated INDEX and MATCH could still take about 194 ms to return its first column. But every subsequent INDEX keeps reusing the MATCH to return results in an infinitesimally small fraction of that time.

    I challenge anyone who doesn’t believe me to try it. My personal test sample was 120,000 lookups against a list of over 127,000 rows, and I too conducted both linear and binary searches. Every subsequent VLOOKUP increased calculation time by an approximate multiple of the first, while the timer could barely even register the difference in each subsequent INDEX.

    Taking this into account makes INDEX/MATCH the clear winner in cases where multiple returns are needed from the same lookup value. In fact, the margin of victory is so wide that it’s not even a contest. And we haven’t even started talking about all the arrays and other variations VLOOKUP can’t perform -- at least not without grasping at CHOOSE or other straws for help. But INDEX/MATCH are ready and able to tackle those variations all day long.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.