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.

Pre-Registration Open

Power BI Dax

Related Tutorials

Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
How to use AVERAGEIFS FUNCTION in Excel
December 10, 2018
How to use AVERAGEIF FUNCTION
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
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 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