Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
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.
Copying and pasting Excel formulas is in many ways similar to copying and pasting a cell's other contents. But there are some important differences you should be aware of. In particular, there are times when copying a formula from one cell to another changes the formula's cell references. In this lesson, I'll show you several ways to cut and copy your formulas while maintaining control over their results. If you want to copy a cell's formula to another cell without the possibility of any of the formulas references changing, you'll need to copy the cell's formula by selecting it on the formula bar.
So in this case, I want to copy the formula from cell B8 and put it in cell E8, but I don't want anything to change between those two cells. So, I will click cell B8, and then on the formula bar select the formula, press Command+C to copy it, press the Escape key to stop editing that cell, which Excel interpreted my actions as doing. Then click cell E8 and press Command+V to paste that formula into the cell.
What I did was copy the formula as if it were text and pasted it into another cell. Excel didn't realize I pasted the formula into the destination cell until it read the cell's new contents and saw the Equal sign at the beginning. Now, let's suppose that I want to copy the result of the formula in cell B8, instead of the formula itself. Well, first I will delete the formula from cell E8, because again, that's my destination cell. I will select cell B8 and press Command+C to copy it. I'll click cell E8 - that's where I want to paste the result - and then on the Home tab, I'll click the Paste button's down arrow and click Values.
Now, instead of pasting in the formula, like Excel did last time, Excel pasted in the value. You see the value here, like you would in either case, but when you look up on the formula bar, you will see that the value 57,836 is there, instead of the formula, which finds the sum of the values from cells B2 through B7. The one down side of pasting a formula's result instead of the formula itself is that if the formula's inputs change, the cell where you put the current value won't update to reflect that change.
So, for example, if I were to change the value in cell B2 to 8,401, the total here would change; the total here would not. Again, this is a value. This is a formula. Now, I am going to show you what happens when you copy formulas instead of using the formula bar, but by cutting and pasting like you would normally. To do that, I am going to switch to the second worksheet, Revenue per Sale, and then in cell D2, I am going to type the formula =B2*C2.
The idea is that I want to try to find my total revenue from sales at 8,400 per average sale, multiplied by 102 sales. When I press Return, I get the value of 856,800, and I'll just format these cells using the accounting format, so that you can see them as dollar values. Now, let's see what happens when I copy the formula from cell D2 and put it into cell D3. If I press Command+C to copy and then click cell D3 and press Command+V to paste, you'll see a value that too large to fit into the cell, but when I expand the cell by dragging the edge of the column header, you'll see that the value is now over one million.
Now, note that the formula that I pasted in B3 to C3 is not the same as the formula that I copied, which is B2*C2, and you might ask why that happened. The answer is, as I mentioned in the last movie, absolute versus relative references. A relative reference can change, so for example here, where I have B2 and C2, these are relative references. So if I copy - as I just did - the formula to another cell, those references change to reflect how far they moved in the worksheet.
In this case, I moved the formula down one row, so that means the number would change, but the letters would not. We are still looking at B and C as our columns. So, this cell contains B2 and C2. When I copied the formula down one cell, it should make it B3 times C3, which it did. If you want to copy formulas to other cells, you can do that using the fill handle the same way you can extend the series. Let's say that I want to copy this formula down to cell D7.
To do that, I hover the mouse pointer over the bottom right corner of the selected cells that contains the formula I want to copy. As soon as the mouse pointer turns into a black four-way cross, I can drag it down, and Excel copies the formula all the way down. Because I use relative references, we now have B4*C4, B5*C5, B6*C6, and B7*C7. But now let's see what happens if I copy the formula down and I change the formula to use absolute references.
So, to do that, I would go to the Formulas tab, start editing the formula on the formula bar, click in the cell reference that I want to change, and then click Switch Reference. Clicking it once makes it an absolute reference on both rows and columns, which is correct. Then I'll click here, on C7, and do exactly the same thing. The dollar signs indicate we now have an absolute reference, and when I press Return, we get the same value. But now remember, copying the formula down will no longer change those cell references because I now have absolute references.
If I press Command+C to copy and then press Command+V, I still get the same formula. It stayed exactly the same, because I used absolute references. At first, copying and pasting Excel formulas seems to cause random changes to your worksheet. Once you know how copying and pasting works though, you'll find that you have a great deal of control over how your worksheet, and its formulas behave.
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.