5 MS Excel tricks for Scoring

1. Get Grades using Lookup  

=LOOKUP(B2,$J$2:$J$5,$K$2:$K$5)

Use LOOKUP, one of the lookup and reference functions, when you need to look in a single row or column and find a value from the same position in a second row or column. For example, let's say you know the part number for an auto part, but you don't know the price. Documentation can be found here.

To freeze the reference table using the key F4.

Demo

2. Convert a cell into a Bar chart

=REPT("|",B2)

Type the Font name 'Playbill'. Common use can be for exploratory analysis.

Demo

3. Use Emojis for Rating

=REPT("⭐",C2)

Open the emoji's dialog box with the shortcut Win key and ; (semi-colon)

Demo

4. Get rid of blanks

Press Ctrl-A 

Then Press F5 

Press Ctrl 9

Demo

5. Trace your Participants

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Interior.ColorIndex = xlColorIndexNone
    Target.EntireColumn.Interior.ColorIndex = 19
    Target.EntireRow.Interior.ColorIndex = 19
    Target.Interior.ColorIndex = xlColorIndexNone
End Sub

Save the workbook as an "Excel macro-enabled workbook".

Demo

The number 19 in the above code is the colour of the active row and column. You may change it to the colour of your choice. So if you change it to 20 it will show light blue, 3 will make it red, 7 will make it pink so on.  

Colour Mapping