Help me with industry sign banking louisiana form easy
Welcome to Excel and Business
Math, video number 27. Hey, in this video we got to
talk about bank reconciliation. And we'll talk a little
bit about checks, types of endorsements for
checks, some terms used in bank reconciliation, but
really bank reconciliation. This is an amazingly
important skill that many graduates from
college do not have. Now I once went to an accounting
conference and this accounting recruiter said that
she couldn't believe these two year, four
year MBA students that came trying to
get accounting jobs couldn't do basic
bank reconciliation. Now it sounds scary, but no way. It is totally
straightforward and easy. And in this video,
we'll see how to think about bank reconciliation so
it is not a difficult topic. We'll also get to see
two cool Excel tricks. We'll see how to view
two different sheets simultaneously. So we'll have a
sheet in a workbook with our checkbook register
and the bank statement. And then on a completely
different sheet we'll have our bank
reconciliation form. And then at the
end of the video, this is not required for
the business math class, but I'll show you a cool trick
for conditional formatting and data validation that will
allow you to simply type an X, meaning we reconciled this item. And the whole row
will turn yellow. All right. We want to start off by going
over to the sheet checks. Now I briefly want
to look at a check, but instead of doing
it over here in Excel, I want to go to our PDF notes. Now a definition of a check,
a check is written, dated, and signed negotiable
instrument. Negotiable instrument means you
can take a check written out to you and sign it over
to someone else that contains an unconditional
order from the payor, that's the person who wrote the
check that directs the bank to pay a definite sum
of money to payee. Now we're going to look
at the parts of a check. But very important,
there's something called a canceled check. Here's two old
checks from the 90s when I ran a business
down in California using Bank of America. This is the front of the
check and this is the back. Now this is Pacific
Bell and Bank One. When those two businesses took
the check and deposited it, the banks actually took
the money from my bank and they put it into the
Pacific Bell's bank account and Bank One's bank account. In the process, they have
these marks on the back. These are stamps. These are the
official record that says the money was moved
from one account to another. These are called
canceled checks. Now in the old days,
businesses would keep these and it was the best proof
that the transaction occurred. In the old days we actually got
an envelope every single month with all of the canceled checks. That doesn't happen anymore. But these pictures are at
your online bank account. And any time there's a
dispute about a payment, if you have this
canceled check that's the best proof that the
transaction occurred. Meaning, the money
moved from your account to the payee's account. Don't get confused, canceled
check does not mean void. It just means the stamps
are on the back that prove that the money was moved
from one account to another. Now let's go to the next sheet. Parts of the check. This check is for
Sue's accounting. That's the payor's address. There's the check number, 4025. That's a unique
sequential check number. Those check numbers
of course, are important because they help
track this check later, like when we're trying to
do our bank reconciliation from the bank statement. And we want sequential numbers. No missing check numbers. If there's a
missing check you've got to go in investing
because that is trouble. There is the date. That means this amount
here, written as a number and as words can be paid
on that day or after. Right there, number four, that's
called the ABA, the American Bankers Association number. The numerator shows us
information about the state and the city for the bank. The denominator
tells us information about the Federal Reserve Bank. Now five, pay to the order of. That's the payee. So Sue's accounting
is paying Home Depot. Now six and seven. These are the amounts
for the check. If there's a discrepancy
the written description of the amount supersedes
the actual written number. Now I told you back in video 8,
I think it was, a funny story. I, back in the 90s
wrote a huge check. And for some reason
I wrote the words $1,000 less than the
actual written number. And of course,
the bank took that and gave the person that
our business owed the money $1,000 too little. So of course, I got a bill
later and I was surprised. And sure enough, when I went
back and looked at the check, there it was. So words supersede numbers. There's the bank address. There's the memo. This is important. Always, if you're a business,
write the description of where this is going. Later, we can use that to make
our entry into the accounting system with the right
expense account. There's the payor signature. That signature has to match
the card kept by the bank. And then down here we
have our routing numbers. There's the numbers associated
with the banks and the Federal Reserve. There's our account number. And there's the check
number, same as up here. Now I'm gonna go
to the next page. Endorsements. Yes, when we try to deposit
a check or someone tries to deposit one of our
checks that we gave to them, you have to sign the back. That allows the bank
to run the transaction and take the money from
one account to another. Now this is called
a blank endorsement. That's when you
just sign your name. The trouble with this is if
you drop this on the ground, anybody can pick it up and use
it, deposit it into their bank. Really, it's much better,
and most businesses have a stamp exactly like
this, for deposit only into, and then you put
the account number, and then you sign your name. This is called a
restricted endorsement. There's also a
qualified endorsement. Pay to the order of Sue Rad
Coolenator without recourse. And then I sign my name. This means I'm transferring
it over to Sue Rad Coolenator. If it didn't say
without recourse, Sue Rad Coolenator
could come back after me if the check bounced
or something like that. But when it says, without
recourse, forget it. Sue cannot come back after
me if the check bounces. All right. Endorsements. Now I want to go over to Excel
and talk about specific terms that we might see for
bank reconciliation. And then we'll do a
bank reconciliation. Now I'm over here on the
sheet, Why Bank Reconciliation? Now what is bank reconciliation? Well, here's the problem. This is our checkbook register. These are the withdrawals
and the deposits that we've been
keeping track of. And look at this. At the end of the month,
our balance is $4243.75. And then either in the
mail or more typically now, we just go online
and look at what's called the bank statement. Well, guess what? This is our checking
account this is our money. Of course, we're going
to keep track of it. But when we deposit the
money into the bank, they owe us this money. They're just using
it temporarily. So they also have to
track, just like we do, every single transaction. But what? The bank statement, or the
online balance is $10656.26. Why is that totally different
than our checkbook balance? The reason is simple. There are some transactions
that we know about, like this deposit that hasn't
made it to the bank yet. So that's an amount
that we owe about that the bank
doesn't know about. And look at this. On the 27th, sure enough
that customer from France transferred in the money. So that's an amount that
the bank knows about that we do not know about. Here's interest paid. That means we get interest on
our amount in this checking account. And a service fee. Those are examples of items
that we don't know about yet. So why do we do
bank reconciliation? Because we have to make
sure that different number and that different number
on the bank statement are actually the same. Once we take all the items
from the bank statement that we don't know about
and use them on our side, and then we take the numbers
that the bank doesn't know about and put
them on the bank side, we better have adjusted
checkbook balance exactly equal to adjust bank balance. Now on this sheet I
want to show you how to do it just in a simple way. Then we're going to go
use an official form and do an official
bank reconciliation. Now, before we do this,
let's look at some terms that we might see associated
with bank statements and bank reconciliation. Now ATM debit cards, we probably
have all used a debit card to directly withdraw cash
from our bank account when we're making a purchase at
Target or something like that. But you can also go
to your ATM machine and do all sorts
of transactions. Withdraw cash, make a
deposit, including checks, check our balance, transfer,
and all sorts of things. Also on our bank statement if
we're not paying attention, if we use ATMs that are not
associated with our bank we can get charged
$1, $2, $3, or more. Now online banking. We can now go online and
do all sorts of things. Make payments. In fact, many of the times
we don't use checks anymore. Checks are not a
thing of the past because oftentimes there
are some situations where you have to use a check,
or we're mailing it, or it's for a large
amount and you have to present it directly. So we still use checks,
but lots of times we go online to
make our payments, or transfer funds, or much more. Now things that can show
up on our bank statement. Electronic payments
or transfers. Now if we go to our online
bank and make a payment, we better be recording
that on our side. But we can receive
funds electronically. So we might get something
from a customer or a bank wire transfer. There also might be fees
associated with that. If you get a transfer
from a different country, you might get shot chart
$20, $30, $40 bucks. We also may find on our
bank statement, uh-oh, NSF. That means not sufficient funds. Now we might see it on our end. We bounced a check. So we tried to withdraw
money from our account, like with a check, but
there wasn't enough money. Also, return deposited item. That's also referred to
not sufficient funds, but that's when we tried to
deposit someone else's check and their bank account
didn't have enough money. In both cases, NSF when we tried
to withdraw from our account or when we tried to
deposit, we might see big fees charged
to our account that we then have to subtract
from our checking account balance. We might have an improperly
filled out check. And in fact, our
third example, we will go check online for
a check we deposited. And sure enough the bank thought
it was for a different amount. Well, if we find an
improperly filled out check, that means our side
and the bank side have them listed for
different amounts. So we will have to fix that. We also might go to the bank
and order a stop payment order, cashier's check, money order. We might have fees
associated with all of those. Also, two really important
terms in bank reconciliation, deposits in transit,
and outstanding checks. Now deposit in transit, that
means exactly what it says. That means we made a
deposit, but it's still on the way to the bank. Outstanding checks, that's
when we wrote the check, but it hasn't gotten
deposited yet. In both cases, those are
items that we know about that the bank
doesn't know about. All right. I'm going to slide
this out of the way. All right. I sort of already mostly
completed this first example just to emphasize
that in our records there's two items that the
bank doesn't know about. And over here there's three
items that we don't know about. All right. I'm just going to see if I
can do this manually here. Right. That's my balance, but it
looks like for my balance to be correct I'm going to
have to add the transfer, add the interest, and
subtract the service fee. So down here I'm
going to say equals, there's what's called the
start checkbook balance, plus there's the transfer in,
plus there's the interest, minus and there's
the service charge. So whatever that
amount is, when I do the same type of
adjustments to the bank I better get exactly
the same number here. So you ready? Equals, there's what the bank
thinks is in our account. We have a plus, that's a
deposit transit, and a minus. That's an outstanding check. So you ready? Plus the deposit that the
bank doesn't know about. Minus the check that
they do not know about. And when I hit Enter, these
two amounts better be equal. We can even check
in Excel, of course. Is that equal to-- there's a
comparative operator, this one. And when I hit Enter,
I hope it's true. That's all there is to
bank reconciliation. You have to make sure
that the actual amounts for both different people. This is us. This is the bank. We're keeping track of what's
happening to our account, but so is the bank. So bank reconciliation, we
have to make sure that the two numbers are actually the same. Now there's other approaches
to doing a bank reconciliation. Notice we did our
calculations off to the side, but what we really
need to do is actually get the numbers from
the bank statement and bring them into
our checkbook register. Also there is an official
bank reconciliation form that sometimes businesses use. All right I'm going to scroll
over and look at example two. Now here we have our
checkbook register and then the bank statement. Now when we do this
either manually-- I remember doing this by hand. Or even in Excel, or even
in an accounting program like Quick Books, we
usually have some column where we put a mark. That means we found this
on the bank statement. What we're going to
do is, we're going to put an X any time we
find a particular item in the checkbook register
and in our bank statement. But even more importantly
than that, anytime we find something in
the bank statement that's not in our records,
we're going to record it in our checkbook register. All right. Let's start here. I'm looking for check
number 12118 $22.75. And sure enough I
find it right here. So watch this. In the reconciliation
column I've added a dropdown arrow that
allows us to select an X. And instantly that whole row
is highlighted in yellow. Meaning, we found this
on the bank statement. Now at the end of
this video, I'll actually show you how to use
data validation and conditional formatting to do this in
your own checkbook register. All right. So I found it in both places. So I'm going to put an X. Now I'm looking for
check 12119 $375.11. And sure enough, there it is. So I'm going to put
an X in both places. I'm looking for this deposit
here for $1257 and so on. There's a deposit,
$1257 and 91 pennies. So that one is in both
statements or both accounting systems. All right. It looks like we have an
electronic transfer to city lights for that amount there. And sure enough, here is the
electronic reference number and the amount is correct. So I'm going to put
an X in both places. All right. Deposit. There it is $3550 and one penny. I do not see it over here. I am not putting an X. Check
number 12120 for that amount. I do not see it over here
so I'm not putting an X. Now I'm looking over here
in the bank statement. I definitely do not
see this over here. I do not see either one
of these items over here. So what I'm going to do is, I'm
going to bring this item over. 2-27 Tab. If I was looking at a piece of
paper, I'd have to type it out. If I was looking
online, I would copy it. Right. So I'm going to
copy that and paste. Tab. No, they don't have any
indication of what this is for, but we know this was for-- this was a customer payment
from Fox LMI company in France. And it was for
invoice 200 and A2. I'm gonna change the
column width here. This was definitely an addition. $7624.50. And there we go. So I'm going to put an
X in both places now. The next one, interest
paid for $247. This was on 2/28. Interest paid. Interest turned on
checking account. And this is definitely
a deposit for $2.47. I'm going to put an
X in both places. And then we have a
service fee on 2/28. Monthly service fee. And that is a reduction. 1.25 and I'm going to put
an X in both locations. We're going to copy
our formula down. We'll actually learn how
to create this checkbook register, including a
couple of cool formulas in our next video. There is the balance. So our checkbook balance
is the correct balance. But I'm going to
double check down here. There's only two items that
the bank doesn't know about. So I'm gonna say, hey, there
is the balance from the bank. There's plus one
deposit, minus one check that they don't
know about yet, and Enter. And so both of those
better be equal. Now any time you do a
bank reconciliation, sometimes you're going
to do it on a form like our next example,
but that's really the most important part, is we
take additions and subtractions from what the bank knows about,
and put those into our system. Now this is just the
checkbook register and this is a math class. So we're learning
just the math of it. But in your accounting
classes and if you're doing accounting
or bookkeeping, you would then have to go enter
these in as official journal entries, debits and
credits and all of that. But for us in this
class, we just got our bank balance in
our check of register up to the correct number. Now we want to go
look at example three. I'm going to click on the
sheet EX parentheses three. Here's our checkbook register. Here's our bank statement. But in this example, we have
slightly different numbers in the bank statement. And we want to see how to use
a bank reconciliation form. So I'm going to
click on EX3 BR form. Here's our bank
reconciliation form. And it's functions the same
way we did in our first exam-- we will start with a
bank statement balance and then add and subtract to
get to an adjusted balance. And then we'll start with
our checkbook balance and add and subtract get
to the adjusted balance. And those two better be equal. Now notice something. We are working between
two different sheets now. Now this will be the first
time we've done this, but we're totally
allowed to create a formula on this sheet
that will pull numbers from this sheet. Now we could do it by
clicking back and forth between the sheets, but I got
to show you a great trick. We can actually view both
these sheets simultaneously by going up to the
View ribbon tab. And what we're
going to do is we're going to click New Window. Now before I click
New Window, I want you to notice the title up
here lists just the title. Not only that, but down here
there's just one workbook open. But watch what happens
when I click New Window. Instantly, it opens up a
copy of the same workbook. Now let's look up
in the title bar. You can tell that there is
a second workbook, which is a copy of that other
one, because there's a number two in the title bar. If you look down to
the task bar down here and hover your cursor,
there's two instances of the same workbook. Now I can click back and
forth and it's exactly the same workbook. Now we want to show both
workbooks side by side. So we go back up to
View and in the Window group we click Arrange All. Now Arrange All will allow us
to show one on the left and one on the right. I'm going to select Vertical,
click OK, and look at that. I'm looking at the same exact
workbook in two windows. Now let's scroll over in
the workbook on the left to expose the sheet EX3. Now let's come over to
the workbook on the right. Scroll over with
your scroll arrows. And there it is, the
same exact sheet is being shown in two different windows. Now you can prove this to you. Watch. I'm going to click here. And when I type sheet and enter,
whoa, it's in both workbooks. Now that's not
what we want to do, but I wanted to show you
that when you have a range, and you've opened up to windows,
it's exactly the same sheet. We're just looking at it twice. Now I'm going to delete this
because what we really want, is I want to click on the
right, on the sheet EX3BR form. Now we have two different sheets
from the same workbook exposed. And we can work back and
forth between the two. Now we can change the
size of the window. And I want to change the size
of the window on the right. Click and drag. And then I'm going to try
to do that on the left. I see my horizontal white arrow. So I can click and drag. Now over on this side, I'm
going to hold Control and roll the wheel on my
mouse, because I want to see both the checkbook
register and our bank statement. Now we still have to
start on this side and verify all of
the transactions that occur in both. And then the ones that
don't occur in both, we'll use in our reconciliation form. All right. Now the first
transaction already has an X in yellow because that
was reconciled last period. But now we're looking for
check 12118 for $22.75. Sure enough we find
it on both sides. So I'm going to add
an X to both sides. We found that transaction. Check 12119, $375.11. It's on the bank side too. All right. A deposit for $1257.91. There's the deposit. There is the exact same amount. So I'm gonna X it
out on both sides. Now here is an electronic
bill pay, Seattle City Lights. There's $298.73. There's the reference number. Uh-oh. $288.73. There is a difference
between the two. Now as soon as we see a
difference, one of the sides made an error. We're going to assume
that the bank is correct. So that means we
wrote a check for $298 or we recorded it $10 more
than the bank subtracted. So when we do our
bank reconciliation, that's a difference. We're actually
going to have to add $10 bucks back in on our side. Now I'm going to leave a
note down at the bottom. I'm gonna get that reference
number, Tab equals, Seattle City Lights, Tab. And the difference is 10. And I have the numbers here
so I'm gonna do a formula. That cell reference minus that
cell reference over there. And that's the $10 bucks that
we'll have to add back in. Now why do we have
to add back in? Well, because we subtracted to
$298, which means $10 more than the bank did. So if we subtracted too much,
when we add it back in that will help us get back to
the same number right there. Now as we look down, this
deposit for $3515.01, I do not see that here. I do not see this check listed
on the bank side either. Over here in the
bank statement there is a customer
transfer from France. That is a positive that
we don't have on our side. There is some interest we don't
have, service fee, and an ATM. All right. Now, all of the
items without color, we're going to have to somehow
incorporate in this bank reconciliation form. Now again, it's going
to work similarly to how we did it the first time. And in fact, we're going to
list our bank statement balance and our checkbook balance. And then we'll fill
in all the detail to get to our adjusted numbers. All right. This will be the first
time we've actually done a formula that looks
to a different sheet. So watch this. Equal sign, and I need to get
the bank statement balance. I click once to
activate this window. Then I click a second time and
it puts the cell reference in. But look at that. That's called a sheet reference. Yes it's got the
cell reference in 11, but it also has the sheet name. Now, if you look over here,
there's the sheet name. Anytime you refer to a
cell on a different sheet, it will always be polite and
put the name of the sheet in. Now it's listing
single apostrophes around the sheet name and then
it has an explanation point. That explanation point is
the syntax that tells Excel, this text in the formula
is actually a sheet name. So when I hit Enter, it'll
totally get that number from that other sheet. Now let's get our
checkbook balance. I click, type in equal sign. I come over, I click once
to activate the window, second time to
click on the cell. There's our sheet reference. When I hit Enter,
there's the number. All right. First thing we want to do is
list the outstanding checks. Those are the checks that we
wrote that the bank doesn't know about yet. So right here I can
see we only have one. So I'm going to do an equal
sign to do a sheet reference. Click once. Click a second time to
get the cell reference. I see my sheet
referencing in Tab. I need the amount. Equal sign. Click once. Click twice. There's our sheet reference. And Enter. Now, we only have one. Oftentimes you have many,
but we only have one. So I'm gonna come to the bottom
and add, ALT equals and Enter. Now let's adjust the
bank statement balance. Now usually these
forms are polite. They'll actually describe the
process you need to go through. Now a form like this
is pretty standard. And back in the day when bank
statements were mailed out instead of electronic,
you would see this form on the back of your
bank statement. And you can use it to make sure
that both balances were really the same. So we read the instructions. List any deposits made by
you and not yet recorded by the bank. Well, we have one deposit. Some I'm gonna
type an equal sign. And our deposit is right there. So I click once. Click on the cell and Enter. Now it's telling us to add all
the numbers from above right here. Alt equals. It doesn't guess right. So I'm going to highlight. And Enter. Now it wants us to put the
total of the checks outstanding and then subtract it. Equal sign. Click on the total at
the bottom and Enter. Now it's polite. It say subtract 4 from 3. This is the adjusted balances. Equals, up arrow, up arrow,
minus up arrow, and Enter. That is our adjusted balance. Hopefully, when we
adjust our checkbook we'll get the same
number down here. Now there's the balance. It's says fees
deducted by the bank, not yet subtracted
from your checkbook. No problem. We have one of those. Equal sign. That's the one fee, $1.25. Now sometimes these
forms don't have the right slots for everything. So now it says,
other subtractions. So you have to go through and
get all of the subtractions. Well, we only have one, an ATM. So equal sign. Click once. Click twice. There is our sheet
reference and Enter. Now subtract the lines. We have to subtractions so
we're going to subtract line eight and seven from six,
equals, up arrow, minus up arrow, minus up arrow. And when I hit Enter, that's the
total after the subtractions. Now additions such
as transfers received by the bank and corrections. So equals. There's our one transfer. Plus, there's our addition. And when I hit Enter,
that's the amount we're going to have to add. And then it has a
line for interest that's different than
those two up there. So I go and get the
interest of $247. Click twice. I get the cell reference. And Enter. Now we can add all
three of these numbers. I'm gonna Alt equals. It gets it totally wrong. So I'm simply going to
highlight those numbers we should add and Enter. And there we go. Luckily we got two numbers
that are in balance, but I'm going to check
using a logical formula. Equal sign. Cell reference equal sign
as a comparative operator. And let's check
if they are equal. And we get our logical true. Now this is just one
of many different types of reconciling forms. And we saw two
other ways to do it. Sometimes you don't really
need to use a reconciling form. You just need to get all
the correct information into your records like we
did in our second example. Now I do want to show you
one last thing in this video. I want to show you how to
add and drop down to add an X and how to highlight
an entire row. Now we still have
two workbooks open. And guess what? I can just close one of
them, maximize this one here, and now when I go over to
the sheet EX3, of course, it's filled out because I was
looking at two different sheets from the same workbook. I'm going to be sure
and click Control S. Now I want to go over
to the sheet DV and CF. All right. The DV is for data
validation and the CF is for conditional formatting. Now what I want is a dropdown. So I'm going to
highlight these cells. And the reason I want a dropdown
that allows me to enter an X is because if I go up
to Data, and there's a button called
data validation, I want to make sure that the
only thing that gets entered into any one of these cells is
the letter X. Because later I'm gonna use something called,
conditional formatting. And it's going to check whether
someone entered a X here. I do not want someone to
accidentally enter the letter E because then this conditional
formatting will not work. So Control Z. Let's highlight
the whole column and validate what kind of data goes
into this range of cells. Data validation, I'm just
going to click the button. And it opens up this dialog box. Now by default you're allowed
to put anything into any cell, but we can click the dropdown. And there's all sorts
of amazing things that we're not going to use in
this class for data validation. You can validate a range of
numbers, decimals, what type of date, all sorts of things. But what we want is a list. Now we're going to
come down to source. And this is kind of
funny because usually you either type out a list or you
highlight a range of cells. What we have is
just a single item. I'm going to type
capital X. And now there will be an in-cell
dropdown that shows this X and that will be the
only thing allowed. Now you could go have
fun and put an input message in an error alert, but
we're not going to do that. We're simply going
to put the X in. Click OK. Now if I come here, there
is a dropdown with my X. Now watch what happens when
I try to type the letter T or whatever. I get this message. Now if you want
to have some fun, you can actually type
out in the dialog box exactly what you
want this to say. But we're not going to. I'm gonna click Cancel. I'm going to delete that. Now how do we get the
row to turn yellow when we put an X there? Well, here's the
thing, we're going to have to do a really fancy
formula that'll give us a true and false in each cell. Once we figure out
what that formula is, we're going to come to Home,
over to Conditional Formatting, and we're going to say New Rule. Now Conditional
Formatting is exactly what the title suggests. It will only format this
row if the condition is met, which means cell
F12 for this row has an X. When we get down
here every one of these cells is going to need either true
add the yellow, or false, don't add the yellow. Now again, this is not
required for this class, but I promised you I
would show you this. But here's how it works. Instead of highlighting
the whole range and going up here and typing the
formula out in the dialog box, we can actually create a
formula down in the cells, copy it over and down,
and see if it works. And if it does then we'll
copy and paste the formula up into the dialog box. Now here's what we want. Imagine this row represents
that row right there. Well, every single cell
has to look at that X. But when we copy
the formula down, notice this went
down three rows. That's going to
represent this row. Well, this row shouldn't
be looking at that X. It should be looking
at the X in F14. So watch this. We're going to do equals. And remember, I'm going to
copy this over and down, pretending that it's up here. Well, this whole row has to
look at that X, which is F12. But notice as I copy to the
side, I need it locked on F12. But when I copy
the formula down, that F12 needs to move to F13. So I have to use a mixed cell
reference in this formula. Now we saw this one
other time in this class. It's not something really
required for this class. But here's how we do it. I hit the F4 key
once, and twice, and a third time to put the
dollar sign in front of the F, but not the 12. Now what happens when
I copy to the side? The F is locked because
of the dollar sign. But when I copy it down, the
12 doesn't have a dollar sign. So it'll move to 13. Now as I copy this
to the side, I can't be just
looking at the cell. I have to create a logical
formula that asks the question, are you an X? So we type in equal sign, a
comparative operator, and then text has to be in double quotes,
capital X, and double quote. That's a logical
formula that asks, is there an X in that cell? Now watch this. Control Enter. I'm gonna copy it to the
side and then copy it down. Now I'm going to go
to the very last cell and hit the F2 key to verify. Is that the correct
cell reference? Yes. And in fact, you can check
any row, F2, look at that. That row represents that
transaction in my checkbook register. And it's looking
at the X column. Tab, F2. Tab, F2. It's always looking at
the correct X column. Now watch this. When I come up here
and select an X, that whole row turns to true. And that's the trick. We put that formula
in the dialog box. And the dialog box
will, in these cells, copy it over and down,
enabling a true and false to be in the dialog box,
which will either true, add the formatting, or false,
do not add the formatting. All right. So watch this. We have F2. That formula right there
represents that cell. And so we're going
to copy that formula. Notice that whole
range right there, we're copying the upper left
most formula in Edit mode. Control C and then Enter. Now we're going to
highlight this entire range. Because I copied the
formula in the upper left, I made sure the active
cell was in the upper left of the highlighted range. That way when I paste the
formula up in the dialog box, the dialog box in memory
will put the formula there and copy it over and down. So you ready? I'm going to go to
Conditional Formatting. New Rule. I don't want any of these. I want Use a
formula to determine which cells to format. Down here it says, Format values
where this formula is true. I click. And I Control V to paste. That formula in
this dialog box will be as if there's a
formula in every cell. That formula will be copied over
and down in memory, delivering a true and false to each cell. Now you can go click Format
and use any one of these tabs to add any formatting
that you want. I'm going to add yellow. Click OK. Click OK. And just like that,
that is amazing. If I delete this, the
formatting is gone. We can see the formula
in action down here. By the way, these formulas
don't have anything to do with the yellow up here. We just put those there
so we could visually see what was going on. If I delete all these just
for a second, Delete key, notice the formula
we copied and pasted and put up in that
dialog box is still there enacting the yellow. Now I'm going to leave that
as a trail Control Z. Now I can come to any row. And click that X. And just like
that, it adds the formatting. Control Z. Now let's do that over here. I'm going to
highlight this range. Go up to data. Data Validation button. I do not want to allow anything. I want to allow from a list. I'm going to type my list,
which is a single item X in cell dropdown. Click OK. Now I'm going to come down
here and create my formula. Equals, and it's
that cell that will be in the dialog box
representing that cell to copy over. I need to lock that
M, but not the 12. So I hit the F4
key, 1, 2, 3 times. And then I make a
logical formula. Are u equal to in double quotes. Capital X in double quotes. Control Enter. Copy it to the side. Copy it down. I'm going to test it. I'm going to test
the second row. I'm testing that X.
They all turn true. Upper left hand corner. I F2 and in edit mode
I Control C to copy. Enter. I highlight the range. Now active cell, right there. Same cell that
represents the top cell where I copied my formula. I go back to Home, over
to Conditional Formatting. New Rule I want use a
formula to determine which cells to format. I need to click in
format values where this formula is true and
Control V. Now I click Format. Whatever you want. You can go crazy. Click OK. Click OK. That is amazing. If I delete it, gone. If I come down here and click,
totally adds the formatting. Now that's some Excel magic. Now again, Data
Validation dropdown list, and conditional formatting
using a logical formula with mixed cell reference is
not required for this class, but I had to show you just
in case you wanted to try it. Now there are two homework
problems where there's a checkbook, and
a bank statement, and the bank reconciliation
form right on this sheet. So two homework problems
you can practice. This is an important skill,
being able to simply pick out what's on the checkbook
side that's not on the bank statement side, and what's on
the bank statement side that's not on the checkbook side. And make sure that both
check book and bank statement are actually in agreement. All right. So we saw a lot about
bank reconciliation. We talked about some
important bank terms. We talked about different
types of endorsements. Back on the sheet
checks, we talked about the different
parts of the checks. But really, it was
bank reconciliation that we wanted to learn. All right. If you like that video be
sure to click that thumbs up, leave a comment, and sub
because there's always lots more videos
to come from Excel Is Fun, including
next video, video 28. We'll actually build
this checkbook register and learn a couple important
formulas using is functions and the if function, which will
be the first time we see if. All right. We'll see you next video.
