US Trends

how to make a pivot table in excel

To make a pivot table in Excel, you need a clean table of data with headers, then use Insert → PivotTable and drag fields into Rows, Columns, Values, and Filters to analyze your data in different ways.

How to Make a Pivot Table in Excel (2026 Guide)

What is a Pivot Table?

A pivot table lets you quickly summarize large lists of data (like sales, orders, tickets) without writing formulas.

You can group, total, count, and filter by different fields (for example, total sales by region, by product, by year) just by dragging fields around.

Step 1: Prepare Your Data

Before you start, make sure your source data is “pivot‑ready.”

  • Put your data in a rectangular table : each column is a field (e.g., Date, Region, Product, Amount).
  • Include a single header row with unique, non‑blank column names.
  • Avoid blank rows and blank columns inside the data range.
  • Don’t use merged cells in the header or data area.
  • (Recommended) Convert your range to an Excel Table:
    • Select any cell in your data.
    • Go to Insert → Table and confirm the range and that “My table has headers” is checked.

This keeps your pivot table more reliable and makes it easier to refresh when data changes.

Step 2: Insert a Pivot Table

Once the data is ready, you can create your pivot table.

  1. Click anywhere inside your data (or select the range manually).
  1. Go to the Insert tab on the ribbon.
  1. Click PivotTable.
  1. In the “Create PivotTable” dialog:
    • Check that the correct table/range is selected.
 * Choose where to place it:
   * **New Worksheet** (recommended for clarity), or
   * **Existing Worksheet** and pick a starting cell.
  1. Click OK.

You’ll see an empty pivot layout on the sheet and a PivotTable Fields pane on the right.

Step 3: Understand the PivotTable Fields Pane

In the PivotTable Fields pane, you’ll see:

  • A list of field names (your column headers).
  • Four key areas you can drag fields into:
* **Rows** – categories listed down the left (e.g., Product, Region).

* **Columns** – categories across the top (e.g., Year, Month).

* **Values** – numbers to be calculated (e.g., Sum of Sales, Count of Orders).

* **Filters** – top‑level filter for the whole pivot (e.g., filter by Year or Region).

You build your pivot by dragging fields into these areas; you can also tick checkboxes and then rearrange them.

Step 4: Build a Simple Pivot Table (Example)

Imagine you have columns: Date, Region, Product, SalesAmount. To get “Total Sales by Region”:

  1. Drag Region to Rows.
  1. Drag SalesAmount to Values.
  1. Excel will usually default to Sum of SalesAmount for numeric fields.

Now you have a basic pivot table showing each region with its total sales. To get “Total Sales by Region and Product”:

  1. Keep Region in Rows.
  1. Drag Product under Region in the Rows area (or into Columns if you prefer).
  1. Keep SalesAmount in Values.

You’ll see a breakdown of sales for each product within each region.

Step 5: Change Calculations (Sum, Count, Average, %)

You aren’t limited to sums; you can use other calculations on your values.

  • Click the drop‑down next to the field in the Values area and choose Value Field Settings.
  • Under “Summarize Values By,” choose:
    • Sum – total sales, total hours, etc.
* **Count** – number of rows/transactions.

* **Average** – average order value, average score.

* **Max/Min** , etc.

You can also show values as percentages (e.g., % of grand total or % of column total) through the Show Values As options.

Step 6: Sort and Filter Your Pivot Table

Sorting and filtering help you find patterns quickly.

  • Sort :
    • Click any value in a row or column label.
    • Use Sort A to Z / Z to A on the ribbon under PivotTable Analyze / PivotTable Tools.
  • Filter with the Filters area:
    • Put a field (e.g., Year) into Filters.
* Use the drop‑down above the pivot to show only selected values.
  • You can also filter directly on row and column labels using their drop‑downs.

This lets you quickly answer questions like “Top regions this year only” or “Sales for a specific product line.”

Step 7: Refresh When Data Changes

If you add or change data in the source:

  • If your source is an Excel Table , new rows are automatically included in the pivot’s source range when you refresh.
  • Right‑click anywhere in the pivot and choose Refresh , or use PivotTable Analyze → Refresh.

Refreshing updates all calculations without rebuilding the pivot.

Step 8: Helpful Extras (Calculated Fields, Design, Charts)

Once you’re comfortable, you can explore more features often used in recent tutorials and 2020s Excel training.

  • Calculated fields
    • Add new metrics that are formulas based on existing fields (e.g., Profit = Sales – Costs).
* Use **PivotTable Analyze → Fields, Items & Sets → Calculated Field**.
  • Layout & Design
    • On the Design tab, change report layout (tabular/outline), show or hide subtotals and grand totals, and apply pivot styles for clearer reports.
  • Pivot charts
    • Turn your pivot into a chart via Insert → PivotChart or the PivotTable Tools.
* Pivot charts update automatically when you filter or refresh the pivot table.

These features are especially popular in current Excel courses and YouTube tutorials that focus on fast, visual analysis.

Simple HTML Table Example (Fields Layout)

Here’s a small HTML table showing a typical field layout for “Sales by Region and Product”:

html

<table border="1">
  <thead>
    <tr>
      <th>Rows</th>
      <th>Columns</th>
      <th>Values</th>
      <th>Filters</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Region</td>
      <td>Product</td>
      <td>Sum of SalesAmount</td>
      <td>Year</td>
    </tr>
  </tbody>
</table>

This setup gives a grid where each cell shows total sales for a product in a region, filterable by year.

Mini “Quick Scoop” Recap

  • Use clean, tabular data with one header row and no blanks or merged cells.
  • Insert a pivot via Insert → PivotTable , choose table/range and location.
  • Drag fields into Rows, Columns, Values, Filters to build your analysis.
  • Change Value Field Settings to switch between Sum, Count, Average, etc.
  • Sort, filter, and refresh to keep your pivot accurate and insightful.

Information gathered from public forums or data available on the internet and portrayed here.