How To Make a Basic Checkbook Spreadsheet With Numbers On a Mac

Hi this is Gary with MacMost.

com.

Today let me show you how to do a basic checkbookin Mac Numbers.

MacMost is brought to you thanks to a greatgroup of more than 600 supporters.

Go to MacMost.

com/patreon.

There you can read more about the PatreonCampaign.

Join us and get exclusive content and coursediscounts.

So I've been asked a few times recently aboutdoing a basic checkbook in Mac Numbers.

So a checkbook is basically a running recordof deposits and withdrawals from you checking account.

But the tricky part is calculating the currentbalance based on each row of the checkbook.

This is actually a good example to use tostart learning how to use formulas in Numbers.

So let's start off with a standard blank templatehere in Numbers.

We're going to label the columns first.

Now you have the Header column here and wecan keep that because there is a unique identifier for each item here.

That's the check number.

Now we're not always going to use a checknumber but it's going to be common enough that we can use that as the header column.

So I'll call that Check Number.

Next we have the Date.

Then we can have the Recipient.

Now here we can put the amount of the check.

But a checkbook should also record any deposits.

So instead of calling it an amount we'll callit Withdrawal and then the next column we'll call the Deposit.

The idea would be that if this row representsa withdrawal, a check written out, the number would go here.

If it's a deposit, money going into the account, the number would go here.

You would never have a row where there's somethingin both of these cells.

Only one or the other.

The next column is going to be the runningbalance which is the whole point of the spreadsheet.

To be able to keep track of how much moneyis in the account at any time according to the amounts deposited or withdrawn.

Then we'll have a column here for MiscellaneousNotes which is always important.

So let's enter in some sample data here.

I'll just create a pretend number there.

Put in a date.

I won't worry about filling out Recipientshere.

But let's say that this is a $100 check.

Let's say there's another check and that'son the next day and that's for $45.

Then let's say on the next day there's a deposit.

Now there may be a deposit number or somethingyou have and you could enter that here or you could leave that blank.

Let's say here there's no withdrawal but thereis a deposit of $400.

Then let's add a few more samples.

You could see here I've added a few more checksand another deposit as well.

Now it's time to calculate the balance basedon that.

To do that we're going to need a startingbalance.

Let's create another table.

First we'll name this one Bank Account andI'll move it down somewhat to make room for another table up here.

We're going to insert another table and justuse a blank table like that.

Move it up here above Bank Account and let'sshrink it down so it's one by one because there's only going to be one number in thistable.

We're going to call that table Starting Balance.

We'll move that over here, kind of above thebalance column and we'll enter in the starting balance.

Let's say we're starting with $2000 in thisaccount at the time that we're starting to keep track of this.

You can adjust these tables and move themaround as you want.

So now I've got a starting balance of $2000.

To do the first balance calculation I'll hitthe equals key here to start entering in a formula.

I'm going to start with the Starting Balanceand we're going to then subtract any withdrawal in this row and add any deposits.

Then I hit Return and I'll get the balanceafter this check has been written out.

Now this first row is the only time we'regoing to refer to the Starting Balance.

For the second row here I'm going to do asimilar formula but I'm going to start with the balance in the row above.

Then I'm going to subtract the withdrawal.

Add the deposit.

Now that I've done that I can Copy this celland paste it in the remaining rows.

Now it will pull the balance from the rowabove and subtract the withdrawal of the current row and add the deposit of the current row.

So here we can see it added $400 and subtracted$67.

Subtracted $154.

Subtracted $300.

Added $256 and subtracted $50.

Let's go and shrink this table down.

I'm going to grab the bottom handle here andshrink it all the way up there so there are no extra rows.

This allows me to enter in new data by simplygoing over here to the check number, hitting Return, and then entering in the check numberand the other data.

Notice the formula is automatically filledin.

So as I put this in here, if I do a withdrawalof $12, you can see it automatically update the balance.

Let's make this a little bit nicer.

I'm going to select this column here and I'malso going to Control Click this column and this column.

Select all three columns.

Go to Cell, change the Data Format to Currency, and now I can check here that it's two decimal places.

I can.

I can add a Thousands Separator as well.

I can do the same thing here for the StartingBalance.

Change that to Currency and add the ThousandsSeparator.

So now I've got a nicer looking set of data.

That's basically what it takes.

It's just a matter of understanding the mainformula here which is the one that appears in all of these cells.

Which is just to simply take the balance fromthe row above, subtract the withdrawal, add the deposit of the same row.

Then realize that the first one is going tobe different.

Instead of taking from the row above it'sgoing to take it from a separate table here that contains just the starting balance.

Note sometimes people like to have thingslike withdrawal as negative numbers and deposits as positive numbers.

That's fine.

All you need to do then is adjust this formula.

So instead of subtracting the withdrawal itadds it because if the withdrawal is a negative number.

You want to add the negative number and notsubtract the negative number.

But other than that it all remains the same.

.