Making a List of Words (either Alphabetical or by number of letters) in Excel 2007

How to make an alphabetized list of words in MS Excel 2007:

  1. Type in the words or Paste in the words (select the list, when highlighted, type Ctrl C to copy and go to Excel, press Ctrl V to paste).
  2. IF the words are in one line rather than a column, or if when you paste them there are several words in one cell, you can break up the words into columns. Here’s how:
    1. Select the cells with multiple words separated by spaces.
    2. Go to the Data Tab/Data Tools click on “Text to Columns”.
    3. Select “Delimited”. Click on “Next”.
    4. Click on “Space”  in the Delimiter box. Click on “Finish”.
  3. Once you have the words, you can drag them into one column.
    1. To select several words, just click on a cell and drag (your cursor will be a white cross, the selected cells will have a thick black outline with a black square dot in the lower right corner.)
    2. To move selected cells, put your cursor over the thick black outline. It should turn into a black cross with arrows pointing in four directions. Click and drag your selection to where you want it.
  4. Select the entire column by clicking on the letter above your word list.
  5. To arrange it alphabetically:
    1. Select the “Data” Tab, and click on “Sort” in the “Sort & Filter” box.
    2. The sort box will come out
    3. Click “Ok” and it will sort for you alphabetically.
    4. If you want to sort the students by family names, select  the two columns for the first names and family names.
    5. Select the “Data” Tab, and click on “Sort” in the “Sort & Filter” box.
    6. The sort box will come out. Select for sorting, Column B (assuming that’s the family name)
    7. Click on “Add Level” and select Column A
    8. So it will sort by family names first then first names second.
  6. To arrange by number of letters:
    1. Type into the cell to the right of the first word on the list  =Len(  then click on your first word to the left of the formula and press “Enter” and the number of letters for the word comes up. After pressing “Enter”:
    2. To copy the formula cells to your entire list, select the cell with the formula, put your mouse over the lower right corner of the cell, right over the black dot. Your cursor should become a black cross. Click and drag until all the cells to the right of your list has the formula.
      From this,  it should look like this: 
      So besides each word on your list is the number of letters in the word.
    3. Select columns A and B (just click and drag from the top of Column A to B)
    4. Click on Data/Sort and the Sort dialogue box will open. Click on the arrow next to the box besides “Sort by” and select Column B.
    5. Click on “Add Level” and click on the arrow besides “Then by” and select Column A. Click Ok.
    6. Your list will be sorted. First by number of letters, then by first letters. So all the three-letter words will be arranged together, four-letter words, so on and so forth. Then all three letter words will be alphabetically arranged.
Advertisements

Scoring Sports Day in a Flash!

The school just had a Sports Day, successfully planned and executed, of course, but one nitpick was after an event, people wanted to know who won, and which house (Red, Green or Yellow) was in first place.

I knew it could be done easily using MS Excel, so I created a spreadsheet that can not only add up the scores and rank the houses, but also give a running account of who’s first at any point during Sports Day.

Here it is:

Note that right on top is the Overall Score and Standing. I froze the panes (select cell B5, View/Freeze Panes) so that the Overall Standing will always be visible.

The secret sauce is a RANK function and an IF function.

For instance, take the Cheer Competition.

  1. Adding the choreography, member participation and execution scores is easy. Just type in the scores in the respective grey boxes
  2. Then type =SUM(B6:B8) into cell B9 to add it up.
  3. Then in cell B10, type =RANK(B9,$B9:$D9,0).
    • That means, Excel will look at cell B9, compare it between cells B9, C9 and D9 and rank it from highest (a “1”) to lowest (a “3”). It will give the ranking in cell B10.
    • The dollar signs on the B and D means when I copy the formula from B9 to C9 and D9, the formula won’t produce an error. Try copy the formula from B10 to D10 without the dollar sign and see what happens.
    • And by the way, copying a formula is simply pointing at the lower-right corner of the box. When your cursor becomes a black cross, click and drag until D10. 
    • The “0” means that it will rank the highest score as 1. If you type “1” instead, the lowest score will be one.
  4. Next, the Sports organizers wanted to give 5 points to every third place win, 10 points to every second place win and 15 points to every first place win. So under the title Points Awarded, I put in the cell F14 the formula =VLOOKUP(B14,$F$4:$G$6,2).
    • It will look at cell B14, compare it to the array in F4:G6, compare it to the values in the first column and give back the value in the second column. If, for example, Red house is first place, VLOOKUP will check the array F4:G6 and give back the value 15.
    • Again, the dollar signs ensure that when I copy this formula to other cells, the B14 will change, but the lookup array F4 to G6 won’t.

So, Overall Score is simply a SUM function (adding all the scores in the column Points Awarded), and Overall Standing is the same RANK function.

However, when I first tried the spreadsheet, the entire table was full of #N/A errors. Apparently, if I still haven’t typed in any scores, the RANK function will give an error message. That means we won’t have a rank until the last score, and we won’t have a running account of who’s first place.

I had to change ALL the RANK and VLOOKUP tables. It now looks like this:

That means, if the cell B16 is 0 (or in this case, empty), then put in “ “ or nothing. Else, if there is a score, Rank it.

Funny enough, the VLOOKUP still returned #N/A errors even when I added the IF function. I had to change the formula to =IF(B17=””,””,VLOOKUP(B17,$F$4:$G$6,2)). That means, if B17 is empty (and not just 0), put in “” or nothing. Else, if there is a rank, perform the VLOOKUP.

The IF statements blanked out the whole spreadsheet when no scores have been written yet, and the #N/A errors given by the RANK and VLOOKUP disappeared.

Lastly, I formatted the cells where the judges should type scores as grey, with thicker black line borders. Then I right-clicked, selected Format Cell, selected the Protection Tab, and removed the check mark on the Locked box. Actually, ALL cells, by default, will have a check on the Locked box. This Lock protection will not work unless you enable Protect Sheet (Review/Protect Sheet). That way, judges can’t accidentally type over a formula or type scores into the wrong cells.

So try it yourself! And try figure out improvements over my worksheet!