A large portion of my workday consists of working within Microsoft Excel. This includes things like keyword research and building reports to more general projects like brainstorming sessions or project management. Using Excel is inevitable in the online marketing world, which motivates me all the more to master it and stay efficient. Beyond the basic keyboard shortcuts and formulas, Excel offers an even more powerful feature – macros.
The best part is you don’t need to know how to code in order to create your own. If you do know how to code, you can extend macros to automate just about anything. This post will walk you through creating two macros that I use constantly and am sure have already saved me hours of time by automating common actions. This example will be using the keyword export of Google’s AdWords Keyword Tool (GAKT), my favorite tool for keyword research. Along the way, you’ll learn the process so you can begin creating your own macros and boost efficiency with any project you have.
When you export your saved keywords, you are given a CSV file that will look like the screenshot above. The first two things I would do from this point are 1) remove the brackets and 2) resize the columns so the workbook is more legible. After months of doing this manually to hundreds of these exports that always come in the same exact format, it was time to automate this process and perform these actions with a single keystroke. It’s actually really easy to make this possible and only takes a second.
To start, navigate to Tools > Macro > Record New Macro. You will then be prompted to name it, choose a Shortcut Key to which you assign the macro and choose the workbook to save it in. I’ve named mine RemoveBrackets, assigned it to Command+Option+E and saved it in the Personal Macro Workbook so that the macro is available in all of my workbooks. Once you click “OK”, recording has begun and every action within the Excel window will be recorded into the macro. From here, remove the brackets with a find and replace. To do this, go to Edit > Find or by using the Command+F keyboard shortcut. Then click the “Replace…” button. In the “Find what:” field, enter the [ character and leave the “Replace with:” field empty. Then select “Replace All” and repeat this with the closing bracket character. Finally, stop the recording by navigating to Tools > Macro > Stop Recording. From this point on, the shortcut key you assigned will run the macro and remove the brackets automatically in a fraction of the time it takes to do it manually as described above.
Next, lets create a macro to resize the columns. The process is exactly the same as the previous macro, only when recording, simply select all cells in the entire workbook by pressing Command+A twice and double click on one of the column edges. I named this macro ResizeColumns and assigned it to the Shortcut Key Command+Option+Y. When finished my worksheet looks like the following:
At this point it’s a good idea to close the workbook without saving and reopening to test the macros. If recorded correctly, you now have two macros that will save you hours of time in the long run. The two actions we recorded here are very simple and only take a few seconds to do manually but after repeating them dozens of times a day, several days a week, I’m very glad to have them in my toolbox.
There is one issue I found with the ResizeColumns macro. If one of your columns only involves small numbers or words, the columns will shrink to that size which can make your worksheet harder to read. In this example, let’s say we didn’t have the column titles in the first row; this macro would make the sheet look like the following screenshot. The fix is easy but involves a couple lines of code.
First make sure that the workbook you saved the macros in is not hidden by navigating to Window > and looking at the currently open workbooks. If you don’t see it there, select Unhide and unhide the workbook. Then navigate to Tools > Macro > Macros, select the ResizeColumns macro and select “Edit”. You’ll see the code that was written behind the scenes when the macro was recorded. Before the “End Sub” line and after the code already provided, I’ve added the following lines to my macro to prevent the columns shrinking past the default width, which is 10.
Dim lastCol As Integer
lastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
For c = 1 To lastCol
If Cells(1, c).ColumnWidth < 10 Then Cells(1, c).ColumnWidth = 10
Basically, what’s going on here is the following process:
- Find the last column with data in it
- Cycle through each of the used columns
- If the current width is smaller than 10, resize the column back to 10
View the following screenshot for how my macro looks now with the new code outlined:
After adding the code, save the file with Command+S, quit the editor and retry your macro. This example was kept very simple in hopes of inspiring others to begin creating their own macros to automate their most tedious processes. These two macros are by far the ones I use most but I have numerous others that are more complex that can format whole multiple-tab reports for me automatically within several seconds, a process that used to take me up to an hour to do manually. Sure I spent several hours to write the code for the macro but I knew that this would be a process I would have to do several times a month, so it was well worth the initial time spent.
Can you think of any Excel action that you perform manually dozens of times a day? Do you wish your most tedious tasks were automated? Create a macro and share it below.