Introduction: Basic Macro Example in Excel
Excel has an electronic spreadsheet program that allows users to store, organize, and manipulate data. It has a wide array of uses in both collegiate and professional worlds. Many employers use excel and it is among the most important programs to learn and use. The Excel macro is an essential part of your excel repertoire. Macros allow you to automate common tasks within excel. This Instructable will examine the basics of creating and using a macro in excel. This Instructable will not cover how to code in Visual Basic. This Instructable will be most valuable to you if you use Excel frequently. This example will serve as a basic outline for those who are trying to learn to use macros. In order to complete this Instructable you will need:
- A PC or Mac running Microsoft Office 2011
- Some working knowledge of Excel
- This Intractable will take anywhere from 5 to 10 minutes to complete
Step 1: Setting Up the Excel Workbook
Start by opening up a standard excel workbook. Once the workbook is open make sure that the developer tab is on the top ribbon, as shown in Figure 1.
If the developer tab is not on the ribbon and you are using a Mac (refer to Figure 1):
- Click the Excel Tab at the top of the Menu Bar
- Then select Preferences
- Under Preferences select Ribbon
- In the Ribbon section make sure that the box next to Developer is selected
If the developer tab is not on the ribbon and you are using a PC (refer to Figure 2)
- Click the File tab and then select Options
- After selecting Options go to the Customize Ribbon section and select Developer
Step 2: Creating Example Data
You will need to enter some data for the macro example. For this Instructable, you will be creating a macro that will automatically sum each of the rows. In other spreadsheets you can use the same macro to preform this function allowing you to save time when going through massive spreadsheets.
- You need to format your workbook to look similar to Figure 3
- The values in columns B through D do not matter, you can type any value you desire
Step 3: Recording the Macro
Click on the developer tab and click the Record icon and a window will appear as shown in Figure 4, a small widow will appear called Record Macro.
- Name the macro TestMacro1
- Fill out the rest of the window as you see fit, then click Ok
- Now, on the sum column E, click on the Formula Bar (refer to Figure 4)
- In the Formula Bar you need to type “=sum(B2:D2)” this will add each one of the rows together
- After the number appears in cell E2 highlight the cells below it and hit “ctrl d”
- You can also copy the formula by copying the cell E2 and the highlight the cells right below E2, right click, and paste
Step 4: Using the Macro
Once you have recorded a macro you can use it at will within the boundries of where you stored the macro. If the macro is stored in the workbook then you can only use the macro within the spreadsheets contained in that workbook, but the macro can be stored in any other workbook as well. There are a few ways you can use the macro once it is recorded.
The first way is to
- Click the Macros button
- Then you will need to select the macro you wish to use and select Run
Another way is for you to assign the macro to a button
- Click the Button icon in the developer tab as shown in Figure 5
- A menu will open with a list of the macros that have been recorded in the workbook select TestMacro1
