As handy as Excel may be, it’s nothing more than a fancy spreadsheet software that can take some numbers and formulas and spew out the results. Until you add macros, that is. Macros take Excel from “good” to “great”, automating most tasks and increasing productivity ten-fold. Any Excel expert worth his salt must be familiar with macros, and this tutorial will teach you how to enable them in Excel 2007.
You can learn more about Excel and other advanced Excel topics in this comprehensive Excel course.
What are Macros?
The easiest way to understand a macro is to see it as a set of instructions for doing something. Fundamentally, these are tiny programs for automating repetitive tasks. For example, if you have a document where you want to make all headlines bold, size 18 and with font ‘Courier New’, you can make a simple macro which will make these formatting adjustments automatically; you only need to select the text to be formatted and press the relevant button or keyboard shortcut.
It’s easy to see how macros can increase productively dramatically by automating common tasks. This is especially true for tasks that don’t require significant external input – like changing formatting or moving the contents of one cell to another in a spreadsheet.
Macros are a fundamental part of most MS Office applications. Powerpoint, Word and Excel all ship with robust macro creation tools. As we will see below, these macro tools are powerful enough to automate complex tasks, yet easy enough for a newbie to pick up in less than 30 minutes.
But just as we must walk before we run, we must first learn how to enable macros in Excel 2007.
Want to finally master Excel? Consider this course on advanced Excel formulas and functions.
Enabling Macros in Excel 2007
Macros are disabled by default in Excel (and indeed, in all Office applications). This is to protect your computer from security threats. Hackers can easily code malicious macros into Excel spreadsheets which, when opened, will spread automatically and harm your system.
The first step, therefore, is to enable macros. To do this, take the following steps in Excel 2007:
Step 1: Click the circular Office icon in the topmost toolbar (next to the undo-redo buttons).
This will bring up the Office menu. Navigate to the bottom and click on ‘Excel options’ as shown below:
Step 2: In the next menu, locate the ‘Trust Center’ in the right menu. Click the ‘Trust Center Settings’ button at the bottom of this menu.
Step 3: You should now see the Trust Center menu pop open. This menu contains settings for controlling trusted publishers, adding or blocking add-ons, and of course, enabling/disabling macros.
Head over to ‘Macro Settings’ from the left pane. You should see four radio buttons for different macro settings.
Let’s take a closer look at these four security settings:
Disable all macros without notification: This is the most secure setting as it disables all macros in any worksheet – including yours – without offering any notifications.
Disable all macros with notification: This is the same as above, except Excel will show you a notification any time it has blocked a macro in a worksheet. You can choose to click on this notification and enable macros in that particular worksheet. This is the default setting as it offers a good tradeoff between security and convenience.
Disable all macros except digitally signed macros: With this setting, only worksheets with a digital security certificate will be allowed to run macros. Such certificates can be obtained from security certification authorities and are a good indication of a document’s security credentials. Still, for security reasons, it’s better to go with option #2 than this.
Enable all macros: Although highly unsecure, this is the setting we must choose if we are to create our own macros.
Select the fourth radio button and hit OK. You may have to restart Excel before the changes take effect. You may also get a pop-up window notifying you about this security change.
You’ve now successfully enabled macros in your worksheet!
P.S.: It’s a good idea to disable macros (i.e. option #2 above) after you’re done creating your macros to keep your worksheets secure.
Accessing Macros in Your Worksheet
You’ve enabled macros, but nothing looks particularly different in your worksheet. You still can’t seem to access your macros, nor see any option for creating them.
This is because you haven’t enabled the Developer tab in your worksheet.
Macros, as mentioned before, are essentially tiny programs. Hence, all macro creation features are tucked away in the Developer tab, which is hidden by default to make the interface more user-friendly. To use macros, you first need to make the Developer tab visible on the Ribbon interface.
To do this, take the following steps:
Step 1: Click on the Office icon to bring up the main menu. Click on ‘Excel Options’ at the bottom as shown above.
Step 2: In the ‘Popular’ settings pane on the left, select the third checkbox – ‘Show Developer Tab in Ribbon’ – and hit OK.
This will show the Developer tab in the Ribbon which will give you access to common programming features in Excel, including the ability to create macros. This is hidden by default since few people need it for everyday Excel tasks.
Besides macros, you can also use Visual Basic to create complex programs within Excel. In fact, combining macros and VB is the secret to Excel productivity. Mastering these two skills will make you a true Excel wizard.
You can learn how to work with Visual Basic for Excel and create advanced macros in this course on advanced Excel.
Note: The process for enabling the Developer tab in Excel 2010 and 2013 is slightly different. You’ll need to go to the options page and select ‘Customize Ribbon’. Here, you’ll have to check the ‘Developer’ checkbox in the right pane to enable Developer mode, as shown below.
That’s it for this Excel tutorial. Stay tuned for more lessons in mastering Excel!
You can fast-forward your Excel training and master macros in this advanced Excel course.