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)
If you missed my last newsletter, you can click here to view it online.
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.
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.
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;
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.
|"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