Buzz
Today, Mytour will guide you through enabling, creating, executing, and saving macro commands in Microsoft Excel. Macros are small programs that allow users to automate complex tasks such as formula calculations or chart creation in Excel. They can save you significant time when performing repetitive tasks, and thanks to the "Record Macro" feature, you can create macros even without any programming knowledge.
Steps
Enable Macros
Open Excel. Double-click the Excel app icon with a white "X" inside a green box, then click on Blank Workbook.
- If you want to open a specific file in Excel, double-click on it.
Click on the File tab located at the top-left corner of the Excel window.
- On a Mac, click the Excel tab in the top-left corner of the screen to open the dropdown menu.
Click on Options. This option is located on the left side of the Excel window.
- On a Mac, click Preferences... in the dropdown menu.
Click on Customize Ribbon. This option is located on the left side of the Excel Options window.
- On a Mac, click Ribbon & Toolbar in the Preferences window.
Check the "Developer" box. This box is located near the bottom of the "Main Tabs" list.
Click OK near the bottom of the window. You can now use macro commands in Excel.
- On a Mac, click Save here.
Record Macro
Enter the necessary data. If you have opened a blank workbook, input all the data you intend to use before proceeding.
- You can also close Excel and double-click a file to open a specific workbook.
Click on the Developer tab at the top of the Excel window. A toolbar will appear here.
Click on the Record Macro option in the toolbar. A pop-up window will appear.
Name your macro command. In the "Macro name" text box, enter a name for your macro. This will help you identify the macro later.
Create a shortcut key combination (optional). Press the ⇧ Shift key along with a character key (e.g., E) to create a shortcut. You can use this combination to execute the macro later.
- On a Mac, the shortcut will include the ⌥ Option+⌘ Command keys and a character (e.g., ⌥ Option+⌘ Command+T).
Click the "Store macro in" dropdown. This box is located in the middle of the window. A dropdown menu will appear.
Click on This Workbook. This option is in the dropdown menu. The macro will be embedded in this workbook, and anyone with the file can use it.
Click OK at the bottom of the window. The macro settings will be saved, and the recording process will begin.
Perform the macro steps. Every action you take from clicking OK until clicking Stop Recording will be added to the macro. For example, if you want to create a macro that converts data from two columns into a chart, you should:
- Click and drag to select the data.
- Click Insert.
- Choose a chart type.
- Click the chart you want to use.
Click the Stop Recording option in the Developer toolbar. The macro will be saved.
Save the Macro-Enabled Workbook
Understand why saving the workbook with macros enabled is essential. If you don't save it as a macro-enabled workbook (XLSM format), the macro won't be saved as part of the file, meaning users on other computers won't be able to use the macro if you share the workbook with them.
Click on File in the top-left corner of the Excel window (Windows) or screen (Mac). A dropdown menu will appear.
Click on Save As. This option is on the left side of the window (Windows) or within the dropdown menu (Mac).
Double-click on This PC. This option is located in the save location column on the left side of the window. The "Save As" window will open.
- Skip this step on a Mac.
Enter a name for the Excel file. Type the name for your Excel workbook into the "Name" text box.
Change the file format to XLSM. Click the "Save as type" dropdown and select Excel Macro-Enabled Workbook from the dropdown menu.
- On a Mac, replace the "xlsx" extension at the end of the file name with xlsm.
Choose a save location. Click the folder where you want to save the Excel file (e.g., Desktop).
- On a Mac, click the "Where" dropdown first.
Click the Save option at the bottom of the window. The Excel workbook with the embedded macro will be saved to your chosen location.
Execute Macro
Open the macro-enabled workbook. Double-click the macro-enabled workbook to open it in Excel.
Click on Enable Content. This option is located in the yellow bar at the top of the Excel window. The workbook will be unlocked, allowing you to use the macro.
- If you don’t see this option, skip this step.
Click the Developer tab at the top of the Excel window.
- Alternatively, you can press the shortcut key combination assigned to the macro. If you choose this method, you can skip the rest of the steps.
Click the Macros option in the Developer toolbar. A pop-up window will appear.
Select the macro. Click on the name of the macro you want to execute.
Click the Run option on the right side of the window. The macro will begin executing.
Tips
- Macros are particularly useful for automating repetitive tasks, such as calculating payroll at the end of the week.
Warnings
- While most macros are harmless, a few can make dangerous changes or delete information on your computer. Never open macros from untrusted sources.
