Microsoft Excel Basics

1
Microsoft ExcelBasics

• David Turton
• Conestoga College
• Institute of Technology and Advanced Learning
• http//www.conestogac.on.ca/dturton
• Doon 1D17 x3610

2
Basic Excel Screen
Cells can contain numbers, formulas, functions or
text. The result of a formula or function is
displayed. Note how this formula refers to the
other cells values by column and row.
3
Formula

• Cells are referred to by column row
• cell A3 has the value 98
• a formula is distinguished by a leading
• cell references will be replaced by the cell
  value
• operation order
• exponentiation
• / multiplication, division
• - addition, subtraction
• Evaluated left-to-right, if operators are of
  equal value
• ( ) change order of operation
• Formulas can contain function calls
• The return value of the function will be used

4
Formatting Cells

• click to select or highlight a cell
• click drag to select a range of cells
• right-click to find actions
• in context of selected object(s)

5
Formatting numbers
Pattern colour pattern fills for a cell or
range
Protection is the cell or range locked when the
spreadsheet is protected?
Alignment wrap text to multiple lines, merge
cells, align left, right, top, etc.
Cells default to general display pattern. If
you declare a cell (or range of cells) as a
number, currency, percent, or date, you
can control how its presented. Note The format
of a cell does not affect its value only the
way its displayed.
6
Formatting Borders
Draw line between cells of range
Line type weight, applied to next line drawn
Draw line on bottom of range
7
Adding a Column
Right-click on column heading(s) to find actions
applicable to a column. Note column will be
inserted to left, with characteristics of column
to left. Option provided to format like one on
right
8
Adding new row(s)
Highlight one or more rows, right-click, and
select insert Note 4 rows highlighted, 4 will be
added above the selection, with characteristics
of the row above selection
9
Sizing Cells

• automatic sizing
• double-click right border of column heading
• manual sizing
• click and drag right border

10
Print set-up landscape/portrait, force sheet to
1 page
11
Print set-up Headers/footers(repeat on every
page)
12
Click on the row(s) you want repeated as headings
13
Moving Cell Contents

• move moves cell contents from one place to
  another without modifying them.
• Highlight a cell or range of cells
• cut paste move
• right-click selection choose cut
• right-click new top-left location select paste
• drag drop move
• Click on selection border (get arrow)
• Drag to new position

14
Copying Cell Contents

• copy copies cell contents from one place to
  another
• cell references are modified by cols and rows
  displaced
• Highlight a cell or range of cells
• copy paste
• right-click selection choose copy
• right-click new top-left location select paste
• drag drop
• hold Ctrl
• click on selection border (get arrow and )
• drop in new position

15
Continue a Series-

• Select range representative of series
• position cursor over bottom right
• cursor becomes black cross
• drag to end of desired range
• Excel will perpetuate series
• if only one cell selected
• will act like copy

16
Copy Example relative addressing

• range copied 4 cols right, 5 rows down
• cell references adjusted accordingly

17
Absolute References

• You can fix (lock) a column and/or row reference
  for copying
• B1 - dont change the column reference
• B1 - dont change the row reference
• B1 - dont change either the row or column
  reference
• F4 rotates between
• these values

18
Example absolute reference

• Copying cell A5 to cell B6
• (right 1 column, down 1 row)
• A5 copy? B6
• A4C1 copy? B5D2
• A4C1 copy? B5C1
• Why important?
• Have GST in one cell
• Formula references it
• Copy formula want copies to refer to same GST
  cell

19
Naming Cells/Ranges- names like income_tax are
easer to remember than B12- name ranges for
lookup functions
Note named cell/range is absolute reference by
default
20
Annotating or Commenting Cellscomment shows when
cursor pauses on cell
Note under View is option to show all comments
21
Function Calls

• A function can be used anywhere an operand
  (number, text, cell reference) can be used
• The cell must start with for functions to be
  recognised.
• Examples
• SUM(A3A9)
• AVERAGE(A3A9,B4)
• 36-SUM(A3A9)
• Note mix of value and function call in formula
• Note also references to a range of cells A3A9,
  meaning cell values A3 to A9 inclusive.

22
Auto-Sum

• sums numbers above current cell
• will sum numbers to left, if none above

23
Function look-upor you could simply enter
average(f9f11) in cell