Excel
Using Excel to Manage Grades | Tips & Shortcuts
EXCEL 2007 SHORTCUTS
(UNLESS OTHERWISE NOTED YOU ARE WITHIN A WORKSHEET OF A WORKBOOK.)
1. To change from a Relative to an Absolute Reference (or vice versa) within a formatted cell(s) highlight the formula in the formula bar then use the F4 key as a toggle.
To type an Absolute reference preface the column and the cell address with a dollar sign, e.g., =C14/$A$10 . If this formula is copied to other cells the C14 will change but the A10 will be constant.
2. Realigning, changing the number of decimals, etc. only changes how the number is displayed and printed, it DOES NOT change the number itself. The number '60' may appear in a cell but the typed-in value may be '59.9998723. If the cell is highlighted this number can be found at the top of the screen in the formula bar (unless this displays a formula). To change number of decimals highlight the column or cell(s), right click, select Format Cells -> Number, then change the number of decimals. Click OK.
3. To center a title over multiple columns highlight all the columns you need (cells must be blank) then either:
To unmerge the cells click Format Cells -> Alignment -> uncheck the Merge Cells box -> OK. (The area you want to unmerge must be highlighted.) Or, on the keyboard Ctrl + z to undo if this was the last change you made.
4. To print your spreadsheet with gridlines click the Page Layout tab -> Gridlines, put a checkmark in Print. Printing gridlines is not the default in Excel 2007.
5. You can create a histogram from a column (or row) of scores.
First, check to see if you have a Data Analysis group under the Data tab. If you don't find this group you will need to "Add-in" the Analysis ToolPak. (Earlier versions of Excel did not require this.)
Under the main Office menu (top left corner) click on the Excel Options button. In the left-hand column click Add-Ins. Click OK. You should now see the Data Analysis group.
You will need to make a column of "break points" for your histogram. If you have room just use the same worksheet, if not, you can put it on another one. See the example below.
You can resize the histogram chart by clicking within the chart to bring up the sizing boxes, then just click and drag the boxes to make the chart any size you want.
| Scores | Breaks | Bin | Frequency |
| ||||||||
10 | 6 | 6 | 1 | |||||||||
8 | 7 | 7 | 1 | |||||||||
8 | 8 | 8 | 3 | |||||||||
9 | 9 | 9 | 1 | |||||||||
7 | 10 | 10 | 2 | |||||||||
10 | 11 | 11 | 0 | |||||||||
8 | More | 0 | ||||||||||
6 | ||||||||||||
6. If you use the MIN or MINA functions to find the minimum value in a range of cells and you have a blank cell within the range the functions will ignore the blank and find the minimum of the values.
5 3 4 4 3 2 5 =min(a1:h1) would = 2 not 0
(This is probably NOT what you want to do. See the "To fill in..." below for replacing blanks with 0s. You may need to do this if you are dropping a grade.)
MINA can be used if you have cells with blank in them (it will assume that the blanks are 0).
5 3 4 4 3 2 5 =mina(a1:h1) would = 0
7. To fill in a region of a spreadsheet containing some blank cells with a constant number (0 in the example below) do the following:
If you want to fill blank cells with characters do steps 1-3 above then type in the word with a leading single quote mark, e.g., 'Fall then hit CRTL + Enter. This is different from instructions for Excel95 version.
8. If you want to drop a score from a series of scores you first need to find the minimum value in the series. You can use the MIN or MINA function to put that value into a new column. Then just sum up all the scores in the series (Columns A, B, and C below) and subtract the minimum value (Column D). Put this formula in Column E Row 1. It is, in this case, =SUM(A2:C2)-D2 . This formula can then be copied into the rest of the column. If you want you can omit creating a new column for the minimum and put the combined function into a cell: =SUM(A2:C2)-MINA(A2:C2).
| Row | Col A | Col B | Col C | Col D | Col E |
| 1 | Scores | Scores | Scores | Minumum | Result |
| 2 | 10 | 6 | 7 | 6 | 17 |
| 3 | 8 | 7 | 2 | 2 | 15 |
| 4 | 8 | 3 | N/A | 0 | 11 |
| 5 | 9 | 0 | 3 | 0 | 12 |
| 6 | 5 | 4 | 4 | 5 |
Note the error in Row 6 because B6 was left blank and not filled with a 0 or N/A before the MINA function was used. Also, if you use =A2+B2+C2 instead of =SUM(A2:C2) you will get a #VALUE error for Row 6.
An alternate method is to use the function SMALL to find the smallest number in a range. It can also be used to find the next smallest, etc. LARGE can be used to find the largest number in a range.
An example of the SMALL function used with Row 6 above is: =small(a6:c6,1) where "1" is the first smallest number. The answer is 4.
9. If you want to replace the formula in cells with the value produced by the formula do this:
You can UNDO this but after you save the changes you can never get the formulas back.
This may be something you will need to do if you want to use the summing formulas and you have #N/A placed in cells from a formula. The summing and averaging formulas won't work with #N/A. However, you can use the SUMIF function to ignore #N/As
10. Information about the FREQUENCY function:
(1) If you have any #N/As in the column of numbers (input array) you won't get any results except #N/A in all the results array. In the example below you want the frequencies for the numbers in Column A.
(2) Blank cells in the column of numbers will be ignored and not counted up.
(3) Type your breaks in an empty column. Make sure you have enough break points. Numbers are not "rounded" up or down. A list of breaks, such as 10, 20, 30 really means to collect the numbers into the following groups: 0-10, 11-20, 21-30, and 31+ (This is what the extra cell is for in the output array.) The breaks are listed in Column B.
| Row | Col A | Col B | Col C | Col C After |
| 2 | 9 | 5 | # | 2 |
| 3 | 10 | 6 | # | 0 |
| 4 | 8 | 7 | # | 0 |
| 5 | 8 | 8 | # | 2 |
| 6 | 9 | 9 | # | 2 |
| 7 | 5 | 10 | # | 1 |
| 8 | 5 | # | 0 |
Here are the steps after you have decided on your input array and have typed in a breaks array.
The a2:a8 refers to the column of input numbers. (input array)
The b2:b7 refers to the column of break points + 1. (bin array)
Instead of hitting Enter you need to use the combination keys of CTRL + Shift + Enter. This is how you hand enter an array into a function, otherwise the cell references will increment when you move from cell to cell in your output array. (a2:a8 will become a3:a9, etc.
You may have a FREQUENCY function window pop up where you can type in the input and bin arrays (or just highlight them). Don't hit OK or Enter. You still need to use the CTRL + Shift + Enter keys if you have not highlighted the column where the frequency array will go. (If you just hit OK the frequencies will be wrong.)
11. To eliminate the first 5 columns of a ID# (for posting purposes) use the MOD function. This function divides a given number and outputs just the remainder.
Example: 904157985 in cell A1
Result: 7985 when using =MOD(A1,10000) in cell A5
12. Use the SUMIF function instead of the SUM function when you have #N/As in some of the cells. You specify the range of cells you want to sum and the criteria to be used:
A1 B1 C1 D1 E1
3 #N/A 5 8 =SUMIF(A1:D1,">0")
This will produce the answer 16 in E1 without having to remove any #N/As from the file. Make sure to use double quotes to surround the criteria. The example above will test to make sure that the value in each cell is both numeric and greater than zero. You can change this if you wish to include negative numbers. The function SUMIF can contain 3 options: range, criteria, and sum range but you usually omit the last one.
You can specify a group of cells to add even if they are not in a contiguous range.
=SUM(A1, C1, H2) This will work even if some of the cell contents are missing.
13. Quote marks: The curly kind (smart quotes) won't work in formulas (if you copied a formula from WORD). Use only the straight kind. You can turn them off in WORD in 2 places. In the Menu click on Format -> AutoFormat -> Options -> AutoCorrect.
14. If you are trying to match numbers (using VLOOKUP, etc.) and it doesn't work it could be that one of the columns was put in as Text instead of Numbers or General. If so, you need to convert the text-style to numeric. The way to do this is to first copy the column (A) into another column (say F). Then use the formula =f2*1 or whatever to convert the character in f2 into a number. Drag the formula down the column.
15. If you want to merge 2 columns, say first name (column A) and last name (column B), use the following formula in column C:
=A1&" "&B1 The " " puts a space between the contents of the two columns. Drag the lower right corner down to automatically fill the rest of the column with the formula. You can delete columns A & B if you want. You won't find directions for this under "merge"; it's under "consolidation".
16. If you have a column of "text" numbers** and want "real" numbers do the following:
(1) In a blank column (same row as original "text" number) enter this formula: =VALUE(Cell#)
Example: Cell A2 contains 0288 (text), formula goes in C2: =value(a2)
(This may not work if the 1st column has text numbers. You will need to highlight Column B and change the Format to Number first.)
(2) If you have lots of cells to convert drag the fill handle down to copy the formula into the rest of Column C.
(3) Now you need to remove the formula from the cells in Column C. Highlight all the cells in Column C. Under Edit, click Copy. Under Edit click Paste Special and select Values. Click OK.
Now you're left with just numbers in the cells. If you need to match this number using VLOOKUP then Column C needs to be in the Column A position. Add a new blank column in front of current Column A and then Copy & Paste the numbers into it (or just make the blank "newA" column first then proceed with the formula).
**You can tell "text" numbers from "real" numbers by the way they look in a cell. Text numbers are left justified, real numbers are right justified.