3 Ways to Organize Your Job Search With Excel Spreadsheets

Search­ing for a job in today’s mar­ket is an often com­plex process. With­out the appro­pri­ate orga­ni­za­tion­al strate­gies, hunt­ing for a job can quick­ly become a com­pli­cat­ed mess. Comb­ing thou­sands of job results across mul­ti­ple web­sites and email­ing hun­dreds of resumes can quick­ly become con­fus­ing.

Main­tain­ing an orga­nized job search is essen­tial for job seek­ers look­ing to a new career oppor­tu­ni­ty. Orga­niz­ing poten­tial jobs based on cat­e­go­ry, scrap­ing the web to cre­ate a fil­ter­able results list, and automat­ing emails can sim­pli­fy the process. For­tu­nate­ly, Excel comes with sev­er­al fea­tures can help man­age your hunt and keep your appli­ca­tions in order.


1. Organizing Jobs by Category in Excel

If your job hunt requires you to search through more than one type of indus­try, keep­ing them sort­ed by cat­e­go­ry is imper­a­tive. Send­ing the wrong resume to an open­ing could mean fail­ing to get an inter­view.

For each posi­tion to which you are apply­ing, add it to an ongo­ing list in an Excel spread­sheet. List the posi­tion title, the com­pa­ny, the job cat­e­go­ry, and your point of con­tact with­in the com­pa­ny if you have one.

With the posi­tions cat­e­go­rized, you can do sev­er­al to ensure that you don’t make any mis­steps with fur­ther appli­ca­tions. When it comes to apply­ing to more than one type of job, you need to have a resume and cov­er let­ter for each.

You can either pre-cre­ate them and have copies wait­ing to be sent out, or you can use Excel’s SUBSTITUTE func­tion to auto­mat­i­cal­ly gen­er­ate them.

If you have mul­ti­ple copies of your resume and cov­er let­ter pre­made, you can make access­ing the right one at the right sim­ple. A few nest­ed IF , or a sim­ple SWITCH func­tion, can ensure you aren’t send­ing out the wrong copies.

 =SWITCH(F2, "Networking", ResumeList!A1, "Development", ResumeList!A2, "Support", ResumeList!A3, "General IT", ResumeList!A4, ResumeList!A1) 

Using a func­tion like the one above, you can pull links for resumes stored on anoth­er sheet. Excel makes it easy to make a sin­gle tem­plate how­ev­er and then use Excel’s SUBSTITUTE func­tion to do the lift­ing.

Nest­ing mul­ti­ple SUBSTITUTE func­tions into a sin­gle for­mu­la allows you to quick­ly and eas­i­ly cre­ate com­mu­ni­ca­tions. Chain­ing mul­ti­ple SUBSTITUTES and IF or SWITCH con­di­tion­als can trans­form a tem­plate or series of tem­plates into intro­duc­to­ry or fol­low-up emails.

Excel can take a series of tem­plates like the ones below, and con­vert them into a com­plete, ready-to-send email.

A set of templates for different pieces of a basic cover letter arranged in Excel.

Using a quick func­tion, you can com­bine each of these tem­plates into a sin­gle, cohe­sive cov­er let­ter.

A set of paragraphs made out of data in Excel that are combined together into a standard cover letter.

Each cell uses a set of sim­ple sub­sti­tu­tions like the one below from G2 to cre­ate the text:

 =SUBSTITUTE(Sheet4!A1, "{ contactname }", C2) 

Here, the con­tact name for the posi­tion is stored in C2. You can also use con­di­tion­als to blend dif­fer­ent tem­plates depend­ing on the cat­e­go­ry of each posi­tion, or spe­cif­ic words in the title of the role.

The for­mat­ting in the end prod­uct can be achieved using the CHAR(10) func­tion to pro­duce line breaks. You can wrap all the para­graphs in a CONCAT func­tion to pro­duce a fin­ished email:

 =CONCAT(G2, CHAR(10), CHAR(10), H2, CHAR(10), I2, CHAR(10), J2, CHAR(10), CHAR(10), Sheet4!A5, CHAR(10), Sheet4!A6) 

2. Using Excel to Parse Scraped Web Data

Most job sites will allow you to fil­ter posi­tions with a few basic options, for exam­ple, based on title, loca­tion, and skills. Apply­ing extra fil­ters to those results can help you make the most out of every appli­ca­tion.

Excel’s func­tions make it easy to apply advanced fil­ter­ing to a list of poten­tial job results. You can auto­mat­i­cal­ly remove any job with a salary below a spe­cif­ic thresh­old, or include only jobs requir­ing spe­cif­ic skills. Excel will allow you to apply as many fil­ters as you want to a dataset to get the most rel­e­vant results.

The first thing you’ll need is a list of jobs from the var­i­ous sites you are try­ing to search through. The quick­est way to get that list is using a web-scrap­ing Google Chrome exten­sion or appli­ca­tion. There are plen­ty of great options avail­able online that are quick to set up and user-friend­ly.

If you’re look­ing for a free option, Google Sheets offers a built-in IMPORTXML func­tion 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 sim­ple. Use the import func­tion­al­i­ty made avail­able after you’ve unlocked this hid­den Devel­op­er tab on the rib­bon.

As you’re import­ing, you’ll be prompt­ed to set up your new dataset. For the best results, try to nor­mal­ize your data as much as pos­si­ble dur­ing the import. Make sure that the same data from each ends up in the same col­umn, prefer­ably with the same for­mat.

The options for the XML Import Wizard in Excel.

Once your data is ful­ly import­ed, you can use Excel’s fil­ter func­tion­al­i­ty to quick­ly sort through your list of prospects. This will give you fine-grained con­trol over your search results, allow­ing you to apply to only the most rel­e­vant posi­tions.

A data table in Excel containing the results of an XML import.

3. Automatically Send Communications From Excel

In today’s job mar­ket, it’s impos­si­ble to land a job with­out being able to send out hun­dreds of emails. Being able to auto­mate the process is essen­tial. Gen­er­at­ing form com­mu­ni­ca­tion is a great first step. Your best option, how­ev­er, is to send emails out direct­ly from Excel.

There are two dif­fer­ent ways to accom­plish this in Excel. The more com­plex way is using VBA to cre­ate a macro that can send emails from Excel. The sim­plest way to do this, how­ev­er, is using Excel’s built-in HYPERLINK func­tion.

HYPERLINK allows users to cre­ate links that will open their default mail appli­ca­tion, and pre­pop­u­late a mes­sage. The func­tion can fill in the send-to email, the sub­ject, the body of the email, and, if nec­es­sary, a CC for the email.

an auto­mat­ic email link is easy to achieve with a func­tion such as the one below:

 =HYPERLINK("mailto:" & A2 & "?subject=" & E2 & "&body=" & F2, "Send Email") 
A set of job search results with an automatically generated link at the end of each to send out an introductory email.

The func­tion will cre­ate a link in Excel that, when clicked, opens up your email client with the entire email you need to send already pop­u­lat­ed. All that will be left for you to do will be to click the send but­ton.

Using the HYPERLINK func­tion, and the auto­mat­i­cal­ly gen­er­at­ed cor­re­spon­dence from above, you can eas­i­ly send out bulk emails.

Excel Is the Ideal Way to Keep Your Job Hunt Organized

Excel’s tools can keep your job hunt orga­nized and increase the chances of your appli­ca­tions reach­ing the right hands. From quick­ly cus­tomiz­ing form com­mu­ni­ca­tions for each posi­tion to auto­mat­i­cal­ly send­ing out emails, Excel can eas­i­ly assist with your job hunt.

It can keep your appli­ca­tions orga­nized and fil­ter poten­tial job open­ings to ensure that you are only apply­ing to the most applic­a­ble posi­tions with the most tar­get­ed ver­sion of your resume.

Read More

Leave a Comment