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 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 look­ing to land 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 that can help man­age your hunt and keep your appli­ca­tions in order.

MAKEUSEOF VIDEO OF THE DAYSCROLL TO CONTINUE WITH CONTENT

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 with­in the com­pa­ny if you have one.

With the posi­tions cat­e­go­rized, you can do sev­er­al things 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 time . A few nest­ed IF func­tions, or a sim­ple SWITCH func­tion, can ensure you aren’t send­ing out the wrong copies.

 =SWITCH(F2, "Networking", ResumeList!A1, "", ResumeList!A2, "", 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 heavy 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 form 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 in the title of the role.

The for­mat­ting in the end 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 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 dur­ing the import. Make sure that the same data from each site 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.

Cre­at­ing 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