Quiz night scoresheet in Excel with an html twist

Table Quiz - Results

My version was based on this Table Quiz Helper.  This spreadsheet uses conditional formatting to change the colour of the Total Cells but I was frustrated by few things.

  1. There was no provision for jokers.
  2. Even when I ran Excel full screen there was still some clutter on the screen.

Here is what I did for a quiz night which raised funds for Smile4Wessex:

Jokers

I found a font set which had a Joker symbol in it called, you guessed it “Joker from FontRiver“. You will have to install the font first (Google how to do it on your operating system). I used that for the jokers in my joker column.  I copied the header joker to the row of the teams when they played their joker.

I also created a cell with white text and maroon background. I used the font painter to copy this to the cells in which a joker was played.  Round 6 was Geography!

Displaying the scores with no clutter and freezing them

I can reduce the clutter in Excel to a minimum by:

  • View ribbon – untick Formula Bar, heading, Gridlines
  • Going Full Screen – I added the Toggle Full Screen View button to the Quick Access Toolbar. Press Esc to close full screen.
  • But this still leaves your live spreadsheet exposed to the audience.

I shared a snapshot of my spreadsheet by:

  • Saving it in html file format.
  • Opening the html file in a browser and running that full screen normally by pressing F11.
  • I set up my PC in extended mode with the live spreadsheet on my desktop and the browser running in the extended monitor/projector. I could also run PowerPoint in Presenter mode from my desktop displaying on the projector.
  • Every time you update and save the spreadsheet, select the browser by using Alt-Tab or your mouse and press F5 to refresh the scores.
  • I could safely edit the spreadsheet without the audience watching me and conceal the scores until they had all been submitted at the end of each round.

Sorting

I wanted to sort the table to put the highest scores at the top. Here is the process to I used during the quiz:

  • Set Sort up to sort the rows by the Total column and sort.
  • Press Ctrl-Z to undo the sort and return to Table order.
  • Right click the Sort tool on the Home ribbon and add to the Quick Access Toolbar.
  • Press Sort Tool button on the QAT to order Total.
  • Save to html
  • Press Ctrl-Z to return to table order.

A Marker for the Leader

I also inserted a column with ₩ symbol to represent the leader/winner. It uses this formula in each cell =IF(N4=N$19,”₩”,””) or in English: if the Total for this team (N4) equals the maximum total (N19) they are the winner.

And finally I changed the colours in the Conditional Formating

They go from blue through orange to red for the leader(s).

Final Final Tip

Save the spreadsheet in Excel format when you finish the quiz.

Download my nearly empty quiz sheet

Leave a Reply

Your email address will not be published. Required fields are marked *