Five Excel tips for data-frazzled students that I wish I had known…

Your undergraduate dissertation is testing enough, even more so if you’ve decided to tackle a masters thesis or research degree! The first months of my MSc research project were spent were spent from 8am till 6pm in the lab, aka the “fish dungeon”, stressing over whether I’d cross-contaminated salmon fry #45 with salmon fry #54. These hours (whilst others were having BBQs and beach sunbathing) were then followed by trying to accumulate all these microsatellite peaks into Excel to get some sort of meaningful statistics until I crawled into bed at 1 am, and tried desperately to shut off my ticking brain (try the Headspace app if you are struggling). Hours of sleep were few and far between, sacrificing that hour-long relaxing bath to have a quick shower and catch up on the Great British Bake-off was an easy choice, and socialising was almost non-existent by the end of the four months.


And thus, this is my gift to you, to those tackling excel that would like to save yourself some time, reduce the excel-induced rage and allow for one pint at the pub.

My five little Excel gems that would have been oh so useful for my degrees if I had known…

1 . Conditional formatting

This (to be frank bloomin’ brilliant) hidden nugget can literally highlight crucial results before your very eyes. You can show whether cells values are greater or lower than others/your ideal value, you can compare lists, you can create alternating row colours to help focus on your screen (after 8 hours of staring at excel you will thank me) and I think best of all… you can highlight mistakes, errors and repetitions.


2. Pivot Tables

I only learnt about these recently, and they have changed my life. You have a huge dataset with tons of data and it’s all just a bit too much to try and swallow. Cue, this nifty button called pivot tables.


3. Transpose

Your table is full of data, but it’s not displayed the way you want it, you want the columns where the rows are and visa versa (it would be really useful for the specific scientific table format). Rather than spending time retyping it all out, you can do this nifty trick called transpose. How to: Select the data you want, copy, go to cell you want to put your new table in, right click, paste options = transpose, and you’re sorted. Honestly, you would not realise how many times this has been used since I found out about it!


4. View side by side/arrange all

View two (or more if you’re daring) spreadsheets side by side at the same time. Now most people will know this, but I think it is so unbelievably useful that it’s worth making the cut in this post. Two ways of doing this: view side by side (if two files open) or arrange all (you can have more files open, and specify layout a bit more with this).


5. Freeze panes

Sick of scrolling endlessly to and from the first column to the 56th column of data to see which value matched which sample number? Freeze the section you need to keep referring back to!


These tips may be well known and I was just late to the party, but if it saves even one tired undergraduate an hour to go have a lie down, it’s worth it!

Excel wizards and warlocks, if you have any more tips, please feel free to comment below!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s