Using Excel Functions Format
The typical Excel function consists of 6 parts:
=Function_Name(P1,P2,…Pn) 1. 2. 3. 4. 5. 6.
Always begins with = The name of the function (not case sensitive) An opening parenthesis 1 or more parameters, P1,P2,…Pn, which can be cell references, numbers, text, other functions Multiple parameters are separated by commas. A closing parenthesis
Function Library
All of Excel’s functions can be found on the Formulas Ribbon.
The Library can be accessed by clicking on Insert Function.
Inline Help
As a function is being typed into a cell, Excel provides inline help.
Here we have started to enter the IF function: As soon as ( has been entered, the following help appears: The name of the function is highlighted and underscored. Clicking on IF will open a help screen about the IF function. As each comma is typed, the next portion of the help is highlighted in bold to indicate what should be entered next.
Excel Functions
1
Displaying all the Functions in a Spreadsheet Hold down the Ctrl key and press ~
To hide the functions again and display the results: hold down the Ctrl key and press ~
Evaluating a Function
Select the cell containing the function. Then: Formulas Ribbon -> Formula Auditing Group -> Evaluate Formula
Click on Evaluate to see step-by-step how the function is performing.
Relative and Absolute References
When a relative reference is used in a function, that reference will be automatically updated as the function is copied down a column or across a row.
When an absolute reference is used in a function, that reference will not be updated as the function is copied down a column or across a row. An absolute reference always points to the same specific cell, row or column no matter how it is used or where it is copied. To create an absolute reference, use $ signs. When copied:
Excel Functions
$A4 - the row reference will be updated but not the column A$4 - the column reference will be updated but not the row $A$4 - neither the column nor the row reference will be updated
2
Exercise 1: Text to Columns Spreadsheet: Text Insert 2 columns to the right of Column A. Then select cells A1:A31 Data Ribbon -> Data Tools Group -> Text to Columns
Step 1: the wizard determines whether or not our list is delimited. Click Next to continue.
Step 2: specify exactly what the delimiters are. In our example, there are 2 delimiters: comma and space.
Click Next to continue.
Step 3: set the data format for each column, if necessary. Click Finish to complete the process.
Excel Functions
3
Exercise 2: Text Functions
Function
Syntax
Purpose
LEFT
=LEFT(text,num_chars)
Returns the first num_chars of text.
RIGHT
=RIGHT((text,num_chars)
Returns the last num_chars of text.
&
--
Adds text strings together to produce a new text string.
LOWER
=LOWER(text)
Converts text to lowercase.
CONCATENATE
=CONCATENATE(text1,text2…)
Joins up to 30 text strings into one new text string.
LEN
=LEN(text)
Returns the number of characters in text.
FIND
=FIND(find_text,within_text, start_num)
Returns a number which is the position of find_text within within_text.
Spreadsheet:
Text
1.)
In cell E2:
=LEFT(D2,6)
2.)
Revise the formula in cell E2:
=LOWER(LEFT(D2,6))
3.)
In cell I2:
=H2&" "&G2
Spreadsheet:
and copy down the column. and copy down the column. and copy down the column.
Email
1.)
In cell B2:
=LEN(A2)
2.)
In cell C2:
=FIND("@",A2,1)
3.)
In cell D2:
=LEFT(A2,FIND("@",A2,1)-1)
4.)
In cell E2:
=RIGHT(A2,LEN(A2)-FIND("@",A2,1))
Now copy all formulas down the column.
Excel Functions
4
Excel Functions
5
Exercise 3: Generating Random Numbers Spreadsheet: Lottery
Function
Syntax
Purpose
RAND
=RAND()
Generates a number in the range 0.0000000000000000 – 0.9999999999999999
ROUNDDOWN
=ROUNDDOWN(number, num_digits)
Rounds a decimal number down to the number of digits specified by num_digits.
ROUNDUP
=ROUNDUP(number, num_digits
Rounds a decimal number up to the number of digits specified by num_digits.
RANDBETWEEN
=RANDBETWEEN(bottom, top)
Generates a random integer in the range where bottom is the lowest number and top the highest.
1.)
In cell B2:
=RAND()
2.)
In cell C2:
=RAND()*1000
3.)
In cell D2:
=ROUNDDOWN(RAND()*1000,2)
4.)
In cell E2
=ROUNDUP(RAND()*1000,0)
5.)
In cell F2:
=RANDBETWEEN(1,500)
6.)
Copy the functions in B2:F2 down to row 21.
Excel Functions
6
Exercise 4: Conditional Functions Spreadsheet: Conditional
Function
Syntax
Purpose
IF
=IF(logical_test, value_if_true, value_if_false)
If the logical_test is true, then enter value_if_true in the cell.; otherwise, enter value_if_false in the cell.
OR
=OR(logical1, logical 2…..)
If any logical test proves to be true, then OR returns TRUE.
AND
=AND(logical1, logical 2….)
All logical tests must be true in order for AND to return TRUE.
VALUE
=VALUE(text)
Converts a text string that represents a number to a number.
1.)
In cell B2:
=RANDBETWEEN(1, 9)
2.)
In cell E2:
=IF(B2=7,”One Shot”,”Try Again”)
3.)
In cell F2:
=IF(B2=7,IF(D2=7,”Two Shot”,””),””).
4.)
In cell G2:
=IF(OR(B2=C2,C2=D2,D2=B2),”2 PLAY”,””)
5.)
In cell H2
6.)
In cell I2:
7.)
Copy the functions in E2:I2 down to row 14.
Excel Functions
=IF(AND(B2=C2,C2=D2),”3 PLAY”,””) =VALUE(B2&C2&D2.)
7
Exercise 5: Evaluating Survey Results Spreadsheet: COUNTIF
Function
Syntax
Purpose
COUNTIF
=COUNTIF(range,criteria)
Returns the number of times that criteria occur in the range. Criteria can take various forms: 78, “>8”, “Total”.
COUNTA
=COUNTA(range)
Returns the number of non-blank cells in the range.
COUNTBLANK
=COUNTBLANK((range)
Returns the number of blank cells in the range.
1.)
In cell P3: =COUNTIF(C$3:C$17,$O3)
2.)
Copy the function in P3 into the cells P4:Z10.
3.)
In cell P11: =COUNTBLANK(C$3:C$17)
4.)
Select cells P3:Z11 and click on AutoSum
5.)
In cell C18:
Excel Functions
Copy across the row to Z11. on the Home Ribbon.
=COUNTA(B3:B17)
8
Exercise 6: Conditional Formatting Spreadsheet: Conditional Formatting
1.)
Select A2:B18.
2.)
Home Ribbon -> Styles Group -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format.
3.)
In the box type: =$B2=$E$2
4.)
Click on Format to select various formatting options. Then click OK.
Excel Functions
9
Exercise 7: Using Functions with Charts Spreadsheet: Chart PLUS
Function
Syntax
MAX
=MAX(range)
Returns the largest value in a set of values
MIN
=MIN(range)
Returns the largest value in a set of values
NA
=NA()
Purpose
Returns the error value #N/A. #N/A is the error value that means "no value is available." Use NA to mark empty cells. By entering #N/A in cells where you are missing information, you can avoid the problem of unintentionally including empty cells in your calculations.
1.)
Select cells A2:A26. Click in the Name Box and type Grades, then hit Enter. We can now use the word Grades to refer to the values in column A instead of typing in the range.
2.)
In cell B2:
=IF($A2=MAX(Grades),$A2,NA())
Copy down the column.
3.)
In cell C2:
=IF($A2=MIN(Grades),$A2,NA())
Copy down the column.
4.)
Select cells A1:C26. Open the Insert ribbon and apply a Line Chart.
5.)
Use the Chart Tools to move the chart to a new location and apply formatting options.
Excel Functions
10
Hold down
And then press
Function
Also work in other Windows programs
Ctrl
c
Copy
*
Ctrl
x
Cut
*
Ctrl
v
Paste
*
Ctrl
z
Undo
*
Ctrl
y
Redo
*
Ctrl
p
Print
*
Ctrl
s
Save
*
Ctrl
a
Select All
*
Ctrl
b
Bold
*
Ctrl
i
Italic
*
st
Go to the 1 cell in the spreadsheet Go to the last cell in the spreadsheet
Ctrl
Home
Ctrl
End
Ctrl
↓
Travel down
Ctrl
→
Travel right
Ctrl
↑
Travel up
Ctrl
←
Travel left
Ctrl Shift
↓
Select down
Ctrl Shift
→
Select right
Ctrl Shift
↑
Select up
Ctrl Shift
←
Select left
Ctrl Shift
8
Select the current region
Ctrl
~
Show all formulas
Tab
Move to the next cell to the right
Tab
Move to the next cell to the left
F1
Open Help
F2
Open Edit mode in a cell
F9
Recalculate the formulas in the spreadsheet
F11
Create a chart
Shift
Excel Functions
11
