How to Show the First Serial Number for Each Item in an Excel PivotTable in WPS Office
Question details
The user needs to display the first serial number for each grouped item in an Excel-style PivotTable in WPS Office, even though standard PivotTables do not natively return the first text value from a group.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is building an item report from data that contains item descriptions and serial numbers, and wants a compact PivotTable that shows each item with its first serial number.
- Observed behavior
- A standard PivotTable returns counts or sums of numeric values, but cannot directly aggregate or return only the first serial number text value for each item.
Confirm that the source data is in a table or contiguous range with separate columns for the item name and serial number, and open the workbook in WPS Office Spreadsheets before adding helper columns or PivotTable layout changes.
Use a Helper Column to Flag the First Serial Number for Each Item
Recommended when you need a dynamic, refreshable PivotTable that reliably shows only the first serial number for each item.
WPS Office PivotTables summarize values, but they do not have a built-in 'first text' aggregation. A helper column in the source data gives the PivotTable a clean field that contains the first serial number and blanks for all later rows.
Open the source sheet in WPS Office Spreadsheets and add a new column with the header “First Serial” beside the existing data.
In the first data row, enter =IF(COUNTIF($A$2:A2,A2)=1,B2,"") if column A contains the item and column B contains the serial number, then drag the fill handle down through all rows.
Select the source range including the helper column, click Insert > PivotTable, and choose a new worksheet or existing worksheet location.
In the PivotTable Fields pane, drag Item, Description, and First Serial to the Rows area to display the item details and the first serial only.
Click the PivotTable, go to Design > Report Layout > Show in Tabular Form, then click Design > Subtotals > Do Not Show Subtotals.
Use a Formula Summary Table with UNIQUE and XLOOKUP or VLOOKUP
Use this when you do not want to modify the original source data with a helper column and need a compact item list.
Create a First Serial Number PivotTable in WPS Office
WPS Office Spreadsheets can handle this reporting task with a helper column and standard PivotTable layout controls, so you can produce the first serial number per item without a separate Microsoft Office workflow.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the Excel workbook that contains the item and serial number data.
- 2. Add a helper column to the source data: Insert a new column named “First Serial” next to the source data range.
- 3. Flag the first serial number for each item: Enter =IF(COUNTIF($A$2:A2,A2)=1,B2,"") in the first data row and fill down to mark only the first serial for each item.
- 4. Insert the PivotTable: Select the source range including the helper column, then click Insert > PivotTable and choose a new worksheet.
- 5. Build the report with tabular layout: Drag Item, Description, and First Serial into the Rows area, then click Design > Report Layout > Show in Tabular Form and Design > Subtotals > Do Not Show Subtotals.
Frequently Asked Questions
Why can’t a standard PivotTable return the first serial number for each item?
A standard PivotTable is designed to aggregate values with functions such as Sum, Count, and Average. It does not have a built-in “first text” or “first serial number” aggregation, so you need a helper column, Power Query, or a formula-based summary.
How do I stop the PivotTable from showing nested item headings?
Click the PivotTable, go to Design > Report Layout > Show in Tabular Form, and then go to Design > Subtotals > Do Not Show Subtotals to keep the rows clean.
What formula can I use to keep only the first serial number for each item?
If column A contains the item and column B contains the serial number, use =IF(COUNTIF($A$2:A2,A2)=1,B2,"") and fill it down. This returns the serial number on the first occurrence and blank on later occurrences.
Will this PivotTable method work with .xlsx Excel files in WPS Office?
Yes. WPS Office Spreadsheets supports .xlsx files, PivotTables, and the Design tab layout commands used in this workflow.
Can I use Power Query instead of a helper column?
If your Excel or WPS environment supports Power Query, you can group the source table by item and take the first serial number before loading the result into a PivotTable, but the helper column method is often quicker for a single report.
