Macros
If you have a series of steps that you find yourself performing often, you can record the steps and assign a keyboard shortcut to the recording. From then on you can run the entire series of steps by simply pressing the keyboard shortcut.
To record a macro
In Excel 2003 or earlier versions, click on Tools in the Menu Bar, Macros, Record New Macro.
In Excel 2007, click on the Developer tab and click on Record Macros.
In the Record Macro window that appears:
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.
Examples
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
Open 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.