How to Automate Repetitive Excel Tasks With Macros (Without Writing Code)
If you work in Excel regularly, you probably repeat the same handful of steps every week. Formatting a report. Copying data between sheets. Sorting the same way, every time.
Macros let you record that sequence once and replay it with a click. No coding required, though you can go further with it if you want to.
What a Macro Actually Does
A macro records a sequence of keystrokes and mouse clicks. When you run it, Excel plays that sequence back exactly as you recorded it.
That means macros can:
- Apply formatting and styles
- Manipulate data and text
- Connect to an outside data source
- Create a new file
- Combine any of the above, in any order
How to Record One
- Go to the Developer tab and click Record Macro.
- Give it a clear name, like FormatWeeklyReport.
- Perform the steps you want it to repeat.
- Click Stop Recording.
That’s it. The macro now exists and can be run any time.
Example: Automating a Weekly Report
Say you get a raw data export every Monday and you always do the same three things to it: bold the header row, add borders around the data, and sort by date.
Instead of doing that by hand every week, record it once:
- Click Record Macro and name it FormatWeeklyReport.
- Bold the header row, add borders, sort by date.
- Click Stop Recording.
Next Monday, open the new export, run the macro, and the formatting is done in seconds instead of a few minutes of repetitive clicking.
Get ProsperSpark’s stories in your inbox
Join Medium for free to get updates from this writer.
Add a Button to Run It
You don’t have to dig through menus every time you want to run a macro. You can assign it to a button.
- Insert a shape onto your worksheet.
- Go to the Developer tab and select the shape from the Control Button options.
- Right-click the shape and choose Assign Macro.
- Select your macro and click OK.
From there, you can format the button like any other shape or text box, resize it, and reposition it.
Common Mistakes to Avoid
- Running the macro from the wrong starting point. Macros often play back relative to wherever your cursor sits when you hit Run. If you don’t position it first, you can end up applying changes to the wrong cells.
- Saving in the wrong file format. If you save a workbook with macros as a regular .xlsx file, Excel strips the macros out silently. Save as .xlsm (macro-enabled workbook) instead.
- Recording with the wrong reference type. By default, Excel records absolute cell references, so the macro always acts on the exact same cells no matter where you start. If you want it to work relative to your starting position instead, turn on relative references before recording.
Changing Macro Security Settings
If Excel blocks a macro from running, it’s a security setting, not a broken macro.
Click the File tab, select Options, then click Trust Center. From there you can choose your security level:
- Disable all macros without notification — most secure, but you can’t run any macros
- Enable all macros with notification — you’ll be prompted each time, useful if you only trust some macros
- Enable all macros — least secure, not recommended
Editing a Macro Without Rerecording It
If a macro isn’t quite right, you don’t need to start over. Open it in the Visual Basic Editor (VBA) and adjust the code directly.
- Open the workbook containing the macro.
- Go to the Developer tab and click Visual Basic Editor.
- Find your macro in the left pane and click it to view its code on the right.
This is also where you can build macros from scratch using VBA, Excel’s built-in programming language. It’s optional, but it opens up more control than recording alone.
The One Thing to Take Away
If you’re doing the same sequence of steps in Excel more than once a week, that’s a candidate for a macro. Start with the smallest, most repetitive task on your list, record it, and see how much time it actually saves before moving on to anything more complex.
If you’re working through this at a business level, this is the kind of work we do at ProsperSpark.
