Brushing Up On Your Excel Skills: Part One
Microsoft Excel can be a really powerful, useful tool for certain kinds of data processing and record keeping, and the chances are you probably don’t even know how to use half of the functions it comes with! That’s OK, personally, I find Excel a bit less user-friendly than Word, but also it’s a programme I never used in school (showing my age here!). I only really started using it when I began my PhD – and even at that, there were many occasions where I would stubbornly sit with a notebook and calculator and work out things the long way rather than spending the time trying to figure out how to do it more efficiently in Excel. Eventually I realised that I needed to bite the bullet and just do a course to refresh some of the basics and also to learn about the some of the advanced functions that I didn’t know existed – hopefully you find a few useful tips or tricks in here.
Back to Basics
Let’s start at the beginning with a quick refresher on formulae and how to use them.
One of the main features of Excel is its ability to do calculations, both basic and advanced, to save you the trouble of doing them manually. When you want Excel to do a calculation for you, click on the cell you want your answer in and enter an equals sign to let it know that this cell is to contain a calculation not a name or a single value.
Excel contains numerous pre-programmed functions that you can use in your calculations (we’ll discuss these next time), but first, in order to get your functions to function, you need to make sure you’re referencing your cells correctly.
Each cell in Excel has a unique reference ID consisting of the row and column identifiers, e.g. A4 or X345. If you want to incorporate a particular cell into your calculation, you can either manually type this into the formula bar or once you have entered your equals sign into the formula bar you can click on the cell you’re referencing. Now that you’ve entered your cell number, there are two ways to reference a cell in a calculation – absolute referencing and relative referencing.
Let’s start with relative referencing. This is for when you want to use a different value for each calculation; for example, if you have a group of samples that you want to compare with a standard curve. For this scenario, you’re going to create a formula using your first sample, but you’ll want Excel to use the same formula with a different value for each of the other samples. This is really straightforward. All you need to do is type the cell reference as it is, e.g. A6, then when you paste the calculation down your sheet, Excel will move the formula down with it, so the next row will use the same formula but the calculation will use A7 instead of A6. Simple!
Absolute referencing on the other hand is for when you want to use the same value in each calculation, for example the value for your blank that you need to subtract from all other cells. Absolute referencing is also easy; you just need to enter a dollar sign before the row and column number to ensure that the formula uses the same cell for all the calculations, e.g. $A$6. You can do this by either manually typing it into the formula bar or by clicking on the cell and while it is selected, pressing F4.
Your reference can also be a mixture of both absolute and relative referencing, putting a dollar sign ahead of just the row or column number will prevent the formula from moving in that direction, which can be useful if you’re working with slightly trickier calculations.
The Fill Handle
Now that we’ve got referencing down, I want to introduce you to my new friend, the fill handle!
The fill handle is the slightly thicker green corner you see when you select a box. It’s a little bit magical when you figure out how to use it! By left clicking on, and dragging the fill handle down (or up) you will fill all subsequent cells with whatever is in your selected cell – this can be quicker than copying and pasting formulae or values, especially if you’re working with a larger data set! There are other useful properties to the fill handle that I’ll talk about another time, but for now all you need is to know the basics.
Creating a Formula
The key things to remember when creating a formula in Excel are:
- Start with an =
- Always check your brackets are closed at the end!
We’ve already mentioned that all calculations need to start with an = to let Excel know that it is a calculation and not a name, but it’s also important to make sure your formulae are complete; if you’ve used brackets, make sure they’re closed at the end!
Basic calculations are easy to set up;
- Import or type your values in to Excel
- Select the cell that you want your answer in and type =
- Figure out what you want Excel to do and write it in the form of an equation. For example: we would like to know the volume of RNA in μl required to yield 10 μg RNA per tube. We then want to dilute to a total volume of 50 μl so that all tubes are at the same concentration and the same volume.
- Equation 1: How many μls of RNA are required? Divide the desired concentration by the concentration in 1 μl
Equation 2: How much water needs to be added to bring final volume to 50 μl? Subtract the volume of RNA you added from 50
- Copy this formula into your other boxes by dragging the fill handle down
- If you look at the equation for any of your filled in boxes, you will see that the values C4 and D4 have updated themselves to match the row that you are in – this is because we used relative referencing!
Now that we’ve seen an easy example, how about something a little more challenging?
Given the values below, you need to normalise all your samples relative to your wild-type control value:
Rather than setting up lots of individual equations, we can use a fairly simple one to do all our calculations at once
- Select your cell and enter =(B3/$A3) – we’re using a mixed reference here because we want the column to remain constant, but the row number to change for each row.
Use the fill handle to drag your equation across and down to fill in your other values and easy peasy! You’ve saved time by not doing a separate calculation for each row.
Sometimes mixed referencing can get a little confusing, and at times like that, it’s handy to trace your formula to see what is moving and what is not.
- Click on the formulas tab
- Select the cell you’re unsure of and click “trace precedents” – arrows will appear directing you to the cells that are used in the calculation
- Select as many cells as you want and add precedent for them
- When you’re finished you can simply hit “remove arrows” and they’ll disappear
That’s all for part one. Next time we’ll try using built-in functions and go through a practical example of how you can put all of this together to do calculations in the lab.
What are your useful Excel tricks? Share them below in the comments!
*All screenshots taken from Microsoft Excel 2013 for Windows.
Leave a Comment
You must be logged in to post a comment.
[…] brushed up on our referencing, constructing calculations and basic functions, let’s move on to some intermediate functions that […]
(not sure if these work on Macs)
The best excel trick I know I found out by accident and it has saved me tons of time. It has to do with moving contents from one place to another. Select a cell/group of cells. Now move the mouse so that the cursor is over the edge of the cell and the cursor will change to a four-way arrow. Click and drag to move the contents. This even works for moving whole columns and rows. Click the number/letter at the left/top to highlight the whole row/column. Now the fun part: if you hold shift while you move the contents, instead of replacing what was there before, you will insert the cells where an I- or H-shaped bracket indicates. Moving while holding shift can re-order things much easier than cut-and-paste or dragging to replace empty cells.
Another way to use the fill handle is to double-click it. It will automatically fill down as far as the column adjacent to it goes. (Only works for filling down–not up, left, or right)
I use the F2 key to show what cells are being used in a formula. F2 and clicking inside the formula bar do the similar things: They start “edit mode” of the formula, which also color codes the cells. The name of the cell in the formula and the highlight box around the cell will be the same color.
Another button I like for large data sets is F8, which toggles “extend selection” mode. To select a really long column, you don’t need to click and drag. Select the top cell (just click and release, no need to hold the button down), then hit F8. Now you can use the mouse, keyboard, scroll bars, etc. to move around to the bottom. Now click on the last cell, and the cells between the first and last one will all be selected. Hit F8 again to toggle off “extend selection” mode.
If you click on the cell, so that you see the formula (instead of value), the implemented cells should color without pressing F2.
Also, instead of pressing F8 during selection, just click on the first cell, then scroll to the place, hold Shift and click on the last cell. This works for rows, lines as well as blocks. If you hold Ctrl, you will select only the cell you click on. These selections actually work everywhere, even in the Explorer for selecting files etc.