Remove Duplicates and Blank Rows from an Excel Table with Office Scripts
Question details
Use an Office Script to sort an Excel table, remove duplicate records, and delete completely blank rows.

- Product
- Microsoft Excel Office Scripts
- Device & OS
- not provided
- Scenario
- Clean table records using table-based references instead of fixed worksheet ranges.
- Observed behavior
- The desired result is a sorted table without duplicate records or completely blank data rows.
Make sure your data is formatted as an Excel table and check its name on the Table Design tab. Decide which columns define a duplicate; column indexes in the script start at 0.
Clean an Excel table with an Office Script
Use this script to sort a named table, remove duplicates based on selected columns, and delete rows whose displayed values are all blank.
The example sorts and checks duplicates using the first table column. Change the zero-based indexes to match your data. Removing duplicates keeps the first matching record in the sorted order, so confirm the sort order and duplicate columns before running the script.
The script treats cells containing only whitespace as blank. It temporarily hides the totals row so that it is excluded from duplicate removal.
In Excel, open the workbook, select the Automate tab, and choose New Script.
Replace the editor contents with the following script: function main(workbook: ExcelScript.Workbook) { const table = workbook.getTable("Table1"); if (!table) { throw new Error("Table1 was not found. Check the table name."); } const hadTotals = table.getShowTotals(); if (hadTotals) table.setShowTotals(false); try { if (table.getRowCount() === 0) return; table.getSort().apply([{ key: 0, ascending: true }]); table.getRange().removeDuplicates([0], true); if (table.getRowCount() === 0) return; const values = table.getRangeBetweenHeaderAndTotal().getValues(); for (let i = values.length - 1; i >= 0; i--) { const isBlank = values[i].every(value => String(value).trim() === ""); if (isBlank) table.deleteRowsAt(i, 1); } } finally { if (hadTotals) table.setShowTotals(true); } }
Replace Table1 with your table name. Change the sort key and the indexes in removeDuplicates([0], true) as needed; for example, use [0, 1] to compare the first two columns together.
Select Run in the script editor, then review the sorted table to confirm that the intended duplicates and blank rows were removed.

Clean spreadsheet data with WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files to sort data and remove duplicates in WPS Spreadsheet. Office Scripts are a Microsoft Excel feature and are not fixed or run by this alternative.

Frequently Asked Questions
How do I find my Excel table name?
Click a cell in the table and look for the table name on the Table Design tab. Use that name in workbook.getTable("Table1").
How do I remove duplicates based on more than one column?
Pass the zero-based indexes of the columns to removeDuplicates. For example, removeDuplicates([0, 1], true) compares the first and second columns together; true indicates that the range includes a header.
Does the script delete rows with just one empty cell?
No. Its blank-row check deletes a data row only when every returned cell value is empty or contains only whitespace.
Why does the script delete blank rows from the bottom up?
Deleting a row shifts the rows below it. Iterating backward with table.deleteRowsAt(i, 1) prevents those shifts from causing rows to be skipped.
What happens to the table's totals row?
The script temporarily hides the totals row before removing duplicates and restores it afterward if it was originally visible.




