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 talk about the contents of cell, the value of a cell and the formatting [of] a cell.
Those are all slightly different things and it is very confusing for people who have never worked with worksheets before.
I am going to go to cell D2 and the first thing I am going to do (because it is going to come up later) is make that cell a little bit wider, so I actually make the entire column wider.
I go to column D, [and with] my mouse [I] drag it to the right, and now we have a really big cell, so we can put really big things in there (but I am not going to put something big in there).
I am going to type in a number: 12345.678.
Now, if I [return to] cell D2, we can see the contents of the cell up in the formula bar and we now have a number: 12345.678 in that cell, [and] that is both the value and the contents of the cell.
Later [we will see] where the value and the contents might be different, and then we will [revisit] the terminology.
The first thing I want to show you is that there [are] different ways of formatting [the cell].
If we look up to the ribbon [we can] see the formatting area, and [this cell is currently] formatted as "General".
If I click [the formatting dropdown box] I can say... "hey, let's make it look like currency" and when I click on the currency it did three things: put in the comma, put in the dollar sign in front, and you will notice now it doesn't say .678 -- it says .68 and that is because we are used to working in cents and dollars and there is no such thing as a fraction of a cent.
[However], if you look at the value of the cell up here [in the formula bar] the value is still 12345.678 -- it is just not displaying everything... it is using a different way of displaying that value. That is very interesting.
If I want to see more decimal places, I can click on the [(← .0 .00)] button and say "oh, show me more decimal places, thank you very much"... or maybe I am so rich I don't care about pennies so I am going to get rid of all of these [decimal places and so I will click on (→ .00 .0) button] and now it says I have 12,346 dollars.
We can adjust how many [decimal places] are displayed, but it is important to remember that the value of the cell itself [has not changed]. When I go back and look at it, it does have all of the precision there: it is just changing how it is displayed on the screen.
If I don't want to display it with a dollar sign I can just [use the] comma format (for example) and we have a very similar view but it just doesn't show the dollar sign. If [we return to] "General" [formatting] it will display all the information it can, so that is an example of how to display numbers.
I can also represent numbers as a percentage, so if I click on the percentage button it now actually multiplies by a hundred [in the displayed formatting], but I am going to go back and just say "you know what? this is just a number", and [change the format to "Number", and] I can [still] control how many decimal places I want to see".
That is a difference between [the] formatting [for a cell] and the contents of a cell.
Earlier I use the terminology contents and value and this is an important distinction that I want to make right now because the contents of the cell may be more than just a simple value.
I just typed in a number right here 12345.678.
I could type in something more complicated, so let's do something slightly more complicated than just typing in a value directly.
I am going to enter a formula, so to do that all formulas in Excel begin with the equal (=) sign.
All I am going to do is add one to it and I hit enter and now we can see what is happening here (I will add a bit more precision just so we can see what is going on).
If I look at the formula bar up top you can see the actual formula I entered: it is still (=12345.678 + 1) but what gets displayed is the actual value.
All formulas get resolved to a value and then it is that value that is displayed. Once again, I could format this as a dollar sign or a number or as a percent or however I want to display it, but the displayed value is different than the actual contents of the cell.
Now we can introduce all three concepts together: we have the actual
All three of those [properties] have to work together and may be slightly different. This [can be] confusing for people when you first learn Excel, [but] after a while it will become second nature.
I am [now] going to show you something a little bit more bizarre. I am going to take that cell and instead of saying... "hey, it is a number", I am going to say "hey, it is not a number... it is a date".
Where did Thursday October 19th 1933 come from? (You don't need to know this, but) Excel keeps track of dates as the number of days since January 1, 1900.
If you go back to 1900 and you start counting days, if you add 12,346 days from January 1st 1900, you will end up at Thursday October 19th 1933, and the fractional part of the number is actually the time... so Excel thinks that the time is 4:16:19 p.m.
If we look at the contents of the cell, it's still a formula but now it produces a value and Excel is going to interpret [display] that value according to how we formatted it so we can go back to date, or, if we say "nope, that was not a date that was actually a number" [it returns to be a number].
This really drives home the concept that we have three separate [properties].
The last thing I want to talk about [are] values.
For the most part, when people think about values they [think of] numbers, but there are a few different types of values in Excel.
We've already seen date values and time values... those are special values that Excel still represents as a number.
If you [recall] the module on digital information, everything can be reduced down to a number whether it's a date or even, if you recall, we can actually enter text.
In excel, if I enter text: I [change the contents of a cell to be] "HELLO THERE" (I just want to shout it out at you) "HELLO THERE". That is also a value. You can have values that aren't just numbers. This just happens to be a text value in Excel... they're called labels.
There's one other type of value I just want to point out and that's if I type in true... that is now a Boolean value and we can also have false as well.
There are basically four different types [of] Excel values. We have:
Now we have had a comprehensive [overview] of the differences between the contents of the cell, the value of the cell and the formatting of the cell.