How to Create an Excel Macro for a Frequently Used Formula

1.
You will need to have the Developer tab displaying. If it is not displaying, click on the Microsoft Office Button (upper left corner red, blue, yellow, green logo). Click on the Excel Options button, and then under the Popular category, click to fill the box next to 'Show Developer tab in the Ribbon.' Click OK to exit. You will need to adjust the security level to run macros, so on the Developer tab, in the Code group, click on Macro Security. Fill the box next to 'Enable all macros (not recommended, potentially dangerous code can run).' Click OK. It is fine to run your own macros.
2.
This demonstration is to insert a formula into cell C2 that will extract the last word in cell A2, but any formula can be saved. In this case, the formula is long, making it a great candidate for a macro. The easiest way to create a macro for a formula is to open a workbook that currently contains the formula, or open a new workbook and input the formula. CUT THE FORMULA FROM THE CELL BY HIGHLIGHTING THE FORMULA AND PRESSING CRTL-X BEFORE YOU START CREATING THE MACRO. Click in any cell, and to start creating the macro, click Record Macro in the Excel Developer tab. The Record Macro dialog box will appear.
3.
Enter a name for the macro. If you use more than one word, separate the words with an underscore ( _ ). Next is the Shortcut key that you will use to later run the macro. The Ctrl key is shown by default as the first key, followed by a box into which you enter one letter. (If you choose a letter that is already assigned to a built-in macro, Shift will automatically appear after Ctrl .) Beneath the shortcut key, you are asked where to store the macro. To be able to use the macro in other workbooks, choose 'Personal Macro Workbook.' Lastly on the Record Macro is the option to enter a Description. When you have filled the boxes on the Record Macro dialog box, click OK. This process creates a file name for the macro.
4.
Next, you need to 'record' the steps that you want saved in the macro. These are the keystrokes or mouse clicks that you want to repeat at another location in the current, new, or saved Excel workbook. To record the macro, click on Record Macro on the Developer tab. After you click Record Macro, it will read Stop Recording, so any keystrokes or mouse clicks you make will be recorded. Click the cell into which you want the formula to appear, in this case, C2, and then press Ctrl-V to paste the formula in cell C2. Click Stop Recording in the toolbar. Your macro has been created.
5.
To use the macro, from any workbook, use your shortcut key selection, which would be Ctrl plus the letter you assigned to the macro. You can see from Step 3 that our macro shortcut key is Ctrl m. If you do not recall the shortcut keys, click on Macros on the Developer tab and the Macro dialog box will appear. Click on the macro name. Notice that if you included a description when creating the macro, the description will appear at the bottom of the Macro dialog box. With the desired macro highlighted, click the Run button and the formula will enter into, in this case, cell C2.