A Rather Excellent Recruiter’s Guide To Spreadsheets

Spreadsheets. Hardly the most rock n roll subject, I know. But bear with.

We all use spreadsheets. But should we? And are we breaking the law by doing so?

Recruiters use spreadsheets for two reasons. One: for processing data. GDPR will tell you this has to be done accurately, minimally, and accessibly.

Technically, spreadsheets are pretty poor at all three.

Recruiters also use Excel to track outcomes. Handy, though most CRMs do this automatically.

And an unpopular yet undeniable truth: some recruiters are just a bit rubbish at Excel.

So why do something that could compromise European data laws, that your CRM already does, rubbishly?

Because we like being in control of our work. We like visualising the moving parts in what we do. And we’re good at taking something ordinary and making it useful.

Spreadsheets are fundamentally very dull things used by finance people and admin bods. But even heavy users only scratch the surface of what Excel can do.

So here’s a couple of really easy things you can do to earn yourself the title of ‘Person Who’s Good With Spreadsheets’ around the office.

spreadsheets 4

Colour scheme

If it’s a document you’re going to spend a lot of time looking at, you need to make it easy on the eye. White cells and black text is clear, but boring. And it’s easy to get lost if you’re working across large workbooks.

Primary colours can be jarring and using several looks rank. Soft pastel-y tones are aesthetically pleasing and clear. You’ll also find it’s not as profoundly soul destroying working on a document that at least looks OK.

Conditional formatting

If you don’t use this already, it automatically applies formatting to cells depending on their contents. It gives data at-a-glance readability which is useful when you’re tracking financial targets.

Select the cells you want to format. Then choose how you want them to look. Design cells differently for high numbers, low numbers, and everything in between.

This applies to percentages, dates, and text. The default 3-colour scales are traffic light colours. So opening up a spreadsheet that’s all green for the month is a good place to be.

conditional formats

You can also add individual icons or progress bars to cells. Aside from looking like the world’s most basic front-end developer, visualising your KPIs like this can keep you motivated during those ‘close but not quite’ months.

Rotate rows and columns

You can change the layout of your spreadsheet by having your rows become columns (and vice versa). Simply copy everything you want to move, hit ‘Paste special’ and check the ‘Transpose’ box.

Functions

These are actually way easier to use than they seem. Use the Insert function button within Excel to pick one from a preset list, filter by category or just type in what you need it to do.

Some require an advanced understanding of algebra to get right. Most simply automate certain calculations, like finding totals, averages, min and max values.

You’ll find an alphabetical list of commands here.

PivotTables

If you’re an important recruiter (ie: you’re allowed out the office to interact with clients unsupervised) you’ll know how vital it is to pitch well. And tracking your metrics gives you a bank of reliable data to pull from.

PivotTables present spreadsheet information in a user-friendly way. These examples use household expenses to demonstrate how data changes into something client-facing:

pivottablechart

Use this to audit your clients, show off your stats or illustrate KPIs ahead of a performance review. To insert a PivotTable, highlight a batch of data and pick the criteria you want to highlight from the menu.

Then go to PivotTable Tools > Analyze > PivotChart to turn it into a fancy chart.

Waterfall charts

These serve little other purpose than visualising increases and decreases better than regular charts. Inserting a waterfall chart is as easy as highlighting your data and choosing it from the menu.

Double click on a data point on the chart, or right click on a row or column of numbers, to access a formatting menu that lets you connect the end of one column to the beginning of the next.

Sparklines

Another visualisation trick, sparklines are miniature line graphs. To add one, you simply highlight the data you want to plot, go to Insert > Sparklines > Lines and pick the cell you want to display in.

From here, you can play around with the design in the menu. And because the sparkline sits in the background of the cell, you can add text over the top of it.

spreadsheets 3

Forecasting

2019’s been a belter. You can’t remember being under 6 figures for the year. Your seat on the top billers trip’s already booked and business is brilliant. So where do you go from here?

Tracking your revenue allows you to create a forecast based on historical data. You only need two things: a column of dates/times and their corresponding figures.

On the Data tab, in the Forecast group, click Forecast Sheet. You can pick between line or column charts and set an end date for your forecast. Hit create and if you want to get really technical, there’s an options menu that lets you tinker with all kinds of variables and stats.

Macros

Macros allow you to code your own functions to automate repetitive tasks, saving you time. If this article hasn’t left you wanting a frontal lobotomy, you might give this a go.

First, you have to get into the Developer tab. Go to File > Options > Customize Ribbon > Main Tabs, then check the ‘Developer’ box.

From here, click ‘Record Macro’ in the ‘Code’ section of the Developer tab. Then simply perform the actions you wish to automate and click ‘Stop Recording’ when you’re done.

Excel replicates your actions to create programmable tasks you can run from the Developer tab whenever you need to.

If you spend a lot of time in spreadsheets – doing the same things over and over – and you really don’t want to, macros are a clever way out.

spreadsheets

Going further

Microsoft Excel was first released in 1987. In that time, millions of nerds have found it within themselves to share their tips online. Myself included.

I’ve linked to the Excel Help Centre about 47 times in this article, and it’s a great resource to tap into. Googling a query usually serves up some decent insight as well.

But the key to being good with spreadsheets is to experiment until it does what you want it to.

Until then: excelsior!