If you’re not using macros, you’re ignoring one of Excel’s most powerful features. Macros save you time and spare you headaches by automating common, repetitive tasks. And you don’t have to be a programmer or know Visual Basic Applications (VBA) to write one. With Excel 2013, it’s as simple as recording your keystrokes.
Here we’ll show you how to create macros for five commonly performed functions.
Macro Basics
To record a macro, click Record Macro under the Developer tab. In the Record Macro dialog box, enter the following information and click OK when you’re done.
Macro Name —the first character must be a letter, followed by your choice of letters, numbers, or an underscore. No other characters are accepted.
Shortcut Key —CTRL+J and CTRL+M are available. If you choose any other character, your macro will overwrite that key’s original function.
Save location —Macros saved in “This Workbook” or “New Workbook” function only in those workbooks. To use in all spreadsheets, save macros to the Personal Macro Workbook (PMW).
Description —Describe the macro.
Name, save, and define your macro.
Because macros perform repetitive tasks, the object is to use them on a lot of different spreadsheets. This means you cannot hard-code the cell addresses (C1, D5, etc.), unless all of the spreadsheets are identical, which means the same number of records in the same columns and rows. To make the macro work on all spreadsheets with similar data, you must use the directional keys to navigate—then, the number of records won’t matter—and always begin at the A1 position.
Organize, format, and sort imported data
Data from other programs is often available in TSV or CSV files (Tab- or Comma-Separated Values). Imagine receiving two dozen of these files every month, which have to be organized, unwanted data removed, and then sorted by company name. It takes hours to do a report like that. This macro does it in seconds.
Open the CSV worksheet. Follow the directions above to name, define and save your macro, then record the keystrokes below.
1. Press CTRL+Home to reposition your cursor in cell A1. Hold down the CTRL key and click the letters over the columns you want to eliminate (B through N plus R). Select Home>Delete>Sheet Columns.
What a mess. First delete unwanted columns.
2. Hold down the CTRL key and click columns A and D. Select Home>Format>Column Width>42>OK. Hold down the CTRL key and click columns B and C. Select tab Home>Format>Column Width>25>OK.
3. Press CTRL+Home, then CTRL+A (to select all data in the spreadsheet).
Select Home>Sort & Filter>Custom Sort. In the Sort dialog box under Column, choose Name. Under Sort On, choose Values, and under Order, choose A-Z.
Sort by company name.
4. Select Developer>Stop Recording, and it’s finished. Save the worksheet as an Excel file. Open the CSV file again, select Developer>Macros, select the BranchCSV macro from the list, and click Run. The entire worksheet is organized in one second.
Open the CSV file again and run the macro.
Split names from one column into two
How many times have you received a long list of names in one column you needed split into two columns, so the first and last name are separated? This macro does it in seconds plus sorts the list, adjusts the column widths, and gives a total list count. Open the Names worksheet, name and define your macro, then record these keystrokes.
1. Press CTRL+Home, CTRL+A. Select Data>Text to Columns. In the first Wizard dialog box, click Delimited>Next. In the second box, choose the character that delimits (separates) your text. Our list is separated by spaces, so check Space>Next. In the last box, click Text>Finish.
Choose Text to Column to separate first and last names.
2. Press CTRL+Home, then CTRL+A. Select Home>Sort & Filter>Custom Sort. In the Sort dialog, select column B in the Sort By field. Click Add Level, then select column A in the Then By field. For Sort On and Order, leave the defaults Values and A-Z, then click OK.
Sort two levels, last name, then first name.
3. Press CTRL+Home. Press Shift-Right-Arrow to highlight A1 thru B1. Click Format>Column Width>15>OK.
4. Press CTRL+Home. Select Home>Insert Sheet Rows, twice. In A1, type Total Names. Use the right arrow key to navigate to B1, then enter this formula: =COUNTA( and press CTRL+Down-Arrow, End, Shift-Down-Arrow, Enter, CTRL+Home. The total appears in B1.
Enter the formula to count the names in the list, then run the macro.
5. Stop Recording, save the worksheet as Names2. Open the Names file again and run the macro.
Split column and adjust for middle names
In Excel 2013, it’s easy to divide one column of names into two columns, but what if half the list contains middle names/initials and half does not? This macro extracts the middle names/initials entries, rejoins them with the first names, then produces one list with first/middle name in first column and last name in second column. Open a three-names file, name and define your macro, then record these keystrokes.
1. Press CTRL+Home, CTRL+A. Select Data>Text to Columns. In the Wizard boxes, click Delimited>Next, Space>Next, and Text >Finish. One column becomes three.
2. Press CTRL+Home, CTRL+A. Select Home>Sort & Filter>Custom Sort>column C. Press Shift-Right-Arrow. Click Format>Column Width>15>OK.
3. Press CTRL+Home, Right Arrow twice. Press End once, Down-Arrow twice, Right Arrow once—this moves the cursor to the first empty cell in column C, then to the adjacent cell in column D. Type: STOP, press Up Arrow, End, Up Arrow. Type this formula: =A1&” “&B1, then press Enter, Up Arrow.
Use directional keys to navigate so the macro works on all similar worksheets.
4. Press CTRL+C, Down-Arrow. Hold down Shift, then press End, Down Arrow, Up Arrow, Enter (copies formula). Press Up Arrow once, hold down Shift, press End, Down Arrow, Up Arrow (this highlights the range without STOP).
5. Press CTRL+C, CTRL+Home, select Paste>Paste Special>Values>OK. Press Escape, CTRL+Home, Right Arrow twice. Hold down Shift, press End, Down Arrow, CTRL+C, Left Arrow, Enter to copy last names. Press Right Arrow, Shift-Right-Arrow.
Copy and paste the formula, then remove extra columns.
6. Select Delete>Delete Sheet Columns. Press CTRL+Home. Stop Recording, save the worksheet as 3Names2; open 3Names again and run the macro.
Repetitive Text
If you’re typing the same information 10 times a day, you’re begging for a macro. Even if that information is brief, a macro does it in seconds and ensures accuracy. This macro adds your company info to the top of a worksheet and inserts the current date. Open a new worksheet, name and define your macro, then record these keystrokes.
1. Press CTRL+Home. Hold down Shift, then Right Arrow twice. Select Home. From the Alignment group, select Merge Across. Enter this formula in cell A1: =TODAY() Enter, Up Arrow. Select Home>Format>Format Cells>Date. Choose a date format from the list, click OK. Press Down Arrow twice.
2. Type the repetitive information and press Enter at the end of each line. Press Down Arrow. Select Developer>Stop Recording. Delete it all, unmerge cells A1 through A3, then run the macro. Save the worksheet.
Create a header macro with the current date to eliminate repetitive typing.
Remove Blank Rows
A worksheet filled with blank rows is impossible to manage, sort, or calculate. The first step is to instruct the macro to highlight the spreadsheet data only, then select and remove the blank rows. Once that’s accomplished, you can easily manage the data.
Open a file with blank rows, name and define your macro, then record these keystrokes.
1. Press CTRL+Home. Note: CTRL+A will not select all the data when blank rows are in the spreadsheet, but this macro will.
2. Select Home>Insert>Sheet Column. Press End, Down Arrow, Right Arrow, End, Up Arrow. Press CTRL+Shift-Home, Shift-Right-Arrow, CTRL+Shift-Right-Arrow. And the data range is properly selected.
Remove blank rows.
3. Select Home>Find & Select>GoTo Special (or press CTRL+G, ALT+S). Click Blanks>OK and all the blanks highlight in gray. Select Delete>Delete Cells>Shift Cells Up>OK and the blanks vanish. Press CTRL+home, then select Delete>Delete Sheet Column to remove the extra column we inserted to highlight the spreadsheet without hardcoding cell addresses.
4. Stop Recording. Undo all steps, then run the macro. Save the worksheet.