How to Find Items Frequently Purchased Together in a WPS Spreadsheets PivotTable
Question details
The user needs a reliable way to analyze transaction-level purchase data in a PivotTable so they can identify which items are frequently bought together, rather than only viewing individual item sales.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has sales or order records and wants to discover product pairs or combinations that appear together in the same transaction.
- Observed behavior
- The analysis goal is to produce a transaction-based view that shows item combinations, co-occurrence counts, or the most common product pairs from the source data.
Make sure your source data contains one row per transaction-item combination, with at least an Order ID column and an Item column. Remove duplicate rows, blank cells, and extra spaces so the PivotTable counts item combinations consistently.
Build an Order ID by Item PivotTable Matrix
Use this method when your source data is already arranged as one row per order-item combination and you want to visually scan which items appear together in the same order.
This PivotTable layout puts each order on its own row and each product in its own column. A value in the intersecting cell means that item appeared in that order, so reading across the row shows the item combination for that transaction.
Open the workbook in WPS Spreadsheets and select the source range including the Order ID and Item header row.
Go to the Insert tab, click PivotTable, keep the selected range, and choose New Worksheet.
In the PivotTable Fields pane, drag Order ID to the Rows area to list each order.
Drag Item to the Columns area to create one product column, then drag Item to the Values area to show counts for each order-item cell.
Use the Values area totals, column filters, or conditional formatting to spot products that frequently appear together in the same order row.
Check a Specific Product Pair with COUNTIFS Helper Columns
Use this method when you already have two target products and need to count how many orders contain both items.
Use WPS Spreadsheets to Find Frequently Purchased Item Pairs
WPS Spreadsheets can open your Excel transaction file, build a PivotTable from Order ID and Item fields, and show co-occurring products without requiring extra add-ins.
- 1. Open the transaction workbook: Launch WPS Office, open the Excel .xlsx file, and review the Order ID and Item columns.
- 2. Format the data as a table: Select the data and press Ctrl+T or click Insert > Table to make filtering and PivotTable source ranges easier.
- 3. Insert the PivotTable: Click anywhere in the table, go to Insert > PivotTable, and place it on a new sheet.
- 4. Arrange Order ID and Item fields: Drag Order ID to Rows, Item to Columns, and Item to Values to create the item co-occurrence matrix.
- 5. Interpret frequent pairs: Scan each order row to see which items are present together, then filter or sort for the most common combinations.
Frequently Asked Questions
How should transaction data be arranged before creating the PivotTable?
Use one row per transaction-item combination. At minimum include an Order ID column and an Item column; for example, one order with three products should have three rows with the same Order ID.
Can WPS Spreadsheets open an Excel PivotTable that already analyzes item combinations?
Yes. WPS Spreadsheets can open .xlsx files and display existing PivotTables. If the workbook uses Excel Power Pivot measures, you may need to recreate those calculations with WPS PivotTable fields or formulas.
Why does dragging Item to Values only show counts and not item pairs?
A basic PivotTable shows item frequencies. To see co-occurrence, put Order ID in Rows and Item in Columns as well as Values, then scan each order row for multiple item columns.
How can I check whether two specific products are purchased together?
Add helper columns using COUNTIFS to flag orders containing each product, then create a PivotTable or filter rows where both flags are 1.
Does this method calculate percentages of orders containing each item pair?
You can calculate percentages by dividing an item-pair count by the total number of orders. Use WPS formulas or add the count field to the PivotTable and show values as a percentage of the grand total.
