Automated Invoice in Google Sheets for Accounting and Tax

Automated invoice in google sheets for Accounting and Tax

A unique invoice number is essential, not just a  formality. It's crucial for keeping your record   straight, ensuring you get paid on time and  avoiding any mixups with clients. Having a   structure to your invoice can also help with  tracking your invoices, identifying specific   transactions and handling your VAT returns. I'm  going to start simple and make it more complex,   including client information and dates. By the end  of this tutorial, you'll be comfortable creating   a un invoice number that includes all of the  elements that work best for you. A super quick   recap, I have my invoice information here, which  is linked to my invoice here. Control P for print   preview. Let's create some invoice numbers. Start  from scratch. Each row is uniquely defined by its   row number. To create a unique invoice we can  just use the row number. Formula is ROW. Select   any cell on the same row, drag that down, double  click here. Now you've got unique invoice numbers.   But it's not easy to tell it's an invoice number.  So why don't we add something at the beginning   that says this is an invoice number. We can add  anything we like to the beginning I often use   in INV. Make sure you put it in speech marks.  And then you can put an and symbol and drag   that down. The alternative to the and symbol  is using concatenate and separating with a   comma. Same result there's no real benefits apart  from sounding clever. And I just like the word   concatenate but the disadvantage is a lot more  keystrokes. Let's go back to the and symbol and we   can always say Ampersand to still sound clever.  Control Z to undo the last few steps. Next, I   don't like the fact that that the invoice numbers  have a different length. I want a consistent   number of digits. So what we're going to do now is  add some leading zeros. We do this using the TEXT   function, which lets us tell Google what format  we want. We use TEXT, what we want formatted,   then in speech marks the format. I want to have  four or five zeros. Now we'll always have unique   invoice numbers up to 9,999. Within this text  function we can even put the INV Dash and start   with a different digit. If we want to so it sound  like we've got a lot more invoices and we're a   lot more experienced than we really are. There  you go. I don't have many clients I just have   a few big clients. So I add the clients initials  the date to the invoice number the date makes it   easier for when I'm doing my vat returns. I know  which months are in that quarter and the client   initials mean I instantly know who's paid me.  And I do love a quick payer! So let's get the   client's name. First here's our client names. I'm  just going to take the first letter and just for   ease I'm going to take the last letter, for now.  To take the first letter we start from the left   and we use the function LEFT. We tell it company  name and how many digits we want. We only want   one digit. Just to make it easier, for now I'm  going to take the last letter. Moving in from the   right we're going to use RIGHT. Select the company  name and one digit again. To put this in capital   letters use the uppercase function UPPER. Next  on this invoice I'm going to add the date. Use   that function TEXT that we saw earlier. This time  we're going to take the date, which is cell B2,   and the format we can use date formats. With a  date format, YY is year MM is month DD date. And   this means that they are just two digits. So two  digits for the year, the month and the date. There   you go. Year is 2024 fifth month is May 25th. You  can add a hyphen if you want to separate them,   you can also add things like slashes. If  you want the full year you do four Y's.   If you want the month written three letters, it's  three M's. Let me drag that down so you can see   that it's always three letters. So June is three  letters and if you want the full month written out   you can do four letters. However I prefer mine to  be almost like a secret hidden code. I'm going to   go back to what we had before, which is YY MM DD  with a little Dash. Now we're going to go back,   so it's actually initials rather than the  first letter and last letter. As you can see,   inc has got a dot at the end. This gives us a bit  of a weird invoice number. Instead of using right   we're not going from the left or the right we want  a letter that's in the middle. The function is   called MID. Mid needs a bit more information than  right which just needed the string and the number   of characters mid needs the string the starting  position and the number of characters. The string   is still the company name and it's going to be one  character long. At the moment it's starting from   the first letter but actually it needs to start  from just after the space. That space is always   at a different place so we need to find the space.  We're going to replace this one with FIND. We need   to tell it that it's looking for a space and where  it's looking. It's looking at D2 again. Here it's   found the space but actually we want the letter  after the space. Find the space then add one. So   it goes along one. I'm going to add the row number  in the background. You can mix and match how it   suits you for example using date and row number  or customer number and row number. You probably   won't want it more complicated than name date  row number. Now we want to make sure that we can   select the invoices in here. Let me take away this  data validation and start again. To do the data   validation, we go to data, data validation, add  rule. Drop down from a range. Click here, equals,   go to your database. Sometimes you have to click  somewhere else first. Select the whole of column   A. Advanced options. Let's have an arrow. Done.  Now you can select your invoices. Scroll all the   way to the bottom for your last one. Everything  else updates automatically. The other thing   that you might want to do is tell people to use  your invoice number when they are paying. At the   bottom, after you've got your payment details, you  can put in... equals something like.. When making   payment, please use the reference number space and  then put in the invoice number here, so that this   always changes. We can make this a little bit  longer so you can see it. We got a long invoice   number now. When we've got QB Queen Bee Creations,  this will give them the invoice number that we   use, which makes it easier when we're reconciling  our accounts. That's it for today's tutorial hope   you found it useful. If you did, subscribe  for more tips on automating that admin. Let   me know what you want help with next. And maybe  watch another video that is now being suggested!