Lab 8: Excel Functions And Formulas Cheat Sheet printable pdf download

NRME 251C

Lab 8: Excel functions and formulas

Assignment due Friday, April 1

Spring, 2000

The goal of this exercise is to explore a variety of Excel functions and

formulas. The vehicle for this is a class grades spreadsheet. Some of the

things in the spreadsheet may seem a little far-fetched. That is because

my goal is to use different kinds of functions and formulas, not to

compute the grades in the simplest possible way.

When entering the

functions use the function wizard (the little fx button) to see the list of

available functions to get a better idea of the possibilities. Once you get

used to it you will probably find it easier to type the functions, but to start

out the wizard is a good way to learn how the different functions work.

1. Open a new blank Excel spreadsheet to enter your class grades.

2. Let’s leave the first two rows available for titles, then have the rest set up so each row

is a student, and the columns are the different information about the student. Fill in a

title for each column as you go along. For each column that is a formula, you will only

need to enter the formula once, then copy it down the column.

3. The first two columns can be the first name and last name of the students. Type in

eight or ten students so you will have some data to work with. Try to do the

capitalization wrong for at least one of them so we can fix it later.

4. Sort them in alphabetical order by last name using the Data Sort command. Be sure to

highlight all columns you have filled in so they will all get rearranged when it sorts.

5. The third column will be a formula which combines the first name and last name and

fixes the capitalization. To do this you will need the two text functions

CONCATENATE( ) and PROPER( ). CONCATENATE puts two or more strings

of characters together into one (e.g. CONCATENATE(a3,” “,b3) would string

together the contents of a3, a blank, and the contents of b3 into a single cell).

PROPER causes the first letter of each word to be capitalized (as for proper nouns --

there are also lower and upper case functions). You can string both functions together

into a single formula that will give the full name with proper capitalization in the third

column:

= CONCATENATE(PROPER(A2),” “,PROPER(B2)).

You can also do this formula using the concatenation operator (&):

=PROPER(A2) & “ “ & PROPER(B2)

Note that there need to be the same number of left and right parentheses by the time

you get done!

6. Once you get this column set up, hide the first and last name columns, and lock the

student names and title rows so they appear as titles no matter where you move in the

Lab 8: Excel Functions And Formulas Cheat Sheet