Duplicate Formating no longer works

Question

Duplicate Formating no longer works

Mccullough, Kathrine 0

Last week I was using Excel's "duplicate" conditional formatting feature and had no issues and this week it does not seem to be working. I've checked the cells are all the same type "General" (also tried text), I've removed spacing at the end of content, and still nothing. It also appears that MS has gotten rid of "paste as text only" and I'd like to know what has replaced that.

0 comments

2 answers

Your answer

Answer 1

Dear @Mccullough, Kathrine,

Thank you for contacting the Microsoft 365 Q&A Forum Community Support team. I understand you’re experiencing two issues in Excel: the “Duplicate” conditional formatting feature no longer works as expected, and you’re unable to find the “Paste as Text Only” option. Let’s address each concern step by step.

Option 1: Fixing the "Duplicate" Conditional Formatting Issue

Even with consistent cell formats and trimmed spaces, duplicates might not highlight due to hidden characters or formatting inconsistencies. Here’s what I recommend:

Step 1: Use the TRIM and CLEAN Functions

In a new column, use the formula =TRIM(A1) (assuming your data starts in A1). This removes non-printable characters and extra spaces.
Copy the results and use Paste Values to replace the original data.

Step 2: Reapply conditional formatting

Select your data range.
Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Ensure the correct range is selected and the rule is applied to the updated data.

For further details on these steps, you can refer to the official Microsoft support guide Find and remove duplicates - Microsoft Support

Option 2: Finding the "Paste as Text Only" Option 

Microsoft has not removed this feature. Here’s how to access it:

Method 1: Use Paste Special

Copy your content (Ctrl+C).
Right-click the destination cell and select Paste Special.
Choose Text or Unicode Text from the options.

If you paste data directly from your Excel spreadsheet:

User's image

If you paste data from external sources (such as websites or other software)

User's image

Method 2: Use the Ribbon

After copying, go to Home > Paste (click the dropdown arrow).
Select Paste Special and choose Text.

Method 3: Add "Paste as Text" to the Quick Access Toolbar

Go to File > Options > Quick Access Toolbar.
Under Choose Commands From, select All Commands.
Find and add Paste Text Only to the toolbar for one-click access.

User's image

Microsoft has recently standardized the "Paste" experience across M365. "Paste as Text Only" hasn't been removed, but it has been renamed and given a new, universal shortcut to match other modern applications.

The New Shortcut: Use Ctrl + Shift + V. This is now the official shortcut for "Paste Values" (Plain Text) in Excel for Microsoft 365.
The Alternative: You can still use Ctrl + Alt + V to bring up the "Paste Special" menu and select Values.
The Menu Name: In the right-click menu, look for the icon with the "123" on it. This is the "Paste Values" option, which effectively replaces "Text Only" by stripping all source formatting.

I hope these solutions resolve your issues. If you continue to face difficulties, please provide additional details, such as your Excel version or a sample of the data, and I’ll be glad to assist further.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Sophie N 18,185 Reputation points Microsoft External Staff Moderator

2026-01-29T02:29:20.1433333+00:00

Dear @Mccullough, Kathrine,

I hope you’re doing well. I just wanted to check in and see how things are going with the steps I shared earlier. If you ran into any difficulties at any point or if something is unclear, please don’t hesitate to let me know. I completely understand that these processes can sometimes be tricky, and I’m here to make it as easy as possible for you.

Feel free to share any details or questions you might have, I’m ready to listen and assist you further.
Gary W 5 Reputation points

2026-11-12T08:55:50.1666667+00:00

Just ran into this conditional formatting for duplicates issue.

There seems to be a hidden cap on how much is tested for duplication and Excel is doing it's famous number to scientific notation shenanigans.

Text in a general or text formatted field works fine.

However if the field can be interpreted by excel to be a number it does exactly that regardless of formatting.
In the background process it takes that number and treats it as scientific notation and only evaluates the first 15 digits.
This makes the function useless where global businesses use 16 digit product numbers which have to be formatted as text anyway.
(yes, I know there's an option to not convert in the option setting but this is at user level not workbook)

[edit] COUNTIF appears to have the same issue in evaluation

Answer 2

It seems you are experiencing issues with the conditional formatting feature in Excel, specifically with the "duplicate" formatting not functioning as expected. Here are a few troubleshooting steps you can consider:

Check for Updates: Ensure that your Excel is updated to the latest version, as updates can sometimes resolve bugs related to features like conditional formatting.
Reapply Conditional Formatting: Try removing the existing conditional formatting rules and then reapplying them to see if that resolves the issue.
Use Different Cell Types: While you mentioned that you checked the cell types, sometimes using a different format (like converting to text or number) can help in recognizing duplicates.
Clear Formatting: If there are any hidden formats or styles applied to the cells, clearing all formatting and then reapplying the conditional formatting rules might help.

Regarding the "paste as text only" feature, while the specific replacement isn't mentioned in the context provided, you can typically use the "Paste Special" option to paste as values or text, which serves a similar purpose. You can access this by right-clicking where you want to paste and selecting "Paste Special" from the context menu.

If these suggestions do not resolve your issue, consider looking for specific updates or community discussions regarding changes in recent versions of Excel that might affect these features.

References: