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:
- Go to File > Options (in older versions, office button > options)
- Select Proofing
- Click on AutoCorrect Options
- Add auto correct rules for typing formulas like this:
- VL -> =VLOOKUP(
- SF -> =SUMIFS(
- etc.
- 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.
14 Responses to “Write faster formulas with Auto-correct”
A word of caution - This change is applied to all the office applications (word, power point, etc.)
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.
I found this time ago and posted on this forum (dec '13)
here's the thread:
http://chandoo.org/forum/threads/use-the-excel-autocorrect-option-to-enter-formulas.13699/
since then I've improved it a lot.
Claudio
nice write up amazing it hasnt being commented on as its very useful ... im likeing the tab version posted below as saves creating names in auto correct but will do some for names not first in dropdown list ....
How do you make the symbols eg in your post you use Alt 5 (my laptop has no number keypad only the usual line across top which when i press alt 5 gives me a snap shot of active cell.)
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.
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
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
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.
[…] uses the AutoCorrect feature to make it quicker to enter formulas. Have you ever tried that? I probably wouldn't be able to […]
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?
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 ?
Hi Zeeshan,
Simple, create a pivot table from this data. Drop date, product, country in to row labels area, amount in to values area. Your calculations will be ready. For more on pivot tables see this:
http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/
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 😉
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.