When you are a one-person communications shop, you look for faster ways to do things wherever you can. Over the years, I’ve picked up a number of tips and tools for automating tasks that would otherwise be time-consuming manual tasks. Here are a few of my favorites, organized by program:
1. Word
Search and Replace
Let’s start with the most humble of automation tool: the search-and-replace in Word. It seems so obvious that it shouldn’t even count, but search-and-replace becomes especially useful when you apply it to special characters, like tabs and paragraph returns. This allows you to quickly clean up a set of data and insert the tab stops and other special characters needed to get that data ready to be easily copied and pasted into columns in a spreadsheet (which then opens the door to further automation).
Macros
If you find yourself frequently using the exact same sequence of search-and-replace operations (e.g., cleaning up a baseball boxscore every night), you can further save yourself time by recording a macro in Word. It’s basically a sequence of operations in Word, and once it’s created, you can run those steps in a document with the click of a button, so you don’t have to type in the same search-and-replace parameters every time.
Mail Merge
When I learned how to do mail merges in Word back in high school/college, it seemed like a function that you would only use for preparing giant stacks of form letters and envelopes. What I have realized in recent years, however, is that you can use it to quickly generate code that involves creating lots of entries with the same structure but different data. For instance, it’s one of the tools I used to relatively quickly hand-code this page that has hundreds of entries, including over 100 that had photo popups. I just wrote the code for one entry and then did a mail merge, selected the “Edit individual documents” option during the merge, and then did a search-and-replace for section breaks (a special character) with a paragraph return, and voila! Code for hundreds of entries.
2. Excel
Concatenate
One of my frequent go-to features in Excel is the Concatenate function, which basically lets you combine text from multiple cells and insert characters like spaces or punctuation marks within the combined text string. For instance, here’s a simple usage case where you can create a full name in column C by concatenating the text from columns A and B:
Joining a couple snippets of text sounds simple enough, and something that you might be able to do quickly via other means. The simple underlying concept of joining content from multiple cells, however, opens the door to more complex operations that can save you more time. For instance, here’s a slightly more complex scenario:
Let’s say I have the first and last names of a list of people, and for each person, I’ve created a page on my website using the naming structure “lastname_firstname.html”. Now, I need to create a list of the URLs of all these webpages. How do I do that quickly?
In this example, I wrote a concatenate function that tells Excel to start with a top-level website URL (https://example.com/), and then append the text from the Last Name column, followed by an underscore, then the text from the First Name column, followed by “.html”.
VLookup
Another time-saving Excel function is VLookup. Basically, you are telling Excel to compare data from two sheets and then populate entries from a specific column on one sheet in the corresponding row on the other.
Quick example: Let’s say I have Sheet A with 50 names, and Sheet B with 1,000 names plus their dates of birth. I can use VLookup to automatically search Sheet B to see if any of its 1,000 names match any of the 50 names from Sheet A. If there are matches, I can tell Excel to automatically populate a column in Sheet A with the birthdates from Sheet B, matched with the corresponding names.
3. Photoshop
Look under File –> Automate for several useful automation tools that allow you to quickly edit images.
Fit Image
Fit Image is a great tool for when you need to quickly resize a bunch of images to specific dimensions, but the images are a mix of landscape and portrait sizes, and some are huge while others are small. Fit Image allows you to resize images to no more than a particular width or height, whichever is smaller. There is also a handy checkbox that tells Photoshop to never enlarge an image so that small files don’t get blown up to pixelated sizes.
But, you say, Fit Image only shrinks the one image I have open. How do I apply this to multiple images quickly? Well, read on.
Actions
Actions are actually in the Actions palette. Basically, they let you record a set of steps, and then you just apply those same steps by clicking a button to “play” an action. This saves you from having to manually apply the same steps every time.
So, continuing with the Fit Image example: You can record an action that contains the use of the Fit Image function. Now, any time you want to apply this to an image that’s open, you just play this action.
Of course, that doesn’t really save you any time. It is, however, a necessary precursor to the next step, which is the real time-saver.
Batch
Go under File –> Automate –> Batch. This allows you to apply a chosen action to an entire folder of files, and then with the option either automatically saving and closing each file or saving them as new files in a designated folder. Once you start a batch operation, you’ll see the files all open and close one after another until Photoshop has churned through the entire folder of files.
Droplet
Think of a Photoshop droplet as an icon that launches a batch operation. Creating a droplet is very similar to creating a batch operation. Once a droplet is created, it shows up as an icon in whatever file system folder you specified. You can then drag-and-drop files over the droplet icon, and Photoshop will automatically launch and run the specified batch operation.
4. InDesign
InDesign is Photoshop’s cousin for laying out documents, anything from brochures to posters to slides. Let’s say you need to create 100 slides with the same layout, just different text in specific text boxes and different images (but all with the same position and size). What do you do?
Data Merge
In short, this is like Word’s mail merge feature, but for design. Get all your information ready in a spreadsheet (including file pathways to your images), save the spreadsheet as a CSV file, and then go into InDesign to create your design template. Once you have set up your placeholder text and image boxes in InDesign, open up the Data Merge palette and select the CSV file as your data source. Then just plop the right field into each box in your design template. Then hit Create Merge Document and watch the magic happen. Here are detailed directions.
Tip: Make sure you create and apply styles to all the text elements in your template before you create the merged document. That way, if you want to tweak a piece of text after merging, you can easily propagate that change to all the entries by just redefining a style.
5. Miscellaneous Tools
Tableizer
Need to convert an Excel or Word table into HTML code? Just copy and paste into this handy web tool, and it will spit out clean barebones code for you. This is particularly useful when inserting tables into websites via WYSIWYG editing interfaces that allow you to paste in limited HTML code snippets.
Image Download and Renaming Tool
You have a bunch of pictures stored online somewhere, and you need to download and rename them in bulk. This Excel macro will do the job for you.
Making it all work together
Individually, each of these tools can help shave some time off repetitive tasks. But if you put some thought into how they might fit together in a multi-step workflow, you can whip up some neat processes that can cut your workload by hours. I’ll write a case study soon demonstrating this. In the meantime, have fun experimenting with these shortcuts.