# Formatting Duration Fields in Excel Pivot Table Reports

## Many Excel Reports use Pivot tables or Pivot Charts. These need to be formatted correctly. Set up your excel report to include the report Type, Dates and Data you require then Generate the Report Template (as shown below)

For more assistance on how to create Excel Reports see the Lessons on Excel Reports. These types of reports can be quite complex to create, so if you are not confident using Excel ask your distributor for assistance.

## In the generated Template, on the second Worksheet (that has the data export) right click on your Duration fields and see how the fields are formatted. This is the EXACT cell formatting you need to apply to the Duration fields you use in a pivot table or pivot chart.

1: Right click on the Duration field or Duration calculation column. For this example, we are selecting the 10m sprint duration.

2: Click on Format cells

3: See the image in the step below

## Now look at the format of the cells. Duration Formats are not Numbers, but in the original data that is exported from the Excel file, format the fields as Numbers. You

The [ ] around the highest duration ensures that if you are summing durations in a pivot table, then the answer will calculate and show in the ss.SSS format, even if the total goes about 60 seconds. If you were summing 400m split times for an 800m, without the [ ] around the ss then a time of 46.89sec and a 48.11sec would sum to 1:35.000(mm.ss.SSS), which would display as 35.00 (in a ss.SSS format and you would not see the minutes). With the [ ] the result would show as 93.000 seconds.

## On the First Worksheet, add in the pivot table and remember to choose the "All fields" from the "Use in Formula" (On the Formulas Tab)

After you select to Insert a Pivot Table, follow the steps below:

1: Click on the cell selection for the Table/Range

2: Click on the Formulas Tab

3: Click on "Use in Formula"

4: Select the All_ name to be able to use all of the fields from your form

5: Click "OK"

## You need to select the Pivot table columns that are duration fields and then right mouse click and select Format Cells. Then apply the correct custom format (ensure it is the same as the format of the exported data in the data worksheet) e.g [ss].000 .

You MUST select a custom field with the [ ] around the duration to ensures that if you are summing durations in a pivot table, then the answer will calculate and show all of the duration field correctly. If you use a ss.SSS format (without the [ ]) and the sum or average goes above 60 seconds, then ONLY the seconds will show and the minutes field will be hidden. E.g 1:44.555 in a ss.SSS format will ONLY show the seconds field of 44.555. If you add in the [ ] around the ss then it will display as 104.555. Similarly, if you were summing 400m split times for an 800m, without the [ ] around the ss then a time of 46.89sec and a 48.11sec would sum to 1:35.000(mm.ss.SSS), which would display as 35.00 (in a ss.SSS format and you would not see the minutes). With the [ ] the result would show as 93.000 seconds.

## Ensure you select Refresh data when opening the file (or the data will not update) and click OK

You MUST click on Refresh data when opening the file.

If you have formatted your cell width click on the Layout & Format and deselect "Autofit column widths on update" so that they do not collapse on opening.

## Delete the Data on the Worksheet tab (as shown here) and Save the Excel worksheet. Then upload the template back into the Excel Reports Page on the system (see the step below).

Delete the data from the worksheet, but not the column headings. Save the file.

## Choose the file and upload it.

1: Now upload the template that has the pivot table in it by clicking Choose File

4: Test the report formatting (shown in the step in the image below)