Download and Clean EPL Data from 2017-2019

Create Master League Tables

1. Download the following data from the Website onto an excel file for the years 2017, 2018 and 2019:

a. Top Assists

b. Top Scorers

c. Discipline

d. League Tables

Name these data files suitably as input worksheets

 

2. Create a Cleaned up Master League Table for Each Year for 2017, 2018 and 2019. (Hint: The master league table should look like the following)

a. Use the LEFT & MID Functions to split the Team Name from the League Tables to create the columns “Club Name”, “Club Abbreviation” and “EPL Rank” column
 

3. Bring over the Discipline Points from Disciple Points Tables for each year into the master table corresponding to each Team (use a VLOOKUP function to bring over the data). The Table for each year should now look like the following: (There will be three tables each for 2017, 2018 & 2019)

4. Create a Master League Trend Table showing the combined league table and discipline points for the EPL clubs from 2017 – 2018.

a. Hint 1. If a certain club did not qualify for the EPL in a Year, please keep the corresponding cells vacant for that year

b. Hint 2. Use the three tables created in Step 3

5. In the Master League Trend Table, create the following columns

a. Three Year Total League Points

b. Three Year Goals Scored

c. Three Year Goals Against

Plot the Three Year Total League Points, Goals Scored and Goals Against on three separate Column Charts.

Answer the following questions:

 Which is the best performing club in the last three years in terms of League Points
 Which is the best performing club in terms of Goals Scored

 

6. Using the Top Scores and Top Assists Tables, create the total goals scored, and total assists by each team and sort them in descending order for each year. (Hint. – Use pivot tables). What is the trend for each team? (Show the trend of the top 5 teams by goals scored, using a trendline on a column chart)