CS 100 (Learn) — CS 100 (Web) — Module 05
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 explore how you can use multiple worksheets within a single workbook.
We mentioned this previously, and now we are just going to see a few more examples.
If you recall, on the bottom left hand corner of the current worksheet it shows other possible [all of the] worksheets [in this file].
Depending on your version of Excel, you might already have a few worksheets. In this version of Excel they default to only having one worksheet [per file], but we can add more.
[Excel] is going to call them Sheet2 and Sheet3.
For example, I am going to enter [on Sheet1] the number 123 and then on Sheet2 I am going to enter 456.
Both of those are in B2: [123 is in] cell B2 of Sheet1, [and] if I go to Sheet2, [456] is cell B2 of Sheet2 and then [on] Sheet3 what I want to do is I will go over to C2 and I want that to be equal to.. now, how do I click on what cell I want? What I can do is once I start [the formula with] the equal sign ([Excel] knows I am creating a formula) I can jump to another sheet and [reference a cell] to add [and then jump to another sheet].
[When] we look at [cell C2] on Sheet3, we can see
=Sheet1!B2+Sheet2!B2
I have calculated something [with a reference from] Sheet1 and [a reference from] Sheet2.
Let's take a look at the syntax a little closer: what it does is it puts the sheet name and then an exclamation mark (!) or a bang, and then the cell reference.
Similarly for Sheet2 it says Sheet2!B2. That may not look very sophisticated... you might want to have different names for your sheets.
For example [Sheet1 can be renamed to] "sales" and [Sheet2 can be renamed to] "marketing".
Now, when I go to Sheet3, you will notice [in the contents of C2] that it changed the name of the sheets for us.
=Sales!B2+Marketing!B2
On the Sales worksheet, I want cell B2 and on the Marketing worksheet, I want B2.
That is an example of referencing cells from other worksheets and that is about [all there is]... there really is not much more to it.
Note these are all relative cell references. For example, if I go back to Sales and I am enter [in cell B3] 100 and I go back to Marketing and [in cell B3] enter 200... if I go to Sheet3 and I copy [from C2 to C3]... because those were all relative references, it still works: it says "OK, this was calculating B2 from that sheet and B2 from that sheet... [so when I copy it down one cell] it says I want B3 and B3".
You [can also] use absolute references. I [change the contents of C2] to say "OK, I want [to use an] absolute reference: I always want B2 [from Sales]". Now, when I copy [on Sheet3 cell C2 to C3], it is going to be 323, because it used 123 from Sales, but the [reference to Marketing] was will relative so it used 200 from that sheet to get 323.
You can combine [references to] other worksheets with absolute [and relative] referencing.
One little trick that I want to point out is that if you rename your sheets to have spaces ([such as] "Sales forecast from 2016"), [we] can do that Excel: will let me do that. Then, if I look at the formula [in C3 of Sheet3] it shows 'Sales forcast from 2016'!B2. To ensure that it interprets [the reference with a space] it uses single quotes: this is a different use of single quotes than before when [we were] working with strings [text labels].
These single quotes are only used to indicate [reference] a worksheet that has spaces in it: these [single] quotes group all this text [reference] together [to indicate that] it is one thing all this this particular description.
That is [an overview of] using multiple worksheets within Excel.