How to Create Invoices Using Microsoft Excel 2013
Creating effective, powerful invoices has long been a primary function of Excel. In Excel 2013, it is even easier to move information across tables and even workbooks, saving time and effort.
Instead of having to find the customer information from another worksheet and type it in, you can tell Excel to automatically dig this up and insert it into your current workbook. Add information to one cell and let Excel find it elsewhere.
Easier, more efficient, and all around more powerful for creating business documents, Excel 2013 is a highly useful improvement over its predecessors.
Watch the free video here, transcripts for the entire video follow:
Need to master Microsoft Excel 2013? Get 19 hours of Microsoft Excel 2013 training – click here.
Video transcripts:
Hello again and welcome back to our course on Excel 2013. By now we’ve covered quite a lot of the basics of Microsoft Excel. You hopefully have done some of the exercises that I provided and you’re starting to feel more confident in your use of Excel. One rather unfortunate thing about Microsoft Excel is that it is a big, very powerful, and quite complex product. And if you’re still quite new to it, there are many, many more things to learn. And I think one of the dangers is trying to take in too much at once and there’s always a good case for getting a bit of practice with what we’ve covered already. So what I’m starting in this section is a small case study to show you how we can use some of the things you’ve already learned together with some new or perhaps more detailed things related to Functions to assist in the production of a business document.
Now the business document we’re going to produce is an invoice for our plumbing supplies company. You’ve seen it already earlier on in the course, but now we’re going to start to look at making it not only more flexible and more powerful but also something that could be more likely to be used in a business environment.
So first of all, let me explain a little bit about this invoice. We have the word Invoice at the top. There’s our name, Ocean Plumbing. Our address is 2324 Main St. South Park. And this particular invoice is going to Zak Stephens of West Beach Bathrooms. There’s his address. It’s going by courier. The order date is that date. The order number is that. The purchase order number from the customer is that. Contact department there is purchasing and the account number for this customer, 2973, terms are 30 days. Now if I were regularly invoicing West Beach Bathrooms, I’d have all of this information in a database. And for the purposes of this exercise, my database is one of the sheets in this workbook. Now normally I wouldn’t set things up like that but in order to demonstrate how to do this, let’s just go along with my approach for the moment.
So let’s assume that the worksheet called Customers here is actually my database of customers. Now I’ve only put four on there at the moment. I’d hopefully have more than four customers, but there you are, account number 2973, West Beach Bathrooms, Zak Stephens is the contact. Their terms are 30 days. They do get a discount and there is their address.
Now if I were preparing an invoice for West Beach Bathrooms in reality, I wouldn’t type in all of these individual lines and pieces of information for West Beach every time I do an invoice. I might copy an old invoice but it will be much better if I could automatically fill in these cells in Excel. And what I’m going to show you how to do now is how to use one of the cells to help to fill in many of the other cells.
Now first of all, what is it about this customer that helps us to identify their information? And in the case of a customer of a company it will usually be an account number. So if I have the account number, in this case 2973, then I can use that account number to find the other information. Now let me just flip back to the Customer Sheet again. On the Customer Sheet the account number is in the first column. What I’d like to be able to do, go back to the invoice, is if I instead of putting 2973 here put say 2972, it would bring up the other information for the other customer. So there was a customer 2972, that’s a company called Bathtime. There’s my contact there. I’d like to automatically bring up that customer’s details and create an invoice for them if I put their account number on here. Let me just put that back to 2073 and then we’ll see how we do that.
Now the first field, the first cell that I’m going to apply this principle to is that one, D5. It’s currently the name of my contact at the customer company. It’s currently got typed into it Zak Stephens, but instead of Zak Stephens I’m going to put in there a formula. And the formula will actually be a function. Click on equals, go to Formulas, and the sort of function it’s going to be is a Lookup Function. Now Lookup and Reference Functions are ones that get information basically from elsewhere. And the Lookup Function we’re going to use here is one called VLOOKUP. There are two direct functions. There’s a VLOOKUP function and an HLOOKUP function. We’re going to go for VLOOKUP. Just read the screen tip there. Looks for a value in the left most column of a table and then returns a value in the same row from the column you specify. By default, the table must be sorted in ascending order. Now we are going to be using account number, therefore one of the requirements is that this table of account numbers must be in ascending order. That’s pretty straightforward because we can always sort a table if we need to.
So it’s the VLOOKUP function that we want. Now when you choose VLOOKUP in Excel 2013, it brings up this very convenient dialog and we can use the dialog to fill in the rest of this. Now, first of all, what’s the value we’re going to look up? Now the value we’re going to look up is the account number which is in C17. If I click in there, where is it going to find it? Now where it’s going to find it is in the Customers Sheet. So if I click on the Customer Sheet and select from the beginning of the table of customer information down to the end, it’s a very small amount of information at the moment. But if I select that, it gives me the table array. What it’s saying is I want you to look up what’s in C17 in the table. It doesn’t actually have to be setup as a table. It can just be a range. The table, customers, that’s on the Customers Sheet, A2 to G5. And as you can see from the marching ants, A2 to G5 covers all of my customers. Now when VLOOKUP does the look up, it always looks up in Column 1. It will look for whatever’s in C17 on the Invoice Sheet in Column 1, so it will only look in the account numbers. But the next question is which column will the answer be in? Here I want in this particular cell the customer contact name and the contact name is in Column C. So which index number that’s 1, 2, 3. That is Column 3, click on OK, and see what I get. I get Zak Stephens. Zak Stephens is, that’s the value but the formula is equals VLOOKUP. C17, that is what do you want to look up? I want to look up the account number. There it is, 2973. Where do I look it up? You look it up in customers bang or exclamation mark, A2:G5; so it’s on the Customer Sheet and it’s in the range A2 to G5. Which column number? Column 3.
So far, so good. Let’s see if that works. Let me click in the C17 field and let me now change that from 2973 to 2972, tick, and what I get now is Amit Nehraj in the contact name field. So it’s automatically and let’s do another one, 2971, tick. It’s automatically changing the name here when I change the account number there. Now you can obviously see what the problem here is and that is that we’re sorting out the contact names automatically based on the account number but we’re not doing everything else. And that’s where there’s a little job for you to do on some of the rest of this invoice.
So what I want to do now is to just take another look at that Customers Sheet again. Note the columns there: account numbers are in Column 1, company name in Column 2, contact Column 3, terms of business in 4, discount in 5. The first two lines of the address in Column 6 and 7 and there could, of course, be many other columns with other information about this particular customer. But I’m only going to use these at the moment. And we’re going to now look at the terms of business column.
Now let’s suppose that the next thing I’m going to do is to put a similar Lookup in here. One option is to literally copy that cell. You might look at it and think, well, I’m copying Dorothy Walinski. I don’t want it to say Dorothy Walinski but don’t worry about that at the moment. Let’s do a Control-C to copy it and let’s do a Control-V to paste it and, of course, we get an error. Now, hopefully if you look at what’s in the formula bar, you can see what the error is. Because the error is that with this Lookup, when I’ve moved the Lookup to a different cell, it’s basically used its referencing to change not only the cell reference for the item to look up, the account number in this case, but also the range on the Customer Sheet for the information about customers. So let me undo that and let me go back to the original formula for Dorothy and you should know that what we need to do there is to put in absolute references. Now we can get over it to some extent in terms of the Customer Sheet by referencing it as a table, but let’s put in the absolute references. Just tick that to make sure that Dorothy still works. Dorothy still works. Let’s do a Copy. Let’s Paste that in here. Well that’s better because we now get Dorothy in there. But you should know why we get Dorothy in there and that is because if I tick in this particular cell, so I’m now in E18, the terms of reference are not in Column 3, they’re in Column 4. So if I change that 3 to a 4 and tick and I get 7 days for Dorothy Walinski. Now bear in mind this is 7 days for account number 2971. If I go back to customers 2971 terms are 7 days.
So what we have here is Example 13. We’ve just effectively automated the terms and the contact name. What I’d like you to do is to do the same for the name of the client company and for the first two lines of their address. Now obviously this will still be a rather strange invoice because part of it, such as the phone numbers, won’t change. But if you were feeling really, really keen and enthusiastic and if you’ve got the time, you could maybe extend that table out to include telephone numbers or anything else indeed that should be specific to individual customers. You’d, of course, have to change your VLOOKUPs to use the bigger table. But all I really need you to do is to have a go at the name of the company and the two lines of the address, address line 1 and address line 2. And then you can test it as here just by changing the client number. If I put this back to 2973 watch what happens in D5 and E18. When I change that, I get the contact name and the terms change back again. So my answer to that question is Example 14 and I’ll see you in the next section.
