The creation of reports indicates collecting and presenting information in a single Excel worksheet. If you want to create a report in Excel as a table, then a pivot table is a handy way to create an interactive summary from a lot of data. The pivot table can automatically sort and filter several data, calculate totals, counts average, and even make cross-tabulations. In this article, you will learn an effective way to create a report in Excel as a Table.
Create a Report in Excel as a Table (With Easy Steps)
Let’s introduce our dataset first. This is a source data table that consists of 4 columns and 7 rows. Our goal is to create a report as a pivot table from this source data table.
Step 1: Create a Table Using the PivotTable Feature
As we have already known the benefits of pivot tables, follow the steps below to create a pivot table.
- First, select the whole worksheet that contains your source data table. Then, go to Insert > PivotTable. A dialog box will pop up.
- In the Table/Range box, put the location of the source dataset ( In this example, B4:E10 under Sheet1). Then select the target location where you wish to keep your pivot table. After that, click on OK. Now, here are 2 cases,
Choosing New Worksheet will set a table in a new sheet.
Choosing an Existing Worksheet will set the table at a particular location in the existing sheet. In the Location box, put the location of the first cell where you wish to place your table.
- A blank pivot table in the target location will be created.
Step 2: Manage the Layout of the Pivot Table
The Pivot Table Field List is located on the right side of the sheet and divided into the following two parts.
The Field Section includes the names of the fields that correspond to the column names of the source dataset.
The Layout section includes the Report Filter, Row Labels, Column Labels, and the Values area. You can modify the fields of the table here.
Read More: How to Create an Expense Report in Excel
Step 3: Add or Remove a Field to Pivot Table
If you want to add a field to the Layout section, make sure the tick mark in the check box next to the field name. Similarly, you can remove a field from a pivot table by unchecking the box next to the field name.
Notes:
MS Excel includes the fields in the Layout section in the following ways.
- Numeric fields are included in the Values area.
- Text fields are included in the Row Labels area.
- Date or time hierarchies are added to the Column Labels area.
Step 4: Arrange a Pivot Table Fields
You can arrange a pivot table in the following ways.
- Drag and Drop fields among the four areas under the Layout section. You can also alter the order of the fields by dragging and dropping.
- Under the Field section, right-click on the field name, and then click on the area where you need to add it.
- Click on the down arrow next to the field name to get a drop-down list that includes all the available options for that specific field.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Conclusion
In this article, we have learned an effective way to create a report in Excel as a table. I hope this discussion has been useful for you. If you have any questions or any kind of feedback, please don’t hesitate to let us know in the comment box.
