Perhaps you’re new to macros or trying to do this for the first time. If you’ve managed to open open Excel’s developer tab successfully, we’re going to show you how to jump right in and record a macro. The macro will record everything you’re doing in Excel as computer code so that you can play back that same routine automatically later. The aim here is to automate a series of actions that you find yourself regularly repeating in Excel. We’re imagining that you’d like to be able to speed things up by getting that sequence of actions to run at the push of a button.
Our example: automating cell formatting with a macro
We could think of lots of examples of routines we regularly carry out in Excel that we might want to automate:
- Perhaps a regular copy and paste of scenario outputs, formatting all of them alongside each other in a table or graph each time you run the model inputs
- Perhaps a regular formatting and presentation of data from your company’s management information systems.
Today we’re going to imagine that your company has certain standards around cell formatting (some big companies do – they want the many people who look at the company’s spreadsheets to know that a cell formatted a certain way is, for example, an input cell).
Imagine that you find yourself regularly applying a set of 3-4 formats to a lot of cells in your model. Each time the whole exercise bores you stupid. Instead of having to go through the same series of actions every time, you’d like to be able to automate the process.
Today we’re going to create a macro that automates cell formatting.
Footnote: what do the observant amongst you think about our example?
The observant amongst you will want to point out that Excel gives us other ways of automating formatting and that raises a great point about Excel and macros. Excel’s standard features are so powerful you should always think through whether you can use any of those to solve your problem before you take the trouble to create your own macro. Today we could have:
- Formatted one cell and then used the format painter (“Home”, “Clipboard”, “Format Painter”) – that’s a great little tool.
- Created a custom cell style (“Home”, “Styles”, “Cell Styles”, “New Cell Style”).
But we want to have some fun with macros don’t we. So shall we just carry on?
Step 1: click on 'use relative references'
1: relative vs. absolute references for macros
You’ve managed to open Excel’s developer tab right? Under “Code” click on “Use Relative References”.
What that’s going to do is set our macro running from whatever cell we’re in at the time (the “active cell” in macro speak). Sometimes you want a macro to start from the exact same absolute Excel spreadsheet cell reference each time. If this were the case you wouldn’t click the button and, once you looked at the computer code created later, you’d notice that the code looked slightly different.
Today we want the macro to run from whatever Excel cell we have our cursor in, so we’re going to use relative references.
Step 2: hit the record button, please feel very nervous
2: click on “Record Macro”
Under “Code” click on “Record Macro”. A box should pop up. Straight away you’ve got the ability to give the macro a name for future reference.
Remember that what we’re doing here is trying to automate a routine in Excel and play it back quickly. Notice under “Shortcut key” you’ve got your first opportunity to set the macro to play back quickly – by assigning a keyboard shortcut key. If you want to set your macro to run using a shortcut key that’s fine, but we’d recommend that you use a key that you don’t use for other common Excel shortcuts (don’t use “Ctrl” “C”, “Ctrl” “V” etc). Let’s leave the shortcut key alone for the moment. We will come back to it later.
Now click on “OK”. Please start feeling very nervous. Now Excel is recording absolutely everything you’re doing behind the scenes in computer code. You’re kind of in danger territory right now. You should have read some of the warnings we gave when we started getting into macros. You should be feeling a bit nervous. Now’s not the time to get distracted by a phone call or decide you’ve finished for the day and start closing down spreadsheets. Feeling suitably worried (good)? We’re only going to start relaxing again once you’ve stopped recording.
Now that you’ve clicked on “OK” you get very little notice that you’re recording. The button at the top of the “Code” section has changed to “Stop Recording”. Down the bottom of your spreadsheet you’ve got a little square button now. That’s all the notice you’ve got that you’re recording. You should be feeling nervous. We’re going to go wild in a second but you should only start relaxing once you’ve stopped recording – that will take you out of the danger zone.
Step 3: format like crazy, then stop recording
3: go crazy with your formatting
We know you’re feeling a bit nervous because Excel is recording absolutely everything you are doing and you don’t want to make any mistakes. Just quickly, temporarily, go a little bit crazy:
- For good measure, click once in the active cell in your spreadsheet (the cell your cursor is in – if it’s a new spreadsheet it’s probably cell A1 – click into the active cell
- Apply a few cell formats to that cell. E.g. shade the cell a particular colour, maybe bold or italicise it, change the font and the font size, apply cell borders – go a little bit wild with your formatting
- Just to be safe, click once back in the active cell again (the cell you’ve been working in)
- Important: click on “Stop Recording”.
Now you can relex. You’re out of the danger zone. Phew!
Watch a video showing how to record an Excel macro
Watch a video of the whole thing
Maybe on some of the steps above you risked getting lost. Here you can watch a quick video that shows you how to record a macro:
Now congratulate yourself
Congratulations, you’ve just recorded your first macro. Next we’re going to show you how to:
- View macro code – you’ve just created some computer code, we’re going to take a look at it
- Play a macro manually so you can check that what you’ve created is working OK
- Run a macro automatically e.g. from a button – so that you can play the macro back super fast and save yourself time in the future.
There’s lots of fun to be had yet!
About this financial modelling course material
Don’t panic! You’re just looking at a financial modelling course extract covering macros – that’s all – it’s nothing to worry about.