MS Excel

1
MS Excel
2
Spreadsheets

• Grids of rows and columns, which make the data
  more readable and visual
• Help to keep track of events, profits or loses
• Electronic spreadsheets allow users not only to
  present information more clear, but perform
  calculations, data manipulations, graphical
  presentation.

3
Spreadsheets are used for

• Entering data
• Editing data.
• Storing worksheets
• Printing worksheets
• The worksheet is an active sheet

4
Excel Workplace

• Is implemented according to MS standards and
  consists of
• Excel screen including Windows standard elements
• Menus, shortcut keys, toolbars, dialog boxes
• Excels Help system

5
Spreadsheet Cells

• A cell is intersection of a row and a column
• The cell address or cell reference reflects a
  given row and a given column.
• Each cell has a unique address composing from a
  letter (or letters) representing a column, and a
  number representing a row.

6
Types of Cell References

• A relative cell reference is the address of a
  cell relatively the current cell D8, AZ155, G1
• An absolute cell reference is exact cell location
  on the spreadsheet D6, BC89
• A mixed reference is reference with one absolute
  coordinate and another relative K25, D123

7
Cell Ranges

• A range is a group of adjacent cells.
• Ranges are composed of a row , a column, or
  several rows and columns
• To select multiple ranges, select first range,
  than press CTRL key down and select another one
  and so on.

8
Cell Content

• A cell can hold any of 4 different types of
  information
• Value
• Label
• Formula
• Function

9
Formulas

• Describe specific calculations to be performed to
  obtain a cell value
• Arithmetic symbols
• - addition
• - subtraction
• - multiplication
• / - division
• - exponentiation

10
Entering Formulas

• By typing in formula content after symbol
• By typing in constant values, arithmetic symbols
  and clicking on the cell which have to be
  referenced in the formula

11
Using Cells from Another Worksheet

• 3-D reference consists of the name of the sheet,
  followed by ! And the cell or range references
• Examples (formula is entered in a cell in
  Sheet2)
• SUM(Sheet1!H6Sheet1!K6)
• SUM(Sheet1!H6K6)
• SUM(Sheet1Sheet4!H6)

12
Copying Cell Content

• Labels and values may be copied based on Copy and
  Paste method
• Formulas may be copied from one cell to another
  or only current value of a formula may be copied

13
Copying Formulas

• Copying a formula with absolute cell references
  Excel creates exact copy
• Copying formula with relative references Excel
  determines new references based on location of
  the destination cell in relation to the source
  cell

14
Functions

• are predefined formulas for a specific kind of
  calculation.
• C4C5C6C7C8C9C10
• SUM(C4C10)

15
Anatomy of Functions

• Each function has 2 parts
• Function name identifies the function
• Arguments determine what values or cell
  references the functions will use in its
  calculation

16
Function Arguments

• Functions
• Error values
• Logical Values

• Numbers
• Text
• Cell references
• Formulas

17
Function Types

• Math Trig Functions
• Statistical Functions
• Financial Functions
• Logical Functions
• Lookup and Reference Functions
• Information Functions
• Date Time Functions
• Text Functions

18
Logical Functions

• IF(logical_test, value_if_true,value_if_false)
• Example the value of the cell D6 is 0.2B6, if
  B6400
• In the cell D6 enter function
• if(B6

19
Financial Functions

• PMT - calculates periodic payments on a loan
  based on constant payments and interest rate
• PMT(rate, nper, pv, fv, type)
• Required arguments rate, nper, pv
• Optional arguments fv, type

20
PMT Function

• Required
• rate interest rate per period
• nper number of periods
• pv- present value or current worth of the total
  payments.
• Optional
• fv future value or balance
• type when payments are due
• 0 (default) at the end of each period
• 1 at the beginning of each period

21
Formatting Basics

• Number formatting
• Alignment
• Font formatting
• Borders
• Column and row formatting

22
Number Formatting

• Currency format
• Accounting format
• Date format
• Time format
• Fraction format
• Scientific format
• Text

23
How to Format the Numbers

• With toolbar button
• Select the cell(s)
• Click the formatting toolbar button
• With Format Cells dialog box
• Select the cell(s)
• Choose Format, then Cells, and Number Tab
• Choose the number format, set options
• Click OK

24
To Merge Cells

• With toolbar button
• Select the cell(s)
• Click the Merge and Center button
• With Format Cells dialog box
• Select the cell(s)
• Choose Format, then Cells, and Alignment Tab
• Choose the Center from Horizontal menu
• Turn on Merge cells Check Box
• Click OK

25
Borders

• With toolbar button
• Select the cell(s)
• Click the appropriate Border button
• With Format Cells dialog box
• Select the cell(s)
• Choose Format, then Cells, and Border Tab
• Choose the line style, color
• Set individual borders
• Click OK

26
Removing the Border

• Select the cell(s)
• Click the arrow from the right of border button
• Choose the first (top left) border style

27
Fill Colors and/or Patterns

• Select the cell(s)
• With toolbar button
• Click the Fill color button
• Choose the color
• With Format Cells dialog box
• Choose Format, then Cells, and Pattern Tab
• Choose the pattern and/or color
• Click OK

28
Removing Formatting

• Select the cell(s)
• Go to Edit, Clear
• Choose Format

29
Charts

• Are graphical representations of data
• Can be embedded in a worksheet,
• Can be a chart sheet of its own

30
Chart Wizard

• Helps to create a Chart
• Uses illustrative dialog boxes to prompt users
  for information
• Get you see what you chart looks like on each
  step
• At any point you could go back and make changes

31
Setting Options

• Go to Tools
• Choose options
• To display formulas choose View tab
• In the Window options turn on Formulas check box