OmniDocHighlight Errors and Duplicates With Conditional Formatting
Learn how to flag formula error cells and duplicate values in an Excel file, then check that the highlights hold after editing or translation.
Spreadsheets with a few hundred rows hide problems well. A total shows #DIV/0! because one input cell is empty, or an invoice number appears twice in a column nobody scrolls through. Conditional formatting can make those cells stand out on their own, so you do not have to scan every row. If you are searching for excel conditional formatting for errors, this is the setup you need.
This guide is for anyone who maintains a data file: a budget, a customer list, an inventory sheet, or a dataset that came from someone else. You will set up two rules, one for formula error values and one for duplicate values, and then check that both work. The same checks apply whether you work in a desktop spreadsheet or in an online one.
The process uses a formula-based rule for each check. Once the rules are in place, they flag problems as you edit the file, not only when you run a report.
How do you set up excel conditional formatting for errors and duplicates? The short answer
Create two formula-based rules over the same column or range: one that tests each cell for an error value, and one that counts how many times each value appears. Give each rule its own color, then test both before you trust them.
| Your situation | What to do | Why |
|---|---|---|
| Formulas sometimes return #N/A, #DIV/0! or #REF! | Add a rule with ISERROR on the top-left cell of the range | Every error cell gets colored, including ones in rows you did not expect |
| Duplicate IDs, invoice numbers or email addresses in one column | Add a rule with COUNTIF over a fixed range, flagging counts above 1 | Every repeated value is highlighted, including the first occurrence |
| You need to share the file or translate it | Check the highlights in the output file before sending it | Formatting is not always carried through conversion, so verify it |
| A long list you edit often | Use absolute references (dollar signs) in the duplicate range | The rule keeps comparing against the whole list when you copy it down |
Before you start
- Work on a copy. Conditional formatting changes how cells look, but a wrong range or a mistaken rule is easier to undo in a copy than in the only version of the file.
- Confirm the file is a spreadsheet (.xlsx). Formatting rules live inside the spreadsheet file. A PDF or image of the sheet shows the colors but cannot hold the rules.
- Decide which columns and rows to check. Leave out the header row. Use a range that covers all current data and a few spare rows. A range that stops short of your last row will miss duplicates below it.
- Look for values that seem identical but are not. A number stored as text, or a value with a trailing space, may not match a duplicate check. Clean these up first, or your duplicate rule will miss real repeats.
- Plan the colors. Pick one color for errors and a different one for duplicates. If both problems share a color, you cannot tell them apart when they overlap.
Setting up error and duplicate highlights, step by step
- Open the file and select the range for error checks. Start at the first data cell, for example B2, and extend the selection down the column, such as B2:B500.
- Open the conditional formatting settings and choose a formula-based rule. Look for the option that lets you type your own formula rather than choose a preset condition.
- Enter the error formula. For the selection above, type =ISERROR(B2). Write the formula relative to the top-left cell of the selection, so the rule applies to B2, then B3, and so on down the range.
- Set a fill color and apply the rule. Choose a color that does not appear elsewhere in the sheet, so any highlighted cell stands out as a problem.
- Create a second rule for duplicates. Select the range for the duplicate check, such as A2:A500, then enter =COUNTIF($A$2:$A$500,A2)>1. The dollar signs fix the range to the full list, while A2 moves with each cell, so every value is counted against the whole column.
- Set a different color and check the rule order. If a cell matches both rules, only one fill shows. Make sure the rule you want to see first is listed at the top, or pick colors that make the overlap clear.
- Save the file, then translate it if needed. Save the spreadsheet as a new .xlsx file. If you need a translated copy, upload it on omnidoc.top, choose translation-only or bilingual output, and download the result. Then run the checks below on the output file.
Check the result
Once the rules are set, test them with data you control, then check the output file. Use this table to find problems quickly.
| Check | How to see it | If it's wrong |
|---|---|---|
| Error highlight works | Type =1/0 in an empty cell inside the error range. The cell should take the error color. Delete the test cell afterward. | If nothing changes, the range may not cover that cell. Open the rule and check the reference. |
| Duplicate highlight works | Type a value that already appears in the column into an empty cell inside the range. Both the original and the new cell should be colored. | If only one cell is colored, confirm the COUNTIF range covers both cells and that the values match exactly, including spaces. |
| Rules survive export | Open the exported or translated file and compare it with the original on the same cells. | If colors are missing, add the rules again in the output file. |
| Data changed in translation | Read the text in the highlighted columns. Translated text can make two values look different, or make two different values look the same. | Re-run the duplicate rule on the translated column, and check important values against the source. Machine translation can change text, so review it. |
❓ FAQ
1. Why does my duplicate rule miss some repeated values?
A common cause is a range that stops above the last row of data, so later rows are never compared. Check the range in the rule and extend it. Values that look the same but differ in type, such as the number 1001 and the text "1001", or differ by a trailing space, will also not match.
2. Does highlighting duplicates remove them?
No. Conditional formatting only changes how cells look. To delete repeated rows, use the removal tool in your spreadsheet app as a separate step, after you have reviewed what the highlights show.
3. Will the error rule catch #N/A values as well?
Yes. ISERROR returns true for any error value, including #N/A, #DIV/0!, #REF! and #VALUE!. If you want to ignore some of them, you need a more specific formula, such as one that checks for a particular error.
4. Do the rules still work if I open the file in UniCell?
Test it before you depend on it. Open the file, look at a cell you already know should be highlighted, and add one new test error. If the highlight does not appear, keep the check in your original spreadsheet app and use the output file only for reading.
📝 Summary
Two formula-based rules cover most data checks: ISERROR for formula errors and COUNTIF with a locked range for duplicates. Test both with deliberate examples before you trust them, and check the highlights again after any export or translation. That way, excel conditional formatting for errors gives you a reliable check instead of a color you have to second-guess.
