Skip to Main Content

BSCI 1510L Literature and Stats Guide: 2 Basic operations in Excel

Introduction to Biological Sciences lab, first semester

2.1 Paste Values

If you copy a cell or range of cells, the contents of that cell will be pasted in whatever form they exist in the source cell.  If the source cell contains a number, the destination cell will contain a number with the same formatting.  If the source cell contains a formula, the destination cell will contain a formula with the cell references adjusted as described in sections 1.2 - 1.4.  However, sometimes you would like for the numbers generated by formulas to remain unchanged if adjustments are made to parts of the spreadsheet that feed into the formulas.  In that case, you can copy the source cell and use Paste Values to paste the actual numbers into the destination cells.  Paste values can be accessed from the Home ribbon by clicking on the drop-down arrow below Paste.  If you want to replace the formulas with the numbers they have generated, you can select the cells containing them, and Paste Values on top of the same cells.

2.2 Sorting

To sort by numbers (or alphabetize cells containing text), select the block of cells that you want to sort.  If there are header rows (column titles) above the data, include them in the selection.  On the Data ribbon, click on sort.  By default, Excel will perform a column sort, but if you want it to do a row sort, you can select that by clicking on the "Options…" button.  Excel will try to guess if you have header rows.  If it guesses wrong, check or uncheck the "My data has headers" box.  Select the appropriate "Sort by" column.  When Excel performs the sort, it will keep all of the values in a row within the selection together.

2.3 Cutting and deleting cells

Cutting and deleting cells are two of the most dangerous activities in Excel and are the easiest way to ruin a large and complex spreadsheet or to create an insidious error that is nearly impossible to locate.  It is much safer to copy cells or to clear the contents of a cell using the delete key.

2.3.1 Cutting vs. copying

There are several differences between Cut and Copy.  The most obvious is that when you use cut, the values disappear from the source cells.  However, there is also another important but less obvious difference.  Let's say that cell C1 contains the formula =A1+B1 .  If cell C1 is copied and pasted into cell C5, cell C5 will contain the formula  =A5+B5 .  However, if cell C1 is cut and pasted into cell C5, cell C5 will contain the formula  =A1+B1 .  In other words, when a cell is cut, the original cell references remain the same and are not adjusted relative to the position into which the paste occurs. 

There is a similar effect if one cuts a cell that is used as an argument in a formula.  In the previous example, if cell A1 were copied and pasted elsewhere on the spreadsheet, it would have no effect on the value of cell C1.  However, if cell A1 were cut and pasted into cell Z27, the formula in cell C1 would change to =Z27+B1 .   Again, cutting a cell maintains the original cell reference.

Empty cells may or may not be treated as a zero in formulas depending on what the formulas do.  Excel usually does a good job of guessing what will do the least damage under the circumstances, but in some cases (e.g. dividing by an empty cell) there is no hope for you.  So be careful.

2.3.2 Delete key vs. Delete cells

If you want to delete the contents of a cell or cells, highlight those cells and press the delete key.  The highlighted cells will be empty as they are after a cut operation, with the important difference that they are not available in the clipboard for pasting. 

If you want to actually eliminate cells entirely, highlight those cells.  On the Home ribbon, click on the drop-down arrow by Delete in the Cells group.  You will have a choice about how you want Excel to move cells around to fill in the vacuum that you are going to create when you delete the cells. 

See section 2.3.1 regarding whether Excel treats an empty cell as zero or not.

2.3.3 Eliminating cells that bound a range argument in a formula

Imagine that you have numbers in cells A1:A5 and in cell A6 you have the formula =MAX(A1:A5).  What happens if you cut or delete cell contents within that range or delete cells or rows within the range?  Even though Excel maintains references to particular cells during a cut and paste operation (see 2.3.1), if cell A2 is cut and pasted into cell B5, Excel will not consider cell B5 when evaluating the function over a range containing the cut cell.

For additional practice, see the Biointeractive tutorials on Autofill Data, Cell References, and Standard Deviation.