4/18/19
7:00 am

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.

You can also watch this video at YouTube (but with ads).

Video Transcript: 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.

Download the sample numbers file.

Comments: 22 Responses to “Creating a Billing and Invoicing System In Numbers”

Craig Miller

6 years ago

Hey, Gary.
Great post. Would it also be possible to create a filter for the Client ID field in the Invoice sheet so only the clients with open items can be selected from a drop down list?
I am coming from an excel background and just getting used to Numbers.

Gary Rosenzweig

6 years ago

Craig: No. The Client ID cell is just a cell. You will be able to type anything you want in it. There's no function to "restrict" it in some way. But I don't see that as a problem... what's the issue?

Matt Chaplin

6 years ago

Would like to see more of the same.

Craig Miller

6 years ago

Thanks Gary. No issue, it was just a thought.
Keep up the great work!

Daniela

6 years ago

Love this, great insight in those functions.Thank you

Tim A

6 years ago

While this demonstrates insightful ways to use Numbers, wouldn't this be a more a suitable and straightforward task for FileMaker or other database app?

Gary Rosenzweig

6 years ago

Tim: I agree that things like this are best done with dedicated billing software (SAS mostly now) or database software. But a lot of people don't want to pay what those solutions cost. And if you do a little consulting on the side, it is nice to have a free option.

Will

6 years ago

Great post!

Vikram

6 years ago

Can we put passwords on various sections of a spreadsheet

Gary Rosenzweig

6 years ago

Vikram: No. You can use the Lock command to lock cells, if you are afraid of people accidentally changing a value or formula. But they either have access to the spreadsheet or they don't.

Vikram

6 years ago

Ok thanks Gary. How do I associate a percentile of growth chart of a child to his data of height and weight so that when I enter the data it gives me the percentile of ht and wt for that particular age.

Gary Rosenzweig

6 years ago

Vikram: I'm not familiar with that type of spreadsheet. Are you comparing a ratio to a list of numbers? If so, LOOKUP, or VLOOKUP is probably what you want. If you know of an example I could look at, you may want to ask it at the forum (link above in the nav bar) and I could answer it there -- I want the comments here to stay more on-topic.

Vikram

6 years ago

Ok, thanks Gary

Steve Coltman

6 years ago

Hi Gary. I've followed this tutorial real close but I'm struggling with the Due Flag column when it comes to adding it to the countif formula. I keep getting an error message saying I can't add the column. I've downloaded your example but can't see where I'm going wrong!! Can you help coz I like this invoice method using Numbers. Thanks. P.S. Love the Numbers tutorials. The more the better!!

Gary Rosenzweig

6 years ago

Steve: What are you doing, exactly, to add the column to the table? And what, exactly, is the error message?

Steve Coltman

6 years ago

Hi Gary. I'm just trying to copy the formula you use to look for the blank Paid cell in the IF AND statement and I'm trying to add the DUE FLAG column in the COUNTIF part of the formula. Im getting an error message saying I can't use the cells that are already being used in a calculation (or words to that effect)!! On most of your videos you build the formulas as you describe them, which I find very easy to follow! Maybe on this occasion I'm just missing something simple!

Gary Rosenzweig

6 years ago

Steve: I would just compare what you are doing to what I have step-by-step. It sounds like there are differences between your spreadsheet and mine, but you are trying to use the same exact formulas. COUNTIF cannot include a reference to itself, for example.

Diane Sands

5 years ago

This demo was GREAT / OUTSTANDING / OVER THE TOP! Thanks Gary, you are brilliant!

UMESH KUMAR

5 years ago

Can I use ₹ symbol instead of $ in my numbers invoices?

Gary Rosenzweig

5 years ago

Umesh: Of course. Just choose Currency as the data format and then under that switch from US Dollar to Indian Rupee.

D. Sands

5 years ago

Issue: Header Column
Why do you use header columns (instead of regular columns) for the client ID in the CONSULTATIONS and CLIENTS tables?

Issue: absolute reference in LOOKUP formula
Why does the LOOKUP formula on the the Consultations sheet (ID cell) contain an absolute row reference, but on the Invoice sheet, the LOOKUP formula in the name cell does not contain an absolute reference?

Gary Rosenzweig

5 years ago

D. Sands: When you use a header column for a critical but of data, like ID, it will remain as you scroll horizontally in your table. Not something I show here as this video is about something else, but it is useful.
I can't remember why I included an absolute column reference in the Consultations sheet Name column. It isn't needed in the Name cell of the Invoice as there is only one cell there. Absolute references are useful if you copy/paste/duplicate a formula to other rows and columns but don't want the reference to change. With one cell in the Name table in the Invoice, there is no chance for that. See https://macmost.com/relative-and-absolute-cell-references-in-numbers.html

Comments Closed.

Creating a Billing and Invoicing System In Numbers

Comments: 22 Responses to “Creating a Billing and Invoicing System In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information