When I was applying to graduate school, I wish that someone had told me that it helps to either:

(a) have an extensive command of the intricacies of office software, or

(b) have a close friend who is an office wizard and willing to magnanimously spend their time and energy fixing your butchered documents.

For me, that person is my labmate Caroline, the Hermione Granger of office applications. I guess that makes me the Dobby. Or, even more accurately given my temperament, the Crookshanks.

In all fairness, I frequently walk around the lab making this exact face.

I often sit in lab, staring at my screen while becoming increasingly frustrated with  [insert any of my datasets here] before finally abandoning hope and collapsing dramatically with my head on my desk. At this point, Caroline patiently asks me what the trouble is, and then comes over to teach me how to set up guides in powerpoint, or hyperlink to another location in a word document, or how to use v-lookup in spreadsheets. However, not all of us have a Caroline, and so I am going to give you a series of Excel shortcuts that I use when setting up my data collection spreadsheets. These are little tricks that make life infinitely easier, especially if you’re working with lots of spreadsheets on a daily basis, namely autofitting columns, freezing panes, and using the filter tool. While these shortcuts may seem obvious to Excel adepts, it took me a few years to pick all of them up and incorporate them into my daily routine. So, without further ado:

I. AUTOFIT COLUMNS: The first thing I do when setting up a new spreadsheet is copy-paste in my column headers and bold them. However, column sizes don’t automatically transfer between datasheets, which makes it seem like you’re forced to go through the annoying process of resizing all the columns manually so you can read everything. Not the case! To easily sidestep this issue, all you need to do is:

1. Paste your column headers into your new spreadsheet. Notice that some columns in my sample spreadsheet, like E, H and K, have text partially hidden, while others, like O through S, are so wide that they are wasting space.

2. Go to Format –> Colums –> Autofit Selection

3. Revel in the neatness of your newly autofit columns. Congratulations on the start of a beautiful spreadsheet!

II. FREEZE PANES: While you’re no doubt extremely enthusiastic about your now OCD-compliant autofit columns, you may run into trouble if you’re entering many rows of data. I was always frustrated after getting past Row 37, the point at which your column headers scroll out of frame, because it’s easy to forget what all of the columns represent, forcing you to scroll back up to Row 1.

The problem – even though you can see the header for the first few rows of the sheet…

Your header row vanishes as soon as you scroll down far enough.

However, I  learned an Excel trick that remedies this problem: the Freeze Panes tool allows you to select a header row or rows, and keep them locked on top of the screen even while scrolling through other rows below. All you need to do is highlight the row UNDERNEATH the row(s) you want to act as your header, then go to Window –> Freeze Panes.

As you can see, you’re now able to scroll down in your spreadsheet as far as you want with your header rows still visible! If you ever want to reverse this, simply highlight your frozen row and then backtrack along your previous steps by going to Window –> Unfreeze Panes.

You can now scroll through your rows of data without ever losing your header row.

III. FILTER: Now that your spreadsheet is beautifully formatted and easy to navigate, you may want to isolate certain sections of your data. For example, let’s say you’ve become passionately obsessed with tarsal bones, and are consequently only interested in examining  the feet recovered from your cemetery. In this situation, you will want to:

1. Activate the filter tool by clicking the filter icon, indicated below. 

Activating the filter means that you can isolate specific types of data by using the drop-down menu in any of the columns.

2. In this case, because you only want to look at foot bones, you’d go to Column H “Anatomical Region”, uncheck  “Select All“, and then check the box next to the “Foot” identifier. This will give you a list of all of the rows that have the identifier “foot” in Column H.

As you can imagine, you can use the filter for any of your columns – for example, if you only wanted to look at bones that were sided as rights, or all bones that were examined on the 16th of June, you could do that too. The filter tool makes it very  easy to search through your data and isolate specific types of information. Importantly, the filter doesn’t delete all of the unchecked rows – to turn it off and have your spreadsheet appear as it did originally, simply click the filter icon a second time.

And that’s how you set up a spreadsheet, like a boss. If anyone’s interested I can do another Excel post in the future, covering a few simple formulae and and my favorite tool of all, the pivot table. Happy data entry!

Image Credits: Photo of Crookshanks found here.