Excel
Using Excel to Manage Grades | Tips & Shortcuts
Excel Tips & Shortcuts
(Unless otherwise noted, you are within a worksheet of a workbook.)
If you are using a later version of Excel you may need to look up the equivalent commands online or in some other documentation. The procedures listed below work with Excel 97 and Excel 2000 and should give you a starting point. If you find that you are missing some of the data analysis functions, you will need to install them. Standard (default) installations of Excel do not contain them. Under Help, type in "install" for instructions.
1. To change from a Relative to an Absolute Reference (or vice versa) within a formatted cell use the F4 key as a toggle. To type an Absolute reference preface both parts of the cell address with a $, e.g., =C14/$A$10. If this formula is copied to other cells the C14 will change but the A10 will be constant.
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.
2. To change number of decimals use Format -> Column/whatever -> Number and change the number of decimals.
3. To center over multiple columns highlight all the columns you need then click on the Merge and Center Text button (looks like a box with an "a" in it).
4. To unmerge the cells click Format -> Cells -> Alignment -> uncheck the Merge Cells box -> OK. (The area you want to unmerge must be highlighted.)
5. To hide a column click on Format, point to Column, then click Hide. To unhide a column highlight the column letters on both sides of the hidden column. Click on Format -> Column -> Unhide. This works for Rows too.
6. To print your spreadsheet with gridlines click on File -> Page Setup. Click on the Sheet tab and under the Print section click on Gridlines.
You can create a histogram from a column (or row) of scores. You will need to make a column of "break points" for the histogram. If you have room just use the same worksheet, if not, you can put it on another one.
Click on Tools -> Data Analysis -> Histogram -> OK.
Type in the location of the Scores in the Input array: E2:E10
Type in the location of the "Break Points" (Bin array): H2:H7
Place a check mark in the Chart output box (to get a bar chart of the data).
If you want you can place the histogram on the same page as the data, just change the radio button from "New Worksheet" to "Output Range". Write the location in the space to the right: J2 (You just need the cell address of the upper-left corner.)
Do not check "Labels" if you have not included the column headers within your arrays.
Click OK.
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.
7. 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. (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 "N/A" in them (it will assume that the N/As are 0).
8. To fill in a region of a spreadsheet containing some blank cells with a constant number (0 in the example below) do the following:
Highlight the region containing the blank cells you want to change.
Edit -> GoTo -> Special -> Blanks -> OK
In the active blank cell type in =0 then hold down the CRTL + Enter keys. (If you just hit Enter it will fill only the active cell.)
Note that each of the filled cells has a "=0" in the formula bar.
Do the same if you want to fill cells with a non-number but instead of typing in =Fall in the cell type in ='Fall' using single quotes (double quote marks (") won't work here.
9. If you want to drop a score from a series of scores you first need to find the minimum value in the series. (See 7. above for this. You will 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).
Note the error in Row 6. Because B6 was left blank and not filled with either a 0 or N/A before the MINA function was used, the sum function returned a "5" instead of a "9". 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. If one of the values is N/A then the result will be #N/A. If one of the values is missing then the result will ignore the blank cell and pick the smallest of the remaining values. If you have both a missing value and a N/A within the series of numbers then the result will be #N/A. An example of the SMALL function used with Row 6 above is: =small(a6:c6,1) where "1" is the first smallest result.
10. If you want to replace the formula in cells with the value produced by the formula do this:
Select the cell or cells.
Under Edit, click on Copy.
Under Edit, click on Paste Special.
In the Paste section, click on Values.
Click on OK at the bottom.
Click on Escape to get rid of the box around the cells.
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.
11. Information about the FREQUENCY function:
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.
Blank cells in the column of numbers will be ignored and not counted up.
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.
After you have decided on your input array and have typed in a breaks array, here are the steps you'll need to:
Highlight cells in a blank column of your worksheet equal to the number of breaks + 1. (Must be in a column, not a row.) In the example above it would be C2 through C8.
Type in =frequency(a2:a8,b2:b7) in the top highlighted cell (C2) but DO NOT HIT Enter.
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 th
e column where the frequency array will go. (If you just hit OK the frequencies will be wrong.)
Drag the fill handle down the column to fill in the remaining frequencies.
12. To eliminate the first 5 columns of a ID number use the MOD function. This function divides a given number and outputs just the remainder.
Example: 904157985 is in cell A1, highlight cell A5
Result: 7985 when using =MOD(A1,10000) in cell A5
13. 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 critera to be used:
This will produce the answer 16 in E2 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(A2, C4, H5) This will work even if some of the cell contents are missing.
14. 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.
15. 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.
Another way to do this is:
In a blank column (same row as original "text" number) enter this formula: =VALUE(Cell#)
Example:
Cell A2 contains 0288 (text), formula that 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 cell format to Number first.)
If you have lots of cells to convert drag the fill handle down to copy the formula into the rest of Column C.
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.
16. 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".