Excel Lesson 7

Use the Start menu to start Excel.

Open the file DIRECTORY1.xls. This file contains part of a student directory. The whole student directory is a lot bigger.

We are going to learn how to get Excel to COUNT for us. We want Excel to COUNT the number of students in the directory.

Click in cell A6. You will be using the =COUNT function. Type in =COUNT.

Now in parentheses, after =COUNT, type A2..A5. (A shortcut to this is to highlight the cellsA2 through A5.

Hit ENTER to see the result. Did you get the right number? It should be a 4.

Save your work and open DIRECTORY2.xls.

Notice that DIRECTORY2 has column headings. This makes it easier to read.

What makes DIRECTORY2 hard to read is that it has so many students in it. Use the scroll bar on the right to find the last student.

Can you still see the headings when you are looking at the last student?

We are going to learn an Excel trick so that we can always see the headings, even if we are at the bottom of a long file.

Click on the 2 (for Row 2) on the left hand side of the spreadsheet. This will highlight all the cells for the first student.

Now, go to the WINDOW menu and choose FREEZE PANES.

Now, scroll all the way to the last student again. Can you still see the headings?

Now, we want Excel to count the number of students in DIRECTORY2 for us. Click in A32 and type =COUNT(

Now, inside the parentheses, we want to tell Excel where to count. The first cell is A2 (we don’t count the headings!) What is the second cell?

After you have typed =COUNT(A2..A31), hit ENTER. How many students are there?

Save your work and open CANDY2.xls.

CANDY2 is a short file. We could easily count the candies ourselves, but we want to practice having Excel count to us.

In cell B9, type =COUNT. Use cell names from the B column this time, instead of the A column. Inside parentheses, type the cell name from the first row of candy, and the cell name from the last row.

Hit ENTER to see the result. Did you get 5 for an answer? Excel should have counted 5 kinds of candy.

Let’s go back to something we learned earlier. Click in B8. What we want to do here is find out how many total pieces of candy there are. You have already done this.

You know two different ways to do this. Let’s try using =SUM.

In parentheses, type the first of the cell names that has a number in it. Type two periods. Then type the last of the cells names that has candy in it. Hit ENTER.

How many total pieces of candy are there?

Using words, write what you think the difference between =COUNT and =SUM is. You can ask a teacher or peer helper to help you.

Save your work, and exit from Excel.