Before we start learning How to create a Pivot table in Excel, We must learn What is a Pivot Table.

What is Pivot Table in Excel?

A pivot table is for sorting information. It’s great for calculating and summarizing information that you can use to break down large tables into just the right amount of information you need. You can use Excel to create a recommended Pivot Table or create one manually. Pivot tables are one of Excel’s most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. Now let’s go through the following steps to create Pivot Table.

Some Issues about Pivot Table

  • You data should be organized in a tabular format and not have any blank rows or columns.
  • Tables are a great PivotTable data source because rows added to a table are automatically included in the PivotTable when you refresh the data and any new columns will be included in the PivotTable Fields List. Otherwise, you need to either manually update the data source range, or use a dynamically named range formula.
  • Data types in columns should be the same. For example, you shouldn’t mix dates and text in the same column.
  • PivotTables work on a snapshot of your data, called the cache, so your actual data doesn’t get altered in any way.

Create a table
Step 1: Create a table same as like above picture. This table shows the simple list of household expenses and we will create a PivotTable based on it. Create pivot table in 2 ways
Step 2: You can Create Pivot Table in 2 ways:

  • Recommended PivotTable
  • Manually create a PivotTable

Recommended PivotTable is the shortcut and easy way to create Pivot Table. Let’s see how to create Recommended PivotTable. Recommended PivotTable is a good choice if you have limited experience with PivotTables, or are not sure how to get started. Click on a cell from our table. Then Go to Insert tab > Tables Groups> Recommended PivotTable.

Excel analyzes our data and presents us with several options
Step 3: Excel analyzes our data and presents us with several options, like in this example using the household expense data. Select the PivotTable that looks best to you and press OK. Excel will create a PivotTable on a new sheet and display the PivotTable Fields List.

New Pivot Table in New Sheet
Step 4: Our new Pivot Table in New Sheet will be looked like above picture. If we click on Pivot table we will get Pivot Table Fields Box on the right side of Excel Interface. There are many options in Pivot Table Fields Box to customization.

Customize Pivot Table and reverse the row to column and column to row
Tips 1: Customize Pivot Table and reverse the row to column and column to row. In Pivot Table Fields Box click and drag Month from box Rows to box Column. Now see the magic! Now the table is changed, Months are on the row and Sum of the amount of expenses in another row below the Month like above picture.

You can add Filter option in your Pivot Table in Excel
Tips 2: You can add a Filter option to your Pivot Table, Just drag a field from Row box or Column box to Filter box like above picture.

Add more field in Pivot Table
Tips 3: Add more field in Pivot Table. Here we can also add Category field in Pivot table. Just check it in Pivot Table Fields box. Then the pivot table with filter option will be looked like above picture.

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.