Excel Applications
§ What is an Excel Application?
o An Excel application transforms data into information that forms the basis for decision making (EX 356).
o An Excel application is a workbook that has customized buttons for users to operate in place of Excel commands. Basically, this frees a user from having to know Excel formulas and commands.
§ Validating Data Entry:
o Excel’s data validation feature enables you to define a set of rules that guide data entry for a specific range of cells (EX 357).
o A validation rule is used to define the parameters by which data is entered in a cell (EX 357).
o To create a validation rule,
§ Click on the cell on which you want to establish the validation rule.
§ Click Data on the menu bar, and then click Validation.
§ Click the Settings tab, and enter the validation rule using the available drop-down menus and input boxes.
§ Click the OK button.
o An input message can provide additional information about the type of data allowed for the cell (EX 360).
o To create an input message,
§ Click Data on the menu bar, and then click Validation.
§ Click the Input Message tab.
§ Enter a title and text for the input message.
§ Click the OK button.
o An error alert is a message that appears if data is entered that violates any of the validation rules that you specified earlier.
o Alert styles:
§ Stop – prevents invalid entries from being entered.
§ Warning – prevents invalid entries from being entered unless the user overrides it.
§ Information – accepts invalid data, but allows you to cancel the data if you choose.
o To create an error alert message,
§ Click Data on the menu bar, and then click Validation.
§ Click the Error Alert tab.
§ Make sure the Show error alert … box is checked.
§ Enter title and text for error alert message.
§ Click the OK button.
§ Locking and Unlocking Cells:
o Every cell has a locked property that determines whether or not changes can be made to that cell. By default, the locked property is turned “on” for each cell (EX 364).
o The locked property has no impact as long as the worksheet is unprotected. By default, worksheet protection is not activated.
Cell Locked Property On / Cell Locked Property OffWorksheet Protection Active / Cell is protected; no data may be entered / Cell is not protected; data may be entered
Worksheet Protection Inactive / Cell is not protected; data may be entered / Cell is not protected; data may be entered
o To protect a cell or range of cells,
§ For the range of cells that you want to be able to enter data, turn the locked property to “off” by clicking Format on the menu bar, click Cells, click the Protection tab, and deselect the Locked text box.
§ Click Tools on the menu bar, point to Protection, and then click Protect Sheet.
§ Click the OK button.
§ Macros:
o A macro is a series of commands that are stored and can be run whenever you need to perform a task (EX 377).
o Macros carry out repetitive tasks more quickly than you can, and once the macro has been created, you don’t have to worry about mistakes that may occur from typing errors (EX 377).
o The easiest way to create an Excel macro is the macro recorder, which records your keystrokes and mouse actions as you perform them (EX 377).
o To record a macro,
§ Click Tools on the menu bar, point to Macro, and then click Record New Macro.
§ Enter a name for the new macro, and specify the location in which you want to store the macro.
§ Click the OK button to start the new macro recorder.
§ Click the Stop Recording button on the Stop Recording toolbar.
o To run a macro,
§ Press the shortcut key assigned to the macro.
OR
§ Click Tools on the menu bar, point to Macro, and then click Macros.
§ Select the macro from the list of macros, and then click the Run button.
o A more complicated way to create or edit an Excel macro is to use the macro’s underlying Visual Basic code (EX 389).
§ Macro Buttons:
o Another way to run a macro is to assign it to a button that is placed directly on the worksheet (EX 393).
o The user can run the macro by simply pressing the button (EX 393).
o To create a macro button,
§ Click View on the menu bar, point to Toolbars, and then click Forms.
§ Click the Button tool on the Forms toolbar, and then click and drag the mouse pointer until the button is the size and shape you want.
§ Release the mouse button. The button appears on the worksheet with a default label, and the Assign Macros dialog box appears.
§ Select the macro you want to assign to the button. With the button still selected, type a new label.