Welcome to Excel and Finance video number four.
And if you want to download this workbook and follow along,
click on my YouTube channel and then click on my college
website link, and you can download the workbook Finance
and Excel Chapter 00.
If you're in the class, just go to our college website.
In this video, we got to talk about number formatting-- very
important because so many people get tricked
by number formatting, and we don't want to.
We want to be able to make our calculations correctly
the first time without getting tricked by number formatting.
Now, here's some numbers.
And I can click in this cell and you can see the formula bar
shows me 1,000.
But watch this.
I'm going to highlight these, and what I would like
is to see a comma, a decimal, and two zeros.
Now, we're going to use the keyboard shortcut
Control-1 to open Format Cells.
There are some number formatting available up
in the home ribbon number group, but we're pretty much
never going to use that.
Control-1-- number tab.
And here it is, all sorts of amazing number formattings.
We're going to try Number.
Notice, immediately, the number format
puts a decimal and two zeros, which is formatting.
It's what we see on the surface.
It's not actually in the cell.
And then I'm going to tell it to use a separator for thousands.
Now, that's what we want.
I'm going to click OK.
Sure enough.
Now, notice, not in the cell.
Couple things about this--
totally amazing-- it keeps the number there.
We don't have to type in the comma, the decimal, and the two
zeros.
That's four characters for every single cell
that we have this number--
very convenient.
But realize it is like paint on the house.
It is the facade.
It is like a Halloween mask at Halloween.
It is the surface.
It's not what's actually in the cell.
This is just what your eyeballs see on the top of the cell,
on the surface.
In the cell is a number.
Here's another example.
I see-- this one already has formatting.
I see 10, but up here I can clearly
see that there's a 0.01.
So to increase this, we could control--
or as we saw earlier, we could just use our Increase Decimal
button.
Sometimes you get formatting like this--
55.
This is the currency format.
It's got a dollar sign, 55.54.
But you can see up here, we have lots of decimals
that are being hidden.
Those decimals are still in the cell.
And if you make a calculation, like adding or whatever--
actually, I neglected here.
Let me decrease what I meant to say.
Pretend like I never said-- oh, look at this 10.
How come this sum function is showing $30.03?
That's just an illustration of how a formula doesn't
look at the formatting.
OK, I messed up that one.
But here we can see there's a bunch of decimals, right?
And we really want to see those decimals.
So how do I get rid of number formatting?
This is important because there's
lots of different types of number formatting,
and sometimes they can really interfere with what you want.
Control-1-- and then you want General.
General wipes away all of these, and each one of these
has 10 or 20 different variations.
General-- you can immediately see the sample right here.
That is how you immediately get rid of number formatting
and see what's actually in the cell.
Now, I'm going to click Cancel because there's
a keyboard shortcut.
And in this class, we will do a lot of percentage number
format, a lot of date formatting, number formatting,
and so this is a handy keyboard shortcut to use.
But if you don't remember, just remember General, Control-Shift
tilde.
It wipes away instantly all the number formatting
and we can actually see the cells.
Again, we're doing finance, and we're
going to have some whacked out interest rates with lots
of decimals stretching out 15 places.
And once in a while, we're going to want to see
all those decimal places.
So that's a convenient one.
Here's another keyboard shortcut.
I use it all time because currency--
we're dealing with dollars a lot.
Control-Shift-4-- that does currency.
If I Control-1, you can see here it did, it applied currency.
Again, we saw this earlier in the class.
If you don't like these parentheses for negatives,
you can select that.
You can also use Currency, which is really nice,
and increase the decimal or decrease, right?
Maybe you want to see all those decimal places there.
Now, what's the difference between Currency
and Accounting?
Let's highlight this, Control-1, come down here, Accounting.
Accounting is nice because it will always line up
the decimal places just right.
So accounting dollar sign out-- it's
called fixed because it's always on the outside of the cell.
Currency is called floating.
Decimals always line up.
Now, they're lined up here too, but there's other examples
where they will not line up.
Let's look at some more very important number formatting.
Now, in this class, we're going to be talking about loans.
We're going to have to figure out maturity date.
When we do cash flow analysis, we're
going to have cash coming in at different dates,
and we actually need to do math on those dates.
Let's just look, before we look at the number formatting,
here's a loan that was due on the 1st, and it's 9/15.
How do we calculate how many days have gone by,
days past due?
Well, dates, this is actually a number formatting,
and underneath every date, even when you type in it-- look,
I'm going to delete, right?
You can see nothing there--
9/15/2010.
I typed it in.
You can see it there.
But, really, underneath behind the scenes in Excel,
there's a number there.
And these two numbers, since this is further through history
than this, you can subtract.
So you make a formula--
equals the later date minus the earlier date and hit Enter.
It tells you 45.
And it simply is looking at the distance between these dates,
and Excel knows that, in fact, there are 45 days.
Now, once in a while when you do a calculation like this,
it accidentally will bring forward the date format.
And this is especially true in earlier versions of Excel.
In 2010 and '07, it doesn't really happen anymore.
If this is the case, you made your calculation, later
date minus earlier date, and you still
see that, just go General--
Control-1 and then General.
I'm going to click Escape, Control-Shift tilde.
Again, that's a great keyboard shortcut.
Now, a date-- let's go ahead and type today's
date and the keyboard shortcut for today's date.
This is for accountants and financiers that are always
busy typing today's date.
I want to do a--
it's actually Control semicolon.
So watch this-- Control semicolon.
Pretty cool.
Another cool thing about dates is
if you copy them-- see that little fill handle?
This is the selection cursor.
When I select the date and move my selection cursor
by the fill handle, I see that cross-hair or angry rabbit.
I click and drag, and it will increment days,
which, again, is really handy.
We can also point here and say, fill months.
Wow.
Is that going to be convenient when
we get to cash flow analysis?
Now, what does that mean?
Those are all the pound signs.
It's like they put a fence up and we can't see it.
That just means the column's not wide enough, so you can click
and drag, or you can double-click to best fit,
but then it will best fit the largest item in the column.
So I don't want to do that.
I want to drag it.
Now, I want to see what these numbers are, because, remember,
I said down here, this is further
through history than this.
So if I highlight all these, how do I
get rid of number formatting?
That's right.
Slap some general number formatting on it--
Control-1.
Now, I'm going to use the Format Cells dialog box here.
I'm going to click there, and immediately you
can see the sample.
Now, I'm going to click Escape, Control-Shift tilde.
What?
40,436-- 40,466 and then 40,497.
That's jumping by one month each.
So that's how it knows, because there's these numbers.
Ah, but what's number one?
Let's just go ahead and apply the date format--
Control-1.
By the way, here we put a date in the cell
and applied General, and it showed us the number.
Here we're going to do the reverse.
Why don't we do all these?
We're going to find out what the first and second day in Excel
history is--
Control-1 and go to date.
Immediately, you can see a sample,
and there's a bunch of cool options.
You can have the day.
So Sunday was the first year in the 20th century.
This is all number formatting.
That word "Sunday," that word "January" is not in the cell.
It's the number one.
Click OK.
All right, so Sunday and then Monday--
those are the first and second days.
Now, why did Bill Gates and his pal pick one as the first day?
Because there's not very many invoices and loans
that are outstanding before that date.
That's pretty much why.
Now, here's the reverse.
I have some days here--
Control-1.
I apply some date and we see there's our example, 9/15.
I think that's the day I'm shooting this video.
Yep.
OK, so we saw a great advantage doing date math, right?
Date math-- I love it.
The first time you go on a date, you
don't go dancing or something.
You do math and Excel.
All right, so you can calculate the days between two dates
to figure out how many days till the loan--
how late is it?
Here's another great example, and we'll be doing this a lot
in this class.
We have a loan, issue date, and it's only a 45-day ,
loan and we need to find out the maturity date.
Equals-- and you cell reference your date-- plus and this.
Now, in this circumstance, we probably
don't want to use the sum because we're probably
not going to be inserting rows here.
But you certainly could.
So there it is.
There is the maturity date.
I'm going to do Alt equals.
Notice, it's waiting for me.
It's not guessing.
So I'm going to highlight these two and then hit Enter.
Either way you do it, adding 45 actual number integers
to this date gives me this.
Now, why did it suck the formatting here but not here?
Well, here-- I mean, it's programmed to do that.
In earlier versions, it didn't do this.
Now they know, when you're subtracting two dates,
you should probably have a general format,
and when you have a single date-- so it's
program-- a single date and a number,
it takes the number formatting from there
and shows you the date.
If it didn't for some reason, what would you do, you know?
You're like, what?
What's that?
I want to see what the maturity date is.
Then you Control-1 and show your date format
and pick whichever one you want.
Totally important-- number formatting.
We're going to save percentage number formatting
for the next video.
One last topic here, just a side note--
it doesn't come into play much in this particular class,
but numbers.
Notice, that's a number because it's a date--
we just saw that there's still a date-- an sales numbers.
I'm going to Control-Shift-4 to add some currency.
Notice that they're always aligned to the right.
Numbers, by default, are always aligned to the right,
and text is always aligned to the left.
Now, where this comes in handy is
if you have dates or any number--
sometimes when you dump some cash flow
numbers from some database into Excel, they come in as text.
So as soon as you see numbers aligned to the left,
be wary because there's some formula calculations that
will not handle dates or numbers that
are stored as text like this.
Now, this one happens to have an apostrophe.
That's one way to convert the date.
It's no longer a date.
You cannot do most date math on this.
As soon as you put an apostrophe, it's text.
This one, Control-1, someone had the nerve
of applying number format text.
For calculating formula inputs, that is a deadly format to use.
So you don't want to use it.
All right, when we come back, we'll talk about percentages.
We'll see you next video.