MVP #79: A Practical Introduction To Excel Macros

If you have got a recurrent report you make in Excel that follows the same steps, uses the same input type and the final report has a non-changing standard structure, then Excel Macros should be your friend. With Macros you can automate the report making steps so that every time you need to make the report, you simple provide the new input data, run the Macro and you'll have in seconds the output that probably used to take you hours to make.

The best part is that Excel Macros aren't that hard to make. Just like Excel, every level of user can make some productive use of it -- be you a beginner, intermediate or advanced Excel user. Also, it is with use of it that you become more proficient and maybe become like me, making money from building Excel Macros for companies.

Today I will be taking you through a practical example that will start you on your journey to harnessing the power of Excel Macros.

You can watch the video for this practice here: https://www.youtube.com/watch?v=yHzAPGqT4f8 and the practice file is here: http://tinyurl.com/ExcelVideo23

To make the example sink well, let's assume you work for a car dealership and you are in charge of sales analysis. The company is obsessed about branding. They've got the office built to reflect the company brand, the security men even wear uniforms that are the company's corporate colour. The part that worries you though is that all reports are meant to have a particular company style format.

Font 12, Arial, Dark Blue and All Borders. That's the style format. 

There are many ways, in the practical world, to automatically apply those formats to your monthly sales report -- from creating custom styles to using copy paste formats. But today we will use Excel Macros. It is a perfect example to introduce Excel Macros without boring you will tedious irrelevant work.

So here is the table of the entire year's report. You will now use Excel Macro to store the formatting process and apply them whenever you want at the click of a button or keyboard shortcut.

First, you need to enable Developer menu.

Go to File, Options, Customize Ribbon and tick Developer in the middle right of the dialog box you get. Click on OK.

Now you have all the tools needed. You will be able to see a record macro button at the bottom left of your Excel and a Developer tab on your menu bar.

Next step is to have a Macro created for the steps we do to apply the company standard format.

Select the January's data, we will use Macro Recording to store our steps. Click on the record button at the bottom left.

Give the macro a name. Start with an alphabet and don't put space or any special symbol except underscore. Let's also tie it to keyboard shortcut CTRL + m (m for macro, but you can use any you prefer, just avoid using common ones used by Excel like CTRL + C or CTRL + V)

Now your steps are being recorded. Apply the company format style of Font size 12, Font type Arial, Font color deep blue and all borders.

Click on the Stop Macro record button. same button you clicked on to start the macro recording.

Let's test for another month -- February.

Select February's table and do CTRL + m. If you followed my steps correctly you will see Excel automatically repeat all your steps and apply the company format to the February table.

Congrats! Now you've created a Macro. You now understand the process. Next is to use the same concept/steps to create one more relevant to you. Automate those repetitive tasks you do. If I had a more complex task, it would have recorded it too and repeated my steps whenever I called on it.

Finally, let's tie the Macro to a button.

Click on the Developer tab, Insert and choose button under the Form Control section.

Draw a rectangle, in the dialog box that will pop up, select the macro you created and attach it to the button by clicking on OK.

Give the Macro button a name. And now test it by selecting March table, then click on the Macro button to watch it apply the company format to March table.

Finally, save your Excel file as a Macro enabled workbook. 

Congrats on your new Macro!