Brushing up on your excel skills, part 3

Having brushed up on our referencing, constructing calculations and basic functions, let’s move on to some intermediate functions that could potentially save you time and hassle in the lab. First up, filling!

Filling

Filling is my new favourite useful function in Excel, one that admittedly I didn’t know existed until very recently, but I’m making up for it now!

We touched on filling last time when we re-acquainted (or acquainted in my case!) ourselves with the fill handle, which allows you to copy and paste formulae or values into other cells without having to copy and paste – but that’s not all you can do with filling, there are also some other useful ways to use this tool.

When filling, Excel can use pre-programmed or custom lists to fill in sheets quickly. For example, months of the year. You type in Jan, you click on the fill handle and you drag the selection down or across to fill in the next months.

Now I’m sure you’re wondering how filling in months of the year is going to save you time in the lab – don’t worry, I’m getting to that! Months is just a good example of a pre-programmed list, but you can also get Excel to create lists of numbers in the same way.

This time,

1. Right click the fill handle
2. Drag the selection down as far as needed
3. Select either “Growth” or “Linear” trend as appropriate

You can left click and drag too, this will give you a linear trend by default. This can be quite useful if you’re setting up a standard curve or a serial dilution.

You can also enter your own lists for Excel to work from, here’s how!

1. Type up and highlight your list
2. Go to “File” and select “Options”
3. Go to “Advanced”, scroll down to the “General section” and select “Edit custom lists”

4. Click “Import” (double check you’re importing the right values!)
5. “Add” your list and hit “OK”
   6. Pick a point in your list that you want to start with and drag the fill handle down for as many boxes as you need.
   7. You can also drag and fill upwards!

Flash Fill

This type of fill can be useful to extract a small amount of data from a cell with a long or complicated title – for example if you get a data file from a collaborator or a core facility and the names they have used are ridiculously long! It is important to be aware that there needs to be a perceivable pattern to the extraction for Excel to follow otherwise it will get a bit confused!

1. Type out the information you want to extract from the first entry, e.g. from row 1, we want a shorter title for our sample that tells us about the treatment, genotype and ID, in this case Control, Wild-Type and sample 1 of that group so we’ll use C WT1
2. Right click the fill handle and drag it to the bottom of the list
3. Select “Flash fill” from the menu
4. Feel just a wee bit smug at having figured out how to do that!

Text functions

Next up, some text functions these can be quite handy for tidying up a spreadsheet you set up in a hurry or got from someone else!

PROPER– Converts a text string to proper case

Useful if you want to get rid of SHOUTY CAPITALS!

1. In your new column, type =Proper(cell ref)
2. Use the filling tool to copy the formula through the other cells required

LOWER – Converts all letters in a string to lowercase

UPPER – Converts a string of text to uppercase

FIXED – Rounds a number to a specified number of decimal places.

This one is useful for when you end up with large numbers after decimal places. Fixed allows you to round off to the desired number of decimal places without having to shrink your columns!

1. Enter =Fixed(cell ref)
2. Determine how many places you’d like to round off to and enter that next to your cell reference
3. Enter true or false to allow or prevent commas
4. You final formula should look like: =fixed(G2,2,true)
5. Don’t forget your commas and to close your brackets when you finish!

SUBSTITUTE– Replaces part of a text string with another text string

This one is useful to make blanket changes on the sheet – for example if you’re reusing it for your next experiment and want to update the labels.

1. Type =substitute(
2. Insert the cell reference and a comma
3. Insert the text that is to be replaced in quotation marks followed by a comma
4. Type in the new text which is to be substituted, also in quotation marks
5. Close your brackets. Your formula should read:

=substitute(C2, “Exp 15”, “Exp 16”)

That’s it for part 3, don’t forget to leave a comment if you have any hints of your own that you want to share!

*All screenshots are taken from Microsoft Excel 2013 for windows