CS 100 (Learn) — CS 100 (Web) — Module 04
NOTE: If your internet access is restricted and you do not have access to YouTube, we have provided alternate video links.
Note: This video transcript has been slightly modified. Corrections are marked with strikethrough, and alternative wording has been placed in [square brackets] to correct some of the awkward or confusing phrasing in the videos.
In this video we are going to [demonstrate] how cut and paste works in Excel and how it works differently with values and formulas.
To start, we are going to enter some values into our worksheet. I am going to enter the number of 42 and then we will use (for example) the number we used before: 12345.678. Note [that I am going to] make this cell a little bigger, and I am going to format that as a dollar amount just to illustrate a couple things.
Remember we can also have text "hello" and we can also have other special types of values such as Booleans.
To illustrate how copy and paste works, I go to the cell [i want to copy], and then I can copy. [With] my mouse I can go to the copy button but you also note that it shows the keyboard shortcut, which in Windows [is] Ctrl+C.
I am going to click that button and something happens... the cell gets a little border around it. In this case, it is moving with horizontal lines... and now I am going to show where I want to copy the contents of that cell to. I [click] paste (which is the keyboard shortcut Ctrl+V), and I have now copied the value from cell B2 to E2.
Excel does something a little differently that may be unfamiliar to you if you are used to how copy and paste works [in] other applications.
If I start entering another number [in another cell], note that the [moving] rectangle has disappeared from the cell I [copied] before. If I want to make another copy of [the cell I copied before], the paste button is actually grayed out... I can no longer paste. That is a little bit different in Excel: if you start copying something and then do something else, it will lose track of what you are trying to copy.
Next, [I will] give an example with the money so I will copy [the cell with the money] and this time we are going to use the keyboard shortcuts Ctrl+C and Ctrl+V (and make the column a little bigger) and we can see that not only did it copy the value but it also copied the formatting of [the cell].
There is actually two different things happening [when we cut and paste]: it is copying the contents of the cell (which was a value) [and] it was also copying the formatting of the cell. That is the default behavior in Excel.
You can specify whether you want to copy the formatting or not. If you go to the paste "pulldown" button it will show you that there are [alternatives]. For example, [you can] only paste values, you can only paste the formatting... there [are a few] different options there if you want to play with how the paste is going to [behave].
The default (which is what you are going to want [in most situations]) [copies] the contents of the cell and the formatting for the cell.
You can copy a range of cells, I am going to highlight this range [and copy] (Ctrl+C) and I am going to [move to a different cell and] paste (Ctrl+V). That is how copy and paste works in Excel.
Next, I am going to reset this worksheet [and do a] new example.
I am going to enter the numbers 12 [in A3] and 15 [in B2], we are going [see] what happens when you copy a formula. In this case, [in cell B3] I am going to add the cell to the left [A3] and the cell above [B2]. It is still formatted as a dollar amount... let's switch that back to general formatting.
Now we can see that the contents of B3 are A3+B2 (the cell to the left and the cell above). To illustrate how copy and paste works I am going to go to a new location in the worksheet [and enter] two new values: I will enter 100 [in cell E2] and 42 [in cell D3]. I want to use the same formula here (a cell to the left plus the cell above). I am going to copy the cell [in B3] and paste it [in E3] so I am copying from B3 to E3.
I pasted [in E3] and what happened [is], it generated a brand new formula based on the formula that was in B3. [In B3,] It added the cell to the left and above, and in cell E3 it does the exact same thing: it copied [adds] the contents of the cell to the left and the cell above.
This is the default behavior in Excel: all references are relative to the cell [from] where they are entered [copied from].
Let's do another example. [In cell C6, I want the value of the cell B3, which is] up 3 and 1 to the left. If I copy that cell [to B6] now [the formula in B6] is again up 3 and 1 to the left.
Whenever you copy and paste an excel formula it always keeps track of [the relative position of] all the cells that it references and then when you copy to the new cell it generates as new references that are in the same relative position.
This will take a little practice for you to get used to. It is counterintuitive for many people, but if you see how it works it is actually quite effective.
For example, I have 1 2 3 4 5 [in cells G2:G6] and [in G7] I want to sum up the contents of the cells above it. Then, [if] I have another group of cells and I want [another] cell to also add up the cells above [it], I [can] just copy [and] paste the [cell from G7].
I am going to show you one more thing that is a little confusing for people new to Excel. In cell B2, I am going to enter 100. Now, if we go [to another cell] in the worksheet, [we want to reference] B2, so [I enter =B2]. We have seen how Excel spreadsheets work: if I change the value [of B2] to 42, the other cell changes automatically.
[However,] watch what happens when I take my mouse and I hover over the edges [of the cell]... not to the corner, like we discussed before but to the edge. Notice that the cursor changes (✣) and this is sort of the universal symbol for "move".
I am going to move cell [B2 by dragging it to D3]. If we go back to our other cell... before it referenced B2 now it references D3.
Excel is smart enough that when you use "move", [it changes] all references to the original cell to be at the new location. This cell now [references] D3. When I moved cell B2 to D3 the formula at G4 changed automatically.
That is how moving works in Excel: it is different than copying and pasting, so be careful when you want to transition some data from one location to another location in the worksheet.
You want to think: "do I want to move, [or ] do I want to copy?... What are the results going to be?". Just be a little careful about how you do this.