Pivot Table Issues

How to Count Shared Excel Contributions with Power Query and Pivot Tables

Kushani Nimanthika Sep 28, 2026 869 views

Question details

The user needs to calculate fractional credit for multiple contributors assigned to a single product, where names are currently combined in a single comma-separated cell.

Count Shared Excel Contributions with Power Query and Pivot Tables

Product: Microsoft Excel
Device & OS: not provided
Scenario: Generating a report to count unique products by category and assign fractional credit to multiple contributors using Pivot Tables.
Observed behavior: Contributor names are grouped in a single cell separated by commas, preventing accurate counting and reporting in standard Pivot Tables without duplicating or distorting product totals.

Before you start

Ensure your dataset is formatted as an Excel Table (by pressing Ctrl+T) before importing it into Power Query. This ensures that any future data additions update dynamically when you refresh your query.

Solution 1Recommended

Normalize Data with Power Query and Create a Pivot Table

This method transforms your data by splitting the comma-separated contributors into individual rows, allowing Pivot Tables to accurately aggregate the data without complex LEN formulas.

Power Query is the most efficient way to normalize data that has multiple values packed into a single cell. By splitting the delimiter into rows rather than columns, you maintain a one-to-one relationship between the product and each contributor.

Load data into Power Query

Select any cell in your data table, navigate to the Data tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.

Split the contributor column

Click the header of the column containing the comma-separated contributor names. Go to the Home tab in Power Query, click 'Split Column', and select 'By Delimiter'.

Choose the delimiter and split into rows

In the dialog box, select 'Comma' as your delimiter. Expand the 'Advanced options' section, select 'Rows' under the 'Split into' options, and click OK.

Load the data and build the Pivot Table

Go to the Home tab and click 'Close & Load To...'. Select 'PivotTable Report' to generate your fractional calculations directly from the newly normalized data.

Normalize Data with Power Query and Create a Pivot Table

Dynamic Updating: Once this Power Query connection is set up, you can simply right-click your Pivot Table and select 'Refresh' whenever new data is added to your source table.

Solution 2

Use Text to Columns as a Manual Alternative

If you do not have access to Power Query, you can manually split the text and restructure the data, though this requires more manual effort.

Free Microsoft Office alternative

Need a Faster Way to Process Data? Try WPS Office

If you are tired of struggling with complex Excel formulas or heavy data processing add-ins, consider WPS Office. It provides a lightweight, highly compatible alternative with robust Pivot Table functionalities to help you analyze your data efficiently.

1. Download WPS Office: Get the free WPS Office suite from the official website and install it on your device.
2. Open Your Dataset: Launch WPS Spreadsheet and open your existing .xlsx data file directly.
3. Analyze with Ease: Use the built-in Data and PivotTable tools in WPS to seamlessly summarize and report your shared contributions.

100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Built-in intuitive Pivot Table and Data tools for easy reportingLightweight application that runs smoothly on most devicesFamiliar user interface for seamless and rapid migration

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just use a standard Pivot Table on comma-separated cells?

A standard Pivot Table treats the entire contents of a cell as a single distinct item. It cannot automatically look inside a comma-separated string to count the individual contributors, which is why the data must be split into rows first before generating the Pivot Table.

How do I calculate fractional credit once the data is split into rows?

Before splitting the data in Power Query, add a custom column that calculates the total number of contributors for that row (e.g., by counting the commas and adding one). After splitting into rows, divide the total value or credit by this count to assign a fractional share in your Pivot Table.

Can I split data into rows without using Power Query?

Yes, but it requires manual copy-pasting. You can use the native 'Text to Columns' feature to split names into separate columns, and then manually copy and paste those columns into a single long column (a process known as manual unpivoting) before creating your Pivot Table.

Excel & Data Problems