Ah, hair, the nightmare of selecting a person and pasting him somewhere else realistically. I can zoom in, use mask or lasso and do it hair by hair, but that would be too tedious. Here in this tutorial is probably the best method I’ve encountered so far in extracting a person from the background, with the added bonus you get to practice with some obscure tools and settings.
I was given the task to give passwords and usernames to 185 kids. I said, “Easy. Do you have an official list of students?” Luckily, they had a list in Excel. So I immediately went to work.
I copied the list of students to another worksheet (I try NEVER to work on originals, so if I make a mistake, I can always open the original list). Last Names in one column then First Names. So the log-in name of the student will be the first letter of their given name then their entire family name. If their name is John Smith, the log-in name will be jsmith.
But to do it for 185 kids? One by one? Getouttahere! Excel can do it for you!
- Arrange the given names as the first column, then the family or surnames as the second column. Type in the function CONCATENATE (meaning, combine text from two cell references).
- It will look like this: =LOWER(CONCATENATE(LEFT(A1,1),B1))
- The LOWER function makes the entire text lower case.
- If I simply typed in =CONCATENATE(A1,B1) the result will be JohnSmith.
- That extra little command, LEFT(A1,1) tells Excel to check the text in A1, get only ONE character from the left (that’s what the “1” means).
- Reducing John Smith into jsmith!
What if you have brothers and sisters who have the same first letters in their names, like John and Jane Smith? They’ll be both jsmith!
- Select the Home Tab, then Conditional Formatting/Highlight Cell Rules/Duplicate Values.
- It will format any duplicates in red letters, making it easy to see which log-in names are duplicated and in need of changing.
For passwords, I was instructed to make it an 8 character alphanumeric password with an uppercase letter. Let’s say I want
- An 8 character password with numbers as the last two characters.
- Randomly capitalized letter in the word.
Online, I found several lists of common six-letter words (six because the last two are supposed to be numbers.
I copied and pasted the entire list to Excel (Select the list, Ctrl C, go to Excel, Ctrl V). Unfortunately, the list was numbered from 1 to 1,631. I had to separate the numbers from the words.
- Select the column with the mixed up words and numbers.
- Click on the Data Tab, click on Text to Columns, select Delimited then click on Next.
- Make sure there is a check mark on “Space” among the delimiters. This will separate the numbers from the text. Click ok. Delete the column with numbers.
- Select 185 of easy to remember words. This one I had to do manually!
Now, I want to combine the six-letter words and the two-digit numbers AND I want to randomly capitalize a letter in the word (not necessarily the first letter). How do you do that?
- In the column besides the word, type =RANDBETWEEN(1,6). This will generate a random number from 1 to 6 (that’s the number of letters the word has).
- In the column besides that random number, type this jawbreaking equation:
- The random number (from 1 to 6) will determine which letter will be capitalized.
- CONCATENATE will combine
-the letters to the left of the capitalized letter: that’s the LEFT (A1,B1-1) in the formula above.
-the capitalized letter itself: UPPER(MID(A1,B1,1)
-and the letters to the right of the capitalized letter: MID(A1,B1+1,6-B1)
- Try it! The letters will randomly capitalized like some kind of cool animation! Only works with 6-letter words, though.
The two digit numbers
- Generate a column of 185 random, two digit numbers. Since there are 185 of them, obviously some numbers will be repeated. Use =RANDBETWEEN(10,99) to create a column of 185 random two-digit numbers, copy the numbers (Ctrl C) and paste them (use paste special, Ctrl Alt V, select “Values”.) besides the six letter words with the random capitals.
- Again, use concatenate to combine the randomly capitalized six-letter words and the two-digit numbers and you have a set of passwords that are hard to guess yet fairly easy to remember!
- Copy the list and paste it besides their log-in names. I suggest you use Paste Special/Values so that the capitalizations won’t change every time you open the Excel file.
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.
- Adding the choreography, member participation and execution scores is easy. Just type in the scores in the respective grey boxes
- Then type =SUM(B6:B8) into cell B9 to add it up.
- 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.
- 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!