Using Excel's AutoCorrect Feature to Store and Insert Formulas
Did you know that you can use Excel’s AutoCorrect feature to store and insert formulas? Many Excel users find themselves frequently entering the same formula and are seeking a method through which they could store the formula and recall it on demand in the future. While this can be done using Excel’s Defined Names feature, a better way to store formulas is to save them as an AutoCorrect rule. This process is more intuitive for most users and formulas can be stored from within any workbook and used in any other workbook without having to modify Excel’s default templates. In this tip, you will learn how to store and recall your complex formulas using AutoCorrect rules.
AutoCorrect in a Nutshell
Among other things, AutoCorrect is a feature in Excel that automatically corrects spelling or typing mistakes. For example, if you type “abbout,” based on a pre-defined rule AutoCorrect automatically fixes the mistake by changing your entry to “about.” In fact, this feature is not limited to Excel; it is shared by other Microsoft Office applications including Word, Outlook, and PowerPoint. However, for purposes of this tip, we will focus on using this tool in Excel. To that end, we can take advantage of this feature to store and recall formulas – including lengthy and complex formulas on demand.
AutoCorrect in Action
In this example, entries will be created for formulas to calculate the fiscal month, fiscal quarter, and fiscal year from transactional data for a company whose fiscal year runs from April 1 to March 31. This is necessary because the company would like to create a PivotTable to summarize the transactional data based on its fiscal reporting periods, but dates in PivotTables are always grouped in calendar periods, by default.
The three entries we will create will be the following.
First, whenever we type “frmMonth” in Excel, AutoCorrect will convert that entry to the formula:
=TEXT([@Date],”MMM”)
This formula converts the data in the Date column of a table to a text field, formatted so that only the first three characters of the month display.
Second, whenever we type “frmQuarter” in Excel, AutoCorrect will convert that entry to the formula:
=”Q”&IFS(MONTH([@Date])<4,4,MONTH([@Date])<7,1, MONTH([@Date])<10,2,MONTH([@Date])<13,3)
This formula analyzes the data in the Date column of a table and if the month number is less than 4 – that is if the month is either January, February, or March – the formula assigns the date to the fourth fiscal quarter. If the month number is greater than or equal to 4, but less than 7, the formula assigns the date to the first fiscal quarter. If the month number is greater than or equal 7, but less than 10, the formula assigns the date to the second fiscal quarter. Finally, if none of the previous conditions were satisfied, the formula assigns the date to the third fiscal quarter.
Third, whenever we type “frmYear” in Excel, AutoCorrect will convert that entry to the formula:
=TEXT(IF(MONTH([@Date]) 4,YEAR([@Date]),YEAR([@Date])+1),”0000″)
This formula is used to determine what the appropriate fiscal year is for the data in the Date column of a table.
To create the entries for formulas, click File, Options, Proofing, AutoCorrect Options to open the AutoCorrect dialog box. On the AutoCorrect tab, type the shortcut for the formula into the Replace box; type or copy the formula, including the equal sign (=), into the With box; and then click OK as shown in Figure 1.
To use the entry, simply type in the shortcut and press ENTER. Remember that AutoCorrect entries are shared across all of your Excel workbooks, so you can take advantage of your newly-created functionality regardless of the workbook in which you are currently working. Note that entries defined with uppercase or mixed case shortcuts, as in this case, are case-sensitive. Those created in lowercase are not case-sensitive.
Summary
All Excel users want to save time and by saving your commonly-used formulas as AutoCorrect entries, you will be able to recall them on demand and reclaim time that would otherwise be consumed by entering the same formula repeatedly.
You can watch the following video to learn more about how you can take advantage of this amazing functionality.