How to Create Pivot Table in Excel - A Step-by-Step Guide

In this detailed blog, we will walk you through the step-by-step process of creating Pivot Tables, from the basics to more advanced techniques. By the end of this blog, you’ll be equipped with the skills to make informed decisions based on your data.

Table of Contents

• What is a Pivot Table?
  • What are Pivot Tables Used for?
• Types of Pivot Tables
• How to Create a Pivot Table in Excel
  • How to Create Pivot Table in Excel Using Macros
  • How to Create Power Pivot Table in Excel
• Pivot Table Examples
• How to Create Slicer in Excel Without Pivot Table
• Pivot Table Must-Knows
• Conclusion

Unlock the power of Excel with our Microsoft Excel Tutorial – watch now and supercharge your spreadsheet skills!

What is a Pivot Table?

A pivot table is a tool that summarizes selected data columns and rows in a spreadsheet or database. It helps obtain a desired report. The tool doesn’t change the spreadsheet or database, it just pivots the data to see it from different angles.

Pivot tables are very helpful when there is a lot of data that would take a long time to calculate manually. A pivot table can do several data processing tasks, like finding sums, averages, ranges, or outliers. The table organizes the information in a clear layout that highlights important values.

A pivot table is a common term, but it can be mixed up with Microsoft’s trademarked term, Pivot Table. This is a particular Excel tool for making pivot tables.

Unlock your Excel mastery with Intellipaat’s Advanced Certification in Microsoft Excel today!

What are Pivot Tables Used for?

If you’re still not sure what pivot tables do, don’t worry! One of those technologies that becomes easier to understand after being used in practice.

Pivot tables are tools that help you quickly make sense of lots of data. They make it easier to look at and study numbers. Once you get the hang of them, you can use pivot tables to figure out answers to unexpected questions about your data.

Here are three examples where pivot tables could come in handy:

1. Creating a Pivot Table to Compare the Sales Totals of Various Products

Let’s say you’ve got a spreadsheet containing monthly sales data for three different products: product A, product B, and product C. Your goal is to find out which of these products is bringing in the most revenue.

One approach might involve going through the spreadsheet, adding up the sales amount every time you encounter product A, and then repeating the process for product B and product C. It’s not too hard, right? 

Now, picture this: your monthly sales sheet has thousands upon thousands of rows. Manually sifting through all that data could literally take forever.

But with pivot tables, you can automatically group and sum up all the sales figures for product A, product B, and product C in under a minute.

Intellipaat provides a Power BI Course. Enroll now to get a professional certificate!

2. Creating a Pivot Table to Display Product Sales as Percentages of the Total Sales

When you create pivot tables, they show the totals of rows or columns. But they can do more than that.

Picture yourself inputting quarterly sales data for three products into an Excel sheet. Then, convert it into a pivot table. The pivot table automatically shows you three totals at the bottom of each column, representing the combined sales for each product over the quarters.

But what if you want to know the percentage of these product sales in relation to overall company sales rather than just their individual totals?

With a pivot table, you can configure each column to not only display the column total but also show the percentage that each column’s total contributes to the sum of all three columns.

For example, if the three products together had total sales of $6500 and the first product A generated $1,800, you can modify the pivot table to indicate that this product contributed 27.69% to the company’s overall sales.

3. Calculating the Number of Employees in Individual Departments

Pivot tables help do complex calculations in Excel that basic tables can’t handle. One such task is counting rows that share a common attribute.

For example, suppose you have a list of employees in an Excel sheet, and alongside their names, you have recorded the departments they work in. You can generate a pivot table from this data that presents the department names and the count of employees in each department.

The pivot table automates organizing the Excel sheet by department and counting the rows.

Struggling to find the right AI tools for data analysis? Browse this blog on Excel AI Tools.

Types of Pivot Table

Let us now explore the three most popular types of pivot charts in Excel:

1. Compact Layout: This format is the most succinct, showcasing all data on a single page. It works well for small datasets or swiftly summarizing key data elements.
2. Outline Layout: This arrangement presents data hierarchically, allowing you to expand and collapse levels. It suits extensive datasets or exploring diverse data relationships.
3. Tabular Layout: Data is exhibited in a tabular structure, where each row signifies a distinct data point. This format is optimal for generating comprehensive reports or making side-by-side comparisons of various data categories.

Check out Intellipaat’s guide on MS Excel Interview Questions to crack good interviews for data analysis jobs.

How to Create a Pivot Table in Excel

Here is the method that is mentioned to create a Pivot Table in Excel:

Assume you have a simple data set containing sales data for a retail store.

Data Set:

Assume you have a simple data set containing sales data for a retail store.

Steps:

1. Prepare Your Data: Ensure your data is organized with headers.
2. Select Your Data: Click anywhere within your data range.
3. Create the Pivot Table:
   1. For Excel 2016 and later versions:
   2. Go to the “Insert” tab.
   3. Click “Pivot Table.”
   4. In the dialog box, ensure your data range is selected and choose where to place the Pivot Table.
   5. Click “OK.”
4. Design Your Pivot Table:
   1. In the Pivot Table Field List, drag and drop fields:
   2. Drag “Product” to “Rows.”
   3. Drag “Sales” to “Values.”

Your Pivot Table should look like this:

Learn how to use AutoSum to quickly and easily sum numbers in your Excel spreadsheets!

How to Create a Pivot Table in Excel Using Macros:

Here is the method that is mentioned to create a Pivot Table in Excel using Macros:

Data Set:

Steps:

1. Open the Visual Basic for Applications (VBA) Editor:
   1. Press ALT + F11 to open the VBA editor in Excel.
2. Write VBA Code:

Sub CreatePivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim ptField As PivotField
    Dim dataRange As Range
    Dim pivotCache As PivotCache
    ' Set a reference to the worksheet with your data
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Define the data range for the Pivot Table (adjust as needed)
    Set dataRange = ws.Range("A1:K11")
    ' Create a PivotCache
    Set pivotCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=dataRange)
    ' Create a new worksheet for the Pivot Table
    Set pt = pivotCache.CreatePivotTable( _
        TableDestination:=Worksheets.Add.Range("A1"), TableName:="MyPivotTable")
    ' Add fields to the Pivot Table
    With pt.PivotFields("Food Item")
        .Orientation = xlRowField
        .Position = 1
    End With
    With pt.PivotFields("Calories")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        .NumberFormat = "0"
    End With
    ' Add more fields as needed
End Sub

3. Run the Macro:
   1. Close the VBA editor and run the macro by pressing ALT + F8, selecting your macro, and clicking “Run.”

The result is the same as creating a Pivot Table manually.

How to Create Power Pivot Table in Excel

Power Pivot is a powerful data analysis and modeling tool available in Microsoft Excel. It’s an add-in that extends Excel’s capabilities to handle large and complex datasets and perform advanced data transformations, calculations, and modeling. 

Steps:

1. Enable Power Pivot:
   1. Go to “File” > “Options” > “Add-Ins.”
   2. In the “Add-Ins” dialog, select “COM Add-ins” from the dropdown and click “Go…”
   3. Check “Microsoft Office Power Pivot” and click “OK.”

2. Import Your Data into Power Pivot:
   1. Click on the “Power Pivot” tab on the ribbon.
   2. Select “Add to Data Model” to import your data into Power Pivot.

3. Design Your Power Pivot Table:
   1. Open the Power Pivot window.
   2. Drag and drop fields into the “Rows,” “Columns,” and “Values” areas to design your Pivot Table.
   3. Use DAX (Data Analysis Expressions) formulas for advanced calculations if needed.

4. Create a Pivot Table:
   1. Go back to the Excel worksheet.
   2. Insert a Pivot Table and choose to use the “Data Model.”

Your Power Pivot Table can now handle larger data sets and more complex calculations.

Check out the Power BI Interview Questions For 3 Years Experience if you are preparing for a job interview.

Pivot Table Examples

From handling finances to monitoring your marketing campaigns, pivot tables can be your go-to tool for managing crucial data. The potential uses are limitless!

Check out the three pivot table examples below to keep your creative juices flowing and discover new ways to leverage this powerful tool.

1. Sales by Product and Region

Data Set:

Assume you have a sales data set with information about products, sales regions, and sales amounts.

Pivot Table Use:

Calculate the total sales amount for each product in each region.

Steps:

• Create a Pivot Table with “Product” in the Rows area and “Region” in the Columns area.
• Place the “Sales” field in the Values area, and set it to summarize as “Sum.”

Resulting Pivot Table:

  2. Employee Count by Department

Data Set:

Assume you have an employee data set with employee names and their respective departments.

Pivot Table Use:

Calculate the number of employees in each department.

Steps:

• Create a Pivot Table with “Department” in the Rows area.
• Place any field (e.g., “Employee Name”) in the Values area. Excel will count the number of employees by default.

Resulting Pivot Table:

    3. Average Test Scores by Subject

Data Set:

Assume you have a student data set with student names, subjects, and test scores.

Pivot Table Use:

Calculate the average test score for each subject.

Steps:

• Create a Pivot Table with “Subject” in the Rows area.
• Place the “Test Score” field in the Values area, and set it to summarize as “Average.”

Resulting Pivot Table:

4. Yearly Sales Trends

Data Set:

Assume you have a sales data set with transaction dates and sales amounts.

Pivot Table Use:

Analyze yearly sales trends.

Steps:

• Create a Pivot Table with “Date” in the Rows area.
• Place the “Sales Amount” field in the Values area, and set it to summarize as “Sum.”
• Group the “Date” field by years.

Resulting Pivot Table:

Pivot Tables can summarize and analyze data in many ways. They can aggregate sales data, calculate averages, and track trends. You can adapt these examples to your specific data and analysis needs.

Learn more about Power BI from this Power BI Tutorial!

How to Create Slicer in Excel without Pivot Table

Creating a slicer in Excel without a Pivot Table can be done by utilizing Excel Tables (also known as lists in older Excel versions). Slicers are often associated with Pivot Tables, but you can apply slicers to filter data within an Excel Table. Here’s how to create a slicer in Excel without a Pivot Table:

Data Set: Assume you have a dataset with information about salespeople, regions, and sales amounts.

Steps:

1. Prepare Your Data: Ensure your data is well-structured with column headers and rows of data. Each column should have a clear label, and your data should be organized in a tabular format.

2. Convert Your Data to an Excel Table:
   1. Select any cell within your data range.
   2. Go to the “Insert” tab in Excel.
   3. Click on “Table” or “Table” (depending on your Excel version). Ensure the “My table has headers” option is checked if your data has headers.

3. Design Your Excel Table:
   1. Customize your table design if necessary. Excel will automatically apply a consistent style to your table.
   2. You can rename your table by typing a new name in the “Table Name” field in the “Design” tab that appears when you select any cell within the table.

4. Create a Slicer:
   1. Click anywhere within your Excel Table.
   2. Go to the “Table Tools Design” tab that appears when your table is selected.
   3. Click on “Insert Slicer.”
   4. In the “Insert Slicers” dialog box, select the column by which you want to filter your data (e.g., “Region”).
   5. Click the “OK” button.

5. Use the Slicer to Filter Data:
   1. The slicer will appear as a separate visual element on your worksheet.
   2. Click on a region within the slicer to filter your table data based on that selection. For example, if you click “East,” your table will display only the rows where the region is “East.”

6. Customize the Slicer (Optional):
   1. You can customize the slicer’s appearance and behavior by right-clicking on the slicer and selecting “Slicer Settings.” Here, you can adjust the slicer’s appearance, layout, and interaction with your table.

By following these steps, you can create a slicer in Excel without using a Pivot Table, allowing you to easily filter and interact with your data for analysis or reporting purposes.

Pivot Table Must-Knows

Certain tasks are essential when it comes to creating and using pivot tables. To make these tasks easier, we’ve included detailed, step-by-step instructions on how to complete them.

How to Create a Pivot Table With Multiple Columns

Now that you’ve got the hang of creating a pivot table, let’s take it up a notch and create one with multiple columns. Follow these steps:

1. Select Your Data Range: Pick the data you want to use in your pivot table, making sure to include the column headers.
2. Insert a Pivot Table: Head to the “Insert” tab on the Excel ribbon and click the “Pivot Table” button.
3. Choose Your Data Range: In the “Create Pivot Table” dialog box, double-check that Excel has automatically chosen the correct data range. Also, specify where you want the pivot table to be placed, whether it’s a new worksheet or an existing one.
4. Designate Multiple Columns: In the Pivot Table Field List, simply drag and drop the fields you want to appear as column labels into the “Columns” area. These fields will be represented as multiple columns in your pivot table.
5. Add Row Labels and Values: Drag and drop the fields you want to summarize or display as row labels into the “Rows” area.

Likewise, drag and drop the fields you intend to use for calculations or aggregations into the “Values” area.

• Personalize the pivot table to your liking: You can enhance your pivot table by modifying the layout, implementing filters, arranging data, and formatting it as necessary.

How to Copy a Pivot Table

To copy a pivot table in Excel, here’s what you need to do:

1. Select the Entire Pivot Table: Click anywhere inside the pivot table, and you’ll see selection handles around it, indicating it’s selected.
2. Copy the Pivot Table: Right-click on the selected pivot table, then choose “Copy” from the context menu. Alternatively, you can use the keyboard shortcut Ctrl+C.
3. Choose the Destination: Navigate to the worksheet where you want to paste the copied pivot table.
4. Paste the Pivot Table: Right-click on the cell where you want to place the copied pivot table, and select “Paste” from the context menu. You can also use the keyboard shortcut Ctrl+V.
5. Adjust the Pivot Table Range (if Needed): If the pasted pivot table overlaps with existing data, you might need to resize it to avoid overwriting the existing information. Simply click and drag the corner handles of the pasted pivot table to adjust its size as necessary.

By following these steps, you can easily duplicate or move pivot tables within the same workbook or even transfer them to different worksheets or workbooks. This gives you the flexibility to organize your data effectively in Excel.

How to Sort a Pivot Table

To sort a pivot table in Excel, here are the steps:

1. Select the Column or Row to Sort:
   1. If you want to sort a column, click on any cell within that column in the pivot table.
   2. If you want to sort a row, click on any cell within that row in the pivot table.
2. Sort in Ascending or Descending Order:
   1.  Right-click on the selected column or row.
   2. Choose “Sort” from the context menu.
   3. In the “Sort” submenu, select either “Sort A to Z” for ascending order or “Sort Z to A” for descending order.

Alternatively, you can use the sort buttons on the Excel ribbon:

1. Go to the Pivot Table Tab:
   1. Select the pivot table.
   2. Access the “Pivot Table Analyze” or “Pivot Table Tools” tab on the Excel ribbon (the name may vary depending on your Excel version).
2. Sort the Pivot Table:
   1.  In the “Sort” group, click on the “Sort Ascending” button (A to Z) for ascending order or the “Sort Descending” button (Z to A) for descending order.

These methods allow you to easily organize your pivot table data in either ascending or descending order, depending on your needs.

Conclusion

We’ve talked about the basics, how to use it practically, and different ways to create it, like macros and Power Pivot. We also explored slicers for interactive filtering and  real-world examples that demonstrated pivot tables’ versatility.

Remember these pivot table essentials: data source connections, refreshing data, and layout customization. Pivot tables empower you to turn raw data into meaningful insights, giving you a competitive edge in data-driven tasks. With this knowledge, you’re well-prepared to navigate Excel’s data analysis landscape. So, explore, experiment, and harness the power of pivot tables for success in your data-driven journey.

Still in a doubt? Visit our Business Intelligence Community.