Enter Complex Excel Formulas Fast

On Chandoo’s blog, Hui is running a series of articles with Excel Tips. There were some great tips in this week’s list, but my favourite one was down in the comments section – how to enter complex Excel formulas fast.

I’ve been using Excel for a long time, and I’ve never seen this suggestion before. And it’s a real time saver!

Excel Tips Articles

Last week was the second article in Hui’s series of Excel tips. It featured Chandoo’s Excel Ninjas, and you can see his list here. The Ninjas are the wonderful people who help out with questions in Chandoo’s Excel forum.

The previous week’s list had some great tips too, and there will be more tips coming in a couple of weeks, so watch for those.

Enter Complex Excel Formulas Fast

Anyway, back to my favourite tip from the Ninja edition – I saw it in the comments section, posted by Wyn Hopkins. Wyn suggested using an AutoCorrect entry, so it’s easier to create INDEX / MATCH formulas.

I almost ignored this tip, because I thought, “Excel already shows us the formula syntax. Why would I need this?” Fortunately, I read it carefully, and realized that it could be a daily time saver.

So, I tried it, and Wyn was right – this tip does make it easy to enter complex Excel formulas fast.

The Complex Formula

In the comment, Wyn showed the formula that he puts in AutoCorrect. It is an INDEX / MATCH formula, with placeholders for the cell references. (You can read more about that powerful function duo on my website.)

If you want to try this tip (and I highly recommend it), then copy his formula. I’ll show you what to do with it in a minute.

=INDEX( DblClk_to_Select_Column_to_return, MATCH( DblClk_Single_Lookup_Cell, DblClk_Lookup_Column, 0),0)

How to Add an AutoCorrect Entry

In the comment, Wyn didn’t describe how to create an AutoCorrect entry, so here’s how to do that:

  1. First, copy Wyn’s formula, if you didn’t do that already
  2. In Excel, click the File tab (or Office circle) at the top left of the screen
  3. Click Options, then click Proofing, and click the AutoCorrect Options button.
  4. In the Replace box, type a short code that Excel can “AutoCorrect” for you — I used fnim (short for Function Index Match). Don’t use a real word, because Excel will change this, every time you type it.
  5. Click in the With box, and press Ctrl + V to paste Wyn’s formula that you copied
  6. Click Add, then click OK, twice, to exit.

Enter Complex Excel Formulas Fast with AutoCorrect

How to Use an AutoCorrect Entry

After you add an AutoCorrect entry, here’s how to use it:

  1. Select the cell where you want to enter the formula
  2. Type your code (fnim), followed by a space character (That tells Excel you’ve finished typing the code)
  3. The formula with placeholders will appear in the cell
  4. Double-click on each placeholders, then click on the cell or range for that reference.

That’s much quicker than starting from scratch!

Enter complex Excel formulas fast with AutoCorrect

Add More AutoCorrect Formulas

If you find that you’re saving time with this AutoCorrect entry, maybe there are a few more that you could add. What other complex formula structures do you use on a regular basis?

I’ll probably add an IFERROR version of INDEX / MATCH, because that’s another one that I use quite often.

Then, you just have to remember to use those shortcut codes!

__________________

You may also like...

5 Responses

  1. ervit says:

    Thanks!!!

  2. Very cool Excel hack!

  3. Doug Glancy says:

    Debra, That was my favorite tip in that post too. By the way, I was featured in this week’s Notable Websites post. (I must have been a quick responder, because I made it to the top of the list). In that post I also liked the last suggestion for quickly adding a table with a SQL connection. I’ve got a tool that does that which I use every day.

Leave a Reply

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