Power Query Problems

How to Mirror an Excel Worksheet Without Confidential Columns Using Power Query

Maira MehtabMaira Mehtab Nov 3, 2026 870 views

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.

How to Mirror an Excel Worksheet Without Confidential Columns
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.
Before you start

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.

Solution 1Recommended

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.

1
Import data into Power Query

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.

2
Remove confidential columns

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'.

3
Load data to a new worksheet

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.

4
Refresh the 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.

Use Power Query to Filter and Load Data
Refreshable Data Setup: Power Query automatically remembers your column deletion steps. Any new rows added to the master table will automatically appear in the mirrored sheet upon refreshing, completely skipping the confidential columns.
Free Microsoft Office alternative

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.

Highly compatible with Microsoft Excel (.xlsx, .xls) and CSV formats.Lightweight software that runs smoothly on most operating systems.Built-in advanced filtering and data consolidation tools for easy data management.Familiar tabbed interface makes switching from Microsoft Office completely seamless.
microsoft office alternative - wps office

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.