today I'm gonna show you how to create an expense tracker in Excel that you can
keep on your phone when I was a kid my mother used to purchase everything by
cheque and she would keep a check register with her checkbook and she'd
always entered the checks into the checkbook tracker so that she would know
exactly what her balance it was at all time I've designed this worksheet to be
just looked like the checkbook trapper that she used to carry around with her
you can carry this on your phone and as you spend you can keep traveling of what
you have spent so in this sheet here I have I have the date I have what we've
done I've said the payee the payment deposits and there's balance always at
the top I have the current balance and that's so that you just you want a quick
look and see if you have enough money to buy something you can just quickly look
and they already is right at the top so let me show you how I did this so I'm
gonna start on another sheet the first thing that I'm going to do is an a.1 I
am going to type in the word balance and let's pick a figure for b1 I'm going to
start with $1,000 just because it's a round number now that's not going to be
our balance for very long so what I'm going to do is I'm going to click do
that Excel and I'm going to come back and I'm going to make it very small so
let's drop down the font size to be 8 because it's not really important okay
so then what we're going to do is we're going to leave this for our real balance
and now we're going to put in a line of headings here we're gonna put in date
we're going to put in type whether it's a purchase or a tea or cash withdrawal
or whatever and then we're going to put in payee
who repay this to
and then you're gonna put in the payment so what you've paid and then if you've
deposited any money you're gonna put it in here and then you're gonna create a
balance so what I'm gonna do now is I'm going to darken this these headers just
because so I can see the rows that are different so I'll just make it gray and
there we go so in the first line I'm gonna put the balance that we're
starting with and so I'm gonna in this balance I'm gonna go equals and I'm just
gonna reference the cell where we put the thousand dollars in here and press
Enter okay so then in the first row we can enter in the date of the what the
transaction we've done so let's say it was a third of December right and then
we'll just make that a little bit bigger and now let's say he was ATM which is
like and so we're taking on cash and what we've took out it we've taken out
is we've taken out let's say $50 let's say on December 4th December 4th you
received a check and we'll put check in here and deposit and it deposits going
to be let's say $300 so now let's work out the balance here
and so what we're gonna do is we're gonna do a formula and we're always
gonna we're gonna go equaling and we're taking the balancer above us and we're
going plus whatever is in this column and then minus whatever is in this
column and so now if you double click this double click the fill handle it
will fill all the way down and we can actually fill it against what goes right
to the bottom okay so now how we're gonna get the total so that it always
appears on the top so you want the total to be always always to balance
whatever's on the bottom so what we're gonna do is we're gonna go equals and we
are going to go the the initial value here and we're gonna go - and then we're
going to go open bracket and we're gonna go sum and then we're gonna go another
opening bracket and we are gonna go starting at the second line here and
then we're gonna go we're going to get to the bottom of our sheet because we
want everything that we've spent here so we're gonna spec press the control shift
and down arrow and it's going to go down to our last cell and then we are gonna
close our our bracket here and close our other bracket here
okay so we're - saying that and then we're gonna plus and we're gonna plus
whatever we've deposited so now I'm going to go back to the top of my sheet
and I am going to go and we're opening bracket and we're gonna go sum and then
another opening bracket and we're gonna go the same thing is we're gonna go in
this first column here or this first cell here we're gonna press the ctrl
shift down arrow and once again so we get to
the bottom of the sheet and then we go and that will give us our total so there
you have that the total here is what's Balint what's there now to make it so
that you can easily see what your balance is at any time and what you've
put in so to keep our headings at the top I have gone to the View tab I've
selected the first cell under the word date and I am going to go to freeze
panes and I'm going to go to the very top where it says freeze panes so now
you are able to scroll down and always have your headings on the top and so
here we go let's enter in let's enter in another date and another so 80 first of
all we know December 6th to 0 1 9 and we're gonna go ATM and we're gonna go
cash and let's say we took out another $75 and so there you go there's your
balance always at the top here and you have an an ongoing track of what you've
spent your money on so this is how you make an expense tracker for your phone
in Excel please subscribe