1
2
• 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)
4
Selecting a Cell
• “Select” a cell by clicking on it once (don’t double click).
• You can move
from cell to cell with the arrowkeys 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.
8
Column Names (letters) & Row Names (numbers)
• The name of a cell is a combination of the Letter Of The Column that the cell is in followed by the
Number Of The Row that the cell is in.
• Example: the selected cell in the picture is named B4 (NOT 4B)
• Excel automatically shows the the name of the currently selected cell in the “name box” (located above the worksheet).
• The letter must come first (i.e. B4, NOT 4B) and there may NOT be any spaces between the letter and the number.
• We will learn later why it is important to understand how to name cells.
11
12
• The word “Name” is in cell
A5
• The words “Hours Worked” are in cell B5 (NOT in cell C5). However, since the information is too wide for cell B5, it looks like it extends into cell C5.
• You can determine that the information is really only INcell B5 by selecting cell B5and looking at the formulabar and then selecting cellC5 and looking at theformula bar.
• If there is information • You can see the in the cell to the right, complete data by then the original cell selecting the cell and still contains all of the looking in the
Change the Width of a Column or the Height of a Row
14
Make a column wider Drag column
separator to the
• 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). Column is now wider
• To get the “exact” width, Double click here double click on the separator instead of dragging it.
Column is now EXACTLY the correct width
Resizing a Row
• Make a row taller or shorter by
5.
Putting an “Enter” inside a cell
Basic Formatting (e.g. bold, colors, fonts, etc)
19
• 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:
• Unformatted worksheet – see next slide for formatting.
• 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).
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.
• Click and drag to
ranges
Selecting entire Rows, entire Columns or ^{25} 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)
Select Entire Columns/Rows/Worksheet
• 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).
More Advanced Formatting
28
• Using the formatting buttons only give you a limited amount of formatting ability.
• For more formatting ability, select one or more cells and right click on the selection. Then choose “format cells” from the popup menu.
• Choose options from the Number,
Alignment, Font, Border and Patterns tabs and press OK to change the way your information looks on the screen.
• The Protection tab is used to lock cells so that their contents can’t be modified.
• We will not go into the details of using the format cells dialog box at this time but you should be able to figure out most of it by yourself.
Formatting changes how things LOOK, ^{30} not how they WORK.
• NOTE: you will probably not understand this slide until after you learn about Excel Formulas. Formulas are covered later in this presentation.
• When you change the format of a cell, Excel still “remembers” the original value.
• Excel will use the un-formatted value when calculating formula values.
• Example: if you change numbers to appear with fewer decimal points the original number with all of its decimal points are used in calculations.
The bread and butter of Excel
31
• 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.
Formulas - correct
Missing = sign
• You can use any of the following operations in a formula:
subtraction: - =100-b3 multiplication: * =a1*b1
division: / =d1/100 exponentiation ^ =a2^2
negation - =-a2+3
(same symbol as subraction)
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
Errors in Formulas
37
• 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
Order of Operations
39
• 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)
• 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.
• The sentence "Please excuse my dear aunt Sally" is a popular mneumonic to remember the order of operations:
Menumonic | Meaning |
– Please | parentheses |
– Excuse | exponents |
– My Dear | mulitplication and division (going left to right) |
– Aunt Sally | addition and subtraction (going left to right) |
Order of operations
• The value of
3 + 2 * 5
is
13
NOT 25!
Order of operations
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
• To see the formulas in the worksheet
– Press the Cntrl key at the same time as you press the ` key (i.e. Cntrl-`)
– Press Cntrl-` again to see the values
Functions 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.
The SUM function
• Examples
=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
SUM(1,2,3,4,5)
– The name of the function is "SUM"
– The parameters or argumentsto 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 returns15.
Ranges (e.g. a1:c3)
50
• 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
Examples of Range Names
B3:E3
• 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)
• 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)
Other Functions
55
• Click the function button to see the available functions:
Function dialog box
categories Functions for the selected category
• 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)
• AVERAGE
=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 |
Combining Functions and other values in a single formula
• You can combine functions, cell references and literal values to make a complex Excel formula
• Examples
=3 + b23 * SUM(d20:g20)
=SUM(a1,100) * AVERAGE(d10:j10)
=100 / ( AVERAGE(b2,c2,d30) + AVERAGE(f1:f20) )
Other Types of Cell References
References to entire ROWs
References to entire COLUMNs
References to cells or ranges on other worksheets (i.e. tabs)
• 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)
• 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)
• 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
• Examples
sheet2!a1 sheet2!b4:c8
'2002 Forecasts'!f3:f10
=sum('2002 Forecasts'!f3:f10)
=sum('2202 Forecasts'!f:f)
• 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
• 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.
• 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.
• 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
• $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.
• $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.
• 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( ), isblank()
• 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"
Text Data
77
• 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"
79
• Text data is used to store general purpose text (e.g. names, places, descriptions, etc)
• You can't do "math" with text values (obviously)
Text isn't part of numerical calculations
(obviously)
Text Functions
81
• Many functions are used to manipulate text values.
• The following are only some of them
right( ) left( )
mid( ) concatenate( ) lower( ) upper( ) len( )
RIGHT, LEFT and MID functions
83
• 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)
RIGHT ( <text>, <numCharacters>)
Formula View
Values View
• The <numCharacters> parameter in the RIGHT function is optional. If you don’t specify it the default is 1 (one).
Formula View
These produce the same results.
Values View
• The LEFT function is the same as the RIGHT function, but it returns characters from the LEFT side of the value.
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
Example: MID ( <text>, <startPosition> ,
<numCharacters>)
• This example extracts the second through the fourth characters from the original text value:
Formula View
Values View
Concatenation
( & ) and CONCATENATE function
90
Concatenation (&)
• Use & to combine (or concatenate) two different text values
Concatenate many values
• You may concatenate many values together
• 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
Concatenating spaces - Example
• You can concatenate spaces into a formula
• You can combine the results of different function calls with concatenation.
Formula View
Values View
• In this example we concatenate periods into the initials.
• You can use the CONCATENATE function instead of the ampersand (&).
• The following formulas are equivalent:
=A1&B1&C1
=CONCATENATE(A1,B1,C1)
• The CONCATENATE function can take as many parameters as you like.
More Text Functions:
98
LOWER ( <textValue> )
UPPER ( <textValue> )
• LOWER converts text to lower case.
• UPPER converts text to upper case.
• Example:
Formula View
Values View
• 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
Formula View
Values View
Dates and Times
101
• Dates are stored in Excel as the number of days since Dec 31, 1899 for that date. (ex. Jan 1, 1900 is stored as the number 1).
• To see this, type a date in a cell and then press Ctrl-` to see the “formulas view”.
• Example
– Values View
• A cell can contain both a date and a time.
• The value of both the date and the time is stored internally as a single decimal number.
• The whole number portion represents the DATE and is the number of days since Dec. 31, 1899
• The decimal part represents the TIME and is the fraction of the day that has elapsed.
• Examples:
– Jan 1, 1900 at 12AM is 1.0 (i.e. 1 day since Dec 31, 1899 and 0 percent of the day elapsed so far)
– Jan 1, 1900 at 12PM is 1.5 (i.e. 0.5 of the day elapsed)
– Jan 2, 1900 at 12PM is 2.5 (i.e. 2 days since Dec. 31, 1899)
– Feb 1, 1900 at 1:05 PM is 32.5451388888889 (i.e. 32 days since Dec 31, 1899 and
0.5451388888889 of the day elapsed by 1:05 PM. This makes sense as it is a little past noon so a
Times and Dates - Example
• Values View
• Formulas View
• You can do arithmetic with dates.
• Add and subtract days by adding and subtracting whole numbers.
• Add and subtract times by adding and subtracting fractional values.
• Examples
=A1+7 (one week after the date in A1)
=A1-5*7 (5 weeks before the date in A1)
=A1- (1/24) (one hour before the time specified in A1)
=A1+ (3/24) (three hours after the time specified in A1)
=A1+2.5 (two and a half days after the time specified in A1)
=A1-A2+1 (the # of days between the date in A1 and the date in A2)
• Right click on the cell and choose “Format Cells”
• From the “Category” list in the
“Number” tab either
– Choose “Date”, “Time” or “Custom” and choose an appropriate looking format
OR
– If you choose “General” or “Number”, the internal number for the Date/Time will be displayed in the spreadsheet even in the “values” view.
Logical (AKA boolean) values
107
• A logical value can be one of only two values
TRUE
or
FALSE
TRUE
• The following statements are TRUE:
Fish live in water. Deer live on land.
• The following statements are also TRUE:
3 is greater than 2
2 is less than 3
2 is less than or equal to 3
2 is less than or equal to 2
3 is greater than or equal to 2
3 is greater than or equal to 3
2 is equal to 2
2 is not equal to 3
• The following statements are FALSE:
Fish live on land.
Deer live in water.
• The following statements are also FALSE:
2 is greater than 3
3 is less than 2
3 is less than or equal to 2
2 is greater than or equal to 3
2 is equal to 3
• In Excel the following "operators" are used
Operator Meaning
> greater than
< less than
>= greater than or equal to
<= less than or equal to
= equal to
<> not equal to
• Examples
3 > 2 true
3 < 2 false
Logical Formulas
Same formulas, different values
IF function Formula View Values View
IF with a numerical result
AND
OR
• The following is TRUE
Fish live in water AND deer live on land.
• The following are all FALSE
Fish live in water AND deer live in water.
Fish live on land AND deer live on land.
Fish live on land AND deer live in water.
AND
• You can use an AND inside of an IF.
• This is called a NESTED FUNCTION CALL
• Example
=IF( AND (A2>A3,B2<>B3) , 500, 1000)
These parentheses "belong to" the if
IF with AND - parameters
Parameters for IF function:
IF with AND - spreadsheet views
• Takes any number of parameters
• Returns TRUE if ALL of the parameters evaluate to TRUE otherwise returns FALSE.
• Takes any number of parameters
• Returns TRUE if ANY of the parameters evaluate to TRUE otherwise returns FALSE
• Takes ONLY ONE parameter
• Returns the "opposite" of the value of the parameter
– returns FALSE if the parameter value is TRUE – returns TRUE if the parameter value is FALSE
Examples of Complex Nested Function Calls
• =IF(AND(A2>A3, OR(B2=B3,C2<C3)), 500, 1000)
• =IF(AND(A2>A3, NOT(OR(B2=B3,C2<C3))), 500, 1000)
Other Logical Functions:
131
ISBLANK( <value> )
• ISBLANK returns TRUE if the value is blank and
false otherwise. (see example below) Total will be wrong if
quantity is blank (since a blank is
Total will be correct even if quantity ^{normally}is blank (quantity is assumed to be 1^{treated as}
in that case) ^{zero)}
Using the mouse to create formulas.
134
• Once you type the equal sign (=) you can click with your mouse to enter cell references into a formula.
Now you can click with your mouse to enter cell references.
• Example on following slides
rest of the formula now:
a dashed line around cell A1 and the text “A1” (without the quotes) will be entered into the
Use mouse to enter other types of cell references.
• Cell ranges:
• Click and drag on a cell to enter a cell range reference
• Cells on a different worksheet
– Click on a cell on another worksheet to enter a reference from a different worksheet.
– Be sure to type the next symbol in the formula (e.g. a plus sign (+) , a comma (,) , etc before you click on the original tab. If you don’t then the formula will be incorrect (try it).
FORMATTING A CELL AS TEXT
139
• Sometimes you desire to have to have zeroes displayed at the beginning of a number.
• For example, US social security numbers are made up of 9 digits. The first few digits may be zeroes.
• This causes in a problem in Excel. When you type in a number with leading zeroes into a cell, Excel removes the leading zeroes when you press Enter.
• EXAMPLE:
If you type the following into a cell (before you press Enter)
When you press Enter you get this:
Leading zeroes are missing
• See next slides for how to fix this …
• To fix this problem you can “format” the cell to display as “text” instead of as a “number”.
• The value will still be able to be used in calculations but it will be displayed on the screen using the rules for text values instead of the rules to display numbers
• One of the rules Excel uses to displaying numbers is to remove leading zeroes.
• However, if a number displayed as "text" data then Excel WILL display leading zeros.
• See next slide for instructions on how to do this …
• Select the cell or cells that you want to format as text.
• Right click on the selected cell(s) and choose the following from the popup menu format cells
or click on a cell and choose the following menu choice format | cells
• Then you will see the "Format Cells" dialog box.
(See the next slide )
"Format Cells" dialog box
• Choose
"Text" from the
"Numbe
r" tab and press the
OK
button.
• When you format the cell as “text” it will display the leading zeroes (you must type them in again).
• However, Excel will warn you that a number is formatted as text. (see next slide)
• Excel indicates this issue with a
green triangle in the upper left hand corner of the cell:
146
Force a Cell to Display as Text by Using an ^{147}
Apostrophe (')
• Another way to display leading zeroes in a number is to type an apostrophe as the first character in the cell.
• When you press Enter, the apostrophe is NOT displayed in the cell (it is displayed in the formula bar).
• The apostrophe tells Excel that the contents of the cell should be treated as text.
• The apostrophe is similar to the = sign.
– The = sign tells Excel that the cell contains a formula.
– The apostrophe (‘) tells Excel that the cell contains a text value.
anymore and leading zeroes remain.
However, Excel willwarn you that a number is formatted as text via the green triangle. (see earlier slides)
Ignoring numbers in calculations
149
• Typing an apostrophe (‘) as the first character in a cell with a number has the additional effect of causing the number to be ignored in calculations.
• NOTE: This does not happen when you format the cell that contains a number to display as text.
Ignoring numbers in calculations.
• By default, all numbers are included in numeric calculations.
• However, you can force a cell that contains a number to be treated as text and not be included in calculations with numeric functions (ex. SUM, AVERAGE, etc.) by placing an apostrophe as the first character in the cell
To fix the problem you can This will force NOTE: When you stop add an apostrophe (') before the number to editing the cell, the