Excel Query Not Removing Row Once Deleted from Source Table with Refresh

Excel Query Not Removing Row Once Deleted from Source Table with Refresh

If you are using Excel and have encountered a situation where a row that you have deleted from the source table is not being removed when you refresh the query, you are not alone. This issue can be frustrating, but there are a few things you can try to resolve it.

First, let's understand why this issue occurs. When you create a query in Excel, it retrieves data from an external source, such as a database or another Excel file. This data is then loaded into a table in your worksheet. The query creates a connection between the source data and the table in Excel, allowing you to refresh the data whenever it changes in the source.

However, when you delete a row from the source table and refresh the query, Excel doesn't automatically remove the corresponding row from the table in your worksheet. This is because Excel treats the query table as a separate entity and doesn't have a built-in mechanism to track and remove deleted rows.

Here are a few steps you can take to address this issue:

1. Clear the table and refresh

The simplest solution is to clear the entire table in your worksheet and then refresh the query. To do this:

1. Select the entire table by clicking on the top-left cell and dragging to the bottom-right cell.
2. Press the Delete key on your keyboard to clear the selected cells.
3. Right-click on the table and select "Refresh" from the context menu.

This will remove all the existing data in the table and load the updated data from the source.

2. Use the "Remove Rows" option

If you don't want to clear the entire table, you can use the "Remove Rows" option in Excel to delete the rows that no longer exist in the source table. Here's how:

1. Right-click on any cell in the table and select "Table" from the context menu.
2. In the "Table Tools" tab that appears, click on the "Design" tab.
3. In the "Table Style Options" group, check the box next to "Total Row".
4. A new row will appear at the bottom of the table with a drop-down arrow in each cell.
5. Click on the drop-down arrow in the first cell of the "Total Row" and select "Remove Rows" from the menu.
6. Excel will remove any rows in the table that no longer exist in the source.
7. Finally, right-click on the table and select "Refresh" to update the table with the latest data from the source.

3. Modify the query

If the above methods don't work, you can try modifying the query itself to remove the deleted rows. Here's how:

1. Right-click on any cell in the table and select "Table" from the context menu.
2. In the "Table Tools" tab that appears, click on the "Design" tab.
3. In the "Properties" group, click on "Properties" to open the "External Data Properties" dialog box.
4. In the "External Data Properties" dialog box, click on the "Definition" tab.
5. Click on the "Edit Query" button to open the Power Query Editor.
6. In the Power Query Editor, you can modify the query to remove the deleted rows.
7. Once you have made the necessary changes, click on the "Close & Load" button to apply the changes and refresh the table in Excel.

By modifying the query, you can customize how Excel retrieves and loads the data from the source, including removing any deleted rows.

These are some of the methods you can try to resolve the issue of Excel not removing a row once deleted from the source table with refresh. Remember to save your workbook before making any changes and always test your solutions on a copy of the original file to avoid any accidental data loss.

References: