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.
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.
thanks for such great information, i have to learn code to add buttons in excel
Hey Vikram, adding buttons is not that difficult. I will write a tutorial on this very soon, so make sure you come back here from time to time, or better subscribe to my feed. Thanks.
In my efforts to learn how to use macros I have lost the floating toolbar with the stop and Relative Reference buttons. Any suggestions on how to get the floating tool bar back?
Yes, Ed. You go to Tools in the main menu, then select Customize, then Toolbars (the first tab). Scroll down and tick the box Stop recording. You’ll get your toolbar back.
i have created list boxes to excel sheet and then —double click on listbox, it is navigating to code sheet, there i have to enter some data to list box ? can any one suggest me
please explain with a detailed example in excel.
this is really helpful to learn about macros …thanks for the information..
Macro is very interesting and easiest option to work in excel. thanks to sharing this information. I wanted to learn more about macro. I don’t know how to start and what are all coding need to use over there in macro. Please could you saggest me out this. I want to learn this from initial stage.
Could you pl help me to get Macro in excel learning tutorial/ready reference material.
I will be appreciated if you can send me on my email-id email@example.com
Thanks & Regards,
More info on macros. i.e any other benefit of macros?