Microsoft Excel 2007

Moving around in Excel

When Excel starts, you are in an empty spreadsheet. At this point, you can use the following keyboard commands to move around:

• left arrow move left by one cell
• right arrow: move right by one cell
• down arrow: move down by one cell
• up arrow: move up by one cell
• ctrl + arrow keys: go to edge of data region
• f5: go to cell
• f2: edit contents of cell
• Jaws key + shift + c: show all cells that contain data in the current column
• Jaws key + shift + r: show all cells that contain data in the current row
• Jaws key + shift + f: show all visible cells that contain a formula
• ctrl + page-up or page-down: go to next or previous sheet
• end key + arrow keys: move to the end of any row or column

Selecting cells, columns and rows

• shift + left arrow: select the current cell and the cell to the left
• shift + right arrow: select the current cell and the cell to the right
• shift + down arrow: select the current cell and the cell below
• shift + up arrow: select the current cell and the cell above
• shift + space: select the whole row
• ctrl + space: select the whole column

Entering information into a cell

• Move to a cell and start typing numbers or words.
• If you make a mistake, you can use delete or backspace to fix it.
• When you have finished, press an arrow key to move away from the cell. If you press enter, you will move down to the cell below the one you were in.
• Press ctrl + semicolon to enter today’s date into a cell.
• Press ctrl + shift + semicolon to enter the current time into a cell.

Entering a formula

Move to a cell, and press =. Jaws will say “formula,” and you can start to enter a formula. Here are some sample formulae:

• add the contents of cells A1 and B1: =A1 + B1
• multiply the contents of D1 and E1: =D1 * E1
• add the contents of cell A1 to B1 and multiply the result by C1: =(A1 + B1) * C1

Using the “sum” function

You can use the “sum” function to add a series of numbers quickly.

• Move to cell A1, and enter the number 1
• Next move to cell A2, and enter the number 2
• Now move to cell A3, and enter the number 3
• Finally, move to cell a4, and type the following: =SUM(A1:A3). Cell A4 now contains the number 6.

Note

Instead of typing the “sum” function into cell A4, you could also move to A4 and press alt + =. This is the shortcut key for the “sum” function.

Referring to More than one Cell

As you can see from the “sum” function example, you can tell a function to use a list of cells, not just one or two.

Examples

• =SUM(A1:A3) adds the contents of cells A1, A2, and A3 together, and enters the result into the current cell.
• =SUM(A1:B2) adds the contents of cells A1, A2, B1, and B2 together, and enters the result into the current cell.

The Countif Function

The “countif” function checks a cell or group of cells for the value you specify, and enters the number of results into the current cell.

Example

• Move to cell A1, and type the word “hello” (without the quotes).
• Now move to cell A2 and type “hello” again.
• Finally go to cell A3, type =COUNTIF(A1:A2,”hello”), and press enter. Cell A3 now contains the number 2, because cells A1 and A2 both contain the word “hello.”

Adding a Text Message to a Formula

You can make a formula’s output more understandable if you add a text message to it.

Let’s say we want to add a text message to the output of the countif function in the previous section. To add a text message to the formula in cell A3, do the following:

• Move to cell A3, and press f2 to edit the formula
• Change the formula so that it looks like this: =COUNTIF(A1:A2,”hello”)&” times”
• Press enter to save the changes to the formula. Cell A3 should now contain the phrase “2 times.”

The Sumif Function

You can use this function to perform a calculation on a range of cells, but only if a specified condition is true. The function takes two values:

• cell range: this is the cells that sumif should check
• condition: this is the condition that must be true before a calculation can be made

 

Example 1

Let’s imagine that the number 10 is in cell A1, the number 20 is in cell A2, and the number 30 is in cell A3. Now move to cell B1 and type the following formula:
=SUMIF(A1:A3,”<50″)

Cell B1 should contain the number 60. This is because the numbers in cells A1 to A3 are all less than 50, so sumif adds them together and puts the result in cell B1.

Example 2

As with the previous example, let’s say that the number 10 is in cell A1, the number 20 is in cell A2, and the number 30 is in cell A3. Now move to cell B1 and type the following formula:
=SUMIF(A1:A3,”<20″)

Cell B1 should now contain the number 10. This is because the only number less than 20 in cells A1 to A3 is the number 10 in cell A1. Therefore, sumif only adds the contents of cell A1, because the numbers in cells A2 and A3 are both greater than 20. Note that the number 20 in cell A2 is not added, because the condition specified in the formula was less than 20, not less than or equal to 20.

Note

The condition of the sumif function can also contain more complex values, for example, greater than equals or less than equals.

Automatically Filling Cells with Data

You can use Excel to fill cells with data automatically. For example, it can fill cells with a range of dates, numbers or words.

Example

Let’s fill some cells with a range of dates:

• First, move to cell A1 and enter today’s date. You can do this by using the shortcut key control + semicolon.
• We want Excel to fill seven cells with dates, so press shift and right arrow to highlight the current cell and the cell to the right.
• Continue to hold down shift, and press right arrow 6 more times.

Now let’s fill the selected cells with a range of dates:

• Press alt + h to move to the Home tab on the ribbon bar.
• Now press tab until you reach the “Fill button submenu.”
• Press space, and then press down arrow until you reach the “Series” item.
• Press enter, and the “Fill” dialogue box will appear. You can ignore most of the controls in this dialogue box except for the “Step value” edit box. Use tab to move to this edit box, and type the number 7.
• Now press tab until you reach the “Ok” button, and press space to press the button and close the dialogue box.

Cells A1 to H1 now contain a series of dates: A1 contains today’s date; B1 contains the date exactly one week from now; C1 contains the date exactly two weeks from now, and so on.

The “If” Function

You can use the “if” function to test if a condition is true or false. This is useful if you want your spreadsheet to make decisions about the information you enter.

The “if” function takes three parameters:

• the condition to test
• The action to take if the test is true
• the action to take if the test is false

 

Example 1

Let’s start with an easy example. The following formula will test to see if the number in cell A1 is equal to 10. If it is, the words “it’s ten” will be added to cell B1. If it isn’t, the words “not ten” will be added to cell B1 instead:

• move to cell A1 and type the number 10
• move to cell B1 and enter the following formula: =IF(A1=10,”it’s ten”,”not ten”)

 

You can see that cell B1 now contains the words “it’s ten.” This is because the number in cell A1 is 10. Now enter the number 5 in cell A1, and move to cell B1. Cell B1 should now contain the words “not ten.” This is because 5 is not equal to 10.

Example 2

Let’s try a slightly more difficult example. The following formula will test to see if the number in cell A1 is greater than 10. If it is, the word “greater” will be added to cell B1. If it isn’t, the word “less” will be added to cell B1 instead:

• move to cell A1 and type the number 11
• move to cell B1 and enter the following formula: =IF(A1>10,”more”,”less”)

 

You can see that cell B1 now contains the word “greater.” This is because the number in cell A1 is 11, which is greater than 10. Now enter the number 5 in cell A1, and move to cell B1. Cell B1 should now contain the word “less.” This is because 5 is less than 10.

Example 3

Let’s try an even more difficult example. This time, we want to check that the number in A1 is greater than or equal to 1 and less than or equal to 10. If it is, we’ll add the message “within range” to cell B1. If it isn’t, we’ll add the message “outside range” to cell B1 instead:

• Move to cell A1 and type the number 5
• Now move to cell B1 and enter the following formula: =IF(AND(A1>=1,A1<=10),”within range”,”outside range”)

 

Cell B1 should now contain the words “within range.” This is because cell A1 contains the number 5, which is greater than 1 and less than 10. Test the formula by entering different numbers into cell A1, for example, what happens if you enter 0 into A1? What happens if you enter 11?

Example 4

This time, we’re going to test for words, not numbers. If cell A1 contains the word “sushi” or the word “sashimi,” then the words “Japanese food” will be added to cell B1. If cell A1 does not contain these words, then the words “don’t know” will be added to cell B1 instead:

• move to cell A1 and type the word “sushi” (without the quotes)
• move to cell B1 and enter the following formula: =IF(OR(A1=”sushi”,A1=”sashimi”),”Japanese food”,”don’t know”)

 

Cell B1 should contain the words “Japanese food.” This is because cell A1 contains the word “sushi.” Try entering “sashimi” into cell A1 instead. Cell B1 should still contain the words “Japanese food.” Now try entering a different word into cell A1, for example, “apple.” Cell B1 should now contain the words “don’t know.” This is because the word “apple” in cell A1 is not “sushi” or “sashimi.”

Example 5

And now for the most difficult example. Excel lets you use up to 7 “if” functions in one formula. This is useful if you want to test for lots of conditions, and not just one.

Teachers often want to give their students a letter grade for an examination or a piece of homework. If the student gets 50 to 59 per cent, then her letter grade will be C. If she gets between 60 to 69 per cent, then her letter grade will be B. If her score is between 70 and 79 per cent, then her letter grade will be A. Finally, if her score is 80 per cent or over, she will get an S, which means “super A.” If her score is anything else, her grade will be “F,” which means “fail.”

The following formula will use the criteria outlined above to produce a letter grade. It will check the number in cell A1, and use that number to decide the letter grade:

move to cell A1 and enter the number 55. If all goes well, this number should cause the formula to add the letter “C” to cell B1

move to cell B1 and enter the following formula: =IF(AND(A1>=50,A1=60,A1=70,A180,”S”,”F”))))

 

Cell B1 should now contain the letter C. This is because cell A1 contains the number 55, which is greater than 50 and less than 59.

This formula looks complicated, but really it isn’t. It uses four “if” functions to check the number in cell A1. Remember that the “if” function has three parameters:

• the condition to test
• The action to take if the test is true
• the action to take if the test is false

 

Here, the “action to take if the test is true” is adding a letter grade to cell B1. The “action to take if the test is false” is another “if” function, which checks cell A1 again. Now try changing the number in cell A1 and watch what happens to the letter in cell B1. This should help you to understand how the formula works.

---

Go back to the Course Notes page.