Create a checkbook with a spreadsheet

Choose your spreadsheet program

There are several free options for online spreadsheet creating these days. The two most popular free options are Google’s Sheets and Apple’s Numbers, for which you need a free iCloud account.  These programs can be used on a wide range of devices.  Most of the screen shots below are from an iPad.  Once you have chosen your program and created an account –

Start with a blank spreadsheet

sheets create new

 

numbers create newCreate a header row

Video explanation


Amazon in Article


Type the following values in cells A1 through A7.  

checkbook headers

  • Date – The date you made the deposit, wrote the check, etc. Note that this date does not always correspond to the date on which the transaction is posted to your bank account. This time lag creates the need to reconcile the account monthly. See how to do that in this post.
  • Check Number – This field is perhaps a bit outdated. 15 years ago, most payments coming out of a checking account would have been from paper checks. Presently, many are ACH’s or payments made directly from a bank’s website. However, this field can still be useful. You can use it simply for the checks that you still write, or you can be more creative with it and put confirmation numbers in it for online payments.
  • Cleared – This is the column that you use when you do your monthly reconciliation. You put an asterisk (or any other symbol) here when you see that the deposit or payment cleared the bank in the month in which you are performing your reconciliation.
  • Description – At the least, use this field to specify the payee for amounts going out and the payor for deposits coming in. Since it is a spreadsheet, and not a paper ledger, you can type as much as you want here. Take advantage of this. No one ever regrets having too much documentation. If you have a deposit from multiple people, write out all of their names and the amounts. Later, when you are trying to figure out if Mrs. So and So paid you, you can use search the spreadsheet (Ctl + F as a shortcut) and quickly find out.
  • Debit – Use this column for your payments out of the account. Admittedly, this is the opposite of what you would do if you were an accountant for a company using the double entry method. However, I chose to make my register this way because your bank calls payments debits and deposits credits so I find this less confusing.
  • Credit – Enter deposit amounts in this column
  • Balance – This will be the running total of your credits less your debits. Now, keeping this number large is your job!

Record your first entry

This is probably a deposit for an opening balance. The amount for this would go in the credit column. The formula for the Balance column is different on the first line. Note that formulas are started with the “=” sign. This is what tells a spreadsheet that your are entering a formula and it should display the result. The formula is simply:

=F2

beginning balance formula

This will display the value that you entered in the credit column.  Note that the screenshots are from an iPad.  Depending on what device you are using, the screen may look a bit different.

Use styles to display dollars and cents

As shown in the image above, numbers do not always display the way that you would like them to.  When working with currency, it is typically helpful to see dollars and cents even when your amounts are in whole dollars.  When you have a larger amount of transactions, having all of the number amounts display the same makes them easier to look through.  If you don’t format these numbers, 1,000.01 will look significantly larger than 1000 when you are glancing at it.  If they both display as 1,000.00 and 1,000.01, you will be able to tell the difference faster.  Also, a comma can be helpful when the numbers get larger but a dollar sign can be redundant.  If you are using Google Sheets, use the following steps to style the numbers. 

First, select all of the columns to which you would like to apply the styles. If you only select certain cells, you will have to redo the styles when you add more rows.

select columns for formatting

Click on formatting and choose cell formatting then number format.

number format


That should do it.  Now your amounts will be easier to scan through.

Entering the next transaction

Now enter your next transaction in row 3. Whether this is a debit or a credit, this next formula will corrrectly capture it and add or subtract it from your balance.  Note that the formula starts with an equals sign which is the universal technique for telling a spreadsheet that you are entering a formula instead of a value.

=G2-E3+E4

running formula for the rest of the cells

This formula is the only one that you will need from this point forward. You should copy this formula down this column for thirty lines or so, enough to give you some room to work without copying it again. Depending on what device you are using, there are multiple ways to copy a formula down. The most intuitive is copy and past the same way that you would with any other program. The easiest is to move the cursor to the lower right corner of the cell, until it changes to a different icon, then drag the outline down as far as you would like. When you copy formulas, the spreadsheet is smart enough to increment the cell references so they are always pointing at the appropriate line.  If you are using Sheets on a tablet, select the rows you want to copy.  Make sure you get a green dot on the rectangle that surrounds your selection as shown below.  This image is shown after the green dot appeared and the user dragged it down with their finger.

cells with values copied down by dragging rectangle with green square

 

When you are entering your descriptions, make them as long as you need to.  You can search through your descriptions to find things later so pack them with the keywords that you think will be helpful.  The next cell will just overlap the data but not erase it.  See the highlighted portion below illustrating how the text is overlapped but is still saved and searchable.

cell overlapping but not deleting a long description

See an example of this checkbook here as a shared Google Sheet. This spreadsheet is configured so that anyone can view it but not edit it.

docs share icon

You know have a simple, useful check register that you can access from anywhere that you have a web connection as Google Sheets and Apple Numbers can be used on mobile devices as well as laptops and desktops.

Science Gossip – Overview of Citizen Science Project to Classify Drawings

The people at Zooniverse have a long track record of creating citizen science projects. These projects harness the power of ordinary internet users looking to make a difference with their spare time.

Their latest creation, Science Gossip, wants users to tag pages from a Victorian Periodical.  You are presented with a simple choice, “Are there any illustrations on this page? Yes, No, Skip this page”. 

 box asking if any illustrations

You scroll up and down the page, and tell it yes or no.  Simple, right?  Well pretty much.  However, there are some finer points that are helpful to learn.  For example, where is the line between illustration and text?  For example, the very first page I saw when writing this contained formulas.

 picture of a handwritten formula

Is this an illustration?  Well, let’s click no the little question mark icon and find out.  It’s right above and to the right of the image.

question mark icon

I am then taken to a small but helpful help section.  There are four example catgories listed – Drawing/Painting/diagram, Chart/table, Photograph or Map.  I think I can safely conclude that it does not fit into one of these catgories, so I mark no and move on.

I clicked through a few more that did not have any images.  Then, I ran into this winner.

two photographs

Wohoo!  Step aside Darwin, here I come.  I then choose the type of illustration – photograph, and I get to draw a rectangle around each one.  I type the name of the photographer, give the pic a few keywords and voila!  The world is a better place because I did that.  At least I hope it is.

The point of the project is pretty straightforward.  You are helping historians by classifying the millions of pages that are available to them.  Imagine if they wanted to find out more about the dodo bird – where do they start?  If you tagged one while using this program, the historian would have quicker access to the information.  It is the rough equivalent of converting these paper pages to web pages by giving them the favorable characteristics that come with the internet – they become sortable, searchable and faster than paper.  

Read in the Science Gossip blog about Mary Anning, a woman whose discoveries were not all properly attributed to her, some of which are within the Science Gossip project.

Projects such as these give “ordinary” people the chance to contribute in ways that were not possible fifteen years ago.  Instead of using your free time binging on old Lost episodes on Netflix, now can be an amateur historian.  Better yet, you can do both at the same time.

Numbers – Sharing Spreadsheets in Apple’s iCloud Numbers

Apple now offers its spreadsheet program to anyone that has an iCloud account.  You can access the spreadsheets from most browsers which effectively opens up the program to Windows users.  You do not get the same functionality that you would from iWork for Mac, but you can access the same files and perform the most common tasks.

Apple’s Numbers spreadsheet has a sharing option that allows you to invite others to collaborate with you on a spreadsheet.  You can give them the ability to view or edit the spreadsheet and you can decide the level of privacy to afford it.

share spreadsheet pop up option

You can find this option by clicking on the sharing icon shown in the image above (the arrow inside the square).  However, tread carefully, as this can leave your file open to viewing by others as will be explained in this article.  At the time of the writing of this article, the sharing feature is only for the entire spreadsheet.  There are no options to specify sharing and editing permissions on a specific cell, range, or worksheet.  If you want to share only a portion of the spreadsheet, you will have to use another spreadsheet options such as Google’s Sheets which has more granular security features.

Video explanation

Once you choose to share the spreadsheet, you are presented with a few different options for how to share it.  This is where things get a little fuzzy.  If you are new to the concept of sharing documents over the web, it is important that you understand what is happening here.  

share warning

Before you share anything, your files are locked down in iCloud and only someone who knows you username and password can access them.  Hopefully that’s just you.  However, if you choose to share a file, you are opening up this particular file to other users on the web outside of your iCloud account.  Others will know be able to access the file even if they do not have an iCloud acount.

Letting someone see but not edit

If you want to grant a user permission to see the spreadsheet, but not edit it, you choose this option during the sharing process.

view only

However, there is a catch here.  This spreadsheet is secured from others by the long, random URL that you see above for the spreadsheet link.  This means that no one else will be able to stumble upon this spreadsheet becasuse it is near impossible to guess the link.  You should feel pretty comfortable that your spreadsheet is still private, but you should also understand what is making it private.  If anyone found this URL, they would be able to see the spreadsheet.  There is no login required for this option and no password has been specified.  If you shared this spreadsheet with multiple people, then decided that one of the peoople should not be able to see it anymore, you cannot remove just that particular user’s access.  If you still want it to be shared, you would have to add a password and control who gets the password.

Letting someone edit

 If you want to grant a user permission to use the spreadsheet with the same permissions that you have, specify “Allow Editing” during the sharing process shown in the picture above.  This will give anyone with the link the ability to edit the spreadsheet.

Password protect

This is Number’s method of allowing a user to share a spreadsheet while still keeping it confidential.  While other cloud spreadsheet programs allow you to share spreadsheets with specific people, Numbers gives you the ability only share your password with specific people.  This accomplishes the same thing, but gets you there differently.

password

Conclusion

The options described above offer enough flexibility to allow simple collaboratoin in Numbers.  As your spreadsheets get more complicated, you may encounter the needs to specify certain ranges or worksheets that you want to protect in different ways.  If all you want to do is share spreadsheets with others to allow them to see or edit, then Numbers has the ability to get this done.