Lab 8: Excel functions and formulas
Assignment due Friday, April 1
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
= 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