Pivot Table Issues

How to Summarize Excel Sales Data by SKU Using a PivotTable

Steve K Oct 10, 2026 868 views

Question details

The user needs to summarize large sales datasets by Merchant SKU efficiently without manually sorting data or inserting formulas and subtotals below each group.

How to Summarize Excel Sales Data by SKU Using a PivotTable

Product: Excel
Device & OS: not provided
Scenario: Organizing and analyzing large volumes of sales data to calculate total shipped quantities and item prices grouped by Merchant SKU.
Observed behavior: The user is currently sorting the SKU column and manually adding totals below each group, which is a highly inefficient and error-prone process. They also struggle to locate the PivotTable Analyze tab.

Before you start

Ensure your sales data is organized in a clear tabular format with unique column headers (e.g., Merchant SKU, Shipped Quantity, Item Price) and remove any completely blank rows or columns within the dataset.

Solution 1Recommended

Use a PivotTable to Summarize Data by SKU

This is the most efficient method to automatically group your sales by Merchant SKU and instantly calculate total quantities, total prices, and averages.

Using a PivotTable replaces the need to manually sort rows and insert subtotal formulas. It dynamically aggregates all identical SKUs into a single row and calculates their corresponding values.

Insert a PivotTable

Select any cell within your source data, navigate to the 'Insert' tab on the top ribbon, and click 'PivotTable'. Choose to place it on a New Worksheet and click OK.

Group by Merchant SKU

In the PivotTable Fields pane on the right side of the screen, click and drag the 'Merchant SKU' field into the 'Rows' area. Your table will now list each unique SKU once.

Calculate Total Quantity and Price

Drag 'Shipped Quantity' and 'Item Price' into the 'Values' area. Ensure the summarization type is set to 'Sum' so that it calculates the total for each SKU.

Add a Calculated Field for Average Price

To find the average price per unit, click inside the PivotTable to reveal the 'PivotTable Analyze' tab. Click 'Fields, Items, & Sets', select 'Calculated Field', and enter a formula such as '= Item Price / Shipped Quantity'.

Use a PivotTable to Summarize Data by SKU

Locating the PivotTable Analyze Tab: The PivotTable Analyze tab is contextual. It will only appear on your main ribbon when you actively click on a cell inside the generated PivotTable.

Smart Data Summarization

Summarize Sales Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet offers a robust, easy-to-use PivotTable tool that lets you group large sales datasets by SKU, generate instant sums, and add calculated fields without any manual sorting or formula entry.

1. Open Your Data: Launch WPS Office and open your sales dataset in WPS Spreadsheet.
2. Insert PivotTable: Navigate to the 'Insert' tab on the top ribbon and select 'PivotTable'.
3. Configure Rows: In the PivotTable configuration pane on the right, drag the 'Merchant SKU' field into the 'Rows' box to group your items.
4. Calculate Values: Drag the 'Shipped Quantity' and 'Item Price' fields into the 'Values' box to automatically generate the total sums for each SKU.

100% compatible with Microsoft Excel (.xlsx and .xls) data formats.Intuitive drag-and-drop PivotTable interface for quick reporting.Built-in support for complex calculated fields to find averages instantly.Free and lightweight software ideal for processing large sales reports.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the PivotTable Analyze tab missing from my ribbon?

The PivotTable Analyze (or Options) tab is a contextual menu that only activates when you are actively working within a PivotTable. Click on any cell inside your existing PivotTable, and the tab will instantly appear at the top of your screen.

Do I need to sort my SKUs before creating a PivotTable?

No. A PivotTable automatically aggregates and groups all identical SKUs regardless of their original order in the source data. You can skip the manual sorting process entirely.

How do I calculate the average price per unit instead of the total sum?

You can either change the Value Field Settings from 'Sum' to 'Average', or create a specific Calculated Field. To do this, go to the PivotTable Analyze tab, select 'Fields, Items & Sets' > 'Calculated Field', and input the formula '= Item Price / Shipped Quantity'.

Will my PivotTable update automatically if I add new sales records?

PivotTables do not refresh in real-time. If you add new sales records to your source dataset, you must click inside the PivotTable, go to the PivotTable Analyze tab, and click the 'Refresh' button to update your totals.

Excel & Data Problems