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.



Download the sample numbers file.

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

    Craig Miller
    5 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.

    5 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
    5 years ago

    Would like to see more of the same.

    Craig Miller
    5 years ago

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

    Daniela
    5 years ago

    Love this, great insight in those functions.Thank you

    Tim A
    5 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?

    5 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
    5 years ago

    Great post!

    Vikram
    5 years ago

    Can we put passwords on various sections of a spreadsheet

    5 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
    5 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.

    5 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
    5 years ago

    Ok, thanks Gary

    Steve Coltman
    5 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!!

    5 years ago

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

    Steve Coltman
    5 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!

    5 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?

    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?

    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.