How to Create Pivot Table Report (with Easy Steps)

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.

create pivot table report

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.

Specify the Data Range

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

Specify the Data Range to Create PivotTable Report


Step 02: Create a Blank Pivot Table

As a result of our previous actions, Excel created a blank PivotTable in a new worksheet.

Create a Blank Pivot Table

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

Create a Blank Pivot Table to Create Report

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.

Lay out the Pivot Table

And the final output looks like the following one.

Lay out the Pivot Table to Create Report

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.

Change the Layout of Pivot Table Report

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.

Alter Number Format

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

Alter Number Format to Create Pivot Table Report

Read More: Excel Pivot Table Formatting (The Ultimate Guide)


Similar Readings


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.

Utilize Filter Options

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.

Utilize Filter Options of Pivot Table Report

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.

Utilize Filter Options

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.

Inserting Slicer in Report

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

Inserting Slicer in Report

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.

Inserting Slicer in Pivot Table Report

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.

Inserting Timeline

  • In the Insert Timelines wizard, select Date and click OK.

Hence, it includes the Date Timeline on the worksheet.

Inserting Timeline into the Pivot Table Report

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

Inserting Timeline

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.

Show Report Filter Pages

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

Show Report Filter Pages of Pivot Table

Simply, it creates two new worksheets named Monday and Tuesday.

Creating Two different sheets

  • Then, click on these sheet names to open the worksheet.
  • Here, you will find the detailed reports for the corresponding days in separate worksheets.

Show Report Filter Pages of Pivot Table

Show Report Filter Pages of Pivot Table

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.

Insert Pivot Chart in Report

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

Pivot Chart to Create the Report More Appealing

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.

Practice Section


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.


Related Articles

Khan Muhammad Kawser

Khan Muhammad Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

1 Comment

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo