Tips for using formulas in MS Excel

Formulas in MS Excel provide a powerful tool for doing mathematical calculations. Once you get used to using formulas, it’s often quicker to use a spreadsheet than a calculator. In addition, if you save your formulas in a spreadsheet, you can repeat calculations on different data sets very quickly and easily.

To start a formula in a spreadsheet, type "=" (without the quotation marks) into a cell.

Once you've started your formula, you have to be very precise about what you type. Any mistake in the formula (such as a stray period or comma or a missing letter) will result in an error. You also need to be careful about clicking with the mouse as that can change a formula.

To add use "+", to subtract use "", to multiply use "*", to divide use "/".

You can type numbers in your formula to do simple calculations. If you type in "=100/2" and then hit the return button, you should see "50" in that cell of the spreadsheet.

If you want to display the formula rather than the calculated value, hold down the ctrl and the ` button (the ` button is on the upper left side of the keyboard). Displaying formulas can be very helpful in checking that they are entered correctly.

If you want to perform calculations on data entered in other cells, you can refer to them by code using the letter corresponding to the column heading and the number corresponding to the row heading. For example, to add the values in the first two rows of the first column of a spreadsheet, you could type in "=A1+A2".

A quick way to enter the value of a cell into a formula is to click on that cell with the mouse.

If you have a formula entered at the bottom of a column of numbers and you want the same formula entered in the bottom of another similar column of numbers, you can copy and paste the formula from one cell to another (the shortcuts for copy and paste are holding down the "ctrl" and "c" buttons, and holding down the "ctrl" and "v" buttons.) The new formula should be automatically adjusted to correspond to the new column if no dollar signs ($) appear in the formula. So in the example below, copying the formula at the bottom of column 1 and pasting it into the bottom of column 2 should give the following results (Excel automatically adjusted the formulas  it changed the As to Bs):

If you want to keep the values for a cell constant rather than having them change when you copy and paste them to a new cell, use "$" in the formula. For example, if you type in "=$A$2", then copy and paste that formula into a new cell anywhere in the spreadsheet, it will remain "=$A$2".

If you want to refer to a range of numbers, you can enter the first cell code, a colon, then the last cell coded. For example "A1:A10" refers to all cells in column A from rows 1 to 10.

MS Excel has many built in functions that perform calculations. For example, "=AVERAGE(A1:A10)" will calculate the mean value for the data entered in column A rows 1 through 10.

If you click on the fx button on the tool bar at the top of the spreadsheet, you can get a list of functions included in MS Excel.

This spreadsheet gives an example of some formulas that can be used to calculate summary statistics.
©2014 EcologyAndEvolution.Org, All rights reserved.