Excel and Google spreadsheet tips

I intend to make some notes on Excel here, as I'm not very good at it so far!

Summarise a column with a categorical variable
If you have a column with names (eg. 'new', 'old', 'ancient') in Excel (eg. column A1:A20) and then want to summarise how many you have of each value, you can get the number of 'new' values by typing in another cell: =SUMIF(A1:A20, 'new')

Count number of cells in a column that contain certain text
eg. count cells in a column R1:R37017 that contain the text 'GO::0006508':
=COUNTIF(R1:R37017,"*GO:0006508*")

Google spreadsheet tips

Fill down
Select the original cell and cells below it, then press CTRL+d.

If statement
This has format: IF(test, value to take if test is true, value to take if test is false)
For example:
=IF(D240>F240,C240,E240)

Colour cells in a column
You can use conditional formatting rules. Select the cells you want to format, then go to Format menu->Conditional formatting, and type in the rule. For example, I wanted to colour the cells in a column (values in H3:H255) with a green background if they were equal to the values in cells D3:D255. I used the formula '=D3:D55' (with 'is equal to') for this.

Adding a new column with ranks of another column
To add a column with ranks of values in another column, in the 2nd column put for example in cell B3: =RANK(A3,$A$3:$A$55) to give the rank of cell A3 in column A3:A55.

Sorting columns by values in a particular column
Select the columns that you want to sort, including headers. Then go to Data->Sort range, and click the box to say your columns have headers, and choose the column that you want to sort by. [I did a little check, and hidden columns are sorted too.]