Introduction to �Excel

Worksheets

• Excel’s main screen is called a “worksheet”.
• Each worksheet is comprised of many boxes, called “cells”.

​

Organize Information

• You can organize information by typing a single piece of data into each cell. (see next slides)

​

Selecting a Cell

• Select” a cell by clicking on it once (don’t double click).
• You can move from cell to cell with the arrow keys or by pressing the “Enter” key.

​

​

Entering Information / The Formula Bar

Double Click to Modify a Cell

• To modify the contents of a cell double click on the cell.
• Then use the right, left arrow keys and the Insert and Delete keys to modify the data.
• When you are done:
• Press the Enter key or Click on the check box.

​

Names of Rows, Columns and Cells

Long Data

Information that is “Chopped Off”

• If there is information in the cell to the right, then the original cell still contains all of the data, but the data appears to be “chopped off”.�

10

• You can see the complete data by selecting the cell and looking in the formula bar.

Make a column wider

• To make Column B wider, point the cursor to the column separator between columns B and column C.
• The cursor changes to a “Double headed arrow”.
• Now, click the left mouse button and without letting go of the button, drag the separator to the right to make the column wider (or to the left to make the column narrower).

11

Column is now wider

Drag column separator to the right

Getting the Exact Width

• To get the “exact” width, double click on the separator instead of dragging it.

12

Column is now EXACTLY the correct width

Double click here

Resizing a Row

• Make a row taller or shorter by dragging the separator between the rows.

​

​

• Click and drag here to resize row 5.

13

Row is now taller

Putting an “Enter” inside a cell

• To add a new line inside a cell
• Double click inside the cell where you want the new line.
• Press Ctrl-Enter (i.e. hold down the Ctrl key and press Enter while still holding down Ctrl).
• When you are done editing, press Enter (without holding down Ctrl) to accept the changes.

14

Step 1: Originally “Hours Worked” is on one line.

Step 2: Double click to edit cell and then press Ctrl-Enter

Step 3: Press Enter (without Ctrl) to accept the changes.

Formatting Cells

• Select one or more cells and then click on any of the formatting buttons (see below) to change the formatting of the selected cells.
• Formatting buttons:

15

font name

font size

bold

italics

underline

center & merge cells�(will explain later)

center

right justify

left justify

These change the way numbers are displayed in cells. (these don’t affect words).

show as currency �(ex. 1000.507 becomes $1000.50)

show with commas (e.g. 12345 becomes 12,345)

show as percent (ex. 0.5 becomes 50%)

remove indent

show fewer decimal points (ex. 10.507 is displayed as 10.51)

show more decimal points (ex. 10.507 is displayed as 10.5070)

indent within cell

put border around cell(s)

color of cell

color of text in cell

click on downward pointing arrows for other colors and border styles

click on downward pointing arrows for other font names and sizes

Example – unformatted worksheet

• Unformatted worksheet – see next slide for formatting.

16

Example –making cells bold

• Click on cell A1 and drag to cell A3.
• Then press the Bold button to make cells A1,A2,A3 bold.
• You could also press the font or background color buttons to change the color or apply any other formatting you like (this is not shown below).

17

Other Ways of Selecting More Than One Cell

• To select a large range of cells, click on the upper left cell in the range. Then hold the shift key and click on the lower right cell in the range.
• You can select different “non-contiguous” areas of cells by holding down the Ctrl key while clicking and dragging.

18

Selecting Non-Contiguous Ranges

• Click and drag to select the first range.

​

• Ctrl-click and drag to select additional ranges

19

(This cell is also selected even though it appears white).

Selecting entire Rows, entire Columns or all cells on the worksheet.

• To select an entire column, click on the letter for the column header. To select several columns, click on the header for the first column and drag to the right.
• To select an entire row, click on the number for the row header. To select several rows, click on the header for the first row and drag down.
• To select all of the cells on the spreadsheet, click on the upper left hand corner of the spreadsheet (where the column headers meet the row headers)

20

Select Entire Columns/Rows/Worksheet

To select ENTIRE COLUMN B� click on “B” column header�

​

​

​

​

To select COLUMNS B,C,D� click on “B” column header and drag to right�

​

​

​

​

To select COLUMNS B,C and F,G,H

• click on “B” column header, drag to right,
• then Ctrl-Click on “F” column header and drag right

To select ENTIRE ROW 2� click on “2” row header

​

​

​

​

To select ROWS 2,3 and 5,6,7

• click on “2” row header, drag down,
• then Ctrl-Click on “5” row header and drag down

​

​

​

​

​

​

To select ENTIRE WORKSHEET � click on select worksheet button � (in corner between “1” and “A” buttons)

​

​

21

Click

Click

drag

Click

drag

Click and drag down

then Ctrl-Click and drag down

Click

drag

Ctrl-Click

Click

Example - continued

• Step 1: Click on row header for row 5

​

• Step 2: �Ctrl-click on row-header for row 11

​

• Step 3: Press Bold button or type ctrl-b

​

• Note: After being “bolded”,�the word “Employee” is now too wide for the column, so make the column wider if necessary (this step is not shown).

22

Excel Formulas

• You must have an equals sign ( = ) as the first character in a cell that contains a formula.
• The = sign tells excel that the contents of the cell is a formula
• Without the = sign, the formula will not calculate anything. It will simply display the text of the formula.

23

Formulas - correct

24

formula with = sign

After pressing ENTER

Types of operations

• You can use any of the following operations in a formula:�operation symbol example

addition: + = a1+3

subtraction: - = 100-b3

multiplication: * = a1*b1

division: / = d1/100

exponentiation ^ = a2^2

negation - = -a2+3� (same symbol as subraction)

25

Explicit (literal) values and cell references

• You can use both explicit values and cell references in a formula
• An explicit value is also called a literal value

​

• Formula with only cell references: =a1*b1

​

• Formula with only literal values: =100/27

​

• Formula with both cell references and literal values:�� =a1/100

26

Common Errors

• The following are some errors that may appear in a spreadsheet (there are others too).�
• #######
• Cell is too narrow to display the results of the formula. To fix this simply make the column wider and the “real” value will be displayed instead of the ###### signs. Note that even when the ###### signs are being displayed, Excel still uses the “real” value to calculate formulas that reference this cell.�
• #NAME?
• You used a cell reference in the formula that is not formed correctly (e.g. =BB+10 instead of =B3+10)�
• #VALUE!
• Usually the result of trying to do math with a textual value. Example: =A1*3 where A1 contains the word “hello”�
• #DIV/0!
• Trying to divide by zero. Example: =3/A1 where A1 contains 0 (zero)�
• Circular Reference
• Using a formula that contains a reference to the cell that the formula “lives in”. Example: putting the formula =A1+1 in cell A1 or putting the formula =SUM(A1:B2) in any of the cells A1, B1, A2, B2

27

Complex formulas

• You can use several operations in one function
• You can group those operations with parentheses

​

• Examples

​

= 3*2+1

​

= c1*(a1+b1)

​

= (100*a2-10)+(200*b3-20)+30

​

= (3+2*(50/b3+3)/7)*(3+b7)

28

Order of operations

• When using several operations in one formula, Excel follows the order of operations for math.
• first: all parentheses - innermost first
• second: exponents (^)
• third: all multiplication (*) and division (/). Do � these starting with the leftmost * or / � and work to the right.
• fourth: all addition (+) and subtraction (-). Do � these starting with the leftmost + or - � and work to the right.

29

Order of operations

• The value of �� 3 + 2 * 5��is�� 13��NOT 25!

30

Order of operations

31

3 + (100 - 20) / 10 - 6 * 2 / 4 + 9

​

3 + 80 / 10 - 6 * 2 / 4 + 9

​

3 + 8 - 6 * 2 / 4 + 9

​

3 + 8 - 12 / 4 + 9

​

3 + 8 - 3 + 9

​

11 - 3 + 9

​

8 + 9

​

​

answer: 17

What is a function?

• A function is a "named operation"
• Functions have
• a name
• parentheses
• parameters/arguments inside the parentheses
• The words parameter and argument mean the same thing
• you can have many parameters for one function separated with commas (,)
• The number of parameters is one more than the number of commas.

32

The SUM function

• Examples��Function Result

=SUM(1,2,3,4,5) 15

​

=SUM(a1,b1,c1) a1+b1+c1

​

=SUM(9,a1,b2,5,c1) 9+a1+b2+5+c1

33

Terminology

SUM(1,2,3,4,5)

• The name of the function is "SUM"

​

• The parameters or arguments to this function are 1,2,3,4 and 5

​

• The entire thing, i.e. SUM(1,2,3,4,5), is a function call

​

• The value of this function call is 15. �Another way to say this is that this function call returns 15.

34

Ranges

• A rectangular box of cells is called a “range”.
• The name of a range is
• the name of the upper left cell of the range
• Followed by a colon :
• Followed by the lower right cell of the range
• Example: A1:B2 is shorthand for A1,A2,B1,B2
• See next slide for more examples

35

A1:B2

Examples of Range Names

• Examples

C3:E10

​

​

​

​

B2:B5

​

​

​

�

B3:E3

36

Using a range as a parameter

• Ranges can be specified as a parameters to a function call.
• Both of the following function calls produce the same result as =a1+b1+c1+a2+b2+c2+a3+b3+c3+a4+b4+c4�however the 2^nd version uses a range and is much shorter.�� without a range� =SUM(a1,b1,c1,a2,b2,c2,a3,b3,c3,a4,b4,c4) �� with a range� =SUM(a1:c4)

37

Function calls with multiple parameters

• You can include multiple ranges and cells as parameters�
• Example: the following function call has 3 parameters. There are two ranges (a1:b2 and c4:c7), one number (100) and one cell reference (d3)��=SUM(a1:b2,100,c4:c7,d3)�� Is the same as:��=SUM(a1,a2,b1,b2,100,c4,c5,c6,c7,d3)

38

Other functions

• Click the function button to see the available functions:

39

Function buton

brings up the function dialog box (see next slide)

Function dialog box

40

categories �(i.e. groups of functions)

Functions for the selected category

Description of currently selected function

Warning: this slide was created using Excel 2000. The dialog box in later versions of Excel looks a little different, but it has the same functionality.

Function Editor

• Double click on the function name to get a dialog box that helps you enter values for the parameters of the function.�(see next slide)

41

Function Editor

42

When you press OK, this will create the function call:� AVERAGE(2,a1:c2,f13)

Put values for the parameters in the edit boxes.

Example

• AVERAGE��formula that contains a function value

=AVERAGE(2,4,10,4) 5

=AVERAGE(a1,f32) (a1+f32) / 2

=AVERAGE(a1:c1) (a1+b1+c1) / 3

=AVERAGE(a1:c1,10) (a1+b1+c1+10) / 4

43

Entire Rows (e.g. 2:2 or 2:4)

• A cell reference of the form <rowName>:<rowName> refers to the range of all the cells for those rows.

​

• Example:
• The reference, 2:2, refers to all of the cells on the 2^nd row.
• The following formula adds up all of the values on the 2^nd and 4^th rows of the spreadsheet:�� =sum(2:2,4:4)

​

• Another Example:
• The reference, 2:4, refers to all of the cells on the 2^nd , 3^rd and 4^th rows,.
• The following formula adds up all of the values on the 2^nd, 3^rd, 4^th , 10^th, 11^th , 12^th, 13^th, 14^th and 15^th rows of the spreadsheet:�� =sum(2:4,10:15)

44

Entire Columns (e.g. B:B or B:D)

• A cell reference of the form <colName>:<colName> refers to the range of all the cells for those columns.

​

• Example:
• The reference, B:B, refers to all of the cells in the 2^nd column.
• The following formula adds up all of the values in the 2^nd and 4^th columns of the spreadsheet:�� =sum(B:B,D:D)

​

• Another Example:
• The reference, B:D, refers to all of the cells in the 2^nd, 3^rd and 4^th columns.
• The following formula adds up all of the values in the 2^nd, 3^rd, 4^th, 6^th and 7^th columns of the spreadsheet:�� =sum(B:D,F:G)

45

References to cells on other worksheets

• Cell on another sheet: sheetName!cellReference
• Range on another sheet: sheetName!range
• Row on another sheet: sheetName!row:row
• Column on another sheet: sheetName!column:column

​

• If a sheet name has a space in it, you must surround the sheet name with apostrophes (i.e. single quotes)

​

• Examples

sheet2!a1

sheet2!b4:c8

'2002 Forecasts'!f3:f10

=sum('2002 Forecasts'!f3:f10)

=sum('2202 Forecasts'!f:f)

46

More examples

• Add up values from 2 different sheets

=sum ( 'great stocks'!b2:c4, 'so so stocks'!b2:c4)

​

• This next one is a little confusing

=sum (a1,a!a1,b1:b4,b1!b4,c!c:c)

�Explanation

a1 this is a cell reference on the current sheet�a!a1 "a" is the name of sheet. "a1" is a cell on the "a" sheet�b1:b4 this is a range on the current sheet�b1!b4 "b1" is the name of a sheet. "b4" is a cell on the "b1" sheet�c!c:c “c" is the name of a sheet. “c:c" is all of the cells in the c column� on the “c” sheet

​

47

Absolute and Relative Cell References

• By default, when you copy a formula that contains a cell reference, excel will automatically adjust the cell reference.

​

• You can stop Excel from automatically adjusting the cell reference by using one or more dollar signs ($) in the cell reference. These are called absolute cell references.

​

• A cell reference without a dollar sign is a relative cell reference.

48

Examples

• The following all refer to the same cell

d9

$d$9

$d9

d$9

• The only difference between these cell references relates to what happens when you copy a formula that contains the cell reference.

49

Relative Cell Reference

• d9 This is a "relative cell reference".
• Changing the column: If I copy this cell reference to another cell:
• the "d" will increment one letter for every cell that I move over to the right.
• The "d" will decrement one letter for every cell that I move over to the left
• Changing the row: If I copy this cell reference to another cell:
• the "9" will increment by one for every cell that I move down.
• The "9" will decrement by one for every cell that I move up

50

Absolute cell reference

• $d$9
• This is an absolute cell reference.
• If I copy a formula with this cell reference, the cell reference will NOT change AT ALL.

51

Mixed References

• $d9 and d$9 These are "Mixed" cell references:

​

• $d9
• The "d" will stay the same when you copy the cell, but the "9" will change.

​

• d$9
• The "d" will change when you copy the cell, but the "9" will stay the same.

52

Data Types

​

​

​

​

• Numeric
• values: any number
• operators: + - * / ^ %
• sample functions: sum( ), average( ), max( ), min( ) etc.�
• Text (AKA Character or String)
• values: Any group of letters or numbers or special characters.� Prefix value in cell with an apostrophe ( ' ) to force a text value
• operators: & (concatenation)
• sample functions: right( ), left(), mid(), lower(), upper(), len(), etc�
• Dates
• values: dates and times�operators: N/A
• sample functions: now( ), today( ), hour(), minute(), etc.�
• Logical (AKA boolean)
• values: true false
• Operators: < > = <> <= >=
• sample functions: if( ), and( ), or( ), not( ), is blank()

53

Data Types for Values in Cells

• By default:
• a cell that contains a number is treated as numeric data
• a cell that contains a date is treated as date data (we'll see more about this later)
• a cell that contains data which is �not numeric and not a date is treated as "text"

54

Text / String / Character

• The following three terms all used to refer to "text" data. All three terms mean the same thing.�
• text data
• string data
• character data�
• This presentation will generally use the term "text data" but you should be familiar with the terms "string data" and "character data"

55

Text data

• Text data is used to store general purpose text (e.g. names, places, descriptions, etc)

​

• You can't do "math" with text values (obviously)

​

​

56

Text isn't part of numerical calculations (obviously)

57

Formula to add up all numbers in column C

​

�(Same Spreadsheet)

​

Text data in C1 is not included in the Sum

​

formula view (press Cntrl-`)

values view (press Cntrl-`)

Text Functions

• Many functions are used to manipulate text values.
• The following are only some of them

right( )�left( )�mid( )�concatenate( )�lower( )�upper( )�len( )

58

RIGHT function

• The RIGHT function is used to isolate a specific number of “characters” from the right hand side of a text value.
• (example on next slide)

59

RIGHT ( <text>, <numCharacters>)

60

Formula View

Values View

RIGHT – numCharacters is optional

• The <numCharacters> parameter in the RIGHT function is optional. If you don’t specify it the default is 1 (one).

61

Formula View

Values View

These produce the same results.

LEFT

• The LEFT function is the same as the RIGHT function, but it returns characters from the LEFT side of the value.

62

MID ( <text>, <startPosition> , <numCharacters>)

• MID is used to get values from the middle of some text.

​

• MID takes 3 parameters:
• The original text
• The position to start taking the new value from
• The number of characters to take for the new value

​

• Example on next slide

63

Example: MID ( <text>, <startPosition> , <numCharacters>)

• This example extracts the second through the fourth characters from the original text value:

64

Formula View

Values View

Concatenation (&)

• Use & to combine (or concatenate) two different text values

65

Formula View

Values View

Notice that there is no space between the two values

Concatenate many values

• You may concatenate many values together

66

Formula View

Values View

Concatenation with "literal" values

• You can also concatenate "literal" values.
• You must include the literal values inside quotes
• For example to display spaces in the "full name" in the previous example you could use the following formula. Each space that you want to display must be included in quotes.�� =A2&" "&B2&" "&C2� (Don't forget any of the &'s )
• See next slide ...

67

Concatenating spaces - Example

• You can concatenate spaces into a formula

68

Formula View

Values View

values contain spaces

LOWER ( <textValue> )�UPPER ( <textValue> )

• LOWER converts text to lower case.
• UPPER converts text to upper case.
• Example:

69

Formula View

Values View

LEN ( <textValue> )

• LEN returns a numeric value equal to the number of character in a text value (i.e. the “length” of the text value).
• Spaces ARE included in the length.
• Example

​

​

​

70

Formula View

Values View

IF Function

71

Parameters for IF function

72

IF function

73

Formula View

Values View

IF with a numeric result

74

IF with a numerical result

75

Formula View

Values View

EXCEL WITH CASE STUDIES

• Sally Hughes is part owner of Sparrow & Pond, a small bookstore in Hudson, New Hampshire. Among her many tasks is to purchase new books from publishers. She also purchases rare and first edition books from online auctions as well as local library, estate, and garage sales.
• Sally needs to quickly track sales data, compile customer profiles, and generate financial reports. She can perform all of these tasks with Microsoft excel  an application used to enter, analyze, and present quantitative data. Sally asks you to use Excel to record a recent book order from a regular Sparrow & Pond customer.

SUM Function

• Syntax: =SUM(range)
• Purpose: Adds all numerical values within a specified range.
• Example: =SUM(A2:A10) calculates the total of cells A2 to A10.
• When to use: When you need to calculate the total of a column or row.

SUMIF Function

• Syntax: =SUMIF(range, criteria, [sum_range])
• Purpose: Adds values in a range based on a condition.
• Example: =SUMIF(B2:B10, "Sales", C2:C10) sums the values in column C if the corresponding value in column B is "Sales.“
• When to use: When you want to calculate totals based on specific criteria.

COUNTIF Function

• Syntax: =COUNTIF(range, criteria)
• Purpose: Counts the number of cells within a range that meet a specified criterion.
• Example: =COUNTIF(A2:A10, "Yes") counts the number of cells in column A that contain "Yes.“
• When to use: When you need to count occurrences of specific values or conditions.

COUNT Function

• Syntax: =COUNT(range)
• Purpose: Counts the number of cells containing numbers within a specified range.
• Example: =COUNT(B2:B10) counts the number of cells in column B that contain numbers.
• When to use: When you need to count the total number of numerical values in a range.