Write faster formulas with Auto-correct

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:

Here is how:

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

   

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.

• Move the formula help box with mouse to see what you need
• Replace formulas with values
• Use CTRL+Enter to type same values or formulas into multiple cells
• Fill blank cells in a table with above value
• More on Excel formulas

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