Excel

Excel
Project 5
Data Tables, Visual Basic for Applications, Hyperlinks, and Scenario Manager

Introduction

Data table - what-if-tool, automates data analysis, organizes answers

Macro - series of Visual Basic statements

Visual Basic - tells Excel how to carry out an operation

programming - set of instructions

VBA (Visual Basic for Applications)

Key Features Excel

Hyperlink - built-in links (URLs or file path names) to other Office documents or Web Pages

can be assigned to text or clip art object

Cell protection - ensures cells are not changed that are critical to the worksheet

Formulas Using Natural Language

Formula entered using row or column titles is called a natural language formula

example:

=price - down payment

Note: do not have to worry about capitalizing letters

Alternative to Using
Natural Language Formulas

Use Name Box (formula bar) or Name command on Insert menu to assign names to cells.

Can enter formulas anywhere on worksheet using the defined names

PMT function

Determines monthly payment

general format:(3 parts)

=PMT(rate,payments,loan amount)

rate - interest rate per payment period (monthly or (yearly interest / 12))

payments - number of total payments (years * 12)

loan amount - amount of loan (negative value) put in formula as minus to make absolute

Data Tables

Data table - is range of cells that shows answers generated by formulas in which different values have been substituted

one-input data table - vary value in one cell

two-input data table -vary values in two cells

Table command on Data menu used to create data table

Important Points Data Tables

1. Formulas must have cell reference to input cell

2. Can have many data tables in worksheet

3. Data table with one varying value has cell in upper-left corner empty

4. Data table can analyze as many functions as you want in one-input data table

5. To add additional formulas enter them in adjacent cells

6. Delete data table - select it press DELETE key

Visual Basic Statements

Statement Syntax:

Object - entry on left side of period Excel will affect this

property - entry on right side of period, what you want done to object

Range("B3:B8").Select

Assignment statements
Visual Basic

Range("B3").Value = InputBox("Item to purchase?","Enter")

"Item to Purchase" - appears as question in InputBox

"Enter" - appears in title bar of InputBox

Range("B6").Value = "price - tax"

5 Steps of Macro

1. Create button

2. Edit button name

3. Assign properties to button

4. Enter the macro (Visual Basic)

5. Execute the macro

Play Back Recorded Macro

Assign macro to button on sheet

Assign macro to custom button on toolbar

point to Macro on Tools menu, click Macros command

click Run Macro button on Visual Basic toolbar

Add command to Tools menu

assign macro to graphic

assign shortcut key to macro

Hyperlinks

Hyperlink - pointer that points to location of a computer on which a destination file is stored.

Other files on personal computer, network, or WWW

Insert Hyperlink button on Standard toolbar used to assign hyperlink to worksheet element

2 Hyperlink Elements

1. Text - text in cell made into hyperlink (text hyperlinks display blue and underlined)

2. Embedded graphic - graphic (clip art) made into a hyperlink

Scenario Manager

Allows you to record and save different sets of what-if assumptions (data values) called scenarios

outlined worksheet - one that contains symbols (buttons) above and to the left, allows collapse and expansion of rows and columns

use Scenarios command on Tools menu

3 Uses Scenario Manager

1. Create different scenarios with multiple sets of changing cells

2. Build a summary worksheet that contains the different scenarios

3. View the results of each scenario on your worksheet

Protecting Worksheet

Used for novice users to protect cells that you don’t want changed

New worksheet - all cells unprotected

Unprotected cells (unlocked cells) - cells whose values can be changed at any time

Protected cells (locked cells) - cells that can not be changed

2 Steps to Protect Worksheet

1. Select cells to leave unprotected, change their cell protection settings to unprotected

2. Protect entire worksheet

Note: Once you protect entire worksheet, cannot change anything including the protection of individual cells

More on Protection

Password - created to keep others from changing worksheet to unprotected

Protect more than one sheet - click Protect Workbook on Protection menu

Unprotect - point to Protection on Tools menu, click Unprotect Sheet

End

Excel

Project 5

Computer Assignment 5

Return to Elective Courses