I took the English Premier League fixture list for 2017 – 2018 from an online newspaper, did some reformatting, created an Excel Table, Used Power Query, put it all into a Pivot Table together with slicers and now I can create the fixture lists for home and away for any and all EPL clubs.

Raw Data

What I did: in brief

Copied and pasted the table from the newspaper

Added a column to the left

Moved the date of the fixtures from, eg, Row 5 to the new column row 6

Copied the dates from the first row to the final row of the fixtures to which it relates

Delete the surplus data: for example, you can see that the home and away team names appear twice … delete the surplus columns. Delete the v column and the blank columns either side of that as well.

Create headers for the list: you should have just four columns

Date

Time

Home [Team]

Away [Team]

Convert the entire list, including the empty rows, to an Excel Table: Ctrl+T to do that. At this stage you could filter out the blank rows and then create the pivot table but I used Power Query for that.

Change the name of the table … I chose epl_2017_2018

Power Query: Data … From Table/Range

Change the name of the Query if you want. I was happy with epl_2017_2018

Date Filter … either Remove Empty or Deselect null

Change the Data Type for the Time column to Time … for me it came across as Whole Number. Now it will show, eg, 3:00:00 PM for a 3 pm fixture

I tried to use Transform … Time … Hour … Hour to turn 3:00:00 PM in 15:00 hours but it only showed me two digits which meant that a 19:45 fixture was shown as 19 only. I undid that!

Close and Load

Create a Pivot Table from the Query

I created the Home Fixtures Table first:

Filters: Home

Rows: Date Away … Field Settings … Number Format … Custom … ddd dd mmm yy to give me, eg Sat 19 Aug 17 (the screenshot below is not quite complete but the file you can download below is up to date)

Values: Time … I put time here so that I could make the time show in the way I wanted … Value Field Settings with Number Format as Time 15:00 and Summarise value field by Maximum

The Away fixtures Table

Copy the Home Pivot Table when you are happy with it and paste it where you like; make the changes necessary to make it show the away fixtures and then create your Slicers:

Slicers: create one for the Home Pivot Table and a separate one for the Away Pivot Table

My Pivot Table with Slicers

What happens when they change the date/time of a fixure? Just change the basic data for that and the Query and Pivot Table will update automatically.

I have created a single Pivot Table for Home and Away … it’s on the tab pivots (2) … maybe you prefer that one. You need to learn how to use the Slicers to make that table work.