Excel Data Interview Series: Macros in Excel

Every week, you create a report for your accounting manager, where you highlight the details of employees, their weekly sales and targets met. You create pivot tables, make use of conditional formatting, array and regular formulas and functions for this task. You do this every week, that’s 4–5 times a month, the same old, boring, repetitive task, for more than 1000 employees. Now it’s another Friday, and everyone else has gone home, while you labour. Where’s an AI assistant when you need one?

Hey there, lovely people 😄. Welcome to Day 5 of the Excel Data Analyst Interview Questions series. You can read up yesterday’s topic — Pivot Tables in Excel — here. Today’s lesson is on Macros in Excel, and how they help to relieve you of doing repetitive tasks everytime (like an AI assistant 😁).
Let’s get to it then!

An Excel macro is an action (or a set of actions) that you can record, save and run as many times as you want. Macros help to save time on repetitive tasks involved in data manipulation and data reports that are required to be done frequently. Now instead of you working on this report for hours, you could get it done within 10 minutes, using Macros. Wow!

How Can You Create a Macro in Excel?

• Well, first off, you click on the ‘File’ menu on your Excel worksheet
• Then click on ‘More’ > ‘Options’ (or just ‘Options’)
• On the left-hand side, click on ‘Customize Ribbons’
• A ‘Main Tabs’ menu will appear on the right-hand side. Since the macro is under the Developer tab, check the ‘Developer’ box, then OK
• Now on your worksheet, click on the ‘Developer’ tab and select the ‘Macros’ option. As you have no existing macro, this will be empty, so you’ll need to record one
• To record a macro, click on the option, and give your new macro a name. So whilst your macro is recording, go ahead and carry out the operations you’d like the macro to carry out. Once you’re done with this, click on ‘Stop Recording’
Tada! Your Macro is saved for the next time you need to automate these tasks. All you need do is to run this existing Macro.
There are different ways to do this, but one is to click on the ‘Insert’ option under the ‘Developer’ tab, insert a button and give it a name, so when you click on your button, it runs your macro.

You can record a bunch of macros, too, so you have one to automate for your Pivot tables, another for your conditional formatting, then one for running your formulas. So easy.
Another amazing feature of this is you can edit your Macro anyhow you see fit and according to the operations you need to carry out at any given time. So goodbye to repetitive tasks. 🥳

Hope you enjoyed this lesson, and understand Excel Macros better, and be able to create and make use of them. If you did, you know the drill already- claps and comments. Be here tomorrow for our discussion on VBAs in Excel. 🤗