Page 178 - Excel Data Analysis

10 537547 Ch09.qxd 3/4/03 12:13 PM Page 164

EXCEL DATA ANALYSIS

RECORD A MACRO

ou can use macros to automate a series of steps used Excel creates the macro with either relative or absolute
to analyze data within a worksheet. For example, if reference to the cell where you apply it. To use a relative
Y you repeatedly sort and sum a series of values, you reference, meaning that the macro is performed based on
can create a macro to perform that task. The easiest way to the location of the cell, specify the cell reference by
create a macro is to use the Macro Recorder, which selecting the Relative Reference button on the Stop
captures everything that you do and saves the steps in a Recording toolbar. For example, you can create a macro
macro with a name that you specify. After you create a that adds the values in the four cells above and places the
macro, you can run the macro again, modify it, or delete it. total sum in the selected cell. With absolute positioning,
however, the macro records in absolute mode and
Because the Macro Recorder records every action that you remembers the specific recorded cells. For example, the
perform when you use it, consider planning your steps macro remembers always to add the same cells (such as A1
before creating a macro. When you plan out the macro through A5) and place the total sum in cell A6. You can
steps beforehand, you can save yourself some recording toggle between relative and absolute referencing while
time, and the macro will run faster and more effectively. recording your macro by selecting the Relative Reference
When you name a macro, use a name that starts with a button. For more on relative and absolute cell references,
letter and has no spaces in it; you can, however, use the see Appendix D.
underscore character to separate words.

RECORD A MACRO

This Workbook

⁄ Click the worksheet cell Note: If you intend to use other ■ The Record Macro dialog › Select where you want to
to contain the results of the worksheet cells in your macro, box opens. store the macro.
macro. make sure that the cells contain
the desired values. ‹ Type a unique name for ˇ Click OK.
the macro.
¤ Click Tools ➪ Macro ➪
Record New Macro. ■ You can create a keyboard
shortcut for your macro by
typing the desired key in
the Shortcut Key box.
164