How to Mirror an Excel Worksheet Without Confidential Columns Using Power Query
Question details
The user wants to create a secondary, mirrored Excel worksheet from a master sheet, but needs to exclude specific confidential columns from the copy.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Sharing or analyzing data from a master worksheet with a broader audience without exposing sensitive or confidential information.
- Observed behavior
- A need to create a refreshable secondary worksheet that automatically updates while omitting specific columns, rather than manually copying and pasting data every time the master sheet changes.
Ensure your master worksheet data is formatted as an Excel Table (select the data and press Ctrl+T) so that Power Query can dynamically capture new rows when the source updates.
Use Power Query to Filter and Load Data
Power Query allows you to import the master data, select only the columns you want to share, and output the result to a new, refreshable worksheet.
This method creates a refreshable copy of your data rather than a live, cell-by-cell mirror. When the master data updates, you simply refresh the secondary sheet to see the changes without ever exposing the confidential columns.
Select any cell inside your master data table. Go to the 'Data' tab on the Excel ribbon and click 'From Table/Range'. This will open the Power Query Editor.
In the Power Query Editor, locate the columns containing sensitive information. Hold the Ctrl key and click the headers of the confidential columns to select them. Right-click any selected header and choose 'Remove Columns'.
Once the confidential columns are removed, click the 'Close & Load' button on the Home tab of the Power Query Editor. The filtered, safe data will automatically populate a new secondary worksheet.
Whenever you add, modify, or delete non-confidential data in the master table, go to the secondary worksheet, right-click anywhere inside the table, and select 'Refresh' to update the mirrored data.

Experience Seamless Data Management with WPS Office
If dealing with Microsoft Excel's advanced data connections feels overly complex or you are looking for a more accessible suite, WPS Office provides an excellent alternative. It offers powerful data processing tools, advanced filtering, and high compatibility with Excel files, completely free of charge.

Frequently Asked Questions
Does this Power Query method create a live cell-by-cell mirror?
No, Power Query creates a refreshable copy. You will need to manually refresh the query (by right-clicking the secondary table and selecting 'Refresh', or setting it to refresh on file open) to see the latest updates from the master sheet.
Can I hide and password-protect the master worksheet?
Yes. You can keep the master worksheet hidden and protect the workbook structure. The Power Query connection will still function, allowing users to refresh the secondary worksheet without accessing the hidden confidential data.
Will the cell formatting of the master table carry over to the mirrored sheet?
Power Query only imports the data values, not the cell formatting (such as background colors, borders, or custom fonts). You will need to apply your desired formatting directly to the resulting table in the secondary worksheet.
What happens if I rename the confidential columns in the master sheet?
If you rename a column that Power Query was previously instructed to remove, the query may fail to refresh because it can no longer find that specific column name. It is highly recommended to maintain consistent column headers in your master data.




