Create a Report in Excel as a Table (With Easy Steps)

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.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Steps to Create a Report in Excel as a Table

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.

An Effective Way to Create a Report in Excel as a 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.

Create a Report in Excel as a Table Using PivotTable Feature.

  • 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.

Create a Report in Excel as a Table Using PivotTable Feature.

  • A blank pivot table in the target location will be created.

Create a Report in Excel as a Table Using PivotTable Feature.

Read More: How to Make a Monthly Expense Report in Excel (with Quick Steps)


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.

Managing the Layout of the Pivot Table

The Layout section includes the Report Filter, Row Labels, Column Labels, and the Values area. You can modify the fields of the table here.

Managing the Layout of the Pivot Table

Read More: How to Create an Expense Report in Excel (With Easy Steps)


Similar Readings


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.

Add or Remove a Field to Pivot Table.

Read More: How to Create an Income and Expense Report in Excel (3 Examples)


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.

Arrange a Pivot Table Fields

  • Under the Field section, right-click on the field name, and then click on the area where you need to add it.

Arrange a Pivot Table Fields

  • 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.

Arrange a Pivot Table Fields

Read More: How to Automate Excel Reports Using Macros (3 Easy Ways)


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. Please visit our website ExcelDemy for more Excel-related content. Happy reading!


Related Article

Hafiz Islam
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo