Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

5 must-know Excel macros for common tasks

J. D. Sartain | Sept. 11, 2014
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.

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.

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.

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.

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.

 

1  2  3  Next Page 

Sign up for CIO Asia eNewsletters.