My shared spreadsheet keeps getting duplicate rows and formula errors

That’s usually caused by one of a few things: multiple people adding the same entry, copy/paste errors, formulas being dragged incorrectly, or an automation/script creating duplicate writes.

Here’s a quick way to troubleshoot and prevent it:

1) Find where duplicates are coming from

• Check if multiple users edit the same rows at the same time
• Look for imports or automations:
  • forms feeding the sheet
  • Zapier/Make/Power Automate
  • Apps Script / macros
  • scheduled CSV imports
• See whether duplicates happen in one specific tab or all tabs

2) Stop duplicate rows

• Add a unique ID column for each row
• Use data validation to limit repeated entries
• If using Google Sheets, consider:
  • Protected ranges
  • Form responses instead of direct edits
  • A deduplication script
• If using Excel, consider:
  • Remove Duplicates
  • Table structure with consistent formulas
  • Restricting edit permissions on key columns

3) Fix formula errors

Common causes:

• Copied formulas shifting references incorrectly
  • Use absolute references like $A$1 when needed
• Blank cells or text where numbers are expected
• Broken references after rows are inserted/deleted
• Array formulas overlapping with existing data

Helpful checks:

• Click the error cell and inspect the formula
• Compare it to a working row
• Look for:
  • #REF! = broken reference
  • #VALUE! = wrong data type
  • #DIV/0! = dividing by zero
  • #N/A = lookup didn’t find a match

4) Prevent future issues

• Lock formula columns so users can’t overwrite them
• Use a form or input sheet separate from the calculation sheet
• Set formulas to fill automatically down a table
• Add a validation rule to flag duplicates
• Keep one “source of truth” sheet and avoid manual edits elsewhere

If you want, I can help you diagnose the exact cause if you tell me:

1. Whether it’s Google Sheets or Excel
2. How data gets into the sheet
3. What the formula error says
4. An example of one duplicate row or broken formula