I know some people would rather sit on hot coals with stinging nettles up their nostrils than entertain a spare thought about spreadsheets but bear with.
We all use them. But should we? And are we breaking the law by doing so?
Recruiters also use Excel to track outcomes. Handy, although most CRMs do this automatically.
And an unpopular opinion: some recruiters are a bit shit at Excel. So why do something that could compromise European data laws, that your CRM already does, shitly?
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 – like a blank workbook – and making it useful.
Spreadsheets are boring things used by finance people and admin heads. But even heavy users only scratch the surface of what Excel can do.
Here’s a couple of easy things you can use to earn yourself the title of ‘person who is good with spreadsheets’ in the office.
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 fine, but boring. And difficult to follow 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.
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.
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.
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.
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.
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:
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.
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.
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.
2018’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 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, and you really don’t want to, this is an easy way out.
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 one 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.
Senior Consultant, UX & Design – New York at Futureheads
Recruiters interested in working Retained Search at SPS International
Associate Director - Digital Tech New York at Twenty Recruitment
Senior Consultant - HR & Reward at Portfolio Group
Senior Consultant - Data & Analytics at Ultimate Asset Digital Recruitment
Senior Recruitment Consultant at Alchemy Recruitment Ltd
Consultant within our London team at Better Placed