Magical Macros

Remember when I promised to introduce you to the wonderful, magical world of macros?

I told you that if you really want to impress your interviewer, you should drop 3 key terms: Vlookups, Pivot Tables, and Macros. So you just explained Vlookups to your interviewer -they were pretty impressed! Then they heard the word “Macros” and their eyes widened like you just placed a 10 pound turkey in front of them while juggling fire. Obviously they’re going to ask you about it. I want you to be able to not only name drop, but be able to back that up with knowledge of how it works and how you can use it in your job to kick butt.

I hope you’re prepared for your relationship with Excel (and probably life too) to change forever … because this is it! I would like to introduce you to The Magical World of Macros…

(If you can’t tell by the way I’ve hyped up macros – I have a slight crush.) Anyway, the interviewer is waiting patiently for you to tell them what a Macro is and how you use it, so let’s get started.  So what is a Macro?

The Scoop:

Macros is an Excel tool used to automate repetitive processes by recording keystrokes and mouse clicks.

At this point you’re probably saying, “what?! that’s it?! that doesn’t sound that cool…”. That’s the most basic definition which I needed you to know before I can tell you this story. I’m not going to lie, I was not that impressed when I first looked it up because I couldn’t visualize the potential applications.

The Real Life Application (Example):

So let’s say that every week you run the same report. Monday morning you get to work, and like clockwork your boss asks you for a usage report of the top 3 customers from last week. You could do this process manually, sure! OR you could create a macro the first time around and sit back and drink your coffee (just kidding)! So let’s get into how you would do that …

The Set-Up:

Step 1: Open up your workbook.

Sheet 1 is a list of customers and usernames exported from the company accounting database (we’ll be sticking with the donut theme for this example too – because who doesn’t like donuts?):

Capture2

Sheet 2 is a list of usernames and quantities sold:

Capture3

Step 2 : Getting into record mode

Open the Macro recording feature. In Excel 2013: Go to View> Macro s> Record Macro.

A screen will pop up asking you to name your macro and where you would like to store it. (I recommend saving it in your Personal Workbook that way you can use the macro on any workbook on your computer without having to open the workbook it’s saved in).

Capture

Woohoo! Now we’re in business. Anything you do from this point until you click stop recording will be saved. (Don’t worry though – it’s okay if it’s not perfect all at once! I always end up doing mine in multiple steps and combining them – I’ll show you that too).

Step 3 : Recording the Macro

Remember how much fun we had learning Vlookups last time?! Well they’re back! Here’s the step by step of what I would do to find the top 3 customers:

1. Select cell C1

2. Type: “Quantity Sold” and make it Bold

3. Select cell C2

4. Type the formula: =VLOOKUP(A2,Sheet2!A:B,2,0)

(Mini Vlookup Refresher: A2 is what I’m searching for, Sheet2!A:B is where I want excel to search, column 2 is the data I want excel to return once it finds the value, and 0 means I want an exact match).

5. Double click the right corner of cell C2 to auto-fill the contents through the last row containing data.

6. Click out of that column (otherwise when you apply the filter it will only filter that column and not the others).

7. Select the Filter feature (Home> Sort & Filter> Filter) (and sort the sheet from from highest to lowest) : > Select the little arrow at the top of column C > Sort Largest to Smallest.

Sheet 1 will now look like this:

Capture4

Step 4 : Stop Recording

At this point you can select stop recording by: View > Macro s> Stop Recording. (Or, as a helpful tip, sometimes there’s also a little square stop button near the bottom corner of your screen.)

Step 5 : Playing A Macro

You just created your first Macro! *high five!* Now, next Monday morning when your boss asks to see the top 3 customers from last week you can run the macro and impress him with how fast you created that report. This is how you’d do that:

View> Macros> View Macros

Macros in .. “Personal Macro Workbook”

Find your Macro and select “Run”

Capture6

Special Notes:

*Let’s say you make a mistake in the middle of recording. You can stop and start recording a new macro then paste the codes together. I didn’t want to get too far into coding because this is supposed to be a beginner tutorial (and I didn’t want to scare you all away with nerdy coding language).

*However! I highly recommend selecting “Step Into Macro”. This will lead you to the code that you created during the recording. This is where you can see what your clicks and keystrokes actually do in programming language. It’s pretty nifty. (I will probably post on this in the future just because it’s something I love.) Below is a screenshot of what the code we just created looks like:

Capture5

To recap:

The interviewer just asked you how proficient you are in Excel and you dropped the magical “M” word bomb: Macros. (His eyes widened like you were wearing a suit of bacon). He played it cool and asked you “What exactly is a Macro and could you give me an example of how you would use it at work?” So you said, “Macros are used to automate repetitive tasks through the recording of keystrokes and clicks. Recording a macro creates a code that can be edited and used in the future on repetitive tasks thus increasing efficiency. An example of when a macro could be used is in an instance of creating a weekly report of top selling customers.”

By this point if you just said all of that, for one you’re much smoother than I am in an interview, and two you probably just got offered the job.

This is a very simple example, but macros truly are powerful. I’ve created macros for things like daily customer usage reports, invoicing certain services, and creating personalized emails to customers. There’s even web-based macro recorders (iMacros). Which is really great for cloud-based company software (SalesForce, Intacct, etc). I’ve even created an iMacro which reads from an Excel sheet, and charges thousands of invoices at a time! I hope not only that you’ve now landed that job, but also fallen in love with Macros like I have. I really encourage you to leap into that little “Step Into Macro” button. You’ll love it and you’re boss will love you.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s