Creating a Billing and Invoicing System In Numbers (2025)

Hi, this is Gary with MacMost.com. Let me show you how to build a simple invoicing system in Mac Numbers. 

Now a few years ago I did a video about creating an invoicing system in Numbers. It used some fairly complex formulas to grab data from on table and put it into another. This proved to be useful for much more than invoices. Over the years I have referred lots of people to that video. But now with Numbers 14.4 there is a new and simpler way to do this. 

So sticking with the same kind of example here's an invoicing system. We'll start off with one sheet that just has clients. Basically each client is assigned an ID number so you don't always have to type their name over and over again. Then you've got the name and address. So you could be, say, a graphic artist working for various people. I know I'm going to be asked about having multiple lines in the same cell and that is simply done by holding the Option Key and pressing return. Like that. So this is all just manually entered data. Every time a new client is added a new number is created for them and then their name and address are added to this table. 

The real data is in this second sheet here. So I've used this Plus button to create a second sheet and in this consultation sheet I've got a table here that's going to keep growing as I do work. Each line has a lot of manually entered data and a couple of functions. So every time a piece of work is done, like eight hours of work for this one client on this particular day, the client number is entered here, so you don't have to type the name, and then the date of the work, the number of hours, and the rate. You may have different rates for different kinds of work for instance. Then two columns are generated based on this data. The simplest one is here as Total and it is just a formula here that is the hours multiplied by the rate. So a really simple formula that saves you the work of having to multiply the numbers yourself and type it in. It's done automatically. As you enter new rows to this you can see the formula carries forward and all you need to do is enter these values and the new value for the total would appear here. 

Also the name here comes from a formula as well. It's a simple Lookup function. If you look here you can see I just used Lookup and then the number here that is in the Client Column. Then it looks up in the Client's table from column A the number and then gets as a result the name. So basically if I were to change this client's number here to 7 you can see how it picks the name from the Client's Table or client ID 7 instead of client ID 1. 

The last column here is also manually entered in and this is just a date of whenever this bill is paid. So if I were to get a payment in for this client I would apply it to the total here and instead of having a checkbox or putting paid or something it's more useful to put in a date. That way you can a little bit more information about it. The general idea is if there is the date here then that amount has been paid. If there is no date here then that amount is still outstanding.

By the way if you find these videos valuable consider joining the more than 2000 others that support MacMost at Patreon. You get exclusive content, course discounts, and more. You can read about it at macmost.com/patreon. 

So now we come to the Invoice. Now you can have an invoice like this and manually enter in all the data. You can go back to this table here and copy and paste or type in date, hours, rate, total. You can calculate the total on your own and that may be the old fashioned way to do that. You may even do it in a Pages document rather than in a Numbers document and then print out a nice invoice or send it as a PDF. But we're going to use formulas to actually create this automatically. All you need to do is enter in a client ID number and the rest will populate by itself. So as an example here under Client we want to have the name and their address. This is a simple Lookup. We're already looking up the names somewhere else so we're going to use a similar thing here. I'm going to press Equals to enter in a formula here and I want to use Lookup and I want to Lookup it a from a Client ID. I want to then go to the Client's table here. Look in column A and get the result from column B, like that. A very simple lookup. Then I can see that the result is the name of the client, based on the client ID, is there. The same thing for the address. I'll do Lookup and then I'll base it on the Client ID. Then I want to go to the Client's table and lookup, from column A the result this time from Column C to get the address, like that. Now I get the address. If I change the client number it will change the values here. 

Now comes the hard part. You want to populate this table. So you want to have a new table that is taking data from this table but where the client matches and they haven't paid yet. Let's start by focusing on just the client ID. Here on the invoice I have the client ID number there. Let's go and grab all of the rows here, just the date, hours, rate, and total, and if the client ID matches let's put them here. To do this we're going to use a new function. Going to press equals here and let's look up the function here. The function is one of these new billing functions that's only available starting with the Numbers 14.4. So we're going to look for Filter and here it is. I'm going to select it and should definitely read about it here before using it. I'm going to double click it and it's going to put it in here. There's the template. Now, first I want to get an array. The whole array of what it is we want data returned from. So that's going to be from the Consultations Table and it's not going to be this entire table. It's only these four columns. So I want to click in C at the top here and drag over to F. It's going to give me Consultations from column C to column F. 

Now, what do I want to test? Well, remember I'm testing the client ID. So it's column A. What sort of test do I want to do? Well, I'm going to see if it's equal to, and then I want to go back to the invoice here, and equal to this value. The client ID. So table A1, just like that. So if column A matches A1, in this case 7, it's going to take the rows from here. If empty it will give a value to use if it can't find any at all. I'm just going to use two quotes there to represent blank. Don't put anything. Now I'm going to click the button right there and I get my result. It picks out the five rows here, the date, hours, rate, and Total from the Consultations. So if I compare all the number 7's here, like this row and this row and these three rows here that's what I'm seeing now on the invoice. There's only a formula in A2 right here. You can see the formula. At the bottom it indicates that it is actually billing from A2 to D6, this entire area. In fact click on one of these cells it will tell me that using this formula, which is in A2, to populate itself. So there is really nothing in any of these cells except this first one. This first one is pushing everything here. 

The problems you may have here is if you don't have enough columns to fit the results. You don't have enough rows. It is going to give you an error. That's why I have included 15 rows. You may want to include 30 rows if you think that's a possibility. You want to make sure the number of columns matches. 

So, I've got the results here but unfortunately it's still falling short because the first row here, if I look, is actually paid. It has a date here. So I want to add an additional criterium to this. I'm going to go back to the formula and the way I compare with two things here is not as you would think using and. But instead multiplying them. This is going to be return a True or False. I want to have a second True and False multiplying them together basically means that they both have to be True for it to be True. Like one times one is one. But one times zero or  zero times one, that's a zero. So I'm going to surround this with parentheses and multiply, that is just Shift 8 to get an asterisk there, and then I want to have a second criterium right here. This one is going to be simply, Is Blank, a simple test. Then I want to make sure I have the right number of parentheses on the right. It kind of auto-corrects and eliminates one by accident but you want to make sure your parentheses match here. So, what is blank? Well, whether the paid column is blank. So G.  Is blank Consultations G and that is multiplied by if Consultation A matches the Client ID. So if the client ID is 7 and the paid column is blank the row will be shown. Sure enough if I do this now I see only four rows here and these four rows will match the four rows here where the client is number 7 and there is nothing in the paid column. So in other words this row and these three rows right here are what I'll now find here. If I simply change the client ID to say 5, it will recalculate everything and give me just two rows. Looking here I can see there's Client 5 but this is paid. There's another client 5 not paid and another client 5 not paid. Those are the two rows that are now included here. All done with this one formula in this one cell that populates the whole table. 

Let's finish this off by having a Footer row here. I've simply gone to Format Table and I've set one Footer row. So the Footer row isn't part of all the calculations for the column. So I can just do things like having sum of the entire column B. That will give me sum of the hours. I can do the same thing here equals sum of, and I click up here, D. So it will give me the sum of all of the amounts right here. I get the Total. I want the total due to also be here so I'll just simply refer to this cell. So a simple formula that just basically says give me the value that is shown in that last cell, the bottom right corner there. So now I have my total. Let's go to Text here and make this bold. So now I've got a nice Bold amount there. All I need to do now is put the client ID here and it instantly fills in the name, the address, puts only the rows that are due for that client, calculates the total and adds the total right here. I can go one step further and get rid of these blank rows pretty easily. By selecting this table and going to Organize, Filter, and then add a Filter and I can choose any one of the columns. I'll just use Dates since it is the first one and say I want the rule to be: if the cell is not blank, and you can see it now has rows 1, 2, 3, 4, 5, skips the rest of the rows until a Footer cell because they are blank. If I were to change this to Client 5 there's only two rows now that aren't blank. So this table now will expand or shrink based on how many rows are not blank. I can add other text boxes like that with terms and conditions and address to send the check to, and all of that if I want and do a simple Print now to print that sheet. 

Just to show you how easy it is to use Filter for other things I've created another sheet here and basically I just want kind of a monthly view to see how much work I've done in a given month. I put a simple table up here that is going to have Year-Month. So 2025-3 or March 2025 and the formula here that is only in this cell is to basically take the entire contents of Consultations, so everything here, and then take the Year of the Date column, append a little dash and then also append the month from the same Date column. In other words for here this would be 2025-2. Then is that equal to the value right here. Then put a blank here for if it is empty. Now it will populate this table here with values that match this. So if I change this, for instance, to 2 you can see I've got just the February one. If I change it to 4 I've got just the April ones. I can do calculations on this like maybe have a Footer column here that has a total number of hours and the total amount billed and all of that. 

So Numbers now includes a lot of these new Billing Functions. But just mastering the Filter function alone gives you a lot of power. You can download this Numbers spreadsheet if you like from this post at macmost.com. I hope you find this useful. Thanks for watching.