Institutional Research & Planning Blog

Home » Tech Tips

Category Archives: Tech Tips

Tech Tip 2 for Excel 2010

Tech Tip 2 for Excel 2010

There are times when you need to use the auto sum feature for a column of numbers but the auto sum button is not on the particular tab you are working with.  The following keystroke method is quick and easy to use.  The only requirement is that there are no blank cells in the column.

In the example below, you would just need to select cell A19, Hold down the Alt key and then hit the = key.  Now hit Enter and the column will be summed.

Tip 2 Picture

Advertisements

Tech Tip 1 for Excel 2010: Using the COUNTIF function

by Mike Wilson

In this example we want to know the frequency of ratings in cells A15 through A34.  In a small sample it is not a problem but with hundreds or even thousands of lines of data it can be quite cumbersome.  Using the COUNTIF function and a couple of simple Excel tricks this can be accomplished rather quickly.  (A pivot table can also accomplish this very easily.)

Click the Tip 1 worksheet link next to the sample worksheet below and you can walk through the following steps.

In the B10 cell type the following =COUNTIF(

When your cursor is positioned next to the left parenthesis above, move your pointer to the A15 cell and left click in that cell.  Now hold the Ctrl and Shift keys and tap the down arrow. You will notice that all the cells from A15 through A34 are now selected.  Next tap the F4 key and you will notice that the selected cell range is locked. ($A$15:$A$A34) If you tap the F4 again you can watch the cell locking change.  Now that the cell range is selected, put a comma after A34. Next you want to move the pointer to the A10 cell and left click it, then hold the Ctrl key and hit Enter which will keep the cursor in the B10 cell and perform the function.  This will give you a frequency of 9 which is how many people found the exhibition “Very Helpful” in this example.  Dragging the formula from B10 to B12 will complete the frequency count for each of the ratings.

Tip 1 PictureTip 1 worksheet