fbpx

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.