Creating a Billing and Invoicing System In Numbers

When building Numbers spreadsheets it is important to remember that a row in a table is the equivalent to a record in a database. To build a billing system, you can put clients in one table and invoice items such as billable hours in another table. Then you can build a sheet with tables that use functions to populate an invoice. The requires some advanced functions like LOOKUP, INDEX, IF, AND, COUNTIF and more. But once you are done, you can easily add more records and create invoices.



Video Transcript / CaptionsCLICK TO EXPAND
Closed captioning for this video is available on YouTube: Creating a Billing and Invoicing System In Numbers.
Hi, this is Gary with MacMost.com. Let me show you how to create an invoicing system using Numbers. So I sometimes get questions from people using Numbers and they're trying to create invoices. What they'll do is create an invoice on each sheet. So they create a nice looking invoice and then they'll duplicate it and have different sheets in the same document or even different documents. The problem is you might as well just be using Pages or something to do that as you can't really do much with the data. The correct way to use a spreadsheet is to put records in rows where each row is a record for something like say billable hours. Then if you want to create invoices have a single invoice that pulls data from that.

So here's what such an invoice may look like. But I haven't entered any of this data except for this client ID. Watch what happens if I change the client ID. I'm going to change it to client 5. If I do that notice the name of the client changes, these records change, the total changes, and I have an entirely new invoice. The way I've created that is by creating Tables using records as rows.

So I actually have two sheets. One is a sheet with clients in it and it's just client ID's and names. The other is a list of records of billable hours. That's the only table that I need to create this invoice. So my first sheet here shows clients. This is a table where I've entered in the data manually. It's the client ID, just a number, and a name. So it's all just entered in. I can add more to the list as I get new clients.

Now Consultations is a sheet here that actually has a record on each row. The record is a client number, name, and information about that set of billable hours. Some of these are entered in manually. I enter in the client number, put a date, the number of hours, and the rate. Now the total, as you can guess, is calculated using a formula. So that's just the number of hours times the rate. Simple enough. The name is actually looked up automatically. So it's using the Lookup function and then it will lookup from this number here, the client number that's entered in, and it will go to this table and lookup the ID in this column and grab the name out of that column. That's how that works.

So all I need to do is say if I wanted to change this client I go to number 3 and you can see it changes the name automatically. So I can start a new record without having to type the entire client's name. Just their ID. In addition I've got this paid field here. I actually manually entered that in. So somebody pays the invoice that has these hours on it. I will type in the date they paid. Which is a good piece of information to have and it also gives me a record of the fact that they paid this line here. So how does this invoice work?

Well, this name here works the same way as the name on the Consultations sheet does. It just looks it up. It looks up the client ID from this number here and grabs the ID from this column and then the name from this column and puts it in the invoice. So that's how that works. This is a lot trickier. So I'm going to start by doing something special in the Consultations table. You may have noticed there's another column in there. I'm going to show you what that is and how it works.

This Due Flag, just a name I came up with, has numbers in it, a bunch of zeros, and you can see the zeros are always when something has been paid. Then you can see a couple of numbers here, there's 1 and 2. What these are indicating is that there is an unpaid record here. So this is client 5 and client 5 matches the client ID here. So client 5 and they have a blank space here under paid. So the formula for this is looking at two things. First is the client ID, this number here, equal to this number here. If it is, so far so good. We're doing an and statement so we have two conditions. The other condition is is this cell here blank. If it is that means we have an unpaid line here. What value are we going to put there. If this is true then we're going to put the number 1 there. But not just the number 1. We're also going to count all of the previous cells in this row. Anything that is greater than zero, right here, anything that is greater than zero is going to count as 1 and we're going to add that together. So 1 plus all the previous ones greater than zero. There's none.

But this one is 1 plus all the previous ones greater than zero there's one so it's a 2. If I were to go here and change the client ID to client 7 and I go back to Consultations you can see I've got a 1 and then a 2 and then 3 and then a 4. So it's just counting up for each one of the rows that has client 7 and a blank, 7 and a blank, etc. So I've got handy stuff in there. I can hide this column if I want to because I don't really need to actually manually use it. It will all happen automatically.

So what's happening with the invoice. It's obviously grabbing the rows where there are numbers there in that special column. So you're not seeing everything here in this table. You can see it starts with column B. I'm going to go ahead and Unhide all columns and you can see what's here. You've got Row A. Row A has 13, 16, 17, and 18 which correspond to the rows that have numbers in it, 13, 16, 17, 18. Okay. How's it doing that? Well, it's using the Match function. It's matching the row number, in this case row 2 minus one so it'll be one. I have to minus one because row 1 is actually the Header here. Then it's looking in this column here, the Due Flag column, and it's also saying, hey if it's an error just use a zero instead because the Match function will return an error if it doesn't find it. I want it to return to zero if it doesn't find it.

So basically it's saying the match for 2 minus 1 is 13. 3 minus 1 is 16. 4 minus 1 is 17, and 5 minus 1 is 18. So they correspond to 1, 2, 3, 4 which corresponds to 1, 2, 3, 4. So this entire column here, its only purpose is to get the row numbers for those records where there are unpaid hours. Now each one of these is simply grabbing the index from this column that matches the value in this row. So the date column row 13. Then the date column row 16. It is using if in saying look if it's greater than zero, this number here is greater than zero, then do it. Otherwise put blank in there. Why? Well because this table here is actually much bigger than 4 different records. If I go to Organize and I turn on Filters you can see I've got ten spaces in there. Zeros are saying, hey I don't have anything here. I've got 1, 2, 3, 4 records and then there's no fifth record, no sixth record, no seventh record. So it's saying zero. So then this here has the same formula in it but it's doing a blank because it's a zero other there. All of these are doing a blank.

So this one, the hours, is looking for the hours column that matches 13. This one is looking for the rate column that matches 13 and the total column that matches 13. So I get four filled in rows and six blank rows and thanks to the Filter all of the blank rows are gone and missing. I don't want them. Then I'm using a simple sum formula for the hours there. Nothing special about that. A sum formula for the Total. Nothing very special about that. This is just saying take the value from here and also stick it there. So we have in total two places. There's the invoice.

The great thing is I can pick another client ID. It will pick up rows 12 and 13 instead of the 13, 15 or whatever those rows were, and give me new blank ones. If I pick a client that has no outstanding hours then I get something that looks like this and I would never, of course, send an invoice. So I can go ahead and use this for any number and it will give me the invoice values. I can get rid of this row here by Hiding it, or the column by hiding it. Right there. So it's nice and neat. Now I can basically add items here. I can add new clients here. Anytime I want to make an invoice all I do is enter a number there. Now I can Print it or save it as a PDF to generate this invoice.

Obviously I'm showing something really specific there but hopefully you get the general idea and I'll make this entire Numbers document available for you to download at this post at macmost.com.

Related Posts:
How Do I Save a Finished Working Table In Numbers for Other Users?, Updates To Pages, Numbers and Keynote Bring a Few New Features, End-Of-Year MacMost Course Sale!, Creating Pie Charts In Numbers With an Other Slice

Download the sample numbers file.