Advanced Excel Reports- An example using Microsoft Excel 2010

The following provides an example of setting up a basic Excel Spreadsheet for a group of athletes using excel 2010.

Click on Excel Report

Click on Excel Report

Click on Create New Report

Click on Create New Report

An excel report will be created with the information specified above that you can open on your desktop

An excel report will be created with the information specified above that you can open on your desktop

e.g for one player,

A Blank workbook will open up with the athlete's data on the second workbook

A Blank workbook will open up with the athlete's data on the second workbook

To Add in a pivot table, click on Insert and select "Pivot Table"

To Add in a pivot table, click on Insert and select "Pivot Table"

To select the data, click on the cell selection icon (as show here)

To select the data, click on the cell selection icon (as show here)

Go to "Formulas", click on "Use in Formula" and then select to use all of the data from the exported sheet (e.g. _All_ name_Report)

Go to "Formulas", click on "Use in Formula" and then select to use all of the data from the exported sheet (e.g. _All_ name_Report)

Click on the selection icon after you have select to use the data

Click on the selection icon after you have select  to use the data

Click on "OK"

Click on "OK"

An empty pivot table will appear and you can easily add in the different fields you want but dragging them into the correct rows or columns

An empty pivot table will appear and you can easily add in the different fields you want but dragging them into the correct rows or columns

Drag in the variables you want to include in the pivot table, click on Value Field settings for each field to show sum/avg/std dev etc an these are applied to the pivot table

You MUST right click on the pivot table and select Refresh on Opening (then save this setting) for the pivot table, or the data will not refresh when you open it.

You MUST right click on the pivot table and select Refresh on Opening (then save this setting) for the pivot table, or the data will not refresh when you open it.

To Add a pivot chart, select "pivot chart"

To Add a pivot chart,  select "pivot chart"

Choose the data you want to chart... use the "Formulas", click on "Use in Formula" and then select to use all of the data from the exported sheet (e.g. _All_ name_Report) as you did for the pivot table

Choose the data you want to chart... use the "Formulas", click on "Use in Formula" and then select to use all of the data from the exported sheet (e.g. _All_ name_Report) as you did for the pivot table

Now drag in the fields you want to chart, e.g. date and total score

Now drag in the fields you want to chart, e.g. date and total score

Drag in other variables, and change the chart type by right clicking on the chart area and selecting "Change Chart Type"

Drag in other variables, and change the chart type by right clicking on the chart area and selecting "Change Chart Type"

Try a couple of different charts to see which bests suits the data

Try a couple of different charts to see which bests suits the data

The chart type will change to the type you selected. Update any settings for the chart using the Design and Layout Capabilities

The chart type will change to the type you selected. Update any settings for the chart using the Design and Layout Capabilities

Format the cells according to your style

Format the cells according to your style

Then delete the Data that is exported on the data pages from the application

Then delete the Data that is exported on the data pages from the application

Save the file as an Excel work book

Save the file as an Excel work book

Go back to the excel reports page in the application, and upload that Excel file

Go back to the excel reports page in the application, and upload that Excel file

You will get confirmation that the report was saved.

You will get confirmation that the report was saved.

The report template will be saved. In this example, we created a template to have one report per athlete. When we run this report for multiple athletes, a separate excel file will be created for each athlete as part of a zip file.

The report template will be saved. In this example, we created a template to have one report per athlete. When we run this report for multiple athletes, a separate excel file will be created for each athlete as part of a zip file.

For two athletes, two separate reports are generated.

For two athletes, two separate reports are generated.

0 Comments

Add your comment

E-Mail me when someone replies to this comment