How to Create a Macro in MS Excel to Paste Formulas Only

I am a serial formatter. Any time I whip up the most basic spreadsheet, I add borders, sometimes shade cells, bold and center headings, bold totals, and format numbers. The proper formatting not only gives a professional touch, but also can make numbers easier to understand. However, you can screw up formatting by copying formulas down a column. To remedy this problem, you can copy and paste the formula without copying the original cell’s formatting. If this is something you do all the time, I recommend setting up a macro to complete this task quickly using keystrokes.

Instructions

Difficulty: Moderately Easy

Things You’ll Need:

• MS Excel

1. First thing, make sure the macro toolbar is active. Click on View on the menu bar across the top of the screen. Select Toolbars to view a list of toolbars available in Excel. Active toolbars are indicated with a check mark. The macro toolbar is called Visual Basic, and if it is not checked, click it to activate.
2. This is what the Visual Basic toolbar looks like. The Run Macro button looks similar to a right arrow found on the Play button on a CD or DVD player. Next to is the Record Macro button; the icon is a simple solid circle.
3. Before you begin recording the macro, you’ll need to copy the formula. Select the cell containing the formula to copy. Either press Ctrl and “c” or go to Edit on the menu bar across the top of the screen and click on Copy. You’ll notice a dashed border running around the outside of the cell indicating that the cell has been copied.
4. Now click on a new cell to paste the formula in.
5. Click on the Record Macro button. Notice that the circle icon will change to a square. Give the macro a name. Specify a shortcut key series, like Ctrl+f so you can run the macro at any time using those keystrokes. Choose to store the macro in your personal macro workbook so you’ll be able to use this macro again in the future. Click OK when complete.
6. Next you will paste the formula. Go to Edit on the menu bar across the top of the screen and choose Paste Special.
7. When the Paste Special window appears, click the radio button to the left of Formulas and then click OK. The formula should now be copied in the new cell.
8. Click the Stop Recording button that looks like a solid square. You have successfully recorded your macro. Any time you run your macro, the exact task you just performed will be repeated by using a keystroke.
9. Test the macro by copying a formula and pasting into a new cell by pressing Ctrl and “f”.