To remove duplicate rows from an Excel file with Python, use pandas: read the worksheet with read_excel(), call drop_duplicates(), and write the result to a new workbook with to_excel(). Use subset to define which columns identify a duplicate; use openpyxl instead when you need to edit a worksheet while retaining much of its existing formatting.
Choose the right Python approach
The best method depends on what you mean by “remove duplicates” and what must be preserved. pandas is convenient for cleaning and analyzing tabular data: it makes it simple to compare all columns or just selected fields, choose which occurrence to keep, and check how many rows were removed. It is usually the clearest option when a clean output file is more important than keeping the original workbook’s visual layout.
openpyxl works directly with workbook cells. It is useful when you want to delete rows from a particular sheet and retain much of the workbook’s formatting, cell values, and formulas. However, deleting rows is a more manual operation, and openpyxl does not update every possible workbook feature or reference. If the workbook contains complex charts, tables, macros, or formula dependencies, plan to verify the result carefully.
- Choose pandas for straightforward data cleaning, repeatable processing, and clear duplicate rules.
- Choose openpyxl when editing the existing sheet structure or preserving cell-level formatting matters.
- Use Excel’s built-in command for a one-off cleanup when Python automation is unnecessary.
Install the packages and prepare the file
For an .xlsx workbook, pandas needs an Excel reader and writer engine. A common setup is to install both pandas and openpyxl in the Python environment that will run the script:
pip install pandas openpyxl
Keep the source file unchanged while developing the cleanup. Write the result to a different filename first, then inspect it before replacing or deleting the original. This is especially important when the workbook has multiple worksheets, formulas, formatting, or information that is not part of the data table.
In the examples below, the workbook is called sales.xlsx, the worksheet is called Data, and the first row contains column headings. Change the file path and sheet name to match your workbook. If the actual data starts below the first row, or the worksheet has a title above the table, adjust the import options rather than treating those title rows as records.
Remove fully identical rows with pandas
When a duplicate means that every column in a row matches another row, pandas can remove repeated records directly. With the workbook named sales.xlsx and a header row in row 1, this script reads the Data sheet, removes repeated rows while keeping the first occurrence, and saves the cleaned table as a new workbook:
import pandas as pd
input_file = 'sales.xlsx'
output_file = 'sales_cleaned.xlsx'
df = pd.read_excel(input_file, sheet_name='Data')
cleaned = df.drop_duplicates(keep='first')
cleaned.to_excel(output_file, sheet_name='Data', index=False)
print(f'Removed {len(df) - len(cleaned)} duplicate rows')
drop_duplicates() compares the DataFrame’s columns by default; it does not use the DataFrame index as a matching field. The default behavior keeps the first row in each duplicate group and preserves the order of the remaining rows. The printed count is the number of rows removed, not the number of duplicate groups. For example, if a record occurs three times, two rows are removed and the remaining copy is kept.
The output is a data export, not a full-fidelity copy of the original Excel workbook. Writing a DataFrame to a new file does not automatically reproduce the original cell formatting, charts, print settings, or other worksheets. That distinction is a frequent source of surprise: the data may be correct even though the new workbook looks plainer or contains fewer sheets.
Choose the columns that define a duplicate
Many real datasets contain rows that differ in some detail but refer to the same person, order, or product. For instance, two customer rows may have the same email address but different phone-number formatting. To treat email as the matching key, set subset to that column. With email addresses in a column named Email, keep the first matching record with:
cleaned = df.drop_duplicates(subset=['Email'], keep='first')
You can use more than one field when a single column is not unique enough. With a dataset whose relevant headings are Order ID and Product, for example, the combination of those two values identifies a duplicate:
cleaned = df.drop_duplicates(subset=['Order ID', 'Product'], keep='first')
This is a composite key: rows are duplicates only when the values in both selected columns match. Choosing the key is a data decision, not just a coding detail. If you compare only a customer name, two different customers with the same name might be incorrectly merged. If you compare every column, repeated transactions with a changed note or timestamp may not be recognized as duplicates.
The keep option controls which rows survive:
keep='first'keeps the first occurrence. This is the default and often makes sense when the first record is authoritative.keep='last'keeps the last occurrence. This can be useful when later rows contain updated information, provided row order really represents recency.keep=Falseremoves every row that belongs to a duplicate group, including the first occurrence. Use this when ambiguous records should be reviewed rather than retained.
With keep='last', pandas still preserves the order of the rows it keeps; it does not automatically sort by a date or determine which record is newest. If recency matters, sort by a trustworthy date column before removing duplicates, and check that dates are interpreted correctly.
Preserve other worksheets and write safely
The short pandas example writes only the cleaned Data sheet. If you want to export all the workbook’s sheets as data, pandas can read them into a dictionary and write each DataFrame to a new workbook. This approach retains the sheet names and tabular contents, but it still does not preserve the original workbook’s formatting or advanced Excel objects.
import pandas as pd
input_file = 'sales.xlsx'
output_file = 'sales_cleaned.xlsx'
sheets = pd.read_excel(input_file, sheet_name=None)
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
for sheet_name, df in sheets.items():
cleaned = df.drop_duplicates(keep='first')
cleaned.to_excel(writer, sheet_name=sheet_name, index=False)
That example removes fully identical rows from every imported sheet. If only one sheet should be cleaned, apply drop_duplicates() only to that sheet’s DataFrame and write the other sheets without changing their rows. Be aware that pandas may need an alternative sheet name if a name is not valid for Excel, and Excel imposes a limit of 31 characters on worksheet names.
Before using a rewritten workbook in a business process, compare the sheet names, row counts, column headings, and important formulas against the source. For valuable files, keep a dated backup and treat the cleaned workbook as a new deliverable until it has been checked.
Edit worksheet rows with openpyxl
To delete duplicate rows directly from a worksheet, openpyxl can read the values in selected columns, remember keys already seen, and delete later matching rows. The following example assumes the first row is a header and that columns A and B together define a duplicate. Column numbers in openpyxl start at 1, so A is 1 and B is 2.
from openpyxl import load_workbook
input_file = 'sales.xlsx'
output_file = 'sales_cleaned.xlsx'
workbook = load_workbook(input_file)
worksheet = workbook['Data']
key_columns = (1, 2)
seen = set()
rows_to_delete = []
for row_number in range(2, worksheet.max_row + 1):
key = tuple(worksheet.cell(row=row_number, column=column).value
for column in key_columns)
if key in seen:
rows_to_delete.append(row_number)
else:
seen.add(key)
for row_number in reversed(rows_to_delete):
worksheet.delete_rows(row_number)
workbook.save(output_file)
Deleting from the bottom upward matters: removing a row shifts the row numbers that follow it, so deleting earlier rows first can make a saved list of row numbers point to the wrong records. This code keeps the first occurrence of each key, matching pandas’ default duplicate policy. To match on just column A, set key_columns to (1,); to match on three columns, include their column numbers in the tuple.
Empty keys deserve special attention. In this example, rows with blank values in both key columns have the same key, so only the first such row remains. If blank-key rows should all stay, add a check that skips deletion for an empty key, based on the rules for your data. Likewise, the code compares the cell values as Python reads them; it does not automatically trim spaces or make text matching case-insensitive.
openpyxl preserves many ordinary workbook features, but it does not calculate formulas, and deleting rows does not guarantee that every formula reference, chart range, table range, or other dependent feature will be adjusted as desired. For an .xlsm macro-enabled file, loading and saving requires the appropriate VBA-preservation option, and the output should retain the .xlsm extension. Even then, make a backup and verify the workbook in Excel before relying on it.
Clean inconsistent values before deduplicating
A row can look like a duplicate to a person and still differ to Python. For example, alex@example.com and alex@example.com have different text because the second value has a trailing space. Capitalization can also matter in pandas string comparisons. Decide whether spaces, capitalization, punctuation, or different spellings should count as equivalent before removing rows; automatic normalization can merge records that are actually distinct.
For a deliberate email cleanup, create a normalized helper column, deduplicate using it, and retain the original email column for review. With an Email column in the DataFrame, one option is:
df['Email_key'] = df['Email'].astype('string').str.strip().str.casefold()
cleaned = df.drop_duplicates(subset=['Email_key'], keep='first')
cleaned = cleaned.drop(columns=['Email_key'])
This converts values to pandas’ string dtype, removes leading and trailing whitespace, and applies Unicode-aware case folding for comparison. It does not validate that an address is real, remove internal spaces, or decide whether two different addresses belong to the same person. Review the matching rule before applying it to important records. If missing email values should not be treated as one shared key, handle those rows separately instead of blindly deduplicating on the normalized column.
Verify what changed
Do not rely only on the fact that the script ran without an error. Count duplicate rows before and after using the same matching columns, inspect representative records, and confirm the saved workbook opens. With df containing the data and Email identifying duplicates, the following check reports how many rows pandas currently flags as repeated after the first occurrence:
duplicate_count = df.duplicated(subset=['Email']).sum()
print(duplicate_count)
For a practical review, keep a copy of the original data, compare row counts, and inspect several duplicate groups—especially groups where the kept record could affect later reporting. Check whether identifiers have leading zeros, whether dates were read as dates, and whether formulas or important worksheet features survived. If the data is used for billing, inventory, or customer records, a person familiar with the source should approve the matching rule and the result.
Python versus Excel’s Remove Duplicates command
For a single manual cleanup in Excel for Microsoft 365 on Windows, select a cell in the data range and choose Data > Remove Duplicates. In the dialog, select the columns Excel should compare, then confirm. Excel removes matching rows from the selected range, so make a copy first if the original must remain unchanged. If the range has headers, make sure the dialog recognizes that the data has headers so the heading row is not treated as a record.
Python is more useful when the same cleanup must be repeated, applied to many files, or recorded as part of a data pipeline. It also makes the keep-first, keep-last, and remove-all policies explicit in code. Excel’s command is faster for a one-time task and does not require a Python environment. The choice is not about one tool being universally safer: both can delete valid information if the matching columns or source range are chosen incorrectly.
On Mac, Excel also provides a Remove Duplicates command on the Data tab, though ribbon layout and available controls can differ by Excel release. Excel for the web may present a different ribbon layout from the Windows desktop version. The Python examples are independent of Excel’s ribbon and can run on any system with a compatible Python installation and the required packages.
Frequently Asked Questions
Does pandas remove duplicates from every column by default?
Yes. drop_duplicates() compares all DataFrame columns unless you supply subset. The DataFrame index is not included in that comparison.
How do I keep the latest duplicate row?
Sort the DataFrame by a reliable date or update-time column, then use drop_duplicates(subset=..., keep='last'). Sorting is essential because “last” means last in the current row order, not necessarily the newest record.
Can Python remove duplicates from just one Excel worksheet?
Yes. Read that sheet by name with pd.read_excel(), clean its DataFrame, and write the result. If you use pandas to create a new workbook, explicitly write any other sheets you need; they are not automatically copied by the single-sheet example.
Does openpyxl preserve Excel formatting when it deletes duplicate rows?
It can retain much of the workbook’s existing cell formatting, unlike a basic pandas export, but it does not guarantee that every workbook feature or reference will remain correct. Check formulas, tables, charts, macros, and other dependent content in Excel after saving.
Why did Python keep two rows that look identical?
The values may differ in a way that is hard to see, such as trailing spaces, capitalization, a different date or number type, or a difference in a column you did not notice. Inspect the chosen matching columns and normalize values only when the data rules justify it.
Can I undo a Python duplicate-removal script?
Not with Excel’s Ctrl+Z after a script has saved changes. The examples write a separate output file, which leaves the source available. Keep a backup before using any script that modifies or replaces an original workbook.
Related
- Load Excel Power Query Into an Existing Table
- Linear Programming Excel Solver Template: Build and Use One
- Link Excel Data to a PowerPoint Table and Keep It Updated
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Share Outlook Calendar URL: Links and AccessTo share an Outlook calendar URL, open Outlook on the web, go to Settings > Calendar > Shared…
- Excel Table Not Pasting Into Outlook? Fixes That WorkIf an Excel table is not pasting into Outlook, copy the cells again, click in the message body,…
- Dependent Dropdown List in Excel: Set Up Each ColumnYes. In Excel for Microsoft 365, you can create a dependent dropdown in one column that changes according…
- Subtract Dates in Excel to Get Days Between DatesTo get the number of days between two dates in Excel, enter the later date in one cell…
- Microsoft Word: How to Add a Header and Page NumberIn Microsoft Word for Microsoft 365 on Windows, choose Insert > Header, select a layout, and type your…
- Excel Shortcut Desk Mat: What It Is and How to ChooseAn Excel shortcut desk mat is a large mouse-and-keyboard surface printed with keyboard commands for Microsoft Excel. It…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.