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: 17 Responses to “Creating a Billing and Invoicing System In Numbers”

    Craig Miller
    1 month 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.

    1 month 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
    1 month ago

    Would like to see more of the same.

    Craig Miller
    1 month ago

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

    Daniela
    1 month ago

    Love this, great insight in those functions.Thank you

    Tim A
    1 month 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?

    1 month 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
    1 month ago

    Great post!

    Vikram
    4 weeks ago

    Can we put passwords on various sections of a spreadsheet

    4 weeks 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
    4 weeks 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.

    4 weeks 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
    4 weeks ago

    Ok, thanks Gary

    Steve Coltman
    1 week 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!!

    1 week ago

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

    Steve Coltman
    1 week 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!

    1 week 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.

Leave a New Comment Related to "Creating a Billing and Invoicing System In Numbers"

:
:
:
0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)