Excel - PowerPoint PPT Presentation

1 / 30
About This Presentation
Title:

Excel

Description:

Easier formula construction and entry ... Click E13 to select the cell where you want to enter the FV function. ... Click FV in the Function name list box. In ... – PowerPoint PPT presentation

Number of Views:37
Avg rating:3.0/5.0
Slides: 31
Provided by: Jess51
Category:

less

Transcript and Presenter's Notes

Title: 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?
Write a Comment
User Comments (0)
About PowerShow.com