1. Hi Guest! Want to share your ideas and knowledge, and be known widely by the community and other readers? Have your article posted in TWF and get your ideas featured! Contact us to know more!

Guide to Printing Address Labels for your eBay Orders

Discussion in 'Payment & Shipping' started by psxgamer, Aug 12, 2011.

  1. psxgamer

    psxgamer

    Joined:
    Jul 14, 2009
    Messages:
    3,447
    After all the info and help I got on TWF it's about time to make a small contribution. Here's my guide:

    (My apologies for the English spelling, but English is not my mothertongue)

    PRINTING ADDRESS LABELS FOR YOUR EBAY BUSINESS: A STEP BY STEP GUIDE

    First of all I want to point out I'm not selling on Ebay UK, but it should work for all Ebaysites. It's possible you can also use it for Amazon and other sites.

    How much does it cost?
    Free

    What do you need?
    • Selling Manager Pro (it might work with the normal selling manager)
    • Ms Word & Ms Excel
    • A printer to print out labels e.g. a Dymo Label Writer
    The different steps
    1. Download your sales history
    2. Getting the addresses out the sales history and give them the right formatting
    3. Prepare the labels
    4. Do the mailmerge
    5. Print your labels
    You'll have to prepare these steps once. Afterwards when repeating it, it only takes 2 minutes.

    1. Downloading your sales history

    First of all you mark all the items that have been paid for as payment received. If the item has been paid with PayPal it will automatically have been marked as payment received.

    Once you've marked them as paid for, on the left you click on the link "awaiting postage". This brings up a list of all items that need to be shipped. Click on the checkbox on top to activate all checkboxes. At the top you have a button "Other Action". Click it and choose for download. This should direct you to a new page where you can download your sales history.

    It doesn't matter where you save this file. Since you'll often use it, I'd suggest to save it on your desktop. Just always save it on the same place and give it the same name, else it won't work afterwards

    The file you download is a csv-file containing all addresses. You can open this in Excel and it will be the basis for the mailmerge you'll do afterwards.

    2. Getting the addresses out of the sales history and giving them the right formatting.

    When opening the csv-file in Excel you'll notice it doesn't only contain the names and addresses but also a whole bunch of rubbish you won't need. To make it a bit easier you create a second Excelfile.

    I've attached the file "Adresses.xls" to help you started. The first page contains the formulas I've used. I've added a second sheet which shows the the result of the formulas.

    What have we done:
    * We used a few formulas to get the date as name, address, ... out of the sales history
    * We used a formula to change the text into capitals to make sure everything looks the same.
    * I expect to never have more than 50 orders a day, so I dragged these formula's up to row 50.

    If done correctly the address should show as

    JOHN SMIT - STREETLANE 1 - 2000 ANTWERP - BELGIUM

    These are the actual address that will be printed on the labels.

    Save this file. I would again advice to save it on your desktop. Just as the other file you always have to save it on the same place and give it the same name.

    3. Prepare the labels

    For this you'll need MS Word. Word contains a function to create labels, however were not gonna use it.

    You can buy labels in all dimensions you want. You can find them cheap on Ebay. Decide which dimension you want to use. Mine are about 5 cm X 10 cm.

    So what you do is in the page setting change the size of your Worddocument giving it exactly the same size as the labels you're using. Print it landscape and set the margins on zero (make sure you've set your label printer as standard printer before you do this). When confirming this you normally get the message that some of the margins are outside of the printable area. Click the repair button, to fix this.
    .
    By this you create a page of the same size as your labels, ready to do the mailmerge.

    4. Do the mailmerge

    In the meantime you have an Excelfile containing all the address you need to print + a Worddocument = your label. To print the addresses you need to do a mail merge. Just google it if you don't know how to do it. I've added the file "Labels.xml" as an example.

    So what you actually do is link you Worddocument with you Exceladdressfile. Afterwards you add the mergefields you need to the Worddocument. I've chosen a big type of label. This allows me to also add my shoplogo and there's enough place for a returnaddress.

    When a customer buys several items you only need one label. You can solve this easily. In the ribbon you find the option "Rules" (don't know if it's the right name for it in English). There you find the rule "Skip record if". I've set it on ship record if the street + number is empty. The empty you fill out by typing ""

    Before actually doing the mailmerge you save this Worddocument.

    5. Printing the labels

    The last thing you have to do is actually do the mail merge. Don't merge them directly to your printer. You have the option to merge them to your screen. Do it and take a look see if everyhing's fine. You can make a correction if necessary. And then all you need to do is print them.

    Conclusion

    Does this sound complicated and time consuming? Think again. You once take the time to create the Word and Excel files. Afterwards you can use them over and over again. So the next time you need the labels the only thing you need to do is:

    * donwload your sales history, overwriting the previous sales history
    * Open the Worddocument containing the label and do the mailmerge
    * Print out the labels
    * Mark the items as dispatched

    Depending on the number of orders this is only takes a couple of minutes.

    Some final words

    The documents I attached are just for guidance. I'm not from the UK. We write our address in a completely different way. So your Excelfile will probably look a bit different.

    You actually need some knowledge of Word and Excel. I've been teaching MS Office for about ten years. The Excelformulas are very easy for me, but I know it can be very complicated for you.

    After downloading the sales history, you may need to open the Excelfile to update the addresses.

    This is just a general guide to give you an idea how to do it. Writing everyting in details would make it too complicated.

    I'm just using this method shortly. For me it works perfectly. Instead of writing everyting by hand I can now create all the labels I need in 2 minutes.

    Attached Files:

  2. UKbuyer1

    UKbuyer1

    Joined:
    Oct 30, 2009
    Messages:
    8,314
    Re: Guide: printing addresslabels for your Ebay orders

    Nice guide Koen :welldone:
  3. psxgamer

    psxgamer

    Joined:
    Jul 14, 2009
    Messages:
    3,447
    Re: Guide: printing addresslabels for your Ebay orders

    There's a lot more I wanted to put in but it would make it too complicated. I hope it's of use for a lot of people. I know it saves me a lot of time.
  4. bumps a daisy

    bumps a daisy

    Joined:
    Nov 5, 2010
    Messages:
    117
    Re: Guide: printing addresslabels for your Ebay orders

    Useful guide. Just deciding how to efficiently address parcels. Thanks!
  5. Michael

    Michael

    Joined:
    May 6, 2009
    Messages:
    11,523
    Re: Guide: printing addresslabels for your Ebay orders

    I was only saying yesterday that I had just printed 1200 different address labels in less than 4 minutes!

    I used avery labels an dowloaded the free avery software for label creation, its much quicker and easier than MS Office mail merge and also has some extra features (adding logos etc)
  6. psxgamer

    psxgamer

    Joined:
    Jul 14, 2009
    Messages:
    3,447
    Re: Guide: printing addresslabels for your Ebay orders

    Well, you can do the same with this. I've put my logo and shop URL on the label. You can put everything you on it, as long you have enough space.

    How do you handle addresses that written in lower case? How many labels do you print when a customer places multiple orders?

    I realise it takes some effort to create the Word and Excel files but once done it's very user friendly and all the previous problems are fixed.

    I could merge using the software that comes with the Dymo Label Writer. It has a feature to do a mailmerge when you have a database with adresses. For my Ebay it didn't work. No solution for adresses in lower cases and when a customer orders 5 items it prints 5 labels.
  7. Michael

    Michael

    Joined:
    May 6, 2009
    Messages:
    11,523
    Re: Guide: printing addresslabels for your Ebay orders

    I dont use it with eBay, the database comes from our Sage software in csv form, I simply add headers and then tell the label software which header goes on which line of the label... if there is less lines from one address to another it simply misses it out on the smaller address and then carries on as normal.

    There is a way on excel to convert all of your text to uppercase or lowercase and possibly even capitalise each word, cant remember how but I think you can find it in the help section or on google.
  8. psxgamer

    psxgamer

    Joined:
    Jul 14, 2009
    Messages:
    3,447
    Re: Guide: printing addresslabels for your Ebay orders

    "There is a way on excel to convert all of your text to uppercase or lowercase and possibly even capitalise each word, cant remember how but I think you can find it in the help section or on google. "


    Yep its:

    =UPPERCASE(A1)
    =LOWERCASE(A1)

    I don't know how it's called in English to capitalise words.

    In my case it's probably a professional distortion to do it with MS Office. But I've been teaching it for 10 year or more. It's incredible what you can do with it, especially when you start to link things. Most people only use a very small % of the possibilities.

    When I was teaching MS Access I used several databases in my lessons, all of them having their own features. If I would have made 1 database with all these functtions I would have had a complete business solution including invoices, stock management, order management,... When you create it the way you should create it, the user doesn't have to know anything about MS Access to use it. Most people find it too complicated, but it's such a powerful program.

    In fact, a database is the backbone of a business. What would you do without a list of customers, products,... So you need something to manage it. Excel is good to make lists. I use it daily. But in Access you can do so much more.
  9. Luke Appel

    Luke Appel

    Joined:
    Dec 30, 2011
    Messages:
    4
    I'm trying to follow your guide and am using Office 2010.

    I can't seem to get it working at all! I'm trying to create the second Excel file but it won't let me link it to the CSV file.

    Is anyone able to help?
  10. psxgamer

    psxgamer

    Joined:
    Jul 14, 2009
    Messages:
    3,447
    Well it's not difficult to do.

    1. dowload your sales history. Just save it on your desktop, and leave it there to overwrite it dayly.
    2. Open a new Excelfile.

    This file will contain the data you need to print on your addresslabels. This is the way my Excel file looks

    Colum A - Colum B - Colum C - Colum D - Colum E
    Name- Street + N° 1 - Street + N°2 - Zip + City - Country

    So these are the headers you put in row 1.

    Now you need the actual data that's in your sales history file. Make sure you've opened both Excelfiles.

    E.g. I want to put the name in cell A2

    1. put the focus on cell A2
    2. click below on the task bar on Sales History.cvs to activate it
    3. Click on the cell that contains the information you want to show in the other Excelfile. In my case this is cell C4.
    4. Press ENTER to confirm

    If you've done it correctly in cell A2 you should have the formula:
    =SalesHistory.csv!C4

    Just drag this formula down. I have to print out 10-30 order a day. So I dragged it down to cell A50 to be sure everything will show.

    I like the addresses to show in UPPERCASE on the labels. The easiest way to do this is in Excel. So I added something to the formula:
    =Uppercase(SalesHistory.csv!C4)

    You just do it the same way for all other columns.
    If you're not familiar with MS Word/Excel it will take some time to create these files, but once done it will save you a huge amount of time. It's been quite some time since I wrote this guide. Since then I'm printing out my address labels like this dayly.

    1. Download my sales history
    2. Open the second Excelfile I created so that the addresses are updated
    3. Open the Word document
    4. Print the labels

    Just takes me 1-2 minutes and saves me lots of time while packing everything.
    Luke Appel likes this.
  11. Luke Appel

    Luke Appel

    Joined:
    Dec 30, 2011
    Messages:
    4
    In terms of simply dragging the formula down, it is not working. All it does is repeat the code ='SalesHistory - Copy.csv'!$A$4 into each cell, this if it were the name Eric I was copying, it would copy it every time instead of moving down the list.

    Is there an option I have to turn on to do so?
  12. psxgamer

    psxgamer

    Joined:
    Jul 14, 2009
    Messages:
    3,447
    Remove the $ in the formula and than drag it down
    Luke Appel likes this.
  13. kahitoo

    kahitoo

    Joined:
    Dec 12, 2010
    Messages:
    1,450
  14. Luke Appel

    Luke Appel

    Joined:
    Dec 30, 2011
    Messages:
    4
    Alright Guys, thanks for the help so far!

    For those wondering, I have found out how to make the first letter of each word uppercase.

    I find this great for names and addresses as I don't like everything to be in uppercase.

    To do this, instead of using =UPPERCASE('SalesHistory.csv'!C3), you simply replace the word UPPERCASE with PROPER.

    So it looks like this:

    =PROPER('SalesHistory - Copy.csv'!C3)

    That will make all the first letters of a string of text uppercase, and the other letters lowercase.

    Now, i'm not a fan of eBays invoicing system as it looks cheap in my opinion. Has anyone here set up a mail merge to also create an invoice? It's easy enough as it is, as you just fill in the terms that are required. But the thing I would like to know is how to deal with multiple items. As in putting multiple items on a single invoice if the buyer purchases more than one.

    Anyone here giving that a shot?
  15. QuickPackSupplies

    QuickPackSupplies

    Joined:
    Nov 18, 2010
    Messages:
    1,402
    Hi psxgamer,

    Firstly this is a great addition to TWF thank you very much!

    I'm not too shabby with Excel but wondered if you could assist, I have a similar process sheet which will produce a invoice sheet which is fully customizable.

    The only issue i am having is that i need to automatically strip out row 1 and 3 and also the final two rows which do not belong to a column number as its dependant on how many sales you make.

    I'm wondering do you have any code or a macro which can strip these lines out or anything which can save myself some time so I can just click a button and I'm done.

    I'm happy to contribute this to the forum once I have modified it a little further.
  16. psxgamer

    psxgamer

    Joined:
    Jul 14, 2009
    Messages:
    3,447
    I don't think this will be possible.

    1 row of Excel data = 1 page in MS World

    So if a customer buys 3 different items in the same order it will automatically create 3 rows in Excel.

    To be able to do this you'll need a database. E.g. in the sale history I downloaded today I have a customer who bought 2 items. This creates 3 rows of data in Excel which contain all the same transaction ID. This transaction ID will be you primary key. A database will know which item is linked to which order.

    The problem is a database works completely different than Excel. So importing the Exceldata into Access or another database would just be the beginning.... The best alternative would be a bookkeeping program. I forgot the name, but they have been mentionned several times on TWF. There are several (paying) online services with wich you can control stock, print labels, invoices,... everything you need.
  17. QuickPackSupplies

    QuickPackSupplies

    Joined:
    Nov 18, 2010
    Messages:
    1,402
    Right think its a bit more advanced than im thinking,

    I'm looking down the lines of having say ITEMTITLE1, ITEMTITLE2 so if it picks up another item where buyers full name = blank (as its already been stated in cell above) then put this in column ITEMTITLE2.

    Then quantities would need to be done.

    Thats probably a lot of work as you dont know what cells they could fall upon or anything grrr.
  18. Luke Appel

    Luke Appel

    Joined:
    Dec 30, 2011
    Messages:
    4
    James, what are you asking? I'm confused.

    Do you want to take out the first row and second out of the SalesHistory.csv because they are blank? And also the last rows which state how many records and your Seller ID? Are you trying to get it so only valid invoices and shipping labels show up?
  19. dawei zhang

    dawei zhang

    Joined:
    Sep 13, 2012
    Messages:
    3
    hello

    i really need yr help
    cna you contact me with email ********************
    i am doing busineses on ebay and i got a word template , but i dont know how to import the saleshistory.csv to the word template

    you know we get lots of order not only like one
    so i dont know how write the fomular like yr example '=HOOFDLETTERS('C:\Users\koenvbr\Desktop\[SalesHistory.csv]SalesHistory'!B4)
    this is one only will showing the b4 cell, and we have 200 orders so we can not generate one by one.


    can you help me
  20. dawei zhang

    dawei zhang

    Joined:
    Sep 13, 2012
    Messages:
    3
    if you have a email address can u tell me

    thx

You must be a member to reply…

Why should you join us?

  • Networking Opportunities
  • Find Genuine Wholesalers
  • Source Profitable Stock
  • 100,000 Members Strong
Back to top

Share This Page