Download Your Free Bill of Lading Template for Sales and Simplify Your Shipping Process

Bill of lading template free for Sales

hey this is Sally and in this video I have a special request from a from Mike he said hey I love your videos but I have not been able to find exactly what I'm looking for what I want to do is to be able to create an order acknowledgement a packing list and a bill of lading form from one main entry sheet this might not be available and some other products that you're using but this is something that is completely doable in Microsoft Excel in fact I've I've done something like this previously so I'm pretty excited it's it's a bit complex so this is going to be a longer video but I'm going to show you how how I would do this so what I'm going to do is I am I'm working out of Excel 2016 some of the more recent versions of it should be fairly similar to this so I'm starting from the start page here usually I just start with a blank workbook but since we already have an idea of what we want for starters I'm going to just type in here order form you know typically I would create my own my own order form but since Excel already has templates here I'm going to start with one of those so we've got some options here we don't really want a purchase order invoice or any of that although all of those could be included in this this whole packet it's it's stupid how excited I am about this so this one looks like it might be right I'm going to click on it take a look oh my monitor is pretty tiny on my laptop here but I did get a look at this on my larger screen so I'm going to go ahead and just start with this this way it gives me a good starting point so I'm going to click on create you don't have to do it this way either and forgive me I'm getting over being sick so I might have to pause it here and there but this is a good starting point here we've got all the necessary fields here you can of course change whatever you want here but this I just like this as a starting point so you can add your own company logo here if you want I'll just leave it there what I would do here is I would fill out your company information here this one right here has today's date see we can see this formula on here another way that we can do this is type in now with those parentheses on either side and that that works that works the same way sometimes I'll throw in the time there if that's not you know needed for you I'm going to put in an order number they're a customer ID now something that you may want to do depending on how many customers you have is if you have a customer database what you can do is create a drop-down so you can select the customer ID from from a drop-down list here otherwise you can you can manually type it in here again you know if in the end this can be prefilled out already you can do that you can also link this to this information here again that's using that drop-down and then if this then that kind of thing here so I'm going to just show you real quick on how you could do that it's it's a little bit of a it's a little advanced but you may want to do that if you already have the information so what we can do here is I'm going to going to copy these so I just highlighted them and control-c or command-c depending on if you use a Mac or not and I'm going to click in the second field here and I'm going to go to home and then paste and then we want to do okay I'm just going to click on paste special we want to do paste a transpose so let's see how this works I don't I haven't usually used this before there we go so it's a little strange here we don't want to merge and center so I'm going to unclick that so here we go double click spread those out there and it looks like there's a fill I'm going to remove the fill there we go so okay so here's what what this is going to do here so customer ID so I will just copy that right there so I'll just do ABC one two three four five I guess oops paste that down here so give it a header customer ID and then I'm going to type the contact name company name and ideally you would actually put in the actual information here and the phone number 100 five five five one two three four there we go so now what you can do from here is oops I can't even get that right there we go so I'm going to just do ABC one two three four six and then I'm going to click on there and just drag down okay there we go and then I'll just copy this down just to give some fields here so okay so say we have our customer information here I'm going to rename this tab here I right clicked on it I'm going to go up to rename so customers and once you're done filling out this information you can always hide these tabs - I'm getting really detailed here but this is how I would probably do it so what I'm going to do here is I'm going to highlight all of these so maybe go down a few extra actually I'm going to name it I'm going to name it a lower case customer ID all one word so now I've named this field so what I can do from here is now create that drop-down menu so from right here I'm just going to delete that and then I'm going to go to data here and I'm going to do data validation so I want to select data validation and right here for the criteria I want to change this to list and then the source so this wants to know the source so I'm going to do equals and then I named it customer ID and I want this to be the in-cell drop-down and ignore blank that's fine so I'm going to click OK so now you see this drop-down button is shown up here and there's all these customer numbers so now what we can do is when you select one of these we can set these fields up to automatically pull this address information for that customer right here so what we're going to do here is we want to do a lookup formula so we can click on this formulas button here so let's see I'm going to delete that and I'm going to click on insert function right here so this is going to be vlookup so it's vertical lookup because my data is stored in columns so I'm going to select vlookup okay so lookup value that is this customer ID number so I'm going to you can click on there but I can already see it right here so I'm just going to click there so it's going to select g5 and then the table array that's where it's going to look up the data for this so I'm going to click here and just going to copy all this and I'm going to drag it down just in case I end up adding more customers and then the column index number so what I'm looking up first is the cost of the contact name so it's going to be 2 so this is 1 this would be 2 and you can see it shows up down here so if it you know as long as it looks correct you're good so range lookup so I'm going to type false ok what what this does so if you leave a blank or you put true what it's going to do is it's going to if it can't find this number it's going to find whatever is closest to it and pull the information from that you don't want that you want exactly the information for this this customer number and you'd probably rather get an error than the incorrect information because last thing you want to do is ship it to the wrong customer or build the wrong customer so I'm going to type in false there and click on OK so there are we see we see that it's pulled this information here so it's pulled in the contact name so now what we're going to want to do is we're going to want to copy this but before we copy it I want to make sure that we are pulling from the same exact areas because if I copy this down here it's going to shift everything so I don't want it to do that so what I'm going to do is I'm going to go here I'm going to put a dollar sign before G and I'm going to put a dollar sign before five that means that no matter where I copy and paste this formula it's always going to pull the customer ID number this first field here from g5 also we want it to pull from customers a one through F 38 or wherever however far now the thing that I do want to change is this last number so I'm going to leave that because that's kind of my that's where I'm telling it to pull the information so I'm going to hit enter okay I'm going to go back I'm going to copy this formula and hit escape and then I'm going to go here and I'm going to paste it so now I'm going to change this to three because I want it to pull from the third column so there's company name that's exactly what we wanted let's see if it'll still let me paste okay perfect so now I want it to look on four for the address boom there we go I'm going to paste this I'm going to change that to five and again if if this is if you cannot remember what you're doing here you can always click on this and bring it up so that way you can see them in a way that makes sense and lastly the phone and paste that there so this should be six all right so that way automatically pulls up the customer information for that by that account number I did enter the same exact information for all the customers but if I were to select a different one this would all update without information if you want to if you have shipped two addresses for these customers I mean you can set that up over here as well maybe have another field here maybe this can be you can update these all to build two and then these you know have the same information but have their shipped to information here you can do that that's just an option for you a salesperson I would do the same thing as I did right here I would list the salespeople go up to data and create that validation so I had dropdowns for it same with probably shipping method shipping terms delivery date I'd probably leave payment terms so if you have specific payment terms for these customers you can do the same thing so you can set up that vlookup and you can add that so maybe they are oh and it's been so long I've worked in this industry but I mean one of our 90 days I'll just put that there so if that's the terms you can do that here so again I would I would do that via actually forget that I'm going to copy this formula here I do that vlookup I'm going to paste that in there I'm going to click on here to open this up and then I'm going to click here because I forget which field it's in so this is I think eight so oh shoot okay so that should be eight nope that was not eight maybe it's nine okay well what is it it is 8 all right well I don't know why that's not pulling the terms but you get where I'm going with that okay you can set that up okay and then due date again you can have that feed off of this so again down here if you want to create those I would definitely create dropdowns for this item number I would create a whole item number sheet here so item item number I would put the description let's see what else unit price I would create fields here so then you can have that drop down here and actually I will do that real quick I'm going to pause it so you don't have to watch me typing but I will be I'll be right back alright I'm back so I created this sheet here items so I just throw in a couple items I named the area items here so if you ever forget what you name something just you can click here on this drop-down and it will show you all the custom named areas you have also something to make my life a little easier is I'm going to name this tab I should have done it for this one exactly what I named that field just as a visual reminder there of what that is so I already put in my vlookup formulas here I'm going to add my drop-down again so again that's data validation I want to select list leave these as is and I'm going to type equals and then items because oops that's in caps so it has to be match the exact case so again keep that simple so I'm going to click on OK and my drop-down box is selected now ok so I'm going to want to have these fields all the way down these formulas here to automatically lookup and add the price but that looks kind of tacky there so I'm going to undo that what I'm going to do is I'm going to throw in this if error formula in here again this is a little complicated don't get overwhelmed it's not like you do this once and it's it's done so I'm going to go right here after the equal sign I'm going to make it all text and type if error I'm going to put a parenthesis here the left one and then I'm going to go to the end and I'm going to put a comma and now I need to tell it what I want it to display if this ends up in an error so that's going to happen any time you don't have something in this item field here so if you if you've copied this formula all the way down and the person orders four things they're going to have a bunch of errors and that looks tacky so what you can do here you can put you know whatever you want so maybe you want a dash or something else I'm going to do maybe a space see how that works out so I did the you know the quotations quotation marks and hit space and another quotation marks and I'm going to close it with a right parenthesis and you can see I don't know if you saw this first one bowled it up when I added the second one you want to just make sure you've got you know start and end here you've got that closure so I'm going to click enter and there we go it worked perfectly so I'm going to do the same thing here I'm going to go in jump right here type if error and then that left parenthesis go down here comma no spaces right now I want to enter the quotation mark space quotation mark right parenthesis boom it bolded over here so we close it out there we go so now we can copy and paste this all the way down so it's going to automatically copy so I also want to copy this validation down here paste it there there we go so you can see we've got the drop-down box option there when I click on it it's going to pull that up and it automatically puts the information in there and I would recommend doing this you know adding data to the forms while you're working them just to make sure everything's functioning properly something else you might want to do is anywhere you've got formulas that should not be messed with maybe change the color of that field at least temporarily so you know later on you're going to want to come back and you're going to want to protect the cells so you can protect the cells so I so no one can change them that's a good idea to do discount you can also link the discount to this customer ID if you want to on this customer item if they automatically get you know 10% off whatever they order you can add that in right here otherwise you can do it manually and then the line total so this one already has a formula added in here so it says you know if this is less than zero then leave it blank okay so what we're going to do how about we type in twenty four so there we go it automatically adds up for us I want 15 and I want 18 of these so there we go so the price automatically adds so they have it so they've got that if scenario in there so if if this number is zero or less it just it just shows a blank space and then down here they have the total so that is the sum of all of this again they've got that if it's zero just leave it blank sales tax again if you want to link sales tax to this customer number over here you know maybe add the sales tax field you can do that and do nine point five or whatever and then have do that that vlookup formula there so if you know if this is the customer selected that's the tax percentage on you'll work a formula in there you can do that so okay so this is our basic order form here now is where we get a little complicated so we've got a couple more items here so we we've got the sales order or this can be the order acknowledgement that goes out to the customer you can change that here so next we need a packing list so that's going to be a little simpler than this this goes out to the warehouse so you know your employees can pick the order and they just need to know how many white item and and be able to mark off what they've selected there so I'm going to I'm going to create this new sheet here so I'm going to rename it I'll name it packing packing list so we've already gone to the trouble of creating these formulas to include this information here so what we can do is if you want to copy a lot of this information but actually what you might actually want to do is I'm going to okay you can you can recreate it here if you want to you can also copy this so I'm going to I'm going to start by copying so I want to create a copy it'll be called sales order two but I'm going to pull it over here I'm going to delete packing list I'll just call it packing list two so we already have all this information here so what you can do is you can delete certain fields on here if you want to like they don't need to know the price or discount or line total but the thing is is you want to you want this information to transfer over so what I would do I want to unmerge this okay so what I'm going to do is I'm going to just delete this information here see this is or where's that merge there we go unmerge I'm going to delete this because they are not we you know the warehouse workers are working their butts off they've got a lot of stuff going on so we want to remove anything that's unnecessary to what they're doing company name and chip - that's that's helpful in case you know somebody needs to follow up on an order you know hey where are we at with that you know XYZ company with their order it's good for them to have that information on their sales person that's good to keep on there in case they have a question about the order job probably good you know it's a reference number shipping method shipping terms due date all that's really helpful so if there's a next day order and or you know next day shipping order that needs to get shipped quickly they can see that right on there but so we have right here quantity item number description so then what I would do over here is I would just have a line so I'm just going to do line and again I have not worked in this industry for a while so you may want to may want to have a couple lines here so so you can have packed and you can have on back order so if any are missing they can indicate that there and I'm just using this underlined here and actually what I'm going to do I'm going to move this over here so there's a space between the two lines so it's really clear so let's do a quick print preview so we'll have those the lines that are so packed and on backorder so I'm going to just click escape to get out and then same for for all of these down here so and they don't need to see all this information down here so I'm just going to delete that so we want to do bottom and we want that to be black there we go see exile gets a little picky about what it shows here so we want top and bottom borders I'm just going to do all borders and then we can done to left and I'm going to undo right there we go although that didn't do it okay and also since you're going to have people writing like manually writing on this what I would do is I would space it out a little bit give them some room so drag it down a little bit make it a little bit bigger I would make the text a lot larger too so they can see what they're looking at that's going to help them be able to see the order clearly especially if they're working in the warehouse it's going to be kind of dark in there so now here's what I would do so you're going to have one sheet where you enter the information okay that's going to be this sheet so what I want to do here is I'm I don't want to have to enter the information more than once that's that's wasteful so I've I'm finished with how I want this I'm going to change this to sorry I'm going to change this to packing list so what I'm going to do here I'll just delete this okay I'm going to delete all of this and I just want it to mimic exactly what's on the sales order so I'm just going to click equals so the first thing is quantity you hit OK same thing here equals item number right there and right here equals there we go that's all I need and then I'm going to copy this and I'm going to drag it down so that way it copies everything that's there some are merged and centered we don't need that okay let's try this again that's why okay description was merged and centered okay so we are going to drag this down there we go if we don't want zero values to show up I do have a video on that but I'll show you real quick so go up to file options we want formulas there we go and then there should be a field about zero values let's see maybe it's an advanced here right here under display options for this worksheet you can do zero uncheck this box there we go I'm just okay so that this means that this is just going to copy whatever is typed on here so you're going to want to do it for probably just do it for all of the fields I would I would get rid of this vlookup we want to just hit enter to right there and so on so I will I will leave you to do that because this video is getting kind of long so this is for the packing list so then from here we want to do the Bill of Lading so again you'll probably want to start by copying the sales order and I'm going to click on this create a copy I'll bring this over here and I'm going to change this to Bill of Lading so again you're going to want to put whatever information is necessary for your bill of lading I would again copy this I would do the equals here you know copy so it automatically pulls from all of these fields and what you might want to do while you're setting this up is change the color or do something so you know that you've already completed it and then go ahead and fill out a dummy order or two just to make sure that the fields are transferring as expected so this I'm going to rename Bill of Lading and then whatever information you need to have on this bill of lading I put down here and you you might want to have two copies because typically the the drivers will sign a copy and leave it with your you know with your warehouse and then they will have a copy to take with them as well that goes along with the shipment so what you can even do is once you've completed this bill of lading you can copy it and let's see I'm going to put it right back here so then I would have Bill of Lading too so then this could be the drivers copy and you can even specify that down at the bottom you can have you can have a you know a line here where the driver needs to sign you know sign here and then you know date and then you can say you know whatever your company is my company copy and then you can then you can have on the bottom here you know drivers copy so so this way you've got all of these on separate tabs now what you might want to end up doing is once you have all of this completed I'm going to slide this over here you might want to just have everything on one page that might be easiest to print everything all at once so have have your sales order where you're going to fill out all the information and then you'll have your additional forms that automatically pull the data from your sales order so you're only filling out the information once and and I would choose the sales order is probably going to be the most complex where you're entering the most information the packing list is not going to have as much information the Bill of Lading may not have the same amount of information so I would select the most complex order to be the one where you're actually entering the data there so okay so now you can combine all of these into two one especially since you've used the same template here so they're going to be pretty similar so I'm going to copy this so this is the whole sales order I'm going to copy this actually forget that why am I making this hard I'm going to move or copy so I want to move it to the end create a copy so I'm just going to rename this all Doc's print okay so I've got the sales order so now I want to insert I want to insert a page break here so I want to make sure that I've I've set the print areas correctly so this one has already set up to print here now we should be able to view the print lines okay so here we are page layout so if I want to insert a break like right here I can insert a page break so you can see it's got this line here so now what I'll do is I'll take packing list - I'm going to copy this so there's nothing down here copy this that's why it was important to keep everything similar I'm just going to go right there make this a little whoops this little taller there there we go so now I can insert see I didn't I didn't I do that formula on here the zero value formula so once I'm done with everything I might want to go and jump in and add that zero value I'll just do it right here so options advanced scroll down to display options for this worksheet zero uncheck that the regards ero problem is gone so I'm at the bottom here I'm going to just highlight this line insert page break now I'm going to go to what Bill of Lading - again copy this well that's numbered I need to bills of lading sorry about that so here's this one this is all Doc's print so I see that break there I'm just going to paste that right there there go underneath insert a page break Bill of Lading to we're already got highlighted so go right under that page Frank print all right so that should be everything and if it's helpful to you you might want to just build them all right in the same on the same worksheet just to start off but that's how I would do it and let's take a look so we've got your sales order let's go to okay I okay there must have been a print area set already so I'm going to highlight everything print area clear print area there we go so sales order packing list Bill of Lading oops we skipped a page there and there's Bill of Lading too so I think we've got two page breaks in here we must delete that that's why because see this is where where the page will automatically break and then I added that second page break in there so might need to there we go and with what I would do then to is if this is all the documents I would just select all of this area here from the top down to the very bottom so here's here's where it ends right I went way too far so this is where the page ends I would just do a print area set print area so I don't know if you notice before it said 12 pages we just want the floor so we've got four so we've got your sales order your packing list bill of lading 1 that the driver signs and bill of lading 2 that the driver takes with them so I hope this was helpful there is a way to do this it is a bit complex but it's not that complex you've got a lot of options and I am all for you if there's a simpler way to do it do it that way don't don't don't double enter your data at all have one place where you can enter all of it even if you want to have some don't even if you don't want to enter it into a sales order like this what you can do is even just have have fields where you just enter all the information and then they automatically get pulled into here by using just that it's so stupid simple that just typing equals equals this equals that but that's how you would do it so like I hope this was really helpful to you this this is a great way to do it you don't have to get expensive software to do it you can do it right in Excel have it saved you can even I mean there's tons of things you can do hopefully this gets you off to a good start so thank you again and guys if you have any questions on anything let me know if I've got the time and the know-how I will show you how to do them so thank you have a great day don't forget to subscribe to my channel and thanks for tuning in