Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Excel for Mac 2011 Essential Training, author Curt Frye gives a comprehensive overview of Excel, the full-featured spreadsheet software from Microsoft. The course covers key skills such as manipulating workbook and cell data, using functions, automating actions, printing worksheets, and collaborating with others. Exercise files accompany the course.
When you use a value in a formula, you have two options: you can put the value in a formula directly, or you can use it in the cell and refer to the cell as part of the formula. In almost every case, it's a better idea to draw the value from a cell in your worksheet. Putting in a value in a cell both makes the value visible instead of hidden in a formula and enables you to change the value without editing the formula. I'll show how to edit chapters later, but for now I'll show you how to use cell references effectively. The most direct way to enter a cell reference into a formula is to type the reference.
So let's say, for example, that I wanted to find the value in cell B4, which is here. I'll just click some other cell away from the data list, and I'll type =B4. So again, I have the column B and the row 4, and the cell at the intersection of that row and column is cell B4. So when I type the Equal sign followed by B4 and press Return, I get this value in this cell. You can also enter a cell's address into a formula by clicking the cell when you come to the point in the formula where you want to put it.
So let's say that I type =, and this time I want to value in cell C14. I have typed the Equal Sign, and I can now click cell C14, and when I do, Excel adds that cell to the formula. Press Return, and there is the result. You can do the same thing for groups of cells. So let's say that I wanted to find the total of the values in cell B3 and C3. To do that, I can type = and then sum plus a left parenthesis, and now I can select cells B3 and C3 and then type a right parenthesis to close, and there is the value.
Now let's say that I want to find the value of four cells, but they're not together; they are spread out around the worksheet. To do that, I type an Equal sign, and then sum, and a left parenthesis, and now I can select the cells that I want to provide values. But I need to select them the same way that you select multiple cells if you want to format them at the same time. To do that, I hold down the Command key and I click the first cell that want. Let's say it's B3. So I click B3, and now, holding down the Command key, I click cell C4.
Now see what Excel did over here in my formula. It has cell B3, which was the first cell that I selected, and then it put a comma and then cell C4, which was the second cell that I selected. If I hold down the Command key again and click cell B5, Excel writes another comma and adds cell B5 to the formula. When I type a right parenthesis to close it and hit Return, Excel adds that value to the cell. If rather than clicking the cells you would like to enter it by hand, you can do it the same way. You just do =SUM(B3,C4,B5).
So it all depends upon what you're comfortable with and what you think would be faster, frankly. Type in a right parenthesis, press Return, and you get the same result. Now I am going to do something a little bit different. I'll move down one cell. First, I'll have =SUMB3:C3. Now I am going to type the references for cells B3 and C3 slightly differently. So I'll press =, so that Excel knows I am entering a formula, SUM, left parenthesis, and then I'll type $B. You can see that cell B3 is highlighted, even though I wrote the reference a little bit differently.
I'll tell you what the distinction is in a moment. So now I will type :$C$3. So I'm taking the total of B3 and C3, press the right parenthesis and you see that I have the same result, even though the formulas are written slightly differently. I have B3 and C3, B3 and C3. Now let's see what happens when I copy the formula from this cell down one. To do that, I will grab the fill handle, drag down, so I am copying the formula, and the result changed.
Instead of having B3 and C3, this formula now summarizes, as you can see here on the formula bar, B4 and C4. We'll examine what happened in a moment, but I want to show you that the formula in cell G8 doesn't change when I copy it. So I'll grab the fill handle, pull it down, and I get the same result. So let's compare what happened. In this formula, that instead of B3 and C3 I have B4 and C4, the cells that are one row below. So in other words, copying this formula one row below its original starting cell increments the rows by one to reflect the movement.
However, when I copy this formula here, with the dollar signs, to here, the dollar signs caused it to remain constant, and this is the difference between absolute references, which were these - they use the dollar signs - and relative references. Relative references change, or can change when you copy them to a different cell. Absolute references will not change. If you want to enter a formula using absolute references or a mix of absolute and relative references, you can do that. And you can either type in the references by yourself, like I did here, or you can just type in the regular references, and then on the Formulas tab, you can use the Switch Reference button to switch the references between absolute and relative.
So I am going to start editing the formula here on the formula bar, and to change a reference between relative and absolute, you need to click inside of it, on the formula bar, and then you can click the Switch Reference button to change between relative and absolute. And there are four different combinations. So right now we have B3, which is a relative reference. If I click the button, it changes it to absolute, in this case both absolute rows and columns they will not change. If I click it again, you get a relative column, which can change, and an absolute row, which will not. Click it again. It reverses.
Now I have an absolute column in a relative row. And if I click it again, it cycles all the way back to B3, with both the rows and the columns as relative values that can change if you copy the formula to another cell. And then we'll press Escape to stop editing the formula. You should take the time to experiment with your formulas, to see whether absolute or relative references provide the correct results. In most cases, either all relative or all absolute references will work, but be on the look out for situations where you need either your rows or your column references to be absolute and the other relative.
Find answers to the most frequently asked questions about Excel for Mac 2011 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.