2. Recording your first macro

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.

Today's example

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.

54 comments so far:

...join the conversation...

Your email address will not be published. Required fields are marked *

  1. Thank you for this tutorial…

    Reply
  2. I interested this lesson.

    Reply
  3. Awesome. We want to learn more. Thank you.

    Reply
  4. Interesting lesson

    Reply
  5. Easy understanding

    Reply
  6. Quickly absorbed.

    Reply
  7. Execllent.

    Reply
  8. Can we have longer lessons here. Otherwise it is nice to be learning something nice.

    Reply
  9. Okay>>>>waiting to learn more

    Reply
  10. Video for recording first Macro is not playing, Is there any link I can download it from?
    Find it very helpful. Give me more of it

    Reply
    • Aaron it’s just a simple youtube video showing the keyboard sequences. If your browser plays youtube videos it should work fine – it’s all tested and working this end. Here is the link to the youtube video: https://www.youtube.com/watch?v=wX6CYBMbDlc

      Reply
  11. its becoming more interesting.

    Reply
  12. How can i run the macro that i have just recorded to view it.

    Reply
    • Ranjit have a look at this next lesson: play a macro – that lesson is due to come your way shortly anyway!

      Reply
  13. Thanks i’m excited wth gd training

    Reply
  14. thanks ,
    i learn something new
    It’s great

    Reply
  15. Great simple to understand

    Reply
  16. Day2, learned about “relative reference” don’t know this before. Thank you.

    Reply
  17. All familiar stuff so far, but similarly clear explanation will be of benefit when the modules move into new territory.

    Reply
  18. very nice

    Reply
  19. Practically absorbed

    Reply
  20. Thanks. But I already know the recording part. Hoping for deepest lesson about macro on the next lesson.

    Reply
    • Hi April,

      We do start you off gently but the pace speeds up: you’ll be looping the loop with your macros in no time. If you’re in a hurry, there should be a link at the bottom of the email that came out to you. By clicking on that link you can get access to all 20 of the Excel macro lessons very quickly. That way you’ll be able to speed through all the lessons at your own pace.

      Reply
  21. This is good

    Reply
  22. ty very nice lesson I wait the next one

    Reply
  23. Great! I am excited.

    Reply
  24. It is good lesson!

    Reply
  25. This is V Good.

    Reply
  26. Very relaxed lesson indeed

    Reply
  27. Good lesson. It was interesting

    Reply
  28. Good and Thanks

    Reply
  29. I like the leason

    Reply
  30. I like the lesson, a bit nervous.
    Thanks.

    Reply
  31. Cool ! Looking forward to the next lesson !

    Reply
  32. really excellent methods to learn the new technologies

    Reply
  33. so far so good

    Reply
  34. The lesson is perfect. I recorded a macro with several functions – formatting the cell as number, centering the text and even used the function “Text to columns”. Applied the macro to the next cell – perfect. But if you have a table with 500 rows ???
    You should pass over each cell and use macro 500 times. Better use copy/paste functions.

    Reply
    • Wait until you discover looping Ivan! It’s coming up soon…

      Reply
  35. Wow. thanks 🙂

    Reply
  36. Thank you that was a very informative lesson. I like it

    Reply
  37. i try to learn now .
    your course is very good .
    That can show me many knowledge .
    So, i really thank you so much

    Reply
  38. Till now, its very simple and clear. I have applies the same practically and got the perfect results so far.
    Thanks.

    Reply
  39. good

    Reply
  40. am very glad for this lesson.

    Reply
  41. Respected sir

    i want to create combobox which is depandent more than two or three combobox

    if you send example with vba codding in excel file
    its helpful for me. plz reply asap

    thanks

    Reply
    • Deepa I have asked one of our trainers to email you separately about this

      Reply
  42. it is really interesting. thanks

    Reply
    • but unable to see the video

      Reply
  43. after few days

    Reply
    • it is good after 5 days I opened so it is good that I can get all the lessons and thanks its oky I can see and do it with self whatever it hasn’t audio

      Reply
      • Nazifa you are correct none of the videos on here have audio. We are hoping that, from the screen shots, you’ll be able to see the steps taken. Please let us know how you get on with the rest of the lessons – it’s great to get your feedback thanks!

        Reply
  44. It is good

    Reply
  45. Good

    Reply