Macros

First, we'll do the TL;DR version, in case you just need a refresher. To start recording a macro, go to the View tab, click on the Macros icon, and click "Record Macro..." in the drop-down menu. Name and assign the macro if you feel like it, then click OK. Your macro is now recording! Record the steps you want the macro to perform - reordering columns, formatting, adding formulas, or any other simple, perfectly repeatable tasks. When you're done, go back to View > Macros > Stop Recording (Or click the stop icon at the bottom of the workbook.) That's it!

Reminders - macros do EXACTLY what you program them to do - always in the same cells you designate, even if your data changes. So if your columns or rows may change, think carefully before using macros. Also, you cannot click Undo after running a macro so always Save before running a macro. Always. Save copies. Trust me.

Now, for those of you who want the full walkthrough:

If you live in spreadsheets, then you probably do a lot of repetitive steps every day. You know that one ugly report that you always have to reformat? Or that co-worker that really needs you to give them a slightly different layout than everyone else gets? This is one way that a very basic macro can rescue you! Instead of repeating those actions every time you run that report, you can record yourself doing the steps, save that recording to a button, and just click a button every time you want those steps done later!

Pros: Macros can do the repetitive motions, whether they're formatting, formulas, or moving data around. They save loads of time, and ensure that all of your spreadsheets get the exact same treatment - no more random colors or columns in the wrong order!

Cons: Macros aren't smart. They can't do what you didn't tell them to do. A macro is a recording of your steps, so if you change column B, and later expect it to change column C, then you will not be happy. It can take several tries to get a macro juuust right, but once you do, you can set it (to a button) and forget it!

So let's make a simple macro! We'll dip our toes in and get an idea of some of the things you can start automating. First, grab a report you can butcher. Next, go to your View tab, and find Macros. Click the little down arrow underneath to record a macro:

Before you even start recording, Excel wants you to name the macro, assign a shortcut key, and type a description. That's a lot of expectation before you've even started! As long as you have a name for it, you can skip the rest. Later, when everything is finished, you can always come back and fill in the blanks, or edit what you entered before. Either way, here's an example of what Excel is asking for:

Key points to remember when you fill in this info:

Macro name: No spaces in the Macro name

Shortcut key: To avoid creating a shortcut that interferes with keyboard shortcuts you already use (like CTRL+C or CTRL+A) use SHIFT as well. This opens up the whole keyboard of letters!

Store macro in: If you store the macro in this workbook, that means it will not be saved in the new workbook you generate when a new report. More on that later, but it matters!

Description: When you've made 20 macros, it may be hard to remember what each does, especially if they're similar. Use this field to describe what your macro does in general.

Once you click OK, your macro starts recording! From this point until you click stop (View > Macros > Stop Recording), everything you do gets recorded. Don't get distracted by a new email or looking something up in a different spreadsheet - you will get all kinds of weird things in your recording that way. So let's go!

We've clicked OK, and now the recording starts. Here's a shot of my base data:

I've decided that while I want to keep all of the data, I want to add a column that combines the Section, Township, and Range into one column. I also want the depth name closer to the other location information, and the Acquired From over next to the Lessee. However, I only want this info for a specific person, and I want to keep the original format for everyone else. So first I create a new tab and give it a name that I want it to be named every time. I copy my data to the new tab and adjust based on my new preferences. I change up the colors some, change some of the formatting, and add a formula to concatenate my Section, Township, and Range into one column. Then I turn my formula into text values and stop the macro. Here's the result:

It's not a huge difference, but having this recorded saves me making ALL of these steps! And macros don't have to be this simple. They can be incredibly complex, performing countless calculations and adjustments, and can eliminate the need for dozens of hours every week! From a staffing perspective, you could complete the job of several workers without actually having to hire new staff. But we're not quite done with this macro. Let's go ahead and clean up some details, while saving you some headaches.

First, that pesky "Store Macro In" option I mentioned before - why does that matter? Let's say I chose that option (I did) and recorded my macro. In order to run my macro again, I have to open this spreadsheet again. But If the whole purpose of the macro is to format a newly-generated data-dump from a database and forward that on to others, I won't be opening the same spreadsheet over and over, I'll be opening a new one that has to be formatted, right?

Sort of. While you would have to open this same spreadsheet to run the macro, you don't have to actually run the macro on this same data. When I recorded the macro, I didn't tell it to open this spreadsheet. I told it to perform steps on the data in front of me. That means as long as this spreadsheet is open, I can have it minimized and still run the macro on whatever data I have on my screen. So I could get the data-dump, open and minimize this spreadsheet, and then run the macro. When you run a macro from a different spreadsheet, it looks slightly different. Go back to your data tab, select Macros, and check out what's available. Below is a side-by-side comparison of what the macro will look like if it's in the active spreadsheet or a different spreadsheet:

If the macro is in the active spreadsheet, you will simply see the macro's name (left). If the macro is in a different spreadsheet, you will see the name of the sheet and then the macro's name (right).

This next tip only applies if you try to run the same macro in the same spreadsheet multiple times - when you create a new spreadsheet tab in your workbook by clicking that little plus sign at the bottom of the sheet, Excel will start off with "Sheet 1" as the name of the new sheet. Even if you delete that sheet, when you click that plus sign again, Excel will name the new sheet "Sheet 2". So if you create a macro that creates a new worksheet and rename it, the actual code that gets recorded in Excel is something like this:

Which works great the first time, but the second time you run it and Excel creates a tab named Sheet 2, then it can't rename "Sheet 1" because "Sheet 1" doesn't exist. If you find yourself in this situation, you have two ways to remedy it - use a new spreadsheet to run the data, and have your macro spreadsheet open but not active, as discussed above, OR, edit your code. Editing code can be very dangerous, so if you can avoid until you're more comfortable with how to read it, I would suggest the first option. However, if you can't easily open a new spreadsheet and try again, or if you just want to see what the macro code looks like and learn how to read it, go back to your Data menu, then Macros. When you see the menu with your list of macros, select your macro and click "Step Into". This will take you into the screen picture above, giving you access to all of the code.

Final tip about macros - you cannot click UNDO after running a macro. Editing code can break things. Macros can do a lot of damage. For all of these reasons, ALWAYS save before starting or editing a macro! That way, if you make a fatal mistake, you can simply close the workbook without saving, re-open, and start again. The more often you save (especially if you also save a separate backup copy!) the less work you have to do in the long run.

Have questions or issues with macros? Drop me a message! I once turned an entire department into a macro in a spreadsheet, and I'd love to help you find similar shortcuts!