Write faster formulas with Auto-correct

Posted on January 6th, 2015 in Excel Howtos - 14 comments

Want to write formulas faster? Here is a quick tip.

Use Auto-correct!

That is right. Excel’s auto-correct feature can be setup to help you write formulas faster. See a demo below:

write-faster-formulas-with-auto-correct

Here is how:

  1. Go to File > Options (in older versions, office button > options)
  2. Select Proofing
    auto-correct-from-file-options-in-excel
  3. Click on AutoCorrect Options
  4. Add auto correct rules for typing formulas like this:
      1. VL -> =VLOOKUP(
      2. SF -> =SUMIFS(
      3. etc.

    setting-auto-correct-rules

  5. Done.

Now, whenever you type VL in a cell, Excel immediately puts the VLOOKUP formula and asks you for extra inputs. Same with other formulas you create.

Caution: Make sure you are not replacing any common 2/3 letter words. For example if you set up a rule to replace IN with =INDEX(, then you might get unexpected replacements when typing the word IN.

Additional tips:

  • In case the replacement is not needed, press CTRL+Z to revert to original word.
  • Replacement rules are case sensitive. So vl will not replaced by VLOOKUP, only VL will.

How do you speed up formula writing?

Often when running a class or demonstrating something in Excel, people ask me, “How come you are writing formulas so much faster?”. Here are the tricks I use,

  • I use auto complete (that little drop down in cell with all formulas) as much as possible
  • I use keyboard shortcuts to select ranges, names or access input values for the formulas
  • I am good with typing.

What about you? What tricks & ideas do you use to type formulas faster? Please share in the comments area.

More on formula writing

Here are few more articles to help you write better formulas.

PS: Thanks to John Long who taught me this tip.

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

14 Responses to “Write faster formulas with Auto-correct”

  1. Sumit Bansal says:

    A word of caution - This change is applied to all the office applications (word, power point, etc.)

  2. John Long says:

    Nice one .... nice to see my tip shown ... whether it will be of much use is another thing but it can have its place ... and as Bansal says "This Change is applied to all offfice Applications" so what i do is put a number with the short word used eg If1 or Sumif1 ... makes it less prone to being used in wrong place eg in word ...eg ... if ...use in excel and you get formula .... use it in word then ... and you try to type if lol lol.
    But with Chandoo tip on Ctrl z makes this less of a problem as it reverts it back to the text.

  3. Isaac S says:

    I take advantage of Excel's uh... automatic auto-correcting. That is, if you type "=vl" TAB, you get the same result. With "=in" you get a drop down of options. Advantages: no accidents, no codes like "in1" to remember, and no set up.

    • John Long says:

      nice didnt know that about using tab .... will make things way quicker ... and will do nicely when requiring multiple choices in formula eg index and match .. being lazy i will still set up for choices not first on drop down list lol lol

  4. John Long says:

    Further to this ... have also just tried this ... name i1 ... =index( so when you type it in excel it comes out as above like a function ... also name i2 .... index( .... or (m1 ... =match( ... m2 .... match( ... )

    Reason being if you are doing a formula with multiple types init it will auto complete even in the formula so for a index match function .. i1 gives =index( ... then add your array then type m2 and you get your .... match( ... without the = sign ... and i used s2 to give me ... Small( ..
    Giving me a function with 3 Auto-Correct functions making it pretty quick and easy to write

  5. Raviraj says:

    I keep use the TAB method as mentioned by Isaac above extensively and is very versatile and quick. You can even scroll down in the list of functions and by pressing TAB the function is selected.

  6. […] uses the AutoCorrect feature to make it quicker to enter formulas. Have you ever tried that? I probably wouldn't be able to […]

  7. Brian Clark says:

    I added the some rules to AutoCorrect. However, the formula doesn't show up in Excel when I enter the letter/s unless I also click on something else such as the space bar. Is this how it works or am I doing something incorrectly?

  8. Zeeshan Ahmed says:

    Date Customer ID Product Country Net Amount
    6-Mar-2014 a tt india $80.82
    6-Mar-2014 a tt india $400.00
    15-May-2014 b kk canada $250.00
    15-May-2014 b kk canada $250.00
    19-Jun-14 c mm australia $1,607.72
    20-Jun-14 c mm australia $1,608.72
    21-Jun-14 c mm australia $1,609.72

    Hello every one ! I want to find out the sum of the net Amount for every similar “date , ID , Product & Country” can any one help me in this scenario ?

  9. Zeeshan Ahmed says:

    Thanks for Replying 😉
    Actually in the pivot table the count of the product type sold "tt" "kk" "mm" over counted as it is against the duplicate ID'S and the partial payments of the customers ,so summering all this, we need the sum of the amount for every duplicate ID & PRODUCT TYPE , Also the Actual count of the product sold 😉

  10. Another little-known shortcut that speeds up formula writing is to use two periods, which Excel autocorrects (by default) to a colon. So, instead of typing in B2:B99, you can type in B2..B99 and Excel will autocorrect it to generate the range. Try it, and be amazed.

Leave a Reply