Pivot Table Issues

How to Use Multiple Data Validation Items Individually in Excel Pivot Tables

Maira Mehtab Sep 27, 2026 872 views

Question details

The user needs to normalize and analyze data where multiple items selected from a drop-down list (data validation) are stored within a single cell, separated by commas.

Product: Excel
Device & OS: not provided
Scenario: Generating a Pivot Table report from a dataset where a single cell contains combinations of items (e.g., 'Apple, Mango, Kiwi').
Observed behavior: The Pivot Table treats the entire comma-separated string as one single unique category rather than counting each item individually.

Before you start

Identify the delimiter used in your multiple-selection cells (usually a comma or semicolon) and ensure your dataset is formatted as an Excel Table before beginning the normalization process.

Solution 1Recommended

Normalize Data Using TEXTSPLIT and TOCOL Functions

Use modern Excel dynamic array functions to split comma-separated values into individual rows dynamically, creating a clean dataset for Pivot Table analysis.

In Microsoft 365, new dynamic array functions make it easy to separate combined values. A normalized table that stores one item per row is the most reliable structure for building a Pivot Table.

Create a Helper Range

Add a new blank sheet or select an empty range next to your data to serve as the helper range where normalized data will be generated.

Split the Values Across Columns

Use the TEXTSPLIT function by typing =TEXTSPLIT(A2, ",") (assuming A2 contains your comma-separated values) to divide the combined text into separate columns.

Transpose the Split Values into Rows

Wrap the previous formula with the TOCOL function by typing =TOCOL(TEXTSPLIT(A2, ",")). This will force the horizontally split data into individual rows vertically.

Build the Pivot Table

Once your helper range has completely separated all combined items into single rows with their corresponding identifiers, select this normalized list, go to 'Insert', and click 'PivotTable' to perform your analysis.

Version Requirement: The TEXTSPLIT and TOCOL functions are exclusive to Microsoft 365 and Excel for the Web. If you are using an older version, consider using Power Query instead.

Solution 2

Split Comma-Separated Values Using Power Query

Use Power Query to automatically split and expand combined cells into new rows without needing complex formulas.

Efficient Data Analysis

Easily Process and Analyze Complex Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing tools, including Text to Columns, comprehensive array formulas, and intuitive Pivot Tables, helping you split multiple cell values and analyze them efficiently without the hassle.

1. Open Your Data File: Launch WPS Spreadsheet and open the workbook containing the multiple data validation selections.
2. Apply Text to Columns: Select the column with the comma-separated items, navigate to the 'Data' tab, and click 'Text to Columns' to split them by the comma delimiter.
3. Organize into Rows: Copy the newly split columns and use 'Paste Special' > 'Transpose' on a new sheet to arrange the items into individual rows vertically.
4. Generate the Pivot Table: Select the neatly transposed column, go to the 'Insert' tab, click 'PivotTable', and drag the item field into the Rows and Values areas to count them individually.

Fully compatible with Microsoft Excel formats (.xlsx, .xls) and Pivot Table structuresBuilt-in Text to Columns feature for straightforward data normalizationIntuitive and highly responsive Pivot Table interface for quick data analysisLightweight application that runs smoothly on Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Pivot Table group comma-separated items together?

Pivot Tables analyze the exact contents of a cell as a single distinct string. If a cell contains 'Apple, Mango', the Pivot Table treats the entire string as one specific category rather than recognizing 'Apple' and 'Mango' as two separate entities.

Can I use the Text to Columns feature instead of formulas?

Yes. You can use 'Text to Columns' under the Data tab to split the values across multiple adjacent columns. However, to analyze them properly in a Pivot Table, you must manually copy and transpose those columns into individual rows, or use a macro to do so.

How do I handle unwanted spaces after splitting comma-separated values?

After splitting your text, it is common to have trailing or leading spaces (e.g., ' Mango'). You should wrap your split data in the TRIM function, such as =TRIM(TEXTSPLIT(A2, ",")), to remove accidental spaces. This ensures the Pivot Table doesn't treat 'Mango' and ' Mango ' as two different categories.

Excel & Data Problems