Last Updated on October 13, 2023

It happens many times that we have to accomplish boring repetitive tasks in MS Excel, such as formatting cells in lots of tables, to make them look more professional, in order to print, let’s say, a report for a client. Those tasks, although easy to do, require a lot of time to complete. Repetitive tasks can become a children’s play and all it takes is a little bit of set up in the very beginning. Then, all you have to do is press one key and look how the computer is doing the job for you. This can be accomplished by using macros.

Macros are symbols, names, or keys that represent a list of commands, actions, or keystrokes. Many programs, including MS Excel, allow you to create macros so that you can enter a single character or word to perform a whole series of actions. For creating a macro, you don’t need any programming knowledge.

It is better if you do, as this allows you to fine-tune your macros in order to get the best out of them. Here’s an article about Excel macros for beginners. Read it to find out how to use these helpful functions in Excel.

The easiest way to make a macro is to record it.

In the menu bar, go to Tools – Macro – Record New Macro. Then you give it a name and choose a location where your macro will be stored in your computer. You can also assign a shortcut key combination to access the macro later on.
Now you are ready for your macro: starting with this moment, all your actions are recorded in the macro you have just defined.
Let’s suppose that you want to format one sheet for printing – you set up the print area, margins and you define the header and the footer. When you are finished, you press the button “Stop” (the one with a blue square on it) on the Macro floating menu which you should have on your screen from the moment you started the recording.
Now go to another sheet which needs the same formatting as the previous one, then press the key combination you assigned to your macro. See what happens: in a few seconds, you have your new sheet formatted, ready for print and all that you did was to press one key combination.
If you forgot to assign a key combination to your macro, you can still launch it from Tools – Macro – Macros – then select your macro and click on Run.

Quick Excel Macro Tip: you probably noticed that the actions in your macro are always performed on the same cells, in every sheet. What if you need to use this formatting macro on the same sheet but on different cells? Well, you have to know this before you start the recording. When starting a new macro, after you give the macro name, and the macro pop-up menu disappears, look at the floating menu you have now on your screen: there is a button in the shape of a grid, on the right side: if you press it once, it switches to relative reference mode recording, meaning that your macro will start to be applied from the current position of the cursor and not of the first cell, as in the case of absolute reference.

How much time one can save with macros?

I can tell you from experience that I managed to to do a three-day job in less than 20 minutes, with only one smart macro. It is true, it took me about one week to make it work properly, as it required a little bit of programming as well (because I needed some dynamic actions, I had to edit the macro and to input those instructions by hand). It was worth doing that, as that job was a monthly report which I had to do for a client for two years.

If you are interested in more resources, you can find some great ones here.