When it comes to invoicing, small business owners are often on the look out for ways of saving time. One of the ways is to create your own invoices, invoice lists and reports using Excel. I currently do this for a few of my clients and usually create a list containing all the information taken from their invoices, this means my client has a fully comprehensive list in the one place without having to open individual invoices to find the information. Below are some of my tips on what to include in this list…
Column Suggestions
These are headings which I would use in my invoice list (click image to enlarge)…
They are self-explanatory, but I would always try to include a formula to make it as automated as possible. For example, if the vat amount is always going to be 23%, then have a formula in that column to calculate 23% of the Net Amount column. Another example could be if your payment terms are always 7 days, then have a formula in the Due Date column to calculate 7 days from the Invoice Date column.
Tables
If you have version 2007 or later of Excel, make use of their table feature. It keeps everything formatted how you want it, and is easy to increase/decrease in size by dragging the very bottom right corner of the table. You can add in a Total Row to automatically keep totals of individual columns, you can create pivot tables to summarise the data with a few clicks of the mouse, and you can quickly & easily change the design of the table to keep in with your branding/company colours etc.
Conditional Formatting
I tend to use conditional formatting within a lot of my lists. One way I make use of it in an invoice list is to apply a condition in the Due Date column that will turn the cell red if the due date is past today’s date; another way is to highlight invoices which are above or below a certain amount. My next tutorial will show you how to do this 🙂
Filter
I ALWAYS use filters on every list I make, regardless of whether it’s in a table or not! If you use the table feature in Excel then the filter is automatically applied. (If you use an earlier version of Excel, then here is a previous tutorial on how to insert autofilters) Filters are a great way of… well, filtering… the data so you only see the information you want to see. For example, you could filter it by name, amounts, dates etc. More in-depth filtering could be that you only show invoices raised in the last month, or invoice amounts that are less than €500, for example. That’s the beauty of autofilter – you decide how much or how little information is shown.
Dates
This last tip is a bit obvious but is one that I see constantly! If you have a column headed Invoice Paid or something to that effect – what do you put? Yes? NO! Don’t just type in yes, type in the actual date that payment was made. There’s no point just saying Yes if you then have to check through your statements to find out when it was paid. Remember, this list is to make it as easy as possible for you to obtain ALL information when needed. If a client calls you asking for information regarding their invoices, all you have to do is filter the spreadsheet by their name & everything will be there… amounts, dates and any notes made.
I hope this helps save you a bit of time when it comes to your invoicing lists, but if you want me to cover something specific, just leave a comment below & I’ll be glad to help 🙂