Get the Ultimate QuickBooks Invoice Template in Excel for Technical Support

Quickbooks invoice template excel for Technical Support

hi my name is Hector Garcia and I want to show you how to import invoices into QuickBooks from a spreadsheet uh obviously a spreadsheet like Excel or Google Sheets or maybe any other spreadsheet type of software that you may have it's going to work with this so uh pretty much how to import invoices into QuickBooks from Excel now there are several scenarios here um you may be importing multiple invoices that have uh single line items that's going to be one scenario which we'll show you or maybe you're you're U importing a single invoice that has multiple line items or maybe multiple invoices that contain a mixture between multiple line items and single line items the most important thing is this this first option here uh multiple line it sorry multiple invoices with single line items this first option here can be used with an internal tool called batch enter so we're going to start with this one first so we're going to start by talking about the batch enter uh transactions utility now this is only available in a QuickBooks accountant or QuickBooks Enterprise so if you're working with QuickBooks Pro QuickBooks Premiere QuickBooks Online none of this stuff is going to work what I'm about to show you only works with QuickBooks accountant or QuickBooks Enterprise uh um later on I'll discuss uh what if you have QuickBooks Pro QuickBooks Premiere or if you have QuickBooks online I'll discuss those options because you're going to need third party tools for that that but with the built-in tool we're going to be able to do multiple invoices with a single line items so let me show you that so as I mentioned if you're working with a QuickBooks uh desktop Edition either accountant Edition or Enterprise and the way you know is up here in the top um menu bar it would say QuickBooks accountant or QuickBooks Enterprise that's how you know if you're not 100% sure if you can't see it up there just hit control one on your keyboard uh and control one your keyboard uh will give you up here where it says product it will say QuickBooks and then after it it will say Pro Premiere accountant Enterprise again if you're working with accountant or Enterprise only uh versions 2014 and above this is going to work so we're going to click on the accountant menu and then click on batch enter transactions if you're working with Enterprise it may be under the Company menu somewhere down here but in my addition it's under accountant menu so I'm going to click on batch enter transactions and let's make sure that we understand this concept the batch enter allows me to copy and paste data from Excel spreadsheets into uh five specific transaction types it can either be checks deposits credit cards bills or invoices so we're going to go ahead and select invoices here and then the setup of the spreadsheet inside QuickBooks needs to match my spreadsheet uh where I have my invoice data so let me switch over to my spreadsheet here and show you so this is a good example here this is a spread sheet that contains multiple invoices with single line items now what do I mean by single line items it means that every single line item represents a different invoice multiple line items would be like this uh like I have three line items all going to the same invoice hitting different individual uh products or services that I'm selling so if I need to import multiple line items this is not going to work we have to use a third party tool for that that comes later on in the video but for the people that need to import single line items or don't need any line line items just need to bring uh the total amount of the invoice this is actually perfect okay so I got I got four lines here each representing a different invoice number and we also got to take a look at the configuration of these uh spreadsheet so we got the uh the transaction number first then we got date then we got the customer name then we have the terms then we have the item then we have description then we have quantity then we have rate and then we have total so we're going to make sure that our QuickBooks importable spreadsheet on batch enter which is this guy right here needs to look the same so again let's just double check we have number date name terms let's start with that I'm going to click on the customize button here on the top right and then I'm going to make sure that number is my first one so I'm going to move that one up and the next one was date and the third one was name customer job is name so I think we're good to go with that so we we got date number date name then we're going to do terms item and description so let's look for terms over here there's terms and then we have items so let me move this one up one two and then we have description afterwards and then we'll go back into that spreadsheet here and then we got quantity rate and total perfect so now we have to make sure that we move up quantity and then rate oops there's rate quantity and then there's the total okay and at the end we have amount I think this will work let's just double check that this is going to work so we got all these names match our spreadsheet we're going to hit okay so now we're going to see sort of the QuickBooks batch enter spreadsheet look very similar to a QuickBook spreadsheet we're going to go ahead and copy that information just the cells that contain the data that I need so if you got cells with that any data that you don't need go ahead and delete them or move them towards the end because we can't copy and paste any any extra information in there so you have to make sure that you you study up on on the columns are available and these are the only amount of columns that you're going to be importing so only keep in mind going into the batch enter tool click on invoices credit memos click on customize columns take a look at your options right because beyond that you're not going to be able to import any additional data so you keep that in mind perfect so we're going to go ahead and go back into our spreadsheet copy that and then go back into QuickBooks and then we're going to go ahead and paste and what you will notice is it's going to go ahead and paste all the individual information anything in red means information that it didn't accept so I'm going show you a couple of scenarios why it didn't accept it for example customer a didn't accept it I'm just going to click on it and then click back out because customer a is not in my customer list let me hit on cancel show you what I mean by that so if I go into my customer center my customer a is not here so QuickBooks is going to warn me to say hey wait a second I can't import this one if the customer doesn't exist so make sure that if the customer is supposed to exist that this is spelled correctly if not you're going to have to go in there and create each customer so we can do it from here we can just select customer a hit the down arrow and then I'll say would you like me to Quick ad then I'll press enter and I hit the down arrow again so it accepted customer AK that that was already on my database I'm going to hit the down arrow again and then hit quick app and then down arrow again and then quick ad so I can actually use the the the workflow for this work now look at terms so if I look at my actual terms see what I had available was net 30 see it's not going to guess because it says 30 days here that is net 30 here so you have two choices one I can create my terms for net 30 so I could go to add new and call this one called uh 30 days and I can configure this one to work that way so that's one of the options I I have I can hit okay and I'll just accept them or the other thing I could have done is make sure my spreadsheet had the correct information so this one should have had you know maybe net 30 which is actually the correct um terminology that I originally had in QuickBooks maybe I should have fix my spreadsheet before and then I could just basically just copy and paste just one column if I wanted to that's actually kind of a real neat thing you can actually just copy and paste one column at a time even if you wanted to and then at the very end the rate didn't work um because QuickBooks will not accept uh dollar signs so you see these dollar signs here it won't accept them so you have to make sure that you remove the dollar signs from them by just keep clicking here on number so just make Excel do regular numbers and then we can just copy and paste and this is smart enough to just copy and paste the columns that you're actually using so I only need to copy and paste uh the rate and the amount see that total actually I I don't get to change or modify so I'm going to customize this and move amount to the top that way I have rate and amount that these are modifiable and the total is just a mathematical equation for it to double check that is correct so I'm going to go ahead and paste that perfect and you see that total just double checks that your total is correct so now that I have all that information in there I'm going to make sure that I select the correct accounts receivable account in the event that I have multiple accounts receivable accounts in QuickBooks and some clients do that so I'm not going to get into why they do that or not but that's pretty much in a nutshell and then I'll click on Save transactions and click yes perfect then I'll just to show you I'm going to pull a report real quick of um of invoices that I did in July of 2016 so let me do 0701 2016 and then up to 0731 2016 and then notice we have $26 worth of Revenue go ahead and double click that and then I got my four invoices that I imported and I can double click on any one of them and you can see that the import the invoice was imported there's my customer name there's the invoice there's the date there's my quantity there's my item code there's my parts and the total so what I meant by a single line items means I don't need to have multiple line items in here again that batch enter tool so under accountant batch enter this batch enter tool only allows you to import invoices have single line items now you could go to split here and enter multiple align items per invoice if I wanted to but unfortunately uh the copy and paste capability doesn't work in here so if I wanted to basically copy and paste this this information into this split tool here in the bottom it just doesn't work okay so it's just a a a limitation that it has right now you can only use batch enter to copy and paste transactions with single line items now what if I have a quick QuickBooks Pro or QuickBooks Premiere so I don't have QuickBooks accountant I don't have QuickBooks Enterprise and I don't want to upgrade uh then what you need to do is you need to use a third party tool called Zed Axis or transaction Pro importer actually there are two tools out there in the market they're extremely similar I'm going to show you set access because that's the one that I use it actually works really really well um you can actually import single line items uh and multiple line items into QuickBooks Pro or QuickBooks Pro PR and this will also work the same exact way if you happen to have QuickBooks online or if you happen to have QuickBooks accounts in our Enterprise but you need to do multiple line items again so if I'm working with QuickBooks Pro Premiere or online and I want to do single line items I must use a third party tool and if I want to do multiple line items in any addition I have to use a third party tool no matter what so let's talk about what multiple line items mean so notice that on this spreadsheet I have two invoice numbers in total I have the first three lines taking invoice number one6 and the second the last two lines invoice number one7 so basically one invoice is going to have three line items in it with three different quantities three different values three different items three different descriptions right um so the most important thing is if I am going to import multiple line items the invoice numbers need to match the dates need to match and the customer name needs to match across all the line items that go into the same transaction that's the only way that the importing software will know to put them all into one single transaction so I'm using a program called Z axis this is what it looks like so I going to go ahead and connect to my Quickbooks desktop and again if I have QuickBooks Pro Premiere Enterprise or online this will all work and um and because I have to do multiple line items even if I have QuickBooks Enterprise or accounting I would have to use this tool so I'm going to go ahead and accept the sync uh question that is asking me it's just asking me whether or not I'm allowing it to connect so now that you see that the company is connected that means the set access is now uh available to import the data so I'm going to click on the import tab here and then here's when it's going to ask me okay which is the spreadsheet that you're going to use to import so I'm going to go ahead and click on browse and then I'm going to look for the spreadsheet somewhere in my computer I I think I put it here in my desktop and hit open and again this set access tool is a third party tool it's an add-on you can't uh this is not built into QuickBooks you have to pay for it extra okay um and again we're doing this because we're either importing to Pro Premiere uh or online because it doesn't have the batch enter feature that I spoke about earlier or because I need to do multiple line items so again if it's just single line items that's going to be easy uh but I'm going to go ahead and do the example with multiple line items I'm going to I click here multiple line items which happens to be the spreadsheet let me just double check here happens to be this spreadsheet here called multiple line items that's exactly what I called it um so we know exactly in context what is it that we're importing so you will notice down here on this general area here we're going to see a preview of the data that's about to be imported but now uh set Axis or transaction Pro import or whatever software you're using to import it's not going to know what these uh pieces of data mean you actually have to do the mapping you have to map it so mapping is done here um here where it says choose mapping I'm going to go ahead and click add new mapping and this is uh when you add the mapping this is where you tell it okay this is actually going to be an invoice map and notice that we can actually import all sorts of transactions with set AIS it's actually pretty cool tool I'm going to click here invoice and then I'm going to give this a mapping name so I'll go ahead I'll just call it invoice import I give it whatever name I want so then it's going to ask me a bunch of questions that this is kind of in code uh so there will be a little bit of sort of uh practice that you're going to have to take if you actually go to Google and you type in the search box if you type Z axis invoice import it's going to take you to the to like a cheat sheet where it's actually going to tell you what every one of those things mean so I'm going to type here set access invoice import it will probably be the first one or the second one that it finds and it's going to get you right into the set access website it's going to show you a preview of what an invoice looks like in QuickBooks it will give you sort of a guide of what all those uh codes mean so you know exactly where you're going to import all that information let me go down here so as you can see here I'm on the set access website and notice I have sort of a screenshot of an invoice and I have a whole bunch of red codes that tell me what each of those things mean so if you are under using the set access import software and you're trying to figure out how to do the mapping you can use uh that little cheat sheet there to let you know what every one of those things mean okay that's actually pretty pretty neat okay so customer full reference name that is going to be my customer name and then currency I don't need to deal with that because I'm not dealing with multiple currencies here where it says account reference number I don't need to touch any of these transaction dates yes I do need that so I'm going to go ahead and put my transaction date here and then invoice reference number yeah that's going to be my number here and you kind of get a data preview here on the side letting you know what is all the information that's coming through um so if you got a building address and that sort of thing you can import that as well so that's actually pretty pretty neat there I'm going to scroll down some more into sort of the the heart of this and just kind of see so we got terms item and description right so somewhere here are the terms here terms reference full name so I'll pick terms and then we got let's see item description quantity okay so let's look for the item somewhere down here you're going to see item and it's a lot of fields right so you're going to have to go through it and see until you find the item item name there it is item reference full name so I'll pick their item and then a transaction ID line ID I don't need any of these things description yes I do need that so I'll pick description and then it says rate okay so I do have that rate and there's my quantity I'm not using classes for this particular but I can import them too I'll put quantity and then amount that would be my total amount here perfect okay that actually should do it let me go ahead and save this and okay so now that we have everything mapped you will now get the same preview of the data but you're going to see the actual uh QuickBooks mapping in the top so you're going to just you can double check that the mapping is correct again as I mentioned earlier if customer D is not in there if your reference number is not in there if your item is not in there this is not going to work so you you got to have these things in your database already now here on the top right before I import there's a little option that says more options and then here it'll it'll ask you whether or not you want uh the default settings in the event that you you want set access to actually create the items for you so if the item is not there we can actually tell yeah go ahead and create the item for me and make it a service you know hit this Custer good sold hit this item that's if you want to sort of force create those items but I wouldn't I would never do that I would just make sure I would suggest you to make sure that those are already in QuickBooks right so I'm going to go ahead and hit import and I hope I get an error that way I can kind of show you how the error system works and how you can troubleshoot the the import so we'll give that a minute all right so there was no errors but that's good then so now we have invoice for customer D and customer e so let's go ahead and go into QuickBooks and let's go into our customer center and then let's go look for customer D and then let's go ahead and look for every invoice that's there double click on that and there it is there's an invoice with multiple line items so that in not kind of lets you know what the process is uh whether or not you're using multiple line items or single line items again in summary if you're working with QuickBooks accountant or Enterprise you don't need a third party tool and you will be able to import single line items if you want multiple line items no matter what uh you're going to have to use a third party Tool uh for any version of QuickBooks you have or if you're working with QuickBooks Pro Premiere or online you have to use a third party tool whether there's a single line item or multiple line items again thank you very much for watching this video hopefully this was helpful for you