Tips > Create an Excel Football League Table

Protect the League Table

With our Excel league table completed, the final steps are to protect the worksheets and workbook to make it more user friendly, and to secure the formulas and prevent unauthorised access.

Download the EnglishPremierLeague1.5.xlsx spreadsheet for a copy of the table up to this point to follow along.

Protect the Worksheets

All of the team sheets and the League Table sheet need protecting. This is done because all of the data works from formulas and the Fixture Grid sheet. Therefore there is no need to edit anything on this sheet until the beginning of each season.

Select each sheet in turn and

  1. Click the Review tab on the Ribbon
  2. Click the Protect Sheet button in the Changes group
  3. Enter a password in the Password to unprotect sheet: box
  4. Protect a sheet and set allowed actions for your users

  5. Select only the first 2 options from the list of allowances for the spreadsheet users
  6. Click Ok
  7. Repeat the password and click Ok

Protect the Fixture Grid Sheet

During the season the Fixture Grid sheet is the only one that requires updating by the user. All of the other sheets run from the formulas that were entered.

Because of this, it needs to be protected a little differently.

We want to be able to enter the results of the fixtures in the appropriate cells, but have everything else protected from use.

To do this we first of all need to unlock the cells where the results will be entered.

  1. Select all the necessary cells
  2. Click the home tab
  3. Click the Format button in the Cells group
  4. Select Lock Cells from the list
  5. Lock cells in a sheet to prevent editing

Now that the cells we need to edit in the future are unlocked, the sheet needs to be protected.

  1. Click the Review tab on the Ribbon
  2. Click the Protect Sheet button in the Changes group
  3. Enter a password in the Password to unprotect sheet: box
  4. Select only the first 2 options from the list of allowances for the spreadsheet users
  5. Click Ok
  6. Repeat the password and click Ok

Hide Sheets and Protect the Workbook

We will hide the Data and Calculations sheets on the spreadsheet and protect it so that users cannot unhide them.

This will prevent any work on the Calculations sheet which is pivotal to the functionality of the spreadsheet. It will also hide our formulas and clean up the workbook.

To hide the worksheets:

  1. Select the Calculations and Data sheets by holding the Ctrl key and clicking on each one
  2. Click the Home tab on the Ribbon
  3. Click the Format button in the Cells group
  4. Select Hide & Unhide and click Hide Sheet
  5. Hide sheets in a workbook

Once the worksheets are hidden, the workbook needs to be protected to prevent users from unhiding the sheets.

  1. Click the Review tab on the Ribbon
  2. Click the Protect Workbook button in the Changes group
  3. Protect the structure of a workbook

  4. Ensure the Structure check box is checked
  5. Enter a password and click Ok
  6. Reenter the password and click Ok

Download the Finished Excel Football League Table spreadsheet.

Follow us on

Facebook  Twitter  You Tube