| FREE
Excel
Tips
Newsletter
from TheExcelAddict.com Subscribe here to get more tips like this every week |
October 20, 2016
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.
If 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
| 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.
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.
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.
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;
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 |
| 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. |
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630