Khan Muhammad Kawser
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 8 easy and detailed steps to create Pivot Table report in Excel with proper illustrations.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
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 its rows and columns fields. You can create numerous necessary reports using a single PivotTable by applying Filters, various summaries, and focusing on particular data.
8 Steps to Create Pivot Table Report
For clarification, we have a file named Bank Accounts Information in our hand. Let’s start with the following figure. This figure shows a portion of the dataset we have used to create the pivot tables in this chapter. The table has 712 rows. Each row represents a new account opened in the month of November at the bank.
The dataset has the following columns:
- The Date the bank account was opened
- On the Day of the Week, the bank account was opened
- The opening Amount
- The bank Account Type (CD, Checking, Savings, or IRA)
- Who Opened the bank account (whether a Teller or a New Account representative)
- The Branch at which the bank account was opened (Central, Westside, or North County)
- The type of Customer (whether an Existing customer or a New customer)
Now, we’ll create a PivotTable report using the above-mentioned dataset. So, let’s explore the steps one by one.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
Step 01: Specify the Data Range to Create Pivot Table Report
Initially, we need to select the specific range of data from which we want to create our PivotTable. It’s simple & easy, just follow along.
- At the very beginning, select any cell inside the dataset. In this case, we selected cell B4 in our dataset.
- Then, go to the Insert tab.
- Later, click on PivotTable on the Tables group.
- Correspondingly, the PivotTable from table or range dialog box opens.
- Here, we can see that our data range got 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 02: Create a Blank Pivot Table
As a result of our previous actions, Excel created a blank PivotTable in a new worksheet.
- Now, select any cell on the PivotTable. For example, we chose cell B4.
- Immediately, the PivotTable Fields task pane opens on the right side of the worksheet.
PivotTable Fields task pane has two parts: the upper part, where the field names reside, and the lower part, where you will place the upper part’s field names as per your necessity. In our example, the upper part of the PivotTable Fields task pane holds the Date, Weekday, Amount, AcctType, OpenedBy, Branch, and Customer fields. The lower part has Filters, Columns, Rows, and Values area.
Read More: Example with Excel Pivot Table
Step 03: Lay out the Pivot Table
Now we shall work on the PivotTable Fields task pane. The following steps will create a simple PivotTable. So, follow along.
- At first, drag the Branch and AcctType fields into the Rows area.
- Then, drag the Customer field into the Columns area.
- After that, move the Amount field into the Values area.
And the final output looks like the following one.
Subsequently, It displays the Branch and AcctType wise total. Besides, it divides the Amount into the Customer type into Columns: Existing and New customers.
Read More: Excel Pivot Table Tutorials for Dummies Step by Step | Download PDF
Step 04: Change the Layout of Pivot Table Report
In the picture above, in cell B5, the heading shows Row Labels. But these are the branches and the account types of the bank. So, the headings should be Branch and AcctType. Therefore, we could change this to a PivotTable. Follow these simple steps.
- Firstly, select any cell inside the PivotTable.
- Secondly, go to the Design tab.
- In the Layout group, click on the Report Layout drop-down.
- Fourthly, select Show in Outline Form from the list.
At this moment, these headings get changed magically.
Read More: How to Modify an Excel Pivot Table
Step 05: Alter Number Format
Then, we want to display the columns Existing, New, and Grand Total as currency. So, we have to make some changes in formatting in those columns. Just follow the steps below.
- Firstly, right-click on cell C7 to open the context menu.
- Secondly, select Number Format from the available options.
- Instantly, the Format Cells dialog box opens.
- Here, choose Accounting in the Category section.
- Then, select 0 in the Decimal places box.
- After that, pick the dollar ($) symbol in the Symbol box.
- Consequently, click OK.
By our previous actions, the cells in the C6:E21 range get changed their format.
Read More: Excel Pivot Table Formatting (The Ultimate Guide)
Similar Readings
- How to Track Income and Expenses in Excel (4 Suitable Examples)
- Make a Line Graph in Excel with Multiple Lines (4 Easy Ways)
- How to Show Zero Values in Excel Pivot Table: 2 Pro Tips
- Use GETPIVOTDATA in Excel (4 Useful Examples)
- How to Reverse Pivot Table in Excel (3 Easy Ways)
Step 06: Utilize Filter Options
Also, we can Filter the table according to our preference. Let’s say, we want to know the information for just Monday and Tuesday. For this type of situation, we can apply the Filter. Allow me to demonstrate the process.
- First of all, select any cell inside the PivotTable to open the PivotTable Fields task pane.
- Then, drag the Weekday field into the Filters area.
Thus, we can see our Filter applied in B4:C4.
To use the Filter, watch the following steps.
- Initially, click on the upside-down arrowhead beside (All) in cell C4.
- Then, check the box of Select Multiple Items.
- Later, select Monday and Tuesday only.
- Correspondingly, click OK.
If you compare the following picture with the previous picture, you will get the difference in the amounts. Because, in this image, it shows the amounts of just Monday and Tuesday. At this moment, all other days have been excluded from the report.
Also, we can insert Slicers in the report. It’ll make the report more dynamic. Then, you can change the report within a few clicks. So, let’s explore this step by step.
- Primarily, select any cell inside the table.
- Secondarily, move to the PivotTable Analyze tab.
- After that, click on the Filter group drop-down.
- Later, select Insert Slicer on it.
- Immediately, the Insert Slicers dialog box opens.
- Here, check the boxes of AcctType and OpenedBy.
- Then, click OK.
Presently, we can see two Slicers placed in the worksheet beside the PivotTable.
In this instance, let’s get introduced to the functionality of the Slicer.
- In AcctType 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 CD, Checking, and IRA from the list.
Currently, we can notice that the Savings account type isn’t visible in the report. Actually, it gets excluded because of the usage of the Slicer.
Another beneficial tool to use in the PivotTable report is the Timeline. We can easily manipulate the report using this feature. Let’s see the process in detail.
- Similarly, go to the Filter group.
- Then, select Insert Timeline on it.
- In the Insert Timelines wizard, select Date and click OK.
Hence, it includes the Date Timeline on the worksheet.
- In the filtering method, select Date as the parameter.
- Later, draw the bar inside Nov 4 to 7.
As a result, we can see the report for Nov 4-7 only.
Read More: Excel Pivot Table Slicers: All Things You Need to Know
Step 07: Show Report Filter Pages
Suppose, you want reports for Monday and Tuesday in separate worksheets. You can do this also. So, without further delay, let’s dive in!
- Firstly, proceed to the PivotTable Analyze tab like before.
- Then, click on the PivotTable group drop-down.
- From the drop-down list, select Options.
- After that, click on Show Report Filter Pages on the sub-list.
- In the Show Report Filter Pages dialog box, click OK.
Simply, it creates two new worksheets named Monday and Tuesday.
- Then, click on these sheet names to open the worksheet.
- Here, you will find the detailed reports for the corresponding days in separate worksheets.
Read More: How to Create Pivot Table in Excel for Different Worksheets
Step 08: Insert Pivot Chart in Report
Furthermore, you can make the report more appealing to the audience by inserting a Chart into it. Let’s see it in action.
- Like before, go to the PivotTable Analyze tab.
- Then, click on the Tools group drop-down.
- After that, select PivotChart on it.
- Suddenly, the Insert Chart dialog box appears.
- Here, select Column from the All Charts list.
- Later, choose 2-D Clustered Column from the options.
- Lastly, click OK.
Without any elaboration, it inserts a PivotChart into the report.
Thus, you can visualize the data easily and will get decisions more quickly. Also, it’ll be beneficial to audiences also to realize the report effortlessly.
Read More: How to Create Pivot Tables for Meaningful Data Analysis!
Practice Section
For doing practice by yourself we have provided a Practice section like the one below in the last sheet of the workbook.
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. Or you can check out our other articles related to Excel functions on this website ExcelDemy, a one-stop Excel solution provider.
