Golf Shotgun Spreadsheet

The purpose of this spreadsheet is to manage the scheduling of players for golf competitions, and to determine the scores and winners afterwards. These competitions, called “shotguns,” are held on a regular basis and 9 holes are being played.

Excel Skills Used

Spreadsheet Shotgun Scheduling

Spreadsheet purpose & Excel skills involved

For golf basics & terminology please refer to the tab “golf terms and basics.”

For a golf club, I created a spreadsheet for recurring competitions. If the competition takes place on odd days, holes 1 – 9 are being played, and on even days, holes 10-18 are in use.

Male and female players play together in a flight, using their own handicap. The point score of the player with the lower handicap in a flight counts 60% and the higher handicap score counts 40% towards the final score.

Upon completion of the game the point score of every flight is entered and the flight with the highest points score wins the game.

Spreadsheet Requirements

(To download copy of my spreadsheet please refer to the tab “dowloads“)

Calculation of the amount of strokes players get based on their handicap
(Sheet tabs: Master list 2015 (columns H:I) & Lookup Handicap)

Include an (automated) filter of participants from a master list of members
(Sheet tab: Master list 2015 (filter based on column G: ‘yes’))

Include a sorted (by gender and handicap) and printable list of the players
(Sheet tab: result: Sample sheet – printable list)

Include an efficient and quick way to enter the player’s names + number of strokes in a schedule.
(Sheets: Flight Schedule Odd 1 – 9; columns A:F)

Include calculations for point scoring & showing best scores
(Sheet tabs: Flight Schedule Odd 1 – 9: columns G:H)

Be error-proof and easy to use for novice Excel users. (Sheet tabs: applies to all)

Create a list (data source) for making labels (automatically).
(Sheets: labels 1-9 and labels 10-18).

Excel skills used to create spreadsheet

Use & rename sheet tabs, copy, cut & paste filtered data
Sum function, weighted average
Relative, absolute and mixed references
Conditional formatting, Cell referencing within and between sheet tabs.
Concatenate function (for full names column)
Lookup (Vlookup) function
IFERROR, IF Function and nested formulas
Name Manager and use of defined names
Data sort & filter functions
Macros, use of controls (buttons) to link to macros
Saving document in .xlsm format and as a template
Ranking function (for winning scores)
Protection: Locking and unlocking cells and sheet protection
MS Word skills: Mail merge project:
in combination with MS Word using an Excel data source to create labels for scoring cards.

In the video’s you will find an explanation on how to work with (use) this spreadsheet.
My spreadsheet involves a variety of skill levels, which makes it suitable to use as a project in a classroom environment with Excel students.

Video 1

Instructions for using my spreadsheet

Intended for Shotgun Organizers

This sheet tab shows a master list of all golf members, from which a list of participants is created. Members sign up to take part in a shotgun. For each contestant, we enter his or her current handicap in column F and place a “yes” in column G next to the person’s name.

For example, Frank Aaron, with ID #1 signed up for the upcoming shotgun and his handicap is 24.3. We enter Frank’s handicap in cell F2 and place a “yes” in cell G2. In column H his playing handicap now shows as “11”. David Anderson, with ID #2, signed up with handicap 21.5, we enter “yes” for him in cell G3; he gets “9” extra strokes. Annie Callahan lowered her handicap from 24 to 22.9, so in cell F7 we enter her new handicap. She now gets 14 strokes instead of 15.

For the purpose of this demonstration, all other entries have been made. In cell G1 we enter the date of the shotgun, which is in this example April 9^th. On odd days, holes 1 through 9 are played, so we will work here with column H and ignore column I (which is used for even days).

The next step is to filter the participants from the master list and to sort them by gender and by handicap (A to Z, so lowest first). To do so, click on the macro button “Filter List 1-9”. In case we need to make changes afterwards, we can remove the current filter by clicking on the “remove filter” button. In our example we keep our current filter.

Now we have a sorted and filtered list of participants. The next step is to create a printable list to see an overview of all players. To do so, click on the button “Copy list 1-9”.

A new “sheet tab” is created. To rename the sheet, right-click on the sheet tab and rename it as “print list April 9” and press Enter. Now we have created a printable overview of participants, which we can use to schedule the players.

The next step is to position the players. Click on the sheet tab “Flight Schedule odd 1-9” to do so. The player with the lower handicap will be placed in the left column, the higher handicapper on the right. In this example we will schedule the players as follows:

In cell A5 enter ID number 16: Justin Evens will show in cell B5, with playing handicap Then hit the tab key on your keyboard to move to cell D5
In cell D5 type 35: Mary Martinez’ name will show, with playing handicap 9
Their combined playing handicap is 7. Together they receive 7 additional strokes.
The next player is #49 for Harry Wood. His playing handicap is He will play in the same flight with #40: Diane Niemeyer. Her playing handicap is 13 and together they receive 10 extra strokes.

We schedule all players in the same manner. When the players’ list is complete, we need to create labels to put on the scoring cards. I will show you how in the next video.

Video 2

Creating labels for scoring cards – Using Word 2010 or 2013

In this video, we will create labels for the player’s scoring cards. The current sheet tab Flight Schedule Odd 1-9 now shows the scheduled players. Click on sheet tab “labels 1 – 9”, showing the same names which have been entered in “Flight schedule odd 1-9”.

The data source, sheet “labels 1-9” shows the field names “Hole”, “Flight”, “Player 1”, “Player 2” and “Extra strokes”. We will need these headings to create the labels.

Next, we will create a mail merge project in MS Word and use this sheet tab “labels 1-9” as our data source.

Open a blank document in MS Word and click Mailings. Under “Start Mail Merge” click the down arrow and select “Step-by-Step-Mail merge Wizard.” In the Mail Merge Pane, select Labels as the document type and click Step 1 of 6: Next: Starting document.

Click Label Options: In our example we will use Avery US Easy Peel address labels 5160.
Click step 2 of 6: Next: Select recipients and click the browse button: Now we select our data source: the spreadsheet Shotgun Schedule April from the appropriate location.

There are many sheet tabs to choose from in this dialog box but we need to select the sheet tab Labels 1-9$ and click OK. Click step 4: Next arrange your labels.

Because we need to create custom labels we can’t use the address block but will choose “More items” from the available options. We manually type the first line: Shotgun April 9 and press Enter. Then we choose the following field names:

Shotgun April 9

«Hole» «Flight_»

«Player_1» &

«Player_2»

Strokes: «Extra_Strokes»

The preview results button will show as follows:

Shotgun April 9

Hole 1 Flight 1

Justin Evans &

Mary Martinez

Strokes: 7

For all the information to fit correctly we need to remove the extra space MS Word adds between lines. Click under Paragraph and click “Remove space before paragraph.”

Next we need to replicate the labels. Click on “Update Labels”. Then in step 4 Click Preview your labels. We do not need to edit the recipients list; click step 5: Next complete the merge. Do not print the labels yet, but click instead on “Edit individual labels”. In the dialog box “merge to new document” click “All” and OK. A new document “labels1” has been created. These labels are ready to be put on the scoring cards. Save or print the labels.

Video 3

Instructions to keep score after the shotgun & to determine the winners

The last step in the process is to enter the point scores of each flight. In this example, the scores of flights 4 through 7 already have been entered. We will enter here the scores of the first three flights.

For the first flight click cell H5 to enter 20 and in cell H6 we enter a point score of 19. Cell H7 shows the total score of 39, which we also find displayed in cell L5.

Currently Flight 1 is ranked number one, but shares this rank with Flight 4. At this moment, they share first place with a winning score of 39.

For the second flight, in cell H9 we enter 20, and in cell H10 a score of 21 and with a score of 41, flight 2 becomes the winning flight.

Next, we enter the score of the third flight, with point scores of 18 and 17 they are ranked 5^th and share their place with flight 6, with both a score of 35.

Flight 2 is the winner, flight 1 and 4 share the second place. Flight 4 shows a ranking of “3” only because Flight 1 is listed above flight 4.

I chose to set up the ranking formula in this manner, instead of showing a ranking of “2” twice and no listing of a #“3” rank.

This completes my instructions for a shotgun organizer.

Golf Terms & Basics

Downloadable files for the non-golfer:

Golf Terminology and basics (PPT) or
Golf Terminology and basics (PDF)

Use of terms:

Flight | Greensome | Handicap| Par | Shotgun | Stableford

Flight

A team of golfers playing together are called a ‘flight’. A flight consists of one to maximum four players.Shotgun:

Greensome:

Golfers compete in teams of two, each starting (teeing off) with their own ball, then the better ball is chosen and alternate strokes are played to complete the hole. Both players tee off separately on every hole. In this situation,

Handicap:

Is a numerical measure of a golfer’s potential playing ability based on the tees (holes) played for a given course. The higher the handicap of a player, the poorer the player is relative to those with lower handicaps. A handicap is calculated with a specific formula that approximates how many strokes above or below par a player might be able to play.

Par:

Is the predetermined number of strokes that a professional golfer (with handicap 0 or below) should require to complete a hole. A hole can be a par 3, 4 or 5: If a (0 handicap) player needs 3 strokes to get in the hole on a par 3 hole, players score is called “par”, which is worth 2 points.

Shotgun:

Is a golf tournament format in which all groups of players start (tee off) simultaneously from different holes. Each hole on a course will be the tee off hole for each foursome. Group 1 would start from hole 1, Group 2 from hole 2, etc. Each group starts play at the same time.

Stable ford scoring:

Is a scoring system used to calculate your point score. Rather than counting the total number of strokes taken, it involves scoring points based on the number of strokes taken at each hole. A weaker player is allowed more strokes to get points than a stronger player based on their handicap.

Downloads

Scripted instructions – with video 1, 2 & 3

Scripted text how to use spreadsheet (PDF)

Shotgun Spreadsheet

Note: (please use Excel 2010 or 2013 version)
Note: Macro’s need to be active! >File>Options>In Trust Center: enable Macros

Shotgun Spreadsheet (.xlsm) Please refer to download instructions below:

(Mozilla FireFox):

Right-click on the link above (“shotgun spreadsheet”),
from the menu (as shown on the right side) choose Save Link As
Save to preferred location (i.e. Desktop) and keep the .xlsm format
(=necessary for the macro buttons in spreadsheet to work properly)
Click on the Arrow (see upper right of browser)
Click on the download menu: The Excel program will open and show Shotgun spreadsheet:
(Internet Explorer)
Right-clicking on the Shotgun spreadsheet link will open the following dialog box:You can choose open to directly open the spreadsheet or choose Save target as. If you choose the latter option, you will be asked to save the spreadsheet before opening the file. Please make sure to use the .xlsm format option instead of the standard .xlsx format.

Golf Shotgun Spreadsheet

Spreadsheet Shotgun Scheduling

Spreadsheet purpose & Excel skills involved

Spreadsheet Requirements

Excel skills used to create spreadsheet

Contact Me