Excel Lecture 1

1
Excel Lecture 1
2
Excel Overview

• Excel is a computerized spreadsheet, which is an
  important business tool that helps you report and
  analyze information.
• We will cover the following topics (4 lectures)
• Intro/Formulas
• Formatting and Working with Charts/Graphs
• Lists / Filtering
• Pivot Tables

3
Identify major components of the Excel window

• Excel stores spreadsheets in documents called
  workbooks.
• Each workbook is made up of individual
  worksheets, or sheets.
• Because all sorts of calculations can be made in
  the Excel spreadsheet, it is much more flexible
  than a paper spreadsheet.
• The Excel window has some basic components, such
  as an Active cell, Column headings, a Formula
  bar, a Name box, the mouse pointer, Row headings,
  Sheet tabs, a Task Pane, Tab scrolling buttons
  and Toolbars.

4
A sample Excel worksheet
5
Excel worksheets and workbooks

• When you set up calculations in a worksheet, if
  an entry is changed in a cell, the spreadsheet
  will automatically update any calculated values
  that were based on that entry.
• When you open Excel, by default it will open a
  blank workbook with three blank worksheets.

6
Identify Excel components
7
Navigate within worksheets

• To navigate within a workbook, you use the arrow
  keys, PageUp, PageDown, or the Ctrl key in
  combination with the arrow keys to make larger
  movements.
• The most direct means of navigation is with your
  mouse.
• Scroll bars are provided and work as they do in
  all Windows applications.

8
Navigate between worksheets

• To move to other Worksheets, you can
• Click their tab with the mouse
• Use the Ctrl key with the Page Up and Page Down
  keys to move sequentially up or down through the
  worksheets

9
The Active Cell
10
Entering Data into a Worksheet

• To enter data, first make the cell in which you
  want to enter the data active by clicking it.
• Enter the data (text, formulas, dates, etc.) into
  the active cell.
• Use the AltEnter key combination to enter text
  on multiple lines within the same cell.
• Use TAB key, arrow keys, or ENTER key to navigate
  among the cells.

11
Entering Data into a Worksheet
12
Entering Formulas

• A formula is a mathematical expression that
  calculates a value.
• In Excel, formulas always begin with an equal
  sign ().
• A formula can consist of one or more arithmetic
  operators.
• The order of precedence is a set of predefined
  rules that Excel follows to calculate a formula.

13
Delete worksheet rows and columns

• To delete and clear cells, rows, or columns, you
  can use the Edit menu, or right click on a
  heading or a selection of cells and choose Delete
  from the shortcut menu.
• Clearing, as opposed to deleting, does not alter
  the structure of the worksheet or shift uncleared
  data cells.
• What can be confusing about this process is that
  you can use the Delete key to clear cells, but it
  does not remove them from the worksheet as you
  might expect.

14
Formulas and Functions
15
Use Excels functions

• You can easily calculate the sum of a large
  number of cells by using a function.
• A function is a predefined, or built-in, formula
  for a commonly used calculation.
• Each Excel function has a name and syntax.
• The syntax specifies the order in which you must
  enter the different parts of the function and the
  location in which you must insert commas,
  parentheses, and other punctuation
• Arguments are numbers, text, or cell references
  used by the function to calculate a value
• Some arguments are optional

16
Work with the Insert Function button

• Excel supplies more than 350 functions organized
  into 10 categories
• Database, Date and Time, Engineering, Financial,
  Information, Logical, Lookup, Math, Text and
  Data, and Statistical functions
• You can use the Insert Function button on the
  Formula bar to select from a list of functions.
• A series of dialog boxes will assist you in
  filling in the arguments of the function and this
  process also enforces the use of proper syntax.

17
Math and Statistical functions
18
Copy and paste formulas and functions

• Copying and pasting a cell or range of cells is a
  simple, but highly effective means for quickly
  filling out a large worksheet.
• To copy and paste a cell or range
• Select the cell or range to be copied and then
  click the Copy button on the standard toolbar
• Select the cell or range into which you want to
  copy the selection and then click the Paste
  button on the standard toolbar
• Once you are finished pasting, press the Esc key
  to deselect the selection

19
Copy and paste effects on cell references

• Copied formulas or functions that have cell
  references are adjusted for the target cell or
  range of cells.
• For example, if cell G5 contains the formula
  F5B5/B7, and you copy and paste this formula to
  cell G6, the formula in cell G6 will be
  F6B6/B8.
• This may or may not be correct for your
  worksheet, depending upon what you are trying to
  do.
• You can control this automatic adjusting of cell
  references through the use of relative and
  absolute references.

20
Use relative references

• A relative reference is a cell reference that
  shifts when you copy it to a new location on a
  worksheet.
• A relative reference changes in relation to the
  change of location.
• If you copy a formula to a cell three rows down
  and five columns to the right, a relative
  reference to cell B5 in the source cell would
  become G8 in the destination cell.

21
Use absolute references

• An absolute reference is a cell reference that
  does not change when you copy the formula to a
  new location.
• To create an absolute reference, you preface the
  column and row designations with a dollar sign
  ().
• For example, the absolute reference for B5 would
  be B5.
• This cell reference would stay the same no matter
  where you copied the formula.

22
Use mixed references

• A mixed reference combines both relative and
  absolute cell references.
• You can effectively lock either the row or the
  column in a mixed reference.
• For example, in the case of B5, the row
  reference would shift, but the column reference
  would not
• In the case of B5, the column reference would
  shift, but the row reference would not
• You can switch between absolute, relative and
  mixed references in the formula easily in the
  edit mode or on the formula bar by selecting the
  cell reference in your formula and then pressing
  the F4 key repeatedly to toggle through the
  reference options.

23
The Average Function

• The average function is necessary to calculate
  the average of a range of cells.
• Like any other formula, the average function may
  be copied across cells.

24
Use Excel's Auto Fill features

• When you need to copy and paste a large number of
  rows or columns, you can use a technique called
  Auto Fill using the fill handle.
• The fill handle is a small black square located
  in the lower-right corner of a selected cell or
  range.
• When you drag the fill handle, Excel
  automatically fills in the formulas and formats
  used in the selected cells.
• The same rules for relative, absolute, and mixed
  references apply for Auto Fill as for copy and
  paste.

25
Date Functions
26
Excel's date functions

• Excel stores dates as integers, where the integer
  value represents the number of days since January
  1, 1900.
• For example, the integer value for the date
  January 1, 2008 is 39448 because that date is
  39,448 days after January 1, 1900
• You typically do not see these numbers, because
  Excel automatically formats them to appear in a
  date format.
• This method of storing dates allows you to work
  with dates the same way you work with numbers.
• Excel's commonly used date functions are DATE,
  DAY, MONTH, NOW, TODAY, WEEKDAY and YEAR.

27
The TODAY and Now functions

• The TODAY and NOW functions always display the
  current date and time.
• You will not normally see the time portion unless
  you have formatted the cell to display it.
• If you use the TODAY or NOW function in a cell,
  the date in the cell is updated to reflect the
  current date and time of your computer each time
  you open the workbook.

28
Use a formula to enter the date
29
Financial functions

• The FV function calculates the future value of an
  investment based on periodic, constant payments
  and a constant interest rate per period.
• The IPMT function provides the interest payment
  portion of the overall periodic loan payment.
• The PMT function calculates the entire periodic
  payment of the loan.
• The PPMT function calculates just the principal
  payment portion of the overall periodic payment.
• The PV function calculates the present value of
  an investment.

30
Create logical functions

• A function that determines whether a condition is
  true or false is called a logical function.
• Excel supports several logical functions such as
  AND, FALSE, IF, NOT, OR and TRUE.
• A very common function is the IF function, which
  uses a logical test to determine whether an
  expression is true or false, and then returns one
  value if true or another value if false.
• The logical test is constructed using a
  comparison operator that compares two expressions
  to determine if they are equal, not equal, if one
  is greater than the other, and so forth.
• The comparison operators are , gt, gt, lt, lt, and
  ltgt
• You can also make comparisons with text strings.
  You must enclose text strings within quotation
  marks.

31
Exercise
32
Overview

• Create a folder on your H drive called
  INFO102_Excel
• Obtain the required spreadsheet budget1, from the
  INFO102 page.
• Save the file budget1 (as budget2) in the
  directory you created.
• Do sessions 2.1 and 2.2 (starting on page 52).
  Ignore steps 1-3 on p. 52
• Start the sessions today, complete prior to next
  class.
• Contact me if you have any issues.