Searching for a job in today’s market is an often complex process. Without the appropriate organizational strategies, hunting for a job can quickly become a complicated mess. Combing thousands of job results across multiple websites and emailing hundreds of resumes can quickly become confusing.
Maintaining an organized job search is essential for job seekers looking to land a new career opportunity. Organizing potential jobs based on category, scraping the web to create a filterable results list, and automating emails can simplify the process. Fortunately, Excel comes with several features that can help manage your hunt and keep your applications in order.
1. Organizing Jobs by Category in Excel
If your job hunt requires you to search through more than one type of industry, keeping them sorted by category is imperative. Sending the wrong resume to an opening could mean failing to get an interview.
For each position to which you are applying, add it to an ongoing list in an Excel spreadsheet. List the position title, the company, the job category, and your point of contact within the company if you have one.
With the positions categorized, you can do several things to ensure that you don’t make any missteps with further applications. When it comes to applying to more than one type of job, you need to have a resume and cover letter for each.
You can either pre-create them and have copies waiting to be sent out, or you can use Excel’s SUBSTITUTE function to automatically generate them.
If you have multiple copies of your resume and cover letter premade, you can make accessing the right one at the right time simple. A few nested IF functions, or a simple SWITCH function, can ensure you aren’t sending out the wrong copies.
Using a function like the one above, you can pull links for resumes stored on another sheet. Excel makes it easy to make a single template however and then use Excel’s SUBSTITUTE function to do the heavy lifting.
Nesting multiple SUBSTITUTE functions into a single formula allows you to quickly and easily create form communications. Chaining multiple SUBSTITUTES and IF or SWITCH conditionals can transform a template or series of templates into introductory or follow-up emails.
Excel can take a series of templates like the ones below, and convert them into a complete, ready-to-send email.
Using a quick function, you can combine each of these templates into a single, cohesive cover letter.
Each cell uses a set of simple substitutions like the one below from G2 to create the text:
Here, the contact name for the position is stored in C2. You can also use conditionals to blend different templates depending on the category of each position, or specific words in the title of the role.
The formatting in the end product can be achieved using the CHAR(10) function to produce line breaks. You can wrap all the paragraphs in a CONCAT function to produce a finished email.
2. Using Excel to Parse Scraped Web Data
Most job sites will allow you to filter positions with a few basic options, for example, based on title, location, and skills. Applying extra filters to those results can help you make the most out of every application.
Excel’s functions make it easy to apply advanced filtering to a list of potential job results. You can automatically remove any job with a salary below a specific threshold, or include only jobs requiring specific skills. Excel will allow you to apply as many filters as you want to a dataset to get the most relevant results.
The first thing you’ll need is a list of jobs from the various sites you are trying to search through. The quickest way to get that list is using a web-scraping Google Chrome extension or application. There are plenty of great options available online that are quick to set up and user-friendly.
If you’re looking for a free option, Google Sheets offers a built-in IMPORTXML function that can be used to scrape web data.
Once you’ve pulled down a dataset, you’ll need to import it into Excel. Excel includes a built-in XML importer that can make pulling in large scraped datasets simple. Use the import functionality made available after you've unlocked this hidden Developer tab on the ribbon.
As you’re importing, you’ll be prompted to set up your new dataset. For the best results, try to normalize your data as much as possible during the import. Make sure that the same data from each site ends up in the same column, preferably with the same format.
Once your data is fully imported, you can use Excel’s filter functionality to quickly sort through your list of prospects. This will give you fine-grained control over your search results, allowing you to apply to only the most relevant positions.
3. Automatically Send Communications From Excel
In today's job market, it's impossible to land a job without being able to send out hundreds of emails. Being able to automate the process is essential. Generating form communication is a great first step. Your best option, however, is to send emails out directly from Excel.
There are two different ways to accomplish this in Excel. The more complex way is using VBA to create a macro that can send emails from Excel. The simplest way to do this, however, is using Excel’s built-in HYPERLINK function.
HYPERLINK allows users to create links that will open their default mail application, and prepopulate a message. The function can fill in the send-to email, the subject, the body of the email, and, if necessary, a CC for the email.
Creating an automatic email link is easy to achieve with a function such as the one below:
The function will create a link in Excel that, when clicked, opens up your email client with the entire email you need to send already populated. All that will be left for you to do will be to click the send button.
Using the HYPERLINK function, and the automatically generated correspondence from above, you can easily send out bulk emails.
Excel Is the Ideal Way to Keep Your Job Hunt Organized
Excel’s tools can keep your job hunt organized and increase the chances of your applications reaching the right hands. From quickly customizing form communications for each position to automatically sending out emails, Excel can easily assist with your job hunt.
It can keep your applications organized and filter potential job openings to ensure that you are only applying to the most applicable positions with the most targeted version of your resume.