Data Protection Issues

How to Prevent Copying in Excel While Allowing Refreshes and Add-Ins

Olivia Miller Sep 30, 2026 869 views

Question details

An organization needs to share an interactive Excel workbook without allowing users to copy data, take screenshots, or use OCR tools, while keeping formulas, PivotTables, add-ins, and automatic data refreshes active.

How to Prevent Copying in Excel While Allowing Refreshes and Add-Ins

Product: Microsoft Excel
Device & OS: not provided
Scenario: Distributing a secure report where end-users need to interact with PivotTables and receive updated data from add-ins or connections, but data exfiltration must be minimized.
Observed behavior: Standard worksheet protection restricts copying by locking cells but often inadvertently breaks add-ins and data model refreshes. Furthermore, Excel natively lacks the ability to block screenshots or OCR extraction.

Before you start

Before applying strict worksheet protection, create a backup copy of your workbook. It is crucial to thoroughly test all data connections, add-ins, and PivotTables after protecting the sheet, as restrictive permissions may unexpectedly block background refreshes.

Solution 1Recommended

Restrict Copying Using Worksheet Protection

Use Excel's built-in protection features to disable cell selection. This natively prevents direct copying while allowing you to grant exceptions for interactive features like PivotTables.

By preventing users from selecting cells, you effectively stop them from using standard copy-paste functions. However, you must explicitly allow PivotTable usage. Be aware that this method might interfere with certain local PivotTable refreshes or external add-ins if they require writing to locked cells.

Lock all cells in the worksheet

Select all cells by pressing Ctrl+A. Right-click anywhere on the sheet, choose 'Format Cells', navigate to the 'Protection' tab, and ensure the 'Locked' box is checked.

Open Worksheet Protection settings

Go to the 'Review' tab on the Excel ribbon and click on 'Protect Sheet'.

Configure selection and interactive permissions

In the protection dialog, uncheck 'Select locked cells' and 'Select unlocked cells' to prevent users from highlighting data. Scroll down the list and check 'Use PivotTable & PivotChart' to ensure interactive reports continue to function.

Apply a password

Enter a strong password in the provided field, click 'OK', and re-enter the password to confirm. Test the workbook to ensure your add-ins and refreshes still operate as expected.

Restrict Copying Using Worksheet Protection

Limitations on Screenshots and OCR: Excel's built-in features cannot prevent users from taking screenshots or using OCR tools. For such strict data loss prevention, OS-level security or specialized DRM software is required.

Solution 2

Apply Microsoft 365 Information Protection

For enterprise environments requiring strict data loss prevention, use Microsoft Purview Information Protection to restrict access and block screenshots.

Secure Your Spreadsheets with WPS Office

Easily Protect Your Spreadsheet Data in WPS Office

WPS Spreadsheet provides a robust and intuitive way to manage data protection. You can easily restrict editing and copying while keeping your interactive reports functional, maintaining complete compatibility with Microsoft Excel (.xlsx) files.

1. Open your workbook: Launch WPS Spreadsheet and open the file you want to protect.
2. Access the Protect Sheet feature: Navigate to the 'Review' tab on the top ribbon and click the 'Protect Sheet' icon.
3. Configure security settings: In the prompt, uncheck 'Select locked cells' to stop copying, and check 'Use PivotTable' to keep interactivity alive.
4. Confirm with a password: Type a secure password, click 'OK', and save your document to finalize the protection.

100% compatible with Microsoft Excel worksheet protection and .xlsx file formats.Easily disable cell selection to prevent unauthorized data copying.Seamlessly supports PivotTable interactivity even when the worksheet is protected.Free and lightweight alternative to heavier office suites with an intuitive user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel add-in stop working when the worksheet is protected?

Worksheet protection restricts the ability of both users and add-ins to write data or modify the layout of the sheet. If your add-in needs to output or update data, you must either unlock the specific ranges the add-in writes to, or use VBA macros to temporarily unprotect the sheet during the add-in's operation.

Can I completely prevent users from taking screenshots of my Excel data?

Excel alone cannot natively prevent screenshots or photographs of the screen. To block screen capture tools, you must implement OS-level restrictions, use third-party Digital Rights Management (DRM) software, or deploy Microsoft 365 Endpoint Data Loss Prevention (DLP) policies.

How do I allow data connections to refresh on a protected sheet?

Some data refreshes (especially background refreshes) are blocked by sheet protection. To fix this, you can go to the connection properties and uncheck 'Enable background refresh'. Alternatively, you can write a VBA macro that unprotects the sheet, refreshes the data model, and then re-applies the protection automatically.

Will PivotTable slicers still work if I disable cell selection?

Slicers can function on a protected sheet, but you must configure them properly before locking. Right-click the slicer, choose 'Size and Properties', go to the 'Properties' section, and uncheck 'Locked'. Then, when protecting the sheet, ensure 'Use PivotTable & PivotChart' and 'Edit Objects' are both checked.

Microsoft Office Security Issues