Excel for genomics
This tutorial could be downloaded as a PDF file (861.2 Kb).
Excel is a very common software in the biology labs, but its use to manipulate big data files is not as simple as it looks like. To avoid being lost when using Excel, here is a practical guide to use it efficiently. Be careful: it is easy to make mistakes in Excel and hard to detect them!
How to select columns:
If your table has long columns, select the first cell and thanks to the right side elevator, get down at the end of the table, press “shift” (the arrow key) and select the last cell of your column (the “shift” key is kept pressed). The whole column is selected.
You can apply the same recipe to copy, paste and apply a mathematical function on the whole column. Copy the first cell (you copy the function) and paste it on the last cell of the column with the “shift” key pressed. The function is then applied to each cell. An alternative method consists in double-clicking on the right bottom corner of the first cell, the function will then be applied to the following cells. In that case, verify the function has been copied all along the column.
How to use functions:
Manipulating microarray data implies a few calculation (logarithm transformation, median calculation...). These functions are implemented in Excel to make your work easier.
Access to the functions through the “Insertion” Menu:
Select the “Function” option, a dialog box containing the list of the implemented functions appears. They are sorted by categories (Mathematics, statistics, text functions...). Each function is documented so that you can have help on the way to use them, the needed parameters...
Once you have chosen the function you need, a guide helps you to apply the function on your spreadsheet.
How to anchor a cell value in a function:
To scale a microarray experiment result, each value has to be divided by the standard deviation of the distribution of all the experiment values. In that case, the first step consists in calculating the standard deviation of the column values, and then to divide each of these values by the result.
The function to apply to each cell of the column has to be identical, the standard deviation has to be set as a constant. To define a constant in Excel, just insert a “$” in front of the coordinate that need to be fixed:
In this example, the G35 cell value is divided by the cell E35, the standard deviation. If we copy and paste in the cells below, the standard deviation value remains the same thanks to the two “$” (E35 = E36) whereas the first cell will be replaced by G36, a new value.
How to copy the function result and not the function itself:
If you need to copy a column of results on a new spreadsheet, you cannot use a simple copy-paste manipulation. You will get in each cell the sign REF!#, because the calculation cannot be achieved in the new spreadsheet without the data source. To paste the values, you have to make a special paste. You will find this option in the “Edition” menu:
Select the “Special paste” option and choose “Values” in the dialog box: the values are dissociated from the function and are the only things you paste in the cells of the new spreadsheet.
How to sort data by columns:
The Excel sorting functions enable you to filter out the flagged spots, the saturating spots or the too weak intensity spots... First, select the whole spreadsheet to sort values by columns. Just a click on the spreadsheet top left corner, and the selection is done:
Once you have selected the whole set of cells, select the sorting option thanks to the “Data” menu:
The dialog box enables you to choose the columns you want to refer to during the sorting process and fix an order in case of a multiple sorting. If your table has a header, select it in the dialog box so that it is not mixed to the data during the sorting.
How to search and replace:
The available tools and Excel macros are often written with English standards. The float separator is a point whereas we use comma in continental Europe. According to the country you work in, you might need to change these separators to be compliant with your tools. You need to use the search and replace Excel function. Get it in the “Edition” menu, put what you search in the first field and the replacement character in the other field. Then, replace them all at once:
|This page is also available in french | Last page update: 9/7/2011 - 11:28|
For any questions or comments send an e-mail to the webmaster