Microsoft Excel

1
Microsoft Excel

• An introduction to spreadsheets

2
The Basics

• A workbook consists of 16 spreadsheets.
• Each spreadsheet is partitioned into rows and
  columns.
• A cell is the intersection of a row and column.
• Rows are labeled 1, 2, , 65536.
• Columns are labeled A, B, , Z, AA, AB, , AZ,
  BA, BB, , IV.

3
Basics ...

• A cell is referenced by its column label followed
  by its row label. For example, B7 AK37 i
  v65536

ROW
COLUMN
4
A cell can contain

• text Total Sales
• a number 234.6
• a formula (must begin with an ) B2-C7
• a built-in function (must begin with an
  ) SUM(K3..K9)

5
cell
6
click and drag your mouse
B4F11
H2M2
K6K16
selected range
7
Changing the size of columns

• Move the mouse between the two columns that you
  want to alter.
• The arrow will change to a plus sign, and then
  you can click on the line and change the width of
  the column.

8
Change the size of rows

• To change the size of the rows you do the same
  thing that you did to change the size of the
  columns.
• The only difference is you put the mouse between
  the two numbers instead of letters

9
Can a text file be converted to a spreadsheet?

• A text file in column formatJohnso,Al 42 5Bow
  ser,Billy 40 5Manston,Gail 35 6.50Jones,Carmin
  e 37 5Miller,Tom 40 7.25Samuels,Ken 45 6Wint
  er,Diedre 43 7

Created in Notepad
10
Importing a Text File

• From the File menu, choose Open
• Enter the name of the text file
• Click OK
• Follow the steps displayed by the Text Import
  Wizard

11
is a delimiter
12
remove delimiters
13
(No Transcript)
14
(No Transcript)
15
insert a row
format cells
change font
16
Adding Style to a worksheet

• Select the desired range of cells you want
• Choose Format, then Style
• Open the Style Name drop-down list box
• Select the style you want
• Click OK

17
Formatting Cells

• Choose Cells from the Format menu
• Click on a tab (for example, Number)
• Select format (for example, choose currency, 2
  decimal places)
• Click OK

18
enter a formula to compute weekly pay
19
(No Transcript)
20
Copying a Formula

• Move the mouse to the bottom-right corner of the
  cell to be copied.
• When the cursor changes shape to , click and
  drag the mouse over the cells you are copying to.
• Release the mouse.

21
Using Autofill

• Quick way to copy cells and their formulas
• - Click on a cell
• - Point the mouse to the Fill handle
• arrow becomes
• - Drag to range of cells then release

22
The autofill feature allows you to copy the first
entry to the end of the column
23
Using Functions

• Click the fx button to start the function wizard.
• Locate the function to be entered into a cell.

24
Function ...

• Enter a cell reference for each argument of the
  function.
• A cell reference may be a range of cells or a
  single cell.
• Click Finish.

25
function wizard
No extra pay for overtime work
26
Add a border
27
if(b2lt40,b2c2,40c2(b2-40)c21.5)
28
Overtime pays 1.5 times regular pay
relative copy of the formula
29
chart wizard
select a range
30
Charts

• To make a chart click on the chart wizard.
• Now decide what kind of chart you would like to
  use.
• Enter the formula E2E6 into the range box.
• Then enter the necessary information into step 2,
  and then press finish.

31
Chart Wizard
32
(No Transcript)
33
(No Transcript)
34
(No Transcript)
35
(No Transcript)
36
This is similar to what your chart should look
like, depending on the type of chart you select.
37
Sorting data in a worksheet.

• To sort any kind of data in a worksheet select
  the data you want sorted and then go to sort in
  the data menu.
• Press continue with the current selection.
• Press ascending to make the information be sorted
  alphabetically A-Z, press ok.
• Your information will now appear sorted by first
  name A-Z.

38
(No Transcript)
39
(No Transcript)
40
(No Transcript)
41
(No Transcript)
42
(No Transcript)
43
(No Transcript)
44
(No Transcript)
45
(No Transcript)
46
(No Transcript)
47
(No Transcript)
48
Template

• A template is a worksheet in which text and
  formulas have been entered but the data
  manipulated by the formulas have been left out. A
  template is created when it is used on a periodic
  basis, e.g., a quarterly report, a grade roster,
  .

49
Example template
Student names and grades are left out
50
Enter data
Add some data see the results
51
Enter more data
Add more data
52
Final form
data
53
Copying cells

• Cell FormulaLet the above notation
  represent that a Formula is stored in a Cell. For
  example, D2 B2 C2 says that formula
  B2C2 is stored in cell D2. To compute the
  formula in Celldestination copied from Cellsource
  use the notation below
• Cellsource Formulasource c r
  Celldestination Formuladestinationwhere
  c change in column from source to destination
  and r change in row from source to destination.
  

54
Relative Addressing

• Copy D2s contents to cell D10 D2 B2 C2
  c0 r8 D10 ? Do not
  change the column but advance the row by 8
  implies B10 C10 is the formula stored in cell
  D10.

Change all columns by c and change all rows by r
55
Relative Addressing

• Copy Q15s contents to cell Z12 Q15 M9 -
  A4 c9 r-3 Z12 ?
  Advance column references by 9 and decrease row
  references by 3 impliesV6 - J1 is the formula in
  cell Z12.

Change all columns by c and change all rows by r
56
Absolute Addressing

• Copy D2s contents to cell D10 D2 B2
  C2 c0 r8 D10 ?
  Do not change the column but advance the row by
  8 whenever they are not preceded by a implies
  B10 C2 is the formula in cell D10.

Change all columns and rows as before except when
both are preceded by a
57
Absolute Addressing

• Copy Q15s contents to cell Z12 Q15 M9
  - A4 c9 r-3 Z12 ?
  Advance column references by 9 and decrease row
  references by 3 whenever they are not preceded by
  a implies M9 - J1 is the formula in cell Z12.

Change all columns and rows as before except when
both are preceded by a
58
Add a bonus (cell C3) to each score
59
Copying with relative addressing gives incorrect
results
60
Cell C3 contains the bonus points not cell C6
61
(No Transcript)
62
Copy formula
63
Cell reference is fixed
64
Mixed Addressing

• Copy D2s contents to cell D10 D2 B2
  C2 0 8 D10 ? Do
  not change the column but advance the row by 8
  whenever one or the other are not preceded by a
  implies B10 C2 is the formula in cell D10.

Change all columns and rows as before except when
one or the other but not both are preceded by a
65
Mixed Addressing

• Copy Q15s contents to cell Z12 Q15 M9 -
  A4 9 -3 Z12 ?
  Advance column references by 9 and decrease row
  references by 3 whenever one or the other are not
  preceded by a implies M6 - J4 is the formula
  in cell Z12.

Change all columns and rows as before except when
one or the other but not both are preceded by a
66
Sheet Referencing

• A formula that contains references to cells in
  other sheets of a workbook allows you to use data
  from multiple sheets and to calculate new values
  based on this idea.
• The formula contains a sheet reference consisting
  of the name of the sheet, followed by an
  exclamation point and the cell range or
  reference.

67
The sheet reference can be accessed if the equal
sign is clicked.
68
Saving and printing a worksheet.

• File Save or Save as
• Entire workbook
• will be saved

69
(No Transcript)
70
Printing

• File Print
• Entire workbook
• Current worksheet