Excel

1
Excel

• Advanced
• By
• Jessica Garza
• And
• Mike Martinez

2
Items Covered

• Arrange a worksheet in sections
• Assign data validation rules to a cell
• Assign and use range names
• Use IF and FV functions in formulas
• Create a series using auto fill
• Protect worksheets
• Delete unnecessary sheet from a workbook
• Plan and record Excel Macros
• Run a macro using menu commands, a shortcut key,
  and a button object
• Link Worksheets

3
Worksheets in Sections

• Type in the following.
• Click cell B3, Type .04 and press the Enter key.
  Return to cell B3 and click the Percent style
  button on the Formatting toolbar.
• B7- Mike Tobey
• B8- 30000
• B9- .05
• B10- .08

4
Data Validation

• Is a way to make sure the correct data is
  entered. You can set an input message to appear
  when the cell is selected. You can also place an
  error message when the wrong information is
  entered and prevent that information from being
  entered.

5
Data Validation

• Click B9, the cell where you want to apply data
  validation.
• Click Data gtValidation
• Select Settings tab and under Allow click Decimal
• Specify the range of values you allow.
• Under Data and click less than equal to
• In the Maximum text box, type.2 . The data
  validation rule of a value less than or equal to
  20 is now specified.

6
Input Message

• Click the Input Message Tab
• Check Show Input
• Click Title and type Valid Data.
• Click Input Message text box, and type Percent of
  salary invested by employee cannot exceed 20.

7
Error Alert

• Click Error Alert Tab
• Click Show error alert
• On the Style drop down menu click Stop
• In the Title text box, type Invalid Data.
• In the Error message text box, type You entered a
  value above 20. A valid percentage is 20 or
  less.
• Click OK.

8
Range Names

• A range name is descriptive name you assign to a
  cell or range of cells that can then be used to
  reference the cell or range of formulas, print
  ranges, etc.
• Easier formula construction and entry
• Improved documentation and clarification of the
  meaning of formulas
• Navigation of large worksheets simply by using
  the Go To command to move the pointer to a named
  range

9
Assigning Range Names

• Click cell B8
• InsertgtNamegtDefine
• In Names in work book text box type Salary
• Repeat Steps 2 and 3 to name cell B9 Invested
  and cell B10 Return

10
Range Name using the Name box

• Click B3
• Click the Name box
• Type MaxMatch and then press the Enter key

11
Enter Formulas using Range Names

• Click E7
• TypeSalaryInvested/12 and press the enter key
• Click E7

12
Building Conditional Formulas

• The IF function allows you to evaluate a
  specified condition, performing one action if the
  condition is true and another action if the
  condition is false.
• IF(logical_test, value_if_true, value_if_false)

13
IF Function

• Click E8
• Click the Insert function button.
• Click Logical in the Function category list box,
  Click IF in the function name list box, and click
  OK.
• In the Logical_test text box, type
  InvestedgtMaxMatch.
• Click the Value_ if _True text box, and type
  SalaryMaxMatch/12.
• Click the Value_if_false text box, and then type
  SalaryInvested/12
• Click OK.

14
Calculate the Total Contribution

• Click E9, Click AutoSum button, and press Enter.
• Name Cell E9 TotContribution
• Format the range E7E9 using the Currency style
  and zero decimal places.
• Click any cell to deselect the range.

15
Generate a Series using AutoFill

• Click D13, enter 5
• Click D14, enter 10
• Select the range D13D14
• Click and Drag the fill handle in cell D14
  through cells D15D18

16
Future Value Formula

• Click E13 to select the cell where you want to
  enter the FV function.
• Click the Insert Function button
• Click Financial in the function category list
  box
• Click FV in the Function name list box

17

• In the rate text box, type Return/12.
• In the Nper (Number per periods) text box, type
  D1312.
• In the Pmt text box, type Totcontribution and
  press Enter.
• Double-click cell E13. Place a minus sign after
  the equal sign in the formula bar.
• Copy the formula in cell E13 to cells E14E18

18
To Create a Line Charts

• Select the range D13E18
• Click the Wizard Chart Button
• Click the Line Chart
• Follow Wizard
• Titles for Chart
• Chart Title Retirement
• (X) Axis Years in future
• (Y) Axis Dollars

19
Cell Protection

• Select the cells you want to remain unprotected
• Click FormatgtCells
• Click the protection tab.
• Remove the check from the Locked check box, and
  then click the OK button.
• Click Toolsgt ProtectiongtProtect Sheet.

20
Fill Color

• Select B7B10
• Click the Fill Color Button
• Click Yellow

21
Protect Sheet

• Click ToolsgtProtectiongtProtect Sheet
• Enter Password
• Click OK

22
Record Marcos

• Click ToolsgtMacrogtRecord New Macro
• Type ClearInputs in the Macro name text box.
• In the Store macro in make sure This Workbook
  is selected
• Under description type Clear the values from the
  input section.
• Click OK

23
Start Recording

• Select the range B7B10
• Press Delete
• Click the Stop Recording Button
• To Run Macros
• ToolsgtMacrogtMacros
• Click Run

24
Insert a Button for Macros

• Click ViewgtToolbargt Forms
• Click Button Icon
• Select Button designation by clicking and
  dragging
• Select the Macro function
• Click OK

25
Create a Macro for Print Preview

• Name Macro Print 401K
• Instructions
• Click FilegtPage setup
• Click Sheet Tab Select Print area
• Click Margins Tab check Horizontally check box
• Click Header/Footer tab, and then click Custom
  Header button, click Tab Name Button click OK
• Click Custom Footer button, Type Prepared by
  (your name) in the right section, and then click
  OK
• Click Print Preview
• Click Close
• Click cell A5
• Click Stop Recording

26
Link Worksheets

• Link worksheets when you need to consolidate
  information from one or more workbooks.
• How does it work?
• The Independent workbook provides the data
• The dependant workbook contains the link to the
  external references in the Independent workbook.

27
Link Workbooks

• Copy data from the source workbook
• Paste it into the dependent workbook using the
  Paste Special
• Click the Paste Link command to create an
  external reference that links the workbooks.

28

• Open the Independent file.
• Open the Dependent file.
• View both workbooks vertically.
  WindowsgtArrangegtVerticallygtOK

29

• In the Independent file. Sheet 1 Select C2-C6
  gtCopy
• In the Dependent file Select D2-D6.
• EditgtPaste SpecialgtPaste Linkgt OK

30
You are now ready to work with Excel!

• Any Questions?