This support article provides some tips and tricks to administrators to enable the quick manipulation of report data using Microsoft Excel. It is by no means meant to be an extensive lesson in Excel but rather provides instruction on some of the more typical tasks that administrators may wish to do with respect to reporting.
Note: Instructions and screen images in this article are for Microsoft Excel for Mac, Version 16.50
First, a quick review - how to download a report from the PlayHQ Admin Portal
An authorised administrator must first login to the PlayHQ Admin Portal at https://bv.playhq.com/
Once logged in, navigate to Reports and select the appropriate report from the available options. Select Generate to run the report you have selected:
The PlayHQ reporting engine will generate the report and a message will be displayed to inform that the report is available for download via the Generated Reports section:
Note: Financial and Order reports are downloaded directly from these tabs.
Select Download to have the report downloaded in CSV format to your computer:
Select Show in Finder will display the downloaded CSV report file typically in the Downloads folder:
Click, drag and drop the CSV file to Microsoft Excel (or open the CSV file via Excel):
The report data should now be viewable in Microsoft Excel
Microsoft Excel tips and tricks
Note: The following illustrates the manipulation of data from a Participation report. Personal/identification data has been hidden for privacy reasons.
Having opened the report CSV file in Excel, you will view this similar to the following screen image:
Row 1 contains the column data headings.
It is typical that not all of the data in a standard PlayHQ report may be relevant to you, so step one is to delete columns of data that are not of interest. To do so, hold the Command key of the keyboard, and select the columns containing the data that is not of interest. Once all such columns are highlighted, right-mouse click and select Delete:
The spreadsheet will now contain just the columns of information that are of interest to you.
There may be rows of individual data that are not of interest to you either. To hide such data from view you can use the Filter option in Excel that is applied by selecting the Home 'ribbon', Sort & Filter, and then Filter option:
With the Filter option available (see below), you can now apply one or more these filters so that only the rows of data that you are interested in are visible. In the following example, Coaches and Team Managers are hidden and only Player data shown:
Create a PivotTable
A PivotTable aims to summarise, group, count, and/or sort data that is stored in a table/worksheet. Microsoft Excel provides an inbuilt means to quickly and easily create a PivotTable and manipulate data that would otherwise prove difficult to summarise - support beyond that provided below is available at Microsoft Support.
Once you have applied the filter/s so that the data worksheet you are viewing has only the information of interest, select the entire worksheet of data by clicking above Row 1 and to the left of Column A, Then select Insert, PivotTable and set the PivotTable to be created in a New worksheet, and finally select OK:
A blank PivotTable will be automatically created.
Click, drag and drop Field names to the Fliters, Columns, Rows, and/or Values sections in order to create your customised PivotTable. The following example PivotTable summarises the number of Female, Male, and Other (gender) players by their home address Postcode:
As another example, using the same data worksheet as above is a PivotTable quickly adjusted to look at the number of Female, Male, and Other gender players by the Club that they registered to:
Of course, ensure that you regularly Save your work making sure to save in Excel format (.xls or .xlsx) to ensure that all your changes to the data file and PivotTable remain!
Here are some other common data summaries that may be useful:
Count of the number of teams fixtured in each competition season for each BV affiliated organisation:
Count of the number of teams fixtured in each competition season for clubs in each BV affiliated organisation:
