For this lesson you need the sample file "First Quarter Sales and Profit-3" from your Sample Files folder.
Let's begin by observing the formula behind the value in cell B9. So I'll click once, and only once, on cell B9 to make it the active cell.
Now look at the Formula Bar at the top of the screen. Notice that the cell displays the value of a calculation and the Formula Bar shows the formula used to calculate the value.
Let's now delete the contents of cell B9. The quickest way to do this is to press the Delete key on your keyboard.
I'll now use the left-arrow key to move the active cell to cell A9.
I'm now going to replace the word "Total" in cell A9 with the words "USA Sales".
Now when you want to replace all of the text in a cell you don't need to delete it first, you can just start typing and it will be automatically deleted. So I'll type "USA Sales" into the cell, and then press the Tab key on the keyboard. This moves me one cell to the right to cell B9.
And in cell B9 I'd like to have the total of the New York and Los Angeles Sales.
Let's begin by talking about cursor shapes.
I'm going to click once on cell B4 to make it the active cell, and then position the mouse cursor at the very center of cell B4. This results in the White Cross or "Select" cursor shape. You see this cursor shape when you hover over the center of the active cell. You can then click and drag to select a range of cells.
Let's look at another cursor shape now. If you hover over one of the borders of the cell, but not the spot on the bottom right hand corner, you'll see the Four-Headed Arrow cursor shape. This is also sometimes called the Move cursor shape.
And now let's look at the third possible cursor shape, by hovering over that spot at the bottom-right of the active cell. And you now see the Black Cross cursor shape. This is also called the AutoFill Cursor. We'll be covering AutoFill later in this session.
Beginners often have difficulty selecting cells and end up moving them or AutoFilling them by mistake. So very carefully position the mouse cursor in the center of cell B4, so that you see the White Cross cursor shape.
When you're sure you have the White Cross cursor shape, carefully press the left mouse button and drag down to cell B5, and then release the mouse button. You've now selected cells B4 and B5. In Excel terminology we say that you've selected the Range B4:B5.
Because you've selected only the cells containing USA Sales, AutoSum can now be used to show the value of the selected cells in the first empty cell at the bottom of the column. That will be cell B9. So on the Home tab of the Ribbon, in the Editing Group, I'll now click the AutoSum button. And when I do, the number 64,000 appears in cell B9.
You can see that the New York Sales are 22,000, the Los Angeles Sales 42,000 and if I add those together I get 64,000. So we are seeing the correct result.
Notice that a green triangle has appeared in the top left corner of cell B9. This is Excel's way of politely saying: "I think you might have made a mistake", so let's see what the mistake is that Excel thinks we might have made.
I'll click once on cell B9 to make this the active cell, and notice that a ! icon has appeared to the left of the cell. This is called a Smart Tag.
Hover the mouse cursor over the Smart Tag and notice that a tip appears saying: "The formula in this cell refers to a range that has additional numbers adjacent to it." This is Excel's way of saying: "I think perhaps you wanted to include London, Paris and Munich sales in the total", but of course we didn't.In other words, Excel's made a mistake.
Let's look at the remedial actions suggested by Excel, by clicking the exclamation mark icon. You can see that the second option is: "Update Formula to Include Cells". If I were to click this option, then the 64,000 figure would change and I'd see the total including London, Paris and Munich.
But of course that's not what I want at all. I want to tell Excel to ignore the error. In other words, I'm saying to Excel: "Everything's fine, that's the result I wanted". So I'll click: "Ignore Error", and the green triangle disappears.
All that remains for this lesson is to save your work, and I shall save this with the new name: "First Quarter Sales and Profit-4". And as usual, I'll save it to the folder above my Sample Files Folder.
I click the Save button, and you've now completed Lesson 2-4: Select a Range of Cells and Understand Smart Tags.