Excel Spreadsheet Template For Pub Quiz League Table
If you run a quiz night with regular teams attending most weeks, keeping a running score in the form of a league leader board is a great way to add additional competitiveness and interest.
In this free Excel Spreadsheet template, are the basic workings of a league table. It adds up every point scored by any particular team over any period of time.
When we use the term “Demo”, the actual blank template is free to download as well. Demo simply means that I’ve populated it with mock teams and scores to let you see it working.
It is important to firstly download the Demo “already populated” workbook to see how this spreadsheet actually works.
The scoreboard system can also save you typing in team names into your excel scoreboard sheet every week as it uses a feature called “Defined Names”.
Working Demo Version & Instructions
DOWNLOAD LINK FOR DEMO POPULATED QUIZ LEAGUE TABLE
(often when launching an Excel sheet that has been downloaded, Excel will prompt you to enable editing. This sheet does not use or contain Macros so is fine to use)
Instructions for using the demo version to test it.
The workbook uses multiple sheets which have tabs down the bottom.
The sheet names are Names, DATA & WK6 which are in tabs at the bottom.
Let’s start with WK6 tab.
Change some scores and you will notice the live league score also changing. This is because using Defined Names, it finds that team from the main league scoreboard and adds it up.
The tab called WK6 can be renamed or copied and it’ll still work. It can be used live on the night.
Note: Scores are not sent from WK6 to DATA. However scores in totals in DATA are sent to WK or any copies you make of Wk6.
To enter a new week in DATA, go to DATA tab, right-click on column D and Insert.
A new column will be inserted ready for you to input this week’s scores. This can be done the following day after your quiz.
To clear WK6 and prepare it for WK7, you can copy it and clear data in blue and yellow columns.
In DATA tab, you can sort by Team name A-Z, Sort by Average Score Largest to Smallest or Sort by Total Largest to Smallest. To sort ranking order, use Total sort.
Blank Quiz League Template Download & Instructions
DOWNLOAD LINK FOR BLANK QUIZ LEAGUE TABLE
(often when launching an Excel sheet that has been downloaded, Excel will prompt you to enable editing. This sheet does not use or contain Macros so is fine to use)
Instructions
First thing to do is go to Names tab and enter all your team names as you want the to display throughout the workbook.
Once you have entered all your teams for the first time, open up Formulas tab in Excel. Click on each name individually and click Define Name, then OK. Repeat until all your team names are defined. this only has to be done once.
Next, go to your DATA tab keeping the Formulas Ribbon active in Excel. Select the 1st blank cell where a team name should go, in Formulas tab click “Use in Formula” and select the team you want. Repeat all the way down until it’s populated.
Note, in DATA tab, orange cells should only be populated by using Formulas tab and Use in Formula. Yellow cells can be manually imputed and blue cells should be left alone because they contain working calculation formulas which will auto-update.
Finally, in your WK6 tab (or whatever you rename it to) you can use this on the night to keep scores. It will give you a running live update on the league totals as you go.
When working with WK6, you don’t have to type team names in. Open the Formulas ribbon and click Use in Formula and let Excel enter the names in. For guest teams, go ahead and type their names in manually.
The reason we use Defined Names is due to the way Excel looksup info. This rules out misspells or alterations in upper and lower case or mixing up caps.
If you want to make a copy of WK6 and rename it LIVE, right-click the tab / Select Move or copy / Select WK6 / Tick Create a copy / OK
This is a first-draft of a pub quiz league table, hopefully you find it useful.