|
Sadly, I have a lot of recent, in-depth experience with unemployment. While that does not make me an expert on getting re-employed, I do have a lot of experience with the attempt, and I've reflected on it to the point of saturation. So I have a sense of familiarity with the topic.
In case you're tempted to have an emotional reaction to the things I am about to say based on your own knowledge and skill with the job search process, please understand that I am not here to compete with the experts. I am not reporting on years of empirical research at Carnegie Mellon, nor am I revealing a Secret Recipe for Job Search Success derived from 25 years of deep reflection in the salt mines of recruiting. Instead, I am taking a confessional approach to the process of finding a job. I also explain how I used Microsoft Excel to keep track of the endless information I accumulated during my quest for gainful employment.
Need a Job? Get a Workbook
Even we amateur job-seekers know that to some degree you need to keep track of your comings and goings, or you may as well forget your future as a taxpayer. You need to keep track of the people you've talked to and what you said to them, especially when you need to make a follow-up call. You need to keep track of the companies you've talked to, what you've talked to them about, and when you need to talk to them again. You need to know whether you sent a thank-you note to the person who interviewed you last week, and if not, to whom you need to send it, and how late it already is. Depending on the state you live in, the law may even require you to keep track of some of these details.
Excel likes to keep track of data, so I recruited it to help me. After a year of collaboration, I have to say it did a good job. So pull up a chair, and I'll explain the twisted path we took down Employment Lane.
Lists, Lists, Lists
I learned quickly that my job search was nothing if not a pulsating nest of lists. I had ever-changing lists of companies, contacts, recruiters, phone calls, letters, meetings, groups, locations, and to-do's. At first I kept information about each of these areas in separate places (mainly in Outlook, Word documents, and a Palm handheld). And because of rules that the State of Illinois imposes on the unemployed, I even kept some information in paper forms. However, I soon realized that I needed to put all of this information in one place.
I settled on a single Excel workbook. Let me first explain the concept behind the structure I used, and then I'll walk you through the process of creating it.
The Tabs
I divided the workbook into five tabs: Communications, People, Companies, Locations, and Picklists. The first four represent the lists that I considered most important, with the nerve center of them all, Communications, at the front. I placed utility lists in the last tab called Picklists.
Communications
The Communications tab is where I captured all of my activities. The "experts" say that the success of a job search depends on the number of communications you initiate, so that's what I recorded. I found that all of my communications could be classified into seven types: phone calls (the ones in which you actually talk to a person), voice mail messages, e-mail messages, networking meetings (face-to-face communication), postal letters, job applications, and interviews. So I created a worksheet that could capture them all.
For each communication, I captured 11 details: date, time, name of the person, the company it concerned, the topic, the essential details of the conversation, the type (phone call, e-mail, etc.), whether it required follow up, the date I should follow up, the next step (future or past), and whether it concerned a job lead. To expedite data-entry, I used pick lists for five of these details, which I'll explain a little later.
People
The People tab is where I captured all of the essential contact information I needed to monitor. This list contained the names of the people I met and talked with over the course of my search. I found it most helpful to capture, in separate columns, the person's first name, last name, full name, source (the person or place I learned of this person), job title, employer, phone number, and e-mail address.
Why did I create this list when I already had a contact management system in my Palm handheld? There were several reasons. First, many of the names were new to me; I did not already have the information elsewhere, so I wasn't duplicating it. Second, the list represented a log of all the people who had some tangible, direct link to my job search; there were no job-search strangers in it. Third, it kept names out of my regular database that I was unlikely ever to need again--recruiters from far off lands whom I'd rather forget, random HR heads, friends of a friend, and so on. Fourth, it was lean; I did not have a bloated set of fields to sift through when all I really needed were these essential ones. And fifth, the data I already had was truly elsewhere, not in this one place, and I needed a single source. For these reasons, I did not pre-populate this list; I simply built it up as I talked to people, and it was free of the clutter of people who were not a part of my search.
Companies
The Companies tab is where I captured the names of all the companies that I targeted, spoke to, or considered. For each one, I kept track of its name and its location. I kept a short and a long version of the name (where applicable) so I could remember what it was really called without having to represent it that way everywhere I used it. For example, I shortened International Truck and Engine to International; I used the short version in my Company pick list on the Communications tab.
I did not keep address, telephone, or e-mail information on this tab because I simply didn't find it useful to do so. But there's no reason you couldn't do it that way if you wished.
Locations
The Locations tab is where I kept track of all the cites and towns I needed to think about; for me, that meant places to which I might have to commute, but it could also mean the distance you'd have to move if you were open to relocating. This list is simple: for each location, I kept data on just the city name and the distance to it from my home. I used this list as a pick list for the Companies list mentioned above.
Picklists
The Picklists tab served as a holding place for utility lists. I had two: types of communications, and a yes/no toggle. I used both of these in my Communications tab. (For information on creating pick lists in Excel, see How Excel Taught Me to Cook in the Fall 2006 edition of Computor Companion.)
I can imagine this tab being fertile ground for any lists you might want to reuse. For example, perhaps you want to keep track of the desirability of each company you are considering; you could put your rating scale here on the Picklists tab, and then refer to it in a column on the Companies tab. Or perhaps you attend several different networking groups and want to keep track of them in your Communications tab; you could list them here. Take advantage of the fact that this worksheet can accommodate as many lists as you want to create and use. Just don't go overboard, because the more you try to keep track of, the harder it is to maintain.
Creating the Workbook
To create a job-search workbook like this should take about 30 minutes. Start by creating a Workbook that has five tabs. Name them Communications, People, Companies, Locations, and Picklists. We'll build out each of these worksheets by working from back to front, to facilitate the data links we need to make.
The Picklists tab
To create the Picklists tab, we'll create two lists and give them names. Follow these steps:
1. Select the Picklists tab and create two lists in column A, like this:
2. Select cells A2 through A8.
3. Choose Insert, Name, Define.... The Define Name dialog box should appear.
4. In the Names in workbook textbox, type CommType, then click OK.
5. Select cells A11 and A12.
6. Choose Insert, Name, Define.... The Define Name dialog box should appear.
7. In the Names in workbook textbox, type YesNo, then click OK.
That's it. You'll be using these pick lists elsewhere in the workbook, so let's move on to the next tab.
The Locations tab
To create the next piece of the puzzle, you need to list some of the places you want to work, or are likely to find work. Follow these steps:
1. Select the Locations tab and enter a list of cities and towns and their distance from home, like this:
Because your list is going to evolve as your search progresses, I suggest that you start with the shortest list you can get away with. I happen to live in a huge metropolitan area, the continent of Chicagoland, so this list could be hundreds of rows long if I tried to get exhaustive with it. Rather than do that, I just started with Chicago, Wheaton, and a few others. The list grew as I kept up the search. To get the distance in miles from home, I used Mapquest. (Of course, if you're Canadian or just smarter than I am, use kilometers instead of miles. If you need the levity, use feet or millimeters. Whatever it takes.)
2. Select the cells in the A column that have a location in them; in the example above, that would be A2 through A15.
3. Using the range-naming technique already described (Insert, Name, Define...), name this range Location.
Tip: Try to include a legitimate A or W city, because items that you add to the very top and very bottom of this list will not automatically become part of the range. That's not the end of the world, but it does cause an inconvenience insofar as you have to redefine the range to include the new item. To make it easier to avoid that problem, bracket the list with location names from the beginning and end of the alphabet. (In the example, it's Bensenville and Wood Dale.) You may need to settle for W or Y for the tail end, unless commuting to a town that starts with an X or a Z is a live possibility for you. Any locations you add to the middle of the list will automatically be picked up by the named range.
Next we'll create the Companies tab.
The Companies tab
To create your list of target companies, follow these steps:
1. Select the Companies tab and name the A, B, and C columns Name, Full Name, and Location, like this:
2. Populate the Name column with the names of companies that you'd like to work for, leading off with (n/a) and (blind) in cells A2 and A3, like so:
3. Select cells C2 through C100 (or at least as many rows as you think you'll need).
4. Choose Data, Validation.... The Data Validation dialog box should appear.
5. In the Settings tab of the Data Validation dialog box, select List from the Allow drop-down. In the Source box, type "=Location", without the quotes, and choose OK.
Your Location column is now linked to your list of Locations. You can select the location of each potential employer from the drop-down.
6. Select cells A2 through the end of your list; do not select any blank cells.
7. Using the range-naming technique already described (Insert, Name, Define...), name this range Company.
Your Companies list is now available as a named range.
The People tab
Now select the People tab, where you'll capture the names of networking contacts, recruiters, and others you need to keep track of.
1. In the first row of columns A through H, enter these field names: First Name, Last Name, Full Name, Source, Title, Employer, Phone, and E-mail, like this:
2. Enter (HR/Recruiter) and (Reception) in cells A2 and A3, like so:
3. Select cell C2, enter the formula =(A4&" "&B4), and hit Enter. As soon as you hit Enter, cell C2 should look exactly like cell A2, like this:
4. Select cells C2 through C300 (or at least as many rows as you think you'll need; I ended up with exactly 250 names in my list), and hit Ctrl-D. This will replicate the formula in C2 to all of the selected cells below it.
5. Enter the first and last names of a few of your networking contacts in the First Name and Last Name fields. (The Full Name cell will fill in automatically by itself.) Remember to start with your mother:
6. Select cells C2 through the end of your list; do not select any blank cells.
7. Using the range-naming technique already described (Insert, Name, Define...), name this range Name.
Your list of People is now available as a named range.
You can handle the sorting of this list however you want, but I found it worked well to enter the records alphabetically by first name. When I thought of someone I wanted to call or write, I didn't think of the person's last name, I thought of the person's first name, so that's how I arranged this list. Each time I entered a new name, I just inserted it in the appropriate spot.
A few words about the other columns:
- Source: Where I got this person's name. If I met the person at a networking group, I entered the initials of the group (WEN, ESN, BCC, etc.). If I already knew the person, I entered n/a. If someone in particular had introduced us, I entered that person's name.
- Title: The person's job title, if you know it.
- Employer: Who the person works for. I did not link this to my Companies list, because many of the people I talked to didn't work for those companies. But I still wanted to know where they worked.
- Phone and E-mail: I entered the one number and address that I was most likely to want to use for that person. I didn't worry about being exhaustive with multiples of these; if for some reason I needed to keep multiples, I made a more extensive entry in my Palm handheld as well.
That wraps up the People tab. Now on to the Mother of All Job Search Worksheets.
The Communications tab
This is where I spent some time every day of my search. To set yours up, follow these steps:
1. In the first row of columns A through K, enter these field names: Date, Time, Name, Company, Topic, Notes, Type, Requires Follow-up, Date to Follow Up, Next Step or Action to Take, and Concerns Job Lead, like this:
2. Format the Date column the way you want the dates to appear:
- a. Select cells A2 through A750 (or as many rows as you think you'll need; you can always expand it later).
- b. Choose Format, Cells..., and choose an appropriate date format on the Number tab.
3. Repeat Step 2 for the Time column.
4. Connect the Name column to your People list:
- Select cells C2 through C750.
- Choose Data, Validation.... The Data Validation dialog box should appear.
- In the Settings tab of the Data Validation dialog box, select List from the Allow drop-down. In the Source box, type "=Name", without the quotes, and choose OK.
5. Connect the Company column to your Companies list:
- Select cells D2 through D750.
- Choose Data, Validation.... The Data Validation dialog box should appear.
- In the Settings tab of the Data Validation dialog box, select List from the Allow drop-down. In the Source box, type "=Company", without the quotes, and choose OK.
6. Connect the Type column to your CommType list:
- Select cells G2 through G750.
- Choose Data, Validation.... The Data Validation dialog box should appear.
- In the Settings tab of the Data Validation dialog box, select List from the Allow drop-down. In the Source box, type "=CommType", without the quotes, and choose OK.
7. Connect the Requires Follow-up column to your YesNo list:
- Select cells H2 through H750.
- Choose Data, Validation.... The Data Validation dialog box should appear.
- In the Settings tab of the Data Validation dialog box, select List from the Allow drop-down. In the Source box, type "=YesNo", without the quotes, and choose OK.
8. Repeat Step 7 for column K, Concerns Job Lead.
9. Apply any other formatting that will aid readability for you. For me, that meant setting all cells (except the header cells) to Left-aligned, Top-aligned, and Word-wrapped.
With all that done, you should have something that resembles the table below. Here's an excerpt from my own log, with apologies for the hacks I've made to make it fit:
By the way, by the time I found a job, my Communications tab had 788 rows in it. It was incomplete, too, as I didn't decide to be thorough about this till midway through the process. You're not quite done yet, though. There is one more important step to take.
10. Set an AutoFilter on the header row:
- Select any cell in Row 1.
- Choose Data, Filter, AutoFilter.
Your header row should look something like this:
If you don't take that last step, you forfeit a simple but power benefit of putting all this information into Excel in the first place. The AutoFilter lets you put several handy moves on this list.
1. First, it keeps the header row always visible, no matter how long your list gets and no matter far you scroll down it.
2. Second, and more importantly, it lets you filter the list like a filtering freak. Here are just a few examples:
- Filter out all communications earlier than the previous month, so you're looking only at what happened most recently. As my job search weeks turned in to months, I found it helpful to filter out the older rows. This was easy to do by setting a Custom filter on the Date column.
- Filter for all the communications that require follow-up. I used this often, to make sure I was staying on top of this crucial activity.
- Filter for all communications related to a particular company. I found this handy when trying to determine whether I was giving my favorite targets enough attention.
- Filter for all your meetings, so see if you're going to networking meetings as often as you should. That's a little cheap and easy self-policing.
- Filter for voice mails, to see how often you're having to leave messages.
- Filter for a particular person's name, to analyze the history of your communication with that person. This really helped when I wanted to know when I last communicated with someone in particular, and what that conversation was about.
- Combine filters from several columns, such as filtering for all the communications that require follow-up and are overdue.
- And so on.
If you're not sure how to work the filtering, see the AutoFilter topics in online Help. It'll explain better than I could.
Conclusion
That's it. You should now be ready to beat your job search into submission, without the legal fees. And if you need to keep track of something else, or don't need to keep track of something I kept track of, just change it.
If I had to do it again, would I use this workbook? No doubt. It is not perfect or pretty, nor does it make recordkeeping fun. But I found it a serviceable way to get that chore done. And the named ranges, pick lists, and AutoFilters streamlined the process and gave me access to bits and pieces of information that otherwise would have been difficult to get.
Of course, I'm hoping I never need it again. But now I'm prepared, just in case.
|
