FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010,
2007, 2003

October 20, 2016

 
Greetings from The Excel Addict
Hi fellow Excel Addict,

In today's 'Excel in Minutes' tip, I want to show you how to 'Use AutoCorrect to Quickly Recall Your Impossible-to-Remember, Complex Formulas'. I hope you find it helpful.

I
f you missed my 'Excel in Seconds' newsletter from Tuesday, I showed you how to 'How to Insert Degree Symbols in Your Worksheet'. You can read that tip here.

I hope you have a great week and keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com

 
Francis Hayes (The Excel Addict) - Cape to Cabot Race, October 16, 2016, St. John's, Newfoundland, Canada

If you missed my last newsletter, you can click here to view it online.


 
TheExcelAddict.com Quote of the Day

"Impossible is just a big word thrown around by small men
who find it easier to live in the world they've been given
than to explore the power they have to change it.
Impossible is not a fact. It's an opinion.
Impossible is not a declaration. It's a dare.
Impossible is potential.
Impossible is temporary.
Impossible is nothing."

-- Muhammad Ali --
 
If you have a favourite quote, send it to me and I may post it in my newsletter.

Today's Microsoft Excel Tip

Use AutoCorrect to Quickly Recall Your Impossible-to-Remember, Complex Formulas

Do you have one or more long**, complex, impossible-to-remember formulas that you use frequently. Some Excel users store these special formulas in a separate workbook or text file that then can be copied from.

Long Complex Formula To Calculate Age in Microsoft Excel 2007 2010 2013 2016 365
But maybe there's an easier way. Add those impossible-to-remember formulas to you AutoCorrect list.

Note: This technique works better for formulas that contain only one cell reference, as you'll usually need to adjust that reference. ** Also note that there is a 255 character limit that you can use in the AutoCorrect 'With' box (see below). Longer formulas will be truncated to 255 characters.

Let's take, for example, the following formula which is used to calculate someone's age based on their date of birth date in cell B2.

=IF(MONTH(TODAY())>MONTH(B2),YEAR(TODAY())-YEAR(B2), IF(AND(MONTH(TODAY())=MONTH(B2),DAY(TODAY())>=DAY(B2)), YEAR(TODAY())-YEAR(B2),(YEAR(TODAY())-YEAR(B2))-1))
 
Rather than trying to remember this formula every time you need to use it, add it to your AutoCorrect list with an easy-to-remember code (e.g. #age#) that will allow you to quickly recall and insert the formula in your worksheet.

Add a Formula to Your AutoCorrect List

1) From the File tab, click Options, Proofing, AutoCorrect Options…, AutoCorrect (tab);

2) In the Replace box, enter a short, easy-to-remember, unique set of characters which will never be used in normal spreadsheet input. In this example I've use #age#;

3) In the With box, type or paste (CTRL+V) your complex formula (**maximum 255 characters). At this point you can modify the cell reference in the original formula, for example, if your formula will always be using a specific cell, column or row;

4) Click Add and OK.
Then click OK to close the Excel Options dialog.

Add Complex Formula To Autocorrect List in Microsoft Excel 2007 2010 2013 2016 365

Rather than trying to remember this formula every time you need it, you can now just enter your 'auto correct code' in the cell where you want to place the formula.

Use this ‘auto correct formula’ in your worksheet…

1) Select a cell where you want to enter your formula;

2) Type the auto correct code #age# for this formula and press ENTER. Excel will immediately replace the text #age# with the 'auto correct formula' you assigned to it;

Use Autocorrect Code To Enter Long Formula in Microsoft Excel 2007 2010 2013 2016 365
3) If necessary, modify the cell references in the formula to match the cell(s) your formula will be working with;

4) Press ENTER to complete your formula.

Give this a try and let me know if this is something that you can use.

If you've never used Excel's AutoCorrect feature before, you may be now be realizing there are many other uses for this.

Do you use AutoCorrect in any creative/unusual ways? Please, tell me about them.


 
FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Thanks for supporting this newsletter and website

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630