How to Create Pivot Table Report in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Are you a business analyst? Are you involved in any kind of job in a certain organization? Need to climb the corporate ladder promptly to advance your career? If the answer to all the above questions is yes, then this article would be the answer to your prayer. Here, we will show easy and detailed steps to create a Pivot Table report in Excel with proper illustrations.

The Pivot table helps to create reports very easily. Although the primary task of a pivot table is to offer different setups for analyzing data effectively, it can go beyond that.

overview image to create pivot table report on sales info

The above overview image shows the pivot table report on sales info of multiple states along with the chart.


What Is Pivot Table Report?

Generally, we use PivotTable for reporting. You can exhibit the information to a diverse group of people once you have constructed a PivotTable. Also, you can analyze the data by moving the fields around in their rows and columns fields. You can create numerous necessary reports using a single PivotTable by applying Filters, and various summaries and focusing on particular data.


Our sample dataset contains product-wise sales info on different branches. Now, we’ll create the pivot table and report using the dataset. So, let’s explore the steps one by one.

sample dataset contains product-wise sales info

Here, we have used the Microsoft Excel 365 version. You may use any other version according to your convenience.


Step 1: Specifying Data Range

click on pivot table to insert

At the very beginning, select the entire dataset. Then, go to the Insert tab. Later, click on PivotTable on the Tables group.

choose a place to insert pivot table

The PivotTable from table or range dialog box opens. Here, we can see that our data range was automatically detected and sat in the Table/Range box. In the Choose where you want the PivotTable to be placed section, select New Worksheet. This will place our PivotTable in a new worksheet. After doing that, click OK.


Step 2: Creating the Pivot Table Layout

drag fields in pivot table pane

In the PivotTable Fields task pane, drag Branch into the Rows area and Product field into the Columns area. After that, move the Total Sales field into the Values area. It will create a simple PivotTable. Hence, the output looks like the above image.


Step 3: Changing the Layout

select Outline Form layout for report

Select any cell inside the Pivot Table. Go to the Design tab. In the Layout group, click on the Report Layout drop-down and click Show in Outline Form from the list.

report output in outline form layout

At this moment, the headings get changed magically.


Step 4: Changing Number Format

select number format in context menu

To display the sales amount in currency, right-click on cell B5 to open the context menu. Select Number Format from the available options.

choose accounting format

In the Format Cells dialog box, choose Accounting in the Category section. Then, select 0 in the Decimal places box.

sales amounts in currency format

Now, you will see the sales amounts in currency.


Step 5: Using Filter Options

place state field in filter

Also, we can Filter the table according to our preference. Select any cell inside the PivotTable to open the PivotTable Fields task pane. Then, drag the State field into the Filters area.

select Virginia state

To use the Filter, click on the drop-down icon beside State. Then, check the box of Select Multiple Items. Later, select Virginia only. Click OK.

pivot table report showing virginia state

The above image shows the sales info of Virginia state only.

insert slicer

Also, we can insert Slicers in the report. It’ll make the report more dynamic, and you can change the report within a few clicks.

Primarily, select any cell inside the table >> move to the PivotTable Analyze tab >> click on the Insert Slicer from the Filter group.

choose month and product for slicers

Immediately, the Insert Slicers dialog box opens. Here, check the boxes of Month and Product. Then, click OK.

select Headphone, Keyboard, and Mouse in the product slicer

Presently, we can see two slicers placed in the worksheet beside the Pivot Table. In the Product slicer, click on the icon of Multi-Select. Alternatively, press ALT+S to do the same task. This enables us to select multiple items at a time. Then, select Headphone, Keyboard, and Mouse from the list.

As a result, you’ll only see the selected products in the pivot table. Another beneficial tool to use in the PivotTable report is the PivotTable Timeline. If you have a date in your data, you can easily apply this.


Step 6: Showing Report Filter Pages

select show report filter pages for individual report sheets

Suppose, you want the filtered reports in separate worksheets. To do this, proceed to the PivotTable Analyze and click on the PivotTable drop-down. From the drop-down list, select Options >> Show Report Filter Pages.

click ok in dialog box

In the Show Report Filter Pages dialog box, click OK.

output of 2 separate worksheets

It creates two new worksheets named Ohio and Utah.


Step 7: Inserting Pivot Chart in Report

insert pivot chart

You can make the report more appealing to the audience by inserting a Chart into it. Like before, go to the PivotTable Analyze tab >> click on the Tools group drop-down. After that, select PivotChart.

choose 2D clustered column

In the Insert Chart dialog box, select Column from the All Charts list. Later, choose a 2-D Clustered Column from the options. Lastly, click OK.

final output of pivot table report

Thus, you can visualize the data easily and will make decisions more quickly. Also, it’ll be beneficial to audiences to realize the report effortlessly.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

Thank you for reading this article. I hope all of the information mentioned above about how to create a Pivot Table report will now prompt you to apply them in your Excel spreadsheets more effectively. Don’t forget to download the Practice file. If you have any questions or feedback, please let me know in the comment section.


Related Articles


<< Go Back to How to Create Pivot Table in Excel | Pivot Table in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

1 Comment

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo