Excel Macros – How to create them?
How would you like to press a few keys, then sit back and watch Excel do your work for you? Creating a Excel macro will allow you to do this. And it’s a lot easier to do than most people think.
You can create Excel macros that will perform a series of routine steps, and then run the macro by simply typing a keyboard shortcut.
For example, if you often need to perform a regular series of steps to format your spreadsheets for printing, you can create a macro to perform all those steps for you. Or if you often import data in a .txt or .csv format from another program, and you need to perform several steps to make the data easier to view, you can record these steps as a macro.
To record a macro, in Excel 2003 or earlier versions, click on Tools in the Menu Bar, Macros, Record New Macro. In the Record Macro window that appears, type a letter into the box under Shortcut key: Since this will be your shortcut key for running the macro, I recommend that you use a letter that is not assigned to a commonly used shortcut.Click OK.
You will now be recording every step you take. Carefully perform each step of the routine exactly as you want it to be recorded. When you are finished, click Tools, Macros, Stop Recording.
Some examples of how you can use a macro:
Example 1: A macro that formats a spreadsheet for printing
Click on Tools in the Menu Bar, Macros, Record New Macro.
In the Record Macro window that appears, type a letter into the box under Shortcut key: (I recommend that you use one that is not assigned to a commonly used shortcut.)
Click OK.
You are now recording.
Click on File in the Menu Bar, click on Page Setup.
Click on the Page tab at the top of the Page Setup window and select the orientation (portrait or landscape)
Click on the Margins tab at the top of the Page Setup window and set the margins (I always check the “horizontally” box under “center on page” to center the spreadsheet on the page)
Click on the Header/Footer tab at the top of the Page Setup window and select a header and a footer from the dropdown lists
Click on the Sheet tab at the top of the Page Setup window. If you would like the top row of the spreadsheet to repeat on every printed page, click once in the “Rows to repeat at top” box and type $1:$1 If you prefer to have the grid lines showing, click once to place a check in the grid lines box
Click on OK.
You have now finished performing the steps you want to record: you now want to stop recording.
Click on Tools in the Menu Bar, click on Macros, click on Stop Recording.
To run the macro, go to the spreadsheet you want to format for printing and type in your shortcut. Excel will go through all of the recorded steps as you watch.
Example 2: A macro that formats exported raw data for easier viewing
To create a macro that formats raw data in an Excel spreadsheet (Excel 2003 or earlier):
Open a spreadsheet (this can be one you have already created)
Click on Tools in the Menu Bar, Macros, Record New Macro.
In the Record Macro window that appears, type a letter into the box under Shortcut key: I recommend that you use one that is not assigned to a commonly used shortcut.
Click OK.
You are now recording.
Select the top row of the spreadsheet by clicking on the row header (the number 1 all the way at the left of the row)
Click on the Bold icon on the toolbar.
Type Ctrl+A to select the entire spreadsheet
Double-click on the vertical line between the header “A” and the header “B” at the top of the spreadsheet, to automatically resize all of the columns to fit their contents.
Select the second row of the spreadsheet by clicking on the row header (the number 2 all the way at the left of the row)
Click on Window in the Menu bar, click on Freeze Panes, to keep the top row in view when you scroll vertically.
Click once in the middle of the spreadsheet.
You have now finished performing the steps you want to record: you now want to stop recording.
Click on Tools in the Menu Bar, click on Macros, click on Stop Recording.
To run the macro, open a spreadsheet that contains raw unformatted data and type in your shortcut. Excel will format the data so you can easily view it and work with it.
