In this lesson, you will learn how to use Pivot Table in Excel.

What is a Pivot Table?

A pivot table is a powerful tool that summarizes and analyzes data in Excel. It allows you to rearrange data, see trends and patterns, and create reports.

Pivot tables are easy to use, even for beginners. You can create a pivot table by selecting the data you want to summarize, and then clicking on the PivotTable button.

In the PivotTable Wizard, you can select the location for the pivot table and the fields that you want to include. You can also customize the pivot table by changing the layout, adding filters, and creating calculated fields.

Pivot tables are a valuable tool for anyone who works with data. They can help you to make better decisions, identify trends, and communicate your findings to others.

Data Preparation

Before you create a Pivot Table, make sure your data is properly organized. Here are some things to check:

• Column headings: Unique column headings should be placed in the first row. Columns cannot be empty.
• Rows: Each row must have the same number of columns.
• Cells: Cells should not be merged.
• Separation: The data to be analyzed should be separated from other data by at least one empty column and one empty row.
• Data meaning: Each row should represent an item (city, person, product) and each column should represent information about that item (date, name, amount, size).
• Common parts: For the Pivot Table to be effective, at least some of the columns should have repeated elements.
• Avoid empty rows: Empty rows will cause only part of the table to be used in the Pivot Table.
• One data type per column: The Pivot Table can only perform certain operations on specific data types (e.g., dates or numbers). If you try to group dates and at least one cell is a different data type, you will not be able to group them because of the type inconsistency.
• Valid data type per column: Sometimes data may look correct but actually be incorrect. Be careful, especially when importing data from an external source. Make sure that numbers are real numbers and not text. Excel cannot sum up text.
• No subtotals: Your table should not contain subtotals or any rows that summarize parts of the data.

How to insert the Pivot Table?

I have prepared sample sales data for an imaginary company.

The table contains columns such as:

• Date
• Product
• Category
• Customer
• CRM Customer Group
• Region
• State
• City
• Sales Manager
• Sales Rep
• Price
• Quantity
• Sales

To insert a pivot table, go to the **Insert** tab and click the **Pivot Table** button. You can also use the keyboard shortcut `ALT + N + V`.

To create a pivot table, select the range of cells you want to analyze. Excel will automatically identify the correct range in most cases. Click **OK** to create the empty pivot table on a new worksheet.

Populate Pivot Table With Data

To populate a pivot table with data, drag and drop the column labels into the four areas on the right side of the screen. You can experiment with different combinations to see how the pivot table changes.

Here are the four areas:

• Rows
• Columns
• Values
• Filters

The possibilities for creating different tables based on the same data set are endless. The only limitation is your imagination.

Here are some common rules to follow when creating a pivot table:

• Filters: Use this area to filter the data in your pivot table. For example, you could filter by region, date, or customer.
• Rows: This area shows the values of the columns you drag into it. For example, if you drag the “Region” column into the “Rows” area, your pivot table will show the sales for each region.
• Columns: This area shows the values of the columns you drag into it. For example, if you drag the “Product” column into the “Columns” area, your pivot table will show the sales for each product.
• Values: This area shows the calculations that are performed on the data in your pivot table. For example, you could calculate the total sales or the average sales.

The more you use pivot tables, the more familiar you will become with the different areas and how to use them.

You can put two different columns in the same area. This will create a more detailed pivot table, but it may also make it more difficult to read.

Let’s see an example: I created a basic report of sales products for customers.

Excel calculated many useful numbers in the pivot table. You can see the sales of each product for each customer. The pivot table also shows the total sales for each row and column.

Adding more categories of products to the pivot table will make it more detailed, but it may also make it more difficult to read.

Let’s see how to create better reports.

Formatting Pivot Table

Right-click on the data values and choose “Number Format” to format your pivot table.

I’ll choose the Currency format to show the values as sales revenues. I’ll reduce the decimal places to 0 to save space.

Number formatting makes the report easier to read.

Sort Pivot Table

To improve visibility even further, sort the pivot table by clicking on the bottom row and selecting “Sort Largest to Smallest”.

Sort the data in the last column in descending order.

This is how the report looks now.

The pivot table is now easier to read because the sales are sorted in descending order. This makes it easier to see the top-performing products and regions.

You can also sort the pivot table by other criteria, such as date or customer.

Keeping your pivot table in a logical order makes it look more professional and helps you to communicate your findings more effectively.

Pivot table filtering

A pivot table can be a very complex report, so you may need to filter the data. To do this, you can add filters to the pivot table.

In this example, I added filters for Region, State, and City.

The filter appears at the top of the pivot table. I filtered the data to show only sales from the Central region in Kansas.

The pivot table shows only sales from the Central region in Kansas. The data is sorted in descending order.

Slicer

Another way to filter a pivot table is to use a slicer.

A slicer is a tool that allows you to quickly filter the data in a pivot table by one or more fields.

To insert a slicer, click on a cell in the pivot table and then go to the Analyze tab.

You will see a window with the options for inserting slicers.

Choose any product you want. I will click on “Product” to see the list of products.

The slicer shows me some useful information even before I select anything. I can see that one product is grayed out, which means that there is no data for that product. I can select a product to see the filtered data quickly.

I can click on each product in the slicer to see how the pivot table data changes. The slicer filters the table very quickly.

To select more than one product, hold down the Ctrl key and click on the other products.

You can add more slicers to filter the data even further. To do this, click on a cell in the pivot table and then use the Insert Slicer button again.

I added another slicer to filter by customer.

My data is filtered the way I want it. If you don’t like the slicer, you can remove it by right-clicking it and selecting “Remove”.

You can also keep the slicer and remove its filter. To do this, click the Clear Filter button in the slicer.

Updating (refreshing pivot table)

Pivot tables do not automatically update when the source data changes. To update a pivot table, right-click it and select “Refresh”.

Updating the data in a pivot table takes just a second. The formatting will remain the same.

Related posts:

How to use Power Query in Excel How to Use DAX (Data Analysis Expressions) Functions How To Use Slicers In Excel