The easiest way to create a report as a table in Excel is to use the PivotTable feature.
This allows you to summarize and organize raw data in the form of a summary table.
The following step-by-step example shows how to do so.
Step 1: Enter the Data
First, let’s enter the following dataset that contains information about basketball players on various teams:
Step 2: Insert a Pivot Table
Next, highlight the cell range A1:C14, then click the Insert tab along the top ribbon and then click the PivotTable icon:
In the new window that appears, choose A1:C14 as the range and choose to place the pivot table in cell E1 of the existing worksheet:
Once you click OK, a new PivotTable Fields panel will appear on the right side of the screen.
Drag the Team field to the Rows box, then drag the Points and Assists fields to the Values box:
The following pivot table will automatically be populated with values that summarize the sum of points and assists for each team:
Step 3: Modify Pivot Table Values
By default, a pivot table uses the sum to summarize the values of numerical variables.
However, you can use a different metric (like count, average, min, max, etc.) to summarize the variables.
To do so, click Sum of Points in the Values box, then click Value Field Settings:
In the new window that appears, click Average and then click OK:
The average value of the points for each team will now be shown in the pivot table:
Feel free to summarize the assists by a different metric as well.
Step 4: Modify Pivot Table Design
Lastly, feel free to modify the appearance of the pivot table by clicking on any value in the table to make it active.
Then, click the Design tab along the top ribbon and choose any design style you’d like.
For example, you may choose a design that uses a black background for the titles and a blue background for the interior values:
The table is now complete.
We have successfully created a report in the form of a pivot table that summarizes the average points and the sum of assists for each team in our original dataset.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Sort Pivot Table by Grand Total in Excel
How to Group Values in Pivot Table by Range in Excel
How to Group by Month and Year in Pivot Table in Excel
