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.
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.
Here, we have used the Microsoft Excel 365 version. You may use any other version according to your convenience.
Step 1: Specifying Data Range
At the very beginning, select the entire dataset. Then, go to the Insert tab. Later, click on PivotTable on the Tables group.
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
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 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.
At this moment, the headings get changed magically.
Step 4: Changing Number Format
To display the sales amount in currency, right-click on cell B5 to open the context menu. Select Number Format from the available options.
In the Format Cells dialog box, choose Accounting in the Category section. Then, select 0 in the Decimal places box.
Now, you will see the sales amounts in currency.
Step 5: Using Filter Options
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.
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.
The above image shows the sales info of Virginia state only.
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.
Immediately, the Insert Slicers dialog box opens. Here, check the boxes of Month and Product. Then, click OK.
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
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.
In the Show Report Filter Pages dialog box, click OK.
It creates two new worksheets named Ohio and Utah.
Step 7: Inserting Pivot Chart in Report
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.
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.
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
- How to Insert A Pivot Table in Excel
- Creating a Pivot Table Automatically in Excel
- How to Create Pivot Table in Excel for Different Worksheets
- How Do I Create a Pivot Table from Multiple Worksheets
- How to Use Excel VBA to Create Pivot Table
- VBA Code to Create Pivot Table with Dynamic Range in Excel
- How to Use VBA to Create Pivot Table from Named Range in Excel
