Welcome to Excel
Basics number two. This is our second
introduction to Excel. And in this video, we'll
see how to create formulas-- formula inputs. We'll talk about
Excel's golden rule. And we'll build a chart. Now, this is part of
the office 2016 series. This is video number 14. Now, here's the end result. We
need a net income projection or budget, and we need
to make this chart here. Then if we change any
of the formula inputs, whether or not it's
to start revenue for January, the assumed
revenue increase, or the expense percentage, everything in
our projection will change and the chart will update also. So that means the
goal of this video is to create Net Income
Projections table and chart that allows easy update. Now, here's all the topics
we're going to cover. Now, I want to remind
you at our class website and this link is
below the video, there are awesome PDF notes
for all the Excel Basic videos in this series. If you download all of
these, collect them together, it's like an awesome free book. Now, I'm going to
close this, and here is our system of folders. There's our 04 Excel folder. And there's the Excel
workbook that we created in our last video. Now, we're going to go down and
click the green x to open up a blank Excel workbook. I'm going to click,
hit Enter, or escape to get my blank workbook. Now, the last video
we learned that we have columns, rows, cells. We can have lots of worksheets. All the worksheets
make up the workbook. But another word
that people often use to describe this Excel
file is a spreadsheet. Now, that word "spreadsheet"
could mean your actual Excel workbook, or it can mean
an individual sheet. That is a loose synonym
when we're using Excel. So we're going to
save our spreadsheet or our workbook
file by hitting F12. We're going to navigate down to
Video Files and there's our 04 Excel. We're going to
click in File Name, and we're going to continue
our naming convention-- EB, for Excel basics. 02 because this is
the second video, dash Net Income Projections. [? .dat ?] file
extension is fine. I can either click
that save or hit Enter to enact that Save button. I'm going to hold Control
and roll my wheel to zoom in. Now, the very first
thing we're going to do is we're going to type
Net Income Projections. And now, I'm going to
hit Enter because I'm going to enter my labels
into this column vertically. Now, we type "Revenue" Enter,
"Expenses," Enter, "Net-- now, the one thing
you want to avoid. If I am not paying attention,
if I type "Net Income" and then hit Enter-- oh, look at that. It thought I wanted the
same word from above. So as soon as you start
typing "Net Income," you got to make sure to
delete that end part. Later, we'll see
when we're entering data like customer names over
and over, that autocomplete will be quite handy. Now, I'm going to Enter, Enter. Up here, we're going
to actually create all of our formulas to
calculate our revenue, expenses, and net income. But down here,
we're going to build what's called an
assumption table or a list of our formula inputs. This is where our
numbers will be that we change to then
get all of our projections and our chart to change. So right in so A6,
I'm of that type "Assumptions," space,
and then in parentheses "Formula Inputs," close
parentheses, Enter. We're going to assume that
the start revenue for January is going to be some amount. I type that and Enter. Revenue percentage increase will
have some percentage increase that we'll assume. We're also going to have
an expense percentage that will always tell us our expense
as a percentage of revenue, now Enter. Now, I want to come up and
change the column width here. Notice that column
is not wide enough to accommodate all of
these different labels. And I want to point
between A and B. We could see that downward
pointing cursor right there that would highlight
the whole column. But right in
between that cursor, we've actually seen that cursor
before in Windows Explorer when we were changing column widths. So I'm going to click and drag. And I'm actually
looking right down here, that expense as a
percentage of revenue is the widest item I want
to fit in this column. This is OK to hang over
because we're actually going to center this label
over two different cells. We'll do that in just a moment. Now, I want to come up to
cell B1 and type "Jan." Now, my goal is to
put this in the cell and keep the cell selected
so I Control-Enter. I wanted to keep it selected
because immediately we're going to use our fill handle. And as I moved my selection
cursor, our crosshair, or angry rabbit
to click and drag I want to increment months. I'm going to drag all
the way to the F column. And just like that,
Excel automatically knows to increment the months. Now, in G1 I'm going to
take "Total" and Enter. Now, we want to do a little
bit of stylistic formatting. I'm going to click in any
one cell in this top table. In the last video, we
learned that we absolutely could use our selection
cursor to click and drag to highlight
because we want to add borders to everything. Or we can simply-- if we have one cell
in our table selected, we can use the keyboard
Control-Asterisk. Now, I use the asterisk
on my number pad or you can use Control-Shift-8. Now, I got to show
you something. I didn't show you
this last video when I showed you this keyboard. If I'm out in the
middle of nowhere land and use Control-Asterisk,
it does not work. It doesn't know
where the data is. If I click in any
one of the cells when I use Control-Asterisk,
it searches in all directions until it runs into
completely empty cells, and that's how it
knows how to stop. One other thing which
is a potential pitfall, if you Control-8 because you
forget to hit the Shift key, you'll see this
message right here. Control-8 is that keyboard
for outline symbols, which is something we don't
get to see in this class. All right, so Control-Asterisk. Now, I can either come
up to the Front Group, drop down, and point
to all borders. Or I can right-click and
there's my mini toolbar. I can click the dropdown
and select All Borders. Now, I want to highlight
January all the way to Total using my selection cursor. Now, I'm going a hold Control
and select from the middle of cell A2 down to A4. That Control-selection
trick is very handy because we're going to add
the same formatting to all of these labels. Right-click mini toolbar
dropdown for the Paint Bucket. Then we're going to choose
Fill Color, dark blue. Now, I'm going to select
Font Color, white. Now, I'm going to select
the top corner, right-click, Fill Color, yellow. You can add whatever
colors you'd like. Now, we're going to build
our revenue and expense and net income
formulas all based off of 1, 2, 3 different numbers. That way we can change one
of the numbers and everything will update. Our assumed start
revenue for January-- that will be that
cell right there-- will be $5,000--
$5,000 and Enter. Our assumed revenue
percent increase, we're going to start
by typing a number, later we'll add a
percentage number format-- 1.05, Enter. Our assumed expense as
a percentage of revenue will be 0.65 and Enter. Now, I'd like to add
some number formatting. I'm going to click in cell B7. And instead of using the
Number Group up here, I want to open up the
Format Cells dialog box. The Format Cells
dialog box will give us many more options
for number formatting and many more options
for formatting cells. So with cell B7 selected, I
use the keyboard Control-1. That opens up the
Format Cells dialog box, Number tab, all sorts
of amazing options that we will see
throughout this series. Alignment, that allows
you to add the alignment. Font, border-- we'll get to use
this one later in this video-- Fill and even Protection. Now, let's go back to Number. I want to click on Currency. And in the last video,
we used currency format from the Number Group. But the advantage to the
Format Cells dialog box is there are hundreds
of more options here. But just for this
one example, I can choose to show different
decimal places. I can choose a
different monetary unit. We have it as dollar. I can also choose how to
display my negative numbers. Now, because we're
never going to have a start revenue that has
pennies, I'm going to decrease. And look at this
right here, there's always a sample when you
use Format Cells Number tab. It will always
give you a sample. So I can decrease and
it shows me sample. That sample will
come in handy when we do more complicated
number formatting. I'm going to leave
the dollar sign, and I kind of like the minus. If you're an accountant you
may like the parentheses to indicate a negative number. All right, I'm
going to click OK. Now, for our
percentages, I'm going to highlight both cells,
Control-1, come down to percentage. I'm going to choose one decimal. I can see the
sample right there. Click OK or hit Enter. Now, I want to add some
formatting to the top title here. So I'm going to
select cell A6 to B6. Control-1 to open
up Format Cells. I'm first going to start
in the Alignment tab. Under horizontal, I'm going to
select Center Across Selection. Now, there's another
way to center text. Up in the Alignment
group, oftentimes people use that button,
Merge and Center. If we're down here
in the dialog box, that would be the
check mark right there. I like Center Across Selection. And later on in the class,
we'll compare and contrast the two different options. I'm going to go over to Font-- actually, over to
Fill first, because I want to fill this title with
red because that's dark. I'm going to go back
over to Font and select Font Color, white. Now, I want to go
over to Border. And Borders allow you to select
a line, select the color, and then in essence we
draw our border over here. For this title,
I'm going to accept the default for line and color. And I'm going to
use the outline. Now, notice outline has a dark
border around the outside, but the inner lines
don't get a border. For us over here, the
outline is the outside. And we only have one inside
line that won't get a border. So I'm going to select Outline
and there's our preview. All right, I've done a number
formatting, alignment, font, border, and fill. That is pretty convenient-- Format Cells dialog box. Click OK or Enter. Now, I want to highlight
the remaining cells, right clicking on
the mini toolbar. I'm going select All Borders. All right, so we have
our formula imports or our assumptions. Now, we can start
building our formulas. The very first formula is easy. I just need to pull from the
assumption area that $5,000. So I simply type an equal sign-- equal sign starts all
formulas-- click on cell B7. I'm never copying
this anywhere, so I don't need to worry
about whether it's a relative or absolute
cell reference like we saw last video for the first time. So I'm going to hit Tab because
the next formula we're going to create is for February. Now, for each
successive month, I need to take the
previous month's revenue and increase it by 5%. The way we can do
that is we can take whatever the previous
months revenue is and multiply it by 1 plus
the percentage increase. Now, when you see 105%, you
can think of it as pennies. If I multiply $5,000 times
100%, I get exactly $5,000. If I took 100% and multiplied
it by $1, I'd get $1. But because we have a 105%, if I
were to multiply 105% times $1, I'd get exactly $1.05. So that means when
we multiply this times our $5,000, it will
take every dollar in there, and for every dollar,
increase it by $0.05. Now, we're going to try our
formula here-- equals And I'm either going to use my mouse
to get my cell reference or because I'm close, I
going to use my left arrow. The previous month's
revenue times-- multiplication symbol in
Excel is a little asterisk, either Shift-8 or number
pad multiplication. Now, I'm going to
click on cell B8. Notice when it was close, I use
my arrow; when it was far away, I used my mouse. Now, I'm going to just
hit Control-Enter, and I want to talk
about why that works. Now, I hit pause and
typed all of this out. If you download the finished
version of this file, this will all be in
the finished file. But I want to examine
why in the world we're allowed to take a 105%
multiplied by some start number, and we get the
correct increased amount. That means we went from
$5,000 to this amount, increasing the $5,000 by 5%. Now, I did a simple
example here. If we started with 100 and
our increase percentage was 5%, then I can multiply
those two and get $5 and finally add them. And I even did a formula here. It's just those two that gives
me the amount of the change, or the increase. And this simply is
adding those two. Now, I could rewrite this
in a different form-- 100 plus 100 times the 0.05. That would give me
100 plus the $5, and then that would be
the total end amount. Because the idea is we're trying
to get from that start amount to the end amount with
the percentage increase. Now, if we rewrote
this even further, this will fully illustrate
why this formula is correct. 100 times 100 plus whatever
that change is, the percentage or decimal-- I could rewrite 100 right
there as 100 times one. Once I have that that means
I'm adding two things, and they both have times 100. So if you remember
factoring from Algebra, we're allowed to factor
those out and this is totally equivalent-- 100 times whatever is left
inside the parentheses. Remember, if we factor
out the 100's, we're left with 1 plus 0.05%. Then if we have that, we can
simplify it even further. And there, that's
the reason we're allowed to take 1.05 and
multiply it by some start amount or format
it as a percentage and you have 105% percent
times some start amount and always get the end amount. So that's what we did up
here, 105% times the $5,000. Now, we need to edit our formula
up here, so I'm going to F2. And notice the cursor is
touching that B8 cell. Now, if I copy this
over, it would not work. Because the blue one would be
relative and that would work. Because when we get over
to April, for example, I do need to look at the
previous month, March, to get the begin
amount or start amount. But B8, that needs to be locked. If I copy a right now, it's
a relative cell reference. If I copy it over to
March, that orange one would move to that
empty cell right there. So I need to lock
this before I copy it. I'm going to use the
F4 key to lock it. Now, I can Control-Enter,
point to the fill handle, and with
your angry rabbit, click and drag all
the way to May. Immediately, click in the
last cell, hit the F2 key. We're verifying that the
blue one is a relative cell reference and that
orange one is locked. That is beautiful. Tab, now I want to
add a total here. So I'm going to use my
keyboard for the sum function, Alt-equals. Now, we always want to make sure
that the sum function guessed correctly and it did. It got each one of
the monthly revenues. Enter. Now just to illustrate
the beauty of Excel, I want to change
the formula input. So start revenue for January's
$5,000, but if I type $6,500. Before I hit Enter,
watch what happens here. As soon as I hit Enter,
everything updates. That's absolutely beautiful. Now, I'm in a
Control-Z to undo that. Now, if our assumed 5% increase
per month was different, I could simply type "10." And I want to know something,
that percentage symbol is sitting there. We've already
formatted it, and it knows that it's a percentage. Now, going to type
a 6 and Enter. And just like that,
everything updated. Now, I'm going to Control-Z.
Later, when we make our chart, it will be even better. All the numbers will update
and the chart will update also. Now, we need our
expense formula. And the way this
works in budgeting is the accountants looked
at historical records and estimated that
on average 65% of the total revenue
for the month will be used up with expenses. Expenses are things
like rent expense, wage expense, cost of
goods sold expense. So we're going to use this 65%. All right, I'm going
to click in cell B3. Equal sign, up arrow,
and this cell reference is going to be a
relative cell reference. As we copy to the
side, that blue cell needs to move to February,
March, April and so on. So I'm not going to lock it. Now, I'm going to
multiply that by and there's our expense
as a percent of revenue. Now, if I think about that
as I copy to the side. If I were to copy this
right now to February, that cell reference would move
because it's a relative cell reference. And I don't want that. Every single month,
I need to look at whatever the monthly revenue
is and multiply it by our 65%. So I'm immediately
going to hit the F4 key. Now, Control-Enter,
point to my fill handle with the angry
rabbit, click and drag. I immediately click in
the last cell and F2. I'm verifying that it got
the correct May revenue, and it's still
locked on our 65%. Now, another way to think of
why this expense formula works, and this is a very
common budget formula. We take our revenue and
multiply it by the percentage the accountants gave us. What this means is that
for every $1 into the cash register, 65 pennies
are spent on expenses. The remaining 35 pennies
are left over for profit. All right, I'm
going to hit Enter. Now, our next formula is
going to be net income. And net income is
revenue minus expenses. So this formula, you
equal sign, up arrow, up arrow minus up arrow. When your cell
references are close, your arrow keys are going to
be much faster than your mouse. Now, our goal is to
put this in the cell and keep the cell selected. So I Control-Enter, I
point to my fill handle, and with my angry
rabbit, I click and drag. Now, I just copy
that formula over. So I have to get the last cell
and select it and use the F2 key to put it in Edit mode. And I want to verify that the
cell references are pointing to the correct location. And they are-- May, Revenue, and Expenses. Now, I want to revert back
to what was in the cell before I put the cell in Edit
mode, so I use the Escape key. Now, I need to add
all of the expenses to get our total expenses. So I select cell G3. So what Alt-equals then I verify
that the cell references are correct and Enter. Alt-equals, wow, look at that. That's why we always
have to verify. That is not correct. Now remember, when the dancing
ants are dancing anywhere you go, you're
still in Edit mode. So simply click and drag. And when you have the correct
number of cells, January to May net incomes, you hit Enter. Now, we're ready for our chart. I'm very carefully going to
select the labels January to May. So I click in B1 and
drag all the way to May. Now, I'm going to
use my Control key. I do not want to
highlight everything. I want to use Control and
select just the net incomes. I have some labels
and some amounts. And I want to visually
compare these using a chart. Charts-- I go up to
the Insert ribbon tab. In the chart group, I'm going
to select the most common chart, the column chart. I' m going to
click the dropdown. We are not going
to use 3D charts. 3D charts are almost
always chart junk. And we want to select
the top one, the 2D one that says Clustered Column. And just like that,
we have a chart. Now, I want to try and
click on that outside edge because I need to
move the chart. What you don't want to do when
you're moving the chart is don't click on the
inside elements because then you might
move just those elements. I want to click on
the outside edge. And when I see my move
cursor, I can click and drag. Now, let's resize it. I'm going to point to
the little white circles and click and drag in. Now, we're going to format this. But you know what? Before we format this, I'm
going to click and drag off to the side. I would like to move all of this
down lower in the spreadsheet. I'm very carefully with
my selection cursor going to highlight,
Control-X to cut. Then I'm going to use
my wheel to scroll down just a little bit
and somewhere down below, I'm going to select
A25, Control-V to paste. All right, now I'm going to
use my wheel to scroll up. Point to the outside edge and
with my move cursor, click and drag. Now, we want to
format our chart. We can see the heights
are continually going up. What I like to do first
is add the actual amount above each column, and there's
two main ways to do formatting. There are some options
off to the side. We can add elements,
format, and filter. We're going to use
this one right here. We're going to click
the green plus. And there's a number of
items that we already have-- our axes, our chart
title, and our grid lines. We want to check Data Labels. You could see by hovering-- I already see them over
there-- but when I check, there they are. That's the first way we can
add elements and format. I'll show you the second
way in just a moment. Now, any time you're
looking at a chart and deciding what to add, what
to format, what to delete-- our guiding rule will be to
ask of each chart element, is it Chart Junk? Now, what is Chart Junk? Chart Junk is something
that doesn't help you articulate your message. A simple example of
Chart Junk is, well, I have some numbers here
which are perfectly fine, but then I also have
the numbers here. I probably don't need
them in both places. By having them in both places,
I'm cluttering up my chart. Now, I think I kind of like
these amounts above the column. So I'm very carefully going
to click on the vertical axis. And when I see my
solid line, I'm going to use the Delete
key to delete that axis. Now, grid lines-- those are
not necessarily Chart Junk, but really those
are there to help you line up the numbers that
were in our vertical axis. So I don't think we need those. Now, if you click
on the outside edge, it highlights the
very outside edge of all the elements in
the middle of the chart. That's not what I want. So I'm going to click on
one of the inside lines. And when I see those little
teeny dots highlighting, then I can use the Delete
key to delete those. The next thing I'd like to
do is format the columns. Now, when you select
the columns in a chart, notice I clicked once and it
highlighted all of the columns. That means I can format all
the columns at one time. If I were to click a
column a second time, then I highlight
just that column. And I can format
just that column. Now, I'm going to click
outside, click back, they're all selected. Now, just like when we're over
in the cells in our sheet, we can use Control-1 to open
up Format Cells dialog box, the same keyboard
works inside of charts. But when we use
Control-1, it will open up a Task pane that allows us
to format different elements. So I'm going to try it-- Control-1. Now, we have to
the column selected so our Task pane over here
will offer us different formatting options. Now, the one thing
that's a little bit confusing about the Task pane
is I see these icons at the top. Sometimes, it's hard to
find exactly what you want. But once the Task
pane opens, you can click through the icons, and
they open up different options. Now, for us, we might be able to
guess that the bucket of paint is what we want. So I'm to click on
the buck of paint. Now, the fill-- I'm going to
open up that little triangle and here are some options. If we want to change the
color, we'd say Solid Fill and then use the dropdown. But what I want to try
is Vary Colors By Point. Once I check that-- there it is, I get a different
color for each column. If I wanted borders, I
could click the triangle, click Solid Line and
then change the color. But that's how I'm going
to leave it for now-- Vary Colors By Point. Now, as we look at our chart
here, is there any Chart Junk? Well, the columns are OK. The labels are definitely OK. The amounts are OK. Yes, that's a
terrible chart title. Now, I do want a chart title. And I actually want it to say
"2017 Net Income Projections." So watch this, we're actually
going to link this chart title to the cell A1. And the way you link
chart elements-- whether it's a chart title or
later will have axis labels-- you make sure you select, in
our case, the chart title. And don't click inside because
then you get those dotted lines and you can't link it. The dotted lines are
fine, you could simply then highlight it and type,
but that's not what we want. Make sure the solid
line is selected, then we come up to the
Formula Bar and click, type in equal sign. And since we want whatever
text is in cell A1, I click on cell A1. Now, it puts the cell reference
in with the sheet name. I'm immediately
going to hit Enter. And I can see right
away, it totally got the text from cell A1. Now, if I wanted
to change this, I could come up and F2,
put it in Edit mode, and then click somewhere. And if I type "net
income," now I've change whatever's in cell A1. As soon as I hit Enter,
instantly our chart title updates. Now, I'm at the
Control-Z to undo that-- Control-Z. Now, let's resize the chart. I'm going to click
on the outside edge and with one of
my white circles, I'm going to click and drag in. Now, there's a great trick. If you point to the outside
edge of your chart and drag, it simply drags. But now, if you hold
the Alt key, Alt, it will snap to the grid. And that's what I want. I want it to exactly
snap to the line directly above
row 6 and the line on the front edge of column D. Now, we have our formula inputs. We have our net
income projections. And we have our chart. Now, I want to come
over, and I want you to notice we didn't
type any numbers in here. The only numbers that are going
to determine what our revenue expenses and net income
are and the chart result are these three numbers. Now, by creating all these
formulas in these charts based on these
formula inputs, we are following
Excel's golden rule. And that golden rule says for
any one of your formulas-- I'm going to put
this in Edit mode. Tab, edit mode. We have a bunch of different
formulas over here. For any one of these formulas,
if the formula inputs can change then you put them
into a cell and label them. Then in your formulas, you
refer to those formulas inputs with cell references. And that's exactly what we did. And here's the magic of
Excel's golden rule-- we can come here and the actual
starting amount was $7,250. When I hit Enter,
everything updates. The actual revenue
increase was 106, Enter. Not only that, but we totally
misheard the accountant. We thought they said 65%. They actually said 85%. So I'm to change
that 85 and Enter and instantly,
everything updates. We have followed
Excel's golden rule. Not only does that
golden rule make it easy for us to change things and
have our entire budget and chart update, but when we close
this and open it up tomorrow or next week, it's very clear-- it says formula inputs. We put the numbers
and we put labels. All right, the last step
is to add Page Setup. If I Control-P--
remember, we always have to look over to
our Print Preview. I definitely want to
exclude all of this. And I want to add a Footer
and change this to landscape orientation, so Escape. I'm going to go up Page Layout,
Page Setup, Dialog Launcher, or Alt, P, S, P.
I want landscape. I'm going to guess again,
increase maybe a 125%. Click Margins-- I'm going to
center of this horizontally. Header or Footer-- I'm going to come
down to the Footer, and right in the middle, I would
like to have the actual Excel workbook name. So I click the
Insert File Name-- that's secret code for
always giving the file name-- then I'm going to type
a space, dash, space. And then I'm going
to put my sheet name. Now, I'm at a click OK. Now, we go over to sheet. And I definitely
want a print area. I'm very carefully going to
select A1 all the way to H15. There it is. Now, it will exclude
all that other stuff that's not in that range. I'm going to click OK. Control-P-- I'm checking
to see how it looks. It's looking great. Look at that. That is bad naming. I'm going to click Escape. It's for January
to May 2017, so I'm going to come down, double-click
the Sheet1 and type "2017 January to May" and Enter. Now, when I Control-P,
look at that. It's looking great. Now, I'm not going
to click Print. I'm going click Escape and save. The last thing we want to do
is I'm noticing right here. I'm showing no decimals
with number formatting. Here, I'm showing one decimal. Over here and
showing two decimals. So I'd like to be consistent. I'm going to highlight
with my selection cursor all the cells, right-click,
and on the mini toolbar, I going to increase decimals. Notice when I increase decimals,
I see that they're all one. Now, you can decide
whichever one you want. I'm going to leave it out one
just so they're consistent, Escape. All right, in this
video, we saw how to create a Net Income
Projection table using all formulas and our chart. Both of these items
were pointing back to our assumption table where we
followed Excel's Golden Rule-- put numbers that can
change into the cells and then labeled them. And saw how amazingly easy it
is to simply change a number, and when I hit Enter,
everything updates. Now, in our next
video, we're going to talk about
adding and counting. And we're going to see a
bunch of cool formulas. And if you like this video,
click that thumbs up, leave a comment, and be sure to sub
because there's always lots more videos from Excel is Fun. All right, we'll
see you next video.
