Excel Challenge #6 - Footballer Dataset 2
For this challenge, create a Pivot Table from a data set of football players, which focuses on league and club names, player positions and total players.
Download the data set here, and complete the tasks below.
- Using the data table, create a Pivot Table where the Rows will focus on the League as primary level and Club as secondary level, column will focus on the Player's position and the Total will be the Total number of players bought.
- Add a Conditional Format to the Grand Total column to show data bars which provide an extra visual on numbers.
- Choose a Pivot Table design of your choice to make the visual more appealing.
The end result should like the table below:
Stuck on the tasks?
+ Reveal Step-By-Step Instructions
Highlight the whole Data table and select Insert Pivot Table (found in Excel -> Insert -> Pivot Table).
From the Create PivotTable pop up, leave the Selected table and then choose Existing Worksheet. Within location, right click the mouse curser and then select the Pivot Table tab in excel, it will then select Pivot Table tab as the destination location and then select a cell within that tab.
Once selected, the PivotTable wizard will show with PivotTable Fields section showing to the right, select Position in Columns, League_To as first selection in Rows and Team_To as second selection in Rows (important to do this as primary and secondary data set split) and then Name in the values (make sure this is set to Count, in the Value Field Settings in the drop down when you select in the Field list).
You can rename all the Rows, Columns and Values fields by selecting Value Field Settings from the drop down option and amending the "Custom Name" section. Finally, rename the Row Labels and Column Labels in the actual Pivot Table created to explain the breakdown of the Rows and Columns respectively.
Highlight the Grand Total numbers (excluding the total at the bottom) and then select Data Bars in the Conditional Formatting section (found in Home -> Conditional Formatting -> Data Bars -> Red Data Bar). This will add the Data Bar highlighting within the Grand Total cells, showing another visualisation style on top of the numeric version provided.
Finally to choose your Pivot Table design select "Design" section and pick a design format of your choice (scroll down).
Sharpen your skills with our range of Excel courses designed to turn you from a table trainee to spreadsheet specialist here >
Enter Your Voucher Code Here
Note: We ask for your billing address for our tax records only. We do not ask for card details for pre-paid items.