The Book Publicity Blog

News, Tips, Trends and Miscellany for Book Publicists

How Excel can make book publicity easier

In the field of book promotion, we don’t often use Excel,  and the truth is that you only need mention “pivot table” or “concatenate” to make my head swim. But even though we don’t need to make use of Excel’s advanced functions, book publicists and authors can use it for one basic purpose: to efficiently maintain lists of names. In fact, storing data in Word is akin to, say, writing a book in Excel.

As handy as Excel can be for our lists of names, it needs to be used correctly so that the information can be easily mail merged and / or imported into various publicity databases and mailing systems. Here are a few Excel issues / questions that I’ve seen arise:

Leading zeros: Excel’s default format causes “leading” zeros to be dropped. For example, if you were to enter “06520” into a cell, it would appear as “6520.” Some users attempt to rectify the situation by replacing the number “0” with the capital letter “O.” This looks correct, but it means the information can’t be imported into a mailing system (or a database connected to a mailing system), because the system does not recognize letters in the zip code field (at least not if you’re in the US).

–> Instead, highlight your column, then click “Format” from the top menu bar, then “Cell,” then select “Text” in the box on the left on the “Number” tab. This will allow you to “keep” all leading zeros.

Address fields: All databases use separate fields for each element of an address, so in order to be able to import the address into any publicity database (to generate address labels) or even to mail merge address labels, you need to separate out the address into its components.

–> All databases are slightly different, but it usually works to create separate fields called Address1 (street number and name), Address2 (Floor / Suite / Apartment number), City, State and Zip. Click here for a template. (One caveat: if you are an author or literary agent working with a publicist who has asked you to submit names, show them the template before you use it; they may ask you to make some small changes to the fields.)

Sorting: If you need to sort your contacts, i.e., some contacts should receive galleys while others should receive books, or some contacts receive personal notes while others do not, do not highlight or use a different color text for those records.

–> It may seem to make sense to highlight certain names — the way one would in a book or on a piece of paper — but in Excel, there’s no function that allows you to sort by color. (Excel alphabetizes, i.e., sorts, by column.) So instead, create a new column, called, say “Personal Notes” and mark off a “P” (or an “X”) next to those contacts who should receive personal notes. Then, when you highlight that column and hit the “ABC” button on the shortcuts menu bar, all your contacts who should receive notes will be in one place.

What are some of your Excel bugaboos / quick fixes?

March 4, 2011 - Posted by | Miscellaneous | ,

14 Comments »

  1. This is truly helpful to publicists, if not exactly scintillating reading for those not in the pr trenches. Thanks, Yen!

    Comment by Susannah Greenberg | March 4, 2011 | Reply

  2. Great post, Yen! For those who use Outlook to store author’s interview and book event schedules, Outlook can export that info into an Excel spreadsheet. This can be uploaded as a Google doc or emailed. I find it easier than entering info directly into a Google calendar since I always have my Outlook open. This suggestion might be more helpful to publicists whose media databases don’t keep track of tour schedules like Publicity Assistant does.

    Comment by Kathryn Tumen | March 4, 2011 | Reply

    • Sounds like a great idea — I’ll have to try that. (I’ve used Outlook and Google Calendar for schedules, but haven’t tried exporting / importing. Always nice to have another option!)

      Comment by Yen | March 4, 2011 | Reply

  3. Why in 2011 would anyone have to deal with a leading 0 in an ISBN?

    ISBN-13 has been the standard for 4 years now.

    http://en.wikipedia.org/wiki/International_Standard_Book_Number

    Comment by Nathan Maharaj | March 4, 2011 | Reply

    • Not ISBNs — zip codes.

      Most book publicists won’t have a need to list ISBNs (that’s more on the managing editorial / sales end), but we do deal with a ton of addresses, many in New England where zips start with “0.”

      Comment by Yen | March 4, 2011 | Reply

      • Starting a number with ‘ (single quote) will format an individual cell as text, too.

        Comment by Lyn Thorne-Alder | March 8, 2011

  4. I’d also add a tip for formatting ISBNs.

    If you don’t format, they’ll appear as formulas.

    To format a 13 digit ISBN (really, the only ones anyone should be using now), highlight your column, then click “Format” from the top menu bar, then “Cell,” then select “Number” in the box on the left on the “Number” tab and choose 0 decimal places.

    Comment by Ashleigh Gardner | March 4, 2011 | Reply

  5. For a Zip Code in Excel, just format it as a Zip Code:

    Select the cell(s)->Right click->”Format Cells…”->Number->Special->Zip Code

    Done.

    Comment by Ed Gray | March 5, 2011 | Reply

  6. If you use an email program like constant contact, keeping your names separated first and last lets you use upload your spreadsheet and “personalize” any mass mails.

    Though if you screw it up its always obvious. About once a month I get “Dear {Contact.First!} from someone.

    Another way to force leading zeroes (Amazon’s ASINs have them) is format the column to User Defined and put in the number of zeros you need for the number of digits in the it (6 for zip, 10 for ASINs).

    Comment by Ross | March 5, 2011 | Reply

  7. […] The Book Publicity Blog on How Excel can make book publicity easier. […]

    Pingback by March 7, 2011 Links and Plugs : Hobbies and Rides | March 7, 2011 | Reply

  8. I like excell to track submissions of my books too.

    Comment by Sharla Rae | March 8, 2011 | Reply

  9. In the last section, sorting, it works mcuh more cleanly to use the filter function rather than A-Z. Most of your worksheets will be alphabetised by surname or company name (to distinguish, rather than have first name and surnmae columns, better to have “name” and “sort name”, the latter of which won’t be a mail merge field but will be what you use to alphabetise). If you have an additional column (as is very handy) for things like (“send galley?”, or “send advance press relase?”) and then sort with A-Z you run the danger of making a dog’s dinner of the rest of the sheet – if, however, you have a filter (highlight row 1, with your column titles, go to sort & filter on the toolbar, click filter, and an arrow will appear in each cell on row one – to use the filter, click the arrow in any cell and it will list all the items that appear, and you can tick the ones you want – so you would tick “yes” in most cases and it will show all “yes” items) it will keep the sheet in the odrer you intended and omit the risk of clicking save at the wrong moment leaving you with a hotch-potch

    Comment by Dan Holloway | March 10, 2011 | Reply

  10. I use excel to keep track of expenses and work logs, but I had never thought of the benefits of using it to keep track of my submissions or similar tasks for followup. Thanks!

    Comment by Kristiina Jefries | April 8, 2011 | Reply

  11. enlightening read, regards.

    Comment by panini maker | April 3, 2012 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: