Collaborate on Create Invoice in Word from Excel Data for Accounting with Ease Using airSlate SignNow

Create invoice in word from excel data for Accounting

in this video I will show you how to do a mail merge from Microsoft Excel to Microsoft Word and let me show you what I mean by that here I have a form letter that I've written up for a hypothetical small business and it's just about ready to be sent out to all of my business contacts but you see that some aspects of the form letter need to be customized instead of saying dear recipient name I want the actual person's name to be inserted there also the recipient's company name their address and other details I have contact information here in Microsoft Excel these are my business contacts with names company name address Etc and the good news is that by using mail merge I don't have to recreate this form letter 50 times once for each of my contacts instead I can simply pull the important data from this Excel spreadsheet let's look at how to do that so step one is to take a close look at the Excel spreadsheet with the contact information in my case this is a spreadsheet that I have customized and I've created from scratch and added to and been pretty careful with I've organized it the way I want it to be organized but in your case you might be exporting this data from another program or another source and so the first thing you need to do is make sure that your Excel data is in good shape for example it really helps to have a header row you can see row number one for me has column headings this is going to be very helpful when doing a mail merge each of these header names will basically become a placeholder in my Microsoft Word document another thing you might consider is do you want the business addresses or the contact addresses to be split up like this you can see I've got the street address here the city the state and zip code all separated out now in your case it may not be necess NE AR to do that it might be okay just to keep the entire address together in one column there are pros and cons but just be aware that if you want to change it it's best to do it before the mail merge same with names do you really want first name and last name separated out it should be okay but you just want to try to make sure that your data is ready to be merged into word I'm satisfied with this data so I'll go ahead at this point I'm going to just exit out of this document in fact I want to save it and then exit and then the data will be ready to be pulled into Microsoft Word now back in Microsoft Word all I have to do to customize my form letter for each recipient in that Excel spreadsheet is use this mailings tab so I'll click on mailings and it opens up the mailings ribbon and here toward the left notice it says start mail merge and it's tempting to just click that start mail merge button instead though I need to First select recipients this gives me three options I could pull my recipients from my Outlook contacts that's definitely a good option if you use Microsoft Outlook a lot you can also click here to type a new list I could go in and type in contact information for all 62 people that I want to mail this to but that's going to be a lot of work and it's unnecessary in this case so I'll cancel out the option I really want to use is the second one use existing list so I click there and hopefully I know where I stored that Excel spreadsheet in my case I stored it on the desktop so I can just click there and there is my contact list for the mail merge in your case you may need to browse your documents you may need to search your computer in order to find where you stored The Source spreadsheet but there it is for me I'll just double click on it to open it up and now Microsoft Word is just trying to verify what part of that Excel workbook do I want to use for my recipient source and in this case there's only one option sheet number one my Excel contacts list only has one spreadsheet that has data and so word figured out that's probably the one I want and it is it's important to also notice this checkbox in the lower left corner first row of data contains column headers if you remember I said it's easier to do a mail merge if you have column headers which I do in my spreadsheet so I need to make sure that that is checked and then click okay now when I clicked okay it looked like nothing happened but if you were watching closely you may have noticed that some of these buttons changed a little bit they look a little different many of them were grayed out like this but now are not and one of the changes is this notice that there's now edit recipient list now that I've selected my recipient list I can go in and click edit and you can see all of the names that were in sheet one of my workbook in Excel are now included here in my recipient list now what if I want to send this form letter to everybody on this list except for Lena well I can just uncheck her name what if I want to send it to everyone except for people that live in one particular State well I can click filter and I could filter out based on the state that the contact lives in or based on any of these other things zip code maybe notice that you can also sort which really shouldn't make that much difference but if you want to you can sort your list of recipients you can also look for duplicates and you can even try to validate the addresses to make sure that they're real addresses so there's lots of great options here I'm going to put Lena back into the list of recipients and I'll click okay okay now I'm ready to begin identifying where I want information from Excel to be pulled into Microsoft Word so this information here in the upper left is information about my fictional small business so I don't need that to change based on who the recipient is but but this I do need to change the recipient name and if you look below there's another recipient name so I'm going to highlight all of that including the comma that's what I'd like to replace so now on the mailings tab mailings ribbon here in the right and insert Fields group notice that there is a greeting line button if I click on that it makes it really easy to customize this so I can say dear so and so or to so and so or just leave out the deer or two I think I'll leave it with deer do I want the recipient's entire name to be listed or is that just a little bit too much maybe I just want the last name Mr Randall or maybe I would want to just use the first name and then I get to decide do I want a comma or a semicolon or nothing after the dear Joshua in this case and next look at this it says greeting line for invalid recipient names so from time to time there's an error if there is an error what do I want want the default message to be and this is it dear sir or ma'am but there are other options here as well now look what we have below that this is great it's a preview to make sure that what I'm doing is going to work so dear Lena dear Ellis dear Douglas for the most part these seem to be first names and it seems to be working really well so I can browse through quite a few of these in just a preview now if I notice some problems you know maybe it says Dear Texas or dear Louisiana in that case I could tell that there was a problem in order to fix that all I have to do is go to match fields and this shows me how Microsoft Word is trying to figure out the data from Excel here at the left word gives me some common data fields that are in form letters and then here at the right these are the column headings from my source from my Excel workbook so it matched first name to first name last name to last name but a lot of these other things aren't matched and it's probably okay I don't need to have something for every possible field but if there is a mistake I can correct that here for now I'll just click okay and click okay now notice what's happened Microsoft Word has put greeting line right here now that it's there I don't need to keep this original wording or text so I can highlight it tap delete or backspace on the keyboard and that one part of my form letter is now ready so it looks like the rest of the letter looks good but up at the Top If you recall I want to have the recipient's name again here and then the rest of this information so just like before I'm going to highlight recipient name you don't actually have to do that I just find it a useful way to approach the task but anyway I highlight recipient name this time it's not quite a greeting line so instead I'm going to select this insert merge field now for some reason it's grayed out I can't use it I've noticed that whenever this happens I can just click away from my selected test text and then just triple click on it fast and sometimes that works but look what it did now I can select insert merge field when I click it it gives me a list of all of the pieces of data from my Excel worksheet you should recognize this so I'll go with first name I'll click insert and last name and I'll click insert and then I can click close now that I've got those in there I can delete company name now I've left out one little step but I want to save that for a little bit later and highlight it so next I'll move on to street address I'll select the street address and the city state ZIP code all of it it's all highlighted and I'll come up here and I could go here again to insert merge field and I could piece together each aspect of an address or notice that there is a specific button for address block so I can just click there and it gives me a preview Ellen Hawkins chip notic music and here's the address and I can cycle through my previews just by clicking these buttons here at the right and also at the left now the more I look at this the more I can see that there's a problem the address is missing the street address it's just got the city state and zip code so similar to what I showed you earlier I can go here to match fields and I'm going to take a look at address address one not matched so here's the problem all I have to do is click the arrow and select business address in my case in your document it might be a different phrase or a different heading but in my case that's where the street address is stored now I can click okay let's see if that fixes it Stephanie ball synth City there's the street address there's the city state and zip code so match Fields can really save you when there are errors in the output of the mail merge notice here at the left you can customize how the name is displayed you can also choose to take out the company name if you don't want it included I do on it and there are other options as well that you can try I'm happy with how this looks I'll click okay now I can delete the previous text that I had there and I think this document now is ready for a mail merge before I show that I do want you to know that it is possible to insert anywhere in this document anything that is included in this insert merge field list so I could put in email addresses I could put in phone numbers or anything else listed here and again this information is being pulled from my Excel workbook okay before I click finish and merge I want to click preview results if you recall I said I had missed a step or two let's take a look at the mistakes that I made in this case I have Stephanie ball repeated also there's no space between the word Stephanie and the word ball now in this preview results mode you can just go in and fix the mistake so let's try that now that I've put a space between Stephanie and ball if I go up and click the next Arrow to look at the next recipient look it's fixed for jeie it's fixed for inz and Reginald and all of these other people so that's one of the nice things about the preview results turn it on and then make your changes now you can turn it off it's a toggle so you can go back and forth and I could have made the same change here outside of preview mode I could have put in a space okay but the larger problem is the name is repeated twice when I set up the address block I didn't think twice about it but notice it is going to insert the recipient's name again so I don't need that first name at all so I'll highlight it delete and just like before I can preview and see that it will fix it for all of the recipients and you'll notice it's changing the data here and here and anywhere else that I've inserted a merge field from Microsoft Excel if you'd like you can click check for errors and you can see the three options there but in my case I can tell that this is working exactly the way I want it to work so I'll click finish and merge and that gives me three options I can edit individual documents this would create basically 62 different separate documents one for each person that I'm going to mail this to I could also send email messages that include this document and notice what it did it's pulling automatically from my source Excel spread sheet it found that I had a column heading named email address and it automatically picked that I could then type in a subject line and double check all of these settings and then just click okay and it should open up Outlook and I'll be able to send an email to each of these people in my case though I just want to print the documents so I can click print and so even though this is just one page you can see it down here in the lower left page one of one I will be printing this directly to the printer and it will print actually 62 different times one for each record that I merged from my Microsoft Excel and I can just click okay select my printer and click okay and it will print out one at a time at this point if I think I might use this document again it's a good idea to go here to the file Tab and save a copy of this document so I can easily bring it up merge it with a different contacts list and send it to those other people now I want you to see that there's also an option to do this same kind of thing with envelopes I can set up an envelope with a mail merge and customize that envelope so that it can also be printed along with my form letter if there's interest in this I'd be happy to create another tutorial that reviews some of these steps and shows you how to create a mail merge envelope thanks for watching I hope you found this tutorial to be helpful if you did please like follow And subscribe and when you do please click the Bell that way you'll be notified whenever I post another video if you'd like to support my channel you can do that through my patreon account and you'll see a link to that in the the description below