Formatting Issues

How to Fix Identical SharePoint Choices Showing Different Colors

Elise Williams Sep 30, 2026 868 views

Question details

Users notice that seemingly identical choices in a SharePoint choice column appear in different colors, are treated as separate values in filters, and show up as duplicates in Power BI.

How to Fix Identical SharePoint Choices Showing Different Colors

Product: SharePoint, Microsoft Forms, Power BI
Device & OS: not provided
Scenario: Populating a SharePoint choice column from Microsoft Forms and analyzing the exported data in Power BI.
Observed behavior: Apparent identical choices display different colors, filters treat them as distinct values, and Power BI identifies them as duplicates due to hidden formatting or spacing differences.

Before you start

Before troubleshooting, ensure you have edit access to the source Microsoft Form and the SharePoint list settings to modify the choice values.

Solution 1Recommended

Check and Remove Trailing Spaces or Special Characters

Identify and remove hidden spaces or characters that cause SharePoint to treat identical-looking text as completely different choices.

When data is imported from Microsoft Forms into SharePoint, invisible characters such as trailing spaces, leading spaces, or non-printable characters are often carried over. Because SharePoint evaluates exact string matches, a choice with a trailing space is treated as a separate value, resulting in different color formatting and duplicated data in Power BI.

Inspect values character by character

Look closely at the incorrect choice value in your SharePoint list and compare it to the correct one. Click into the text field to check specifically for trailing or leading spaces.

Correct the source data

Go back to the original Microsoft Form and remove any unintended spaces or hidden characters from the choice options to prevent future mismatched entries.

Clean existing SharePoint data

Edit the affected items in your SharePoint list using 'Edit in grid view'. Replace the incorrect values with the exact matching correct choice.

Refresh your dataset

Once the SharePoint data is manually cleaned, refresh your Power BI dataset to ensure the duplicate values are merged and resolved.

Check and Remove Trailing Spaces or Special Characters

Avoid Manual Color Changes: Manually changing the colors in SharePoint formatting rules will only change the appearance. It will not fix the underlying data issue, and Power BI will still detect them as duplicates.

Solution 2

Clean Data using Power Query in Power BI

If you cannot change the historical source data in SharePoint, use Power BI's Power Query Editor to trim and clean the data before reporting.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Data Management?

While specialized tools like SharePoint and Power BI handle complex enterprise workflows, everyday data cleaning and document management just require a reliable office suite. WPS Office is perfect for spreadsheet analysis, data formatting, and document editing. It's free, lightweight, and fully compatible with Microsoft formats.

Free and lightweight alternative to Microsoft OfficeSeamless compatibility with Microsoft Word, Excel, and PowerPoint (.docx, .xlsx, .pptx)Use WPS Spreadsheet to easily trim spaces and clean duplicate text data before uploadingFamiliar, easy-to-use interface that requires zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does SharePoint apply different colors to the same choice text?

SharePoint assigns formatting based on the exact string value. If one choice has a trailing space (e.g., 'Option A' versus 'Option A '), SharePoint treats them as completely different values and assigns them different default colors.

Can I fix the duplicate issue by changing the column formatting rules?

No. Manually changing the colors using SharePoint column formatting rules only masks the visual appearance. The underlying data still contains different values, meaning filters and connected apps like Power BI will continue to show them as duplicates.

How do hidden characters end up in Microsoft Forms choices?

Hidden characters or extra spaces typically occur when choice options are copied and pasted from other documents, websites, or emails directly into the Microsoft Forms builder.

Document & File Issues