Yes. In Excel for Microsoft 365 on Windows, you can protect a worksheet and leave a slicer usable: unlock the slicer first, then protect the sheet and allow the relevant filtering or PivotTable actions. The exact permissions depend on whether the slicer filters a PivotTable or an Excel table, so test it after protection.
What sheet protection changes
Worksheet protection is designed to prevent selected changes to cells and objects, not to make a file secret. When protection is on, Excel can block edits to locked cells and changes to locked objects such as slicers, charts, and shapes. A slicer can therefore appear on the sheet but fail to respond when a user clicks a button.
Most worksheet cells are locked by default, but that setting only takes effect once the sheet is protected. Slicers also have a Locked property. Clearing that property lets users interact with the slicer while the worksheet is protected; it does not unlock the worksheet’s cells. You can protect formulas and layout while still providing a slicer as a controlled way to view different records.
Protection is not encryption or access control for confidential information. A user who can open the workbook may be able to copy data or work around worksheet protection. Use file permissions and appropriate storage controls if the data itself must be restricted.
Prepare the sheet and identify the slicer
Before changing settings, identify what the slicer controls. A slicer may filter a PivotTable, an Excel table, or more than one compatible report. The distinction matters because Excel’s protection dialog has different permissions for PivotTable reports and AutoFilter actions.
- Confirm the source by selecting the slicer and checking which table or PivotTable it is connected to. The Slicer tab includes options for managing connections when the slicer supports multiple reports.
- Decide which cells users should be able to edit. Leave formula and heading cells locked; unlock only intended input cells if users need to enter data.
- Set up the filters and layout before protection. It is easier to position, resize, and connect a slicer while the sheet is unprotected.
- Save a copy before changing protection settings, especially if the workbook has several sheets, connected PivotTables, or shared workflows.
Protection applies separately to each worksheet. Protecting one sheet does not automatically protect other sheets, the workbook’s structure, or the source data stored elsewhere in the file.
Unlock the slicer in Excel for Windows
Use Excel desktop for Microsoft 365 on Windows for the following steps. If the sheet is already protected, unprotect it first using Review > Unprotect Sheet. Excel may ask for the password used when the sheet was protected.
- Select the slicer by clicking its border, rather than one of its filter buttons. If needed, click a blank area of the sheet first and then select the slicer.
- Right-click the slicer and choose Size and Properties. You can also select the slicer and press Ctrl+1 to open its formatting options.
- In the Format Slicer pane, expand Size & Properties, then Properties. Clear the Locked checkbox.
- Close the pane. If there are several slicers that users must operate, repeat these steps for each one.
- Choose Review > Protect Sheet. Set a password if appropriate, then select the permissions users need. For a PivotTable slicer, enable Use PivotTable & PivotChart reports if that option is available and relevant.
- Click OK, confirm the password if prompted, and try selecting a slicer button.
Unlocking is not the same as enabling Edit objects in the Protect Sheet dialog. Edit objects is a broader permission that can let users change drawing objects, including moving or modifying objects you may want to keep in place. Prefer leaving it off unless users genuinely need to edit objects, and test the intended slicer interaction with your workbook.
Choose permissions for a PivotTable or table slicer
For a slicer connected to a PivotTable, the key setting to check in Protect Sheet is Use PivotTable & PivotChart reports. It allows permitted interaction with PivotTable reports on the protected sheet. Unlocking the slicer and allowing PivotTable use address related but distinct restrictions: one concerns the slicer object, and the other concerns PivotTable operations. Depending on the workbook and Excel build, other PivotTable actions, such as refreshing or changing its layout, may still be restricted.
For a slicer connected to an Excel table, check the protection options for the intended filtering behavior. If the sheet has an existing AutoFilter, Use AutoFilter is the permission associated with changing its filter criteria. It does not grant general editing rights to the cells. Because table slicers and protected sheets can behave differently from PivotTable slicers, verify that the slicer buttons change the displayed table rows after protection rather than assuming the PivotTable permission applies.
Permission labels and availability can depend on the objects on the sheet. If an option is unavailable or the slicer remains blocked, try the troubleshooting checks below instead of turning on every permission. Broad permissions can expose more of the sheet to accidental changes than necessary.
Test the result before sharing
Do not judge success only by whether the slicer looks normal. Test the actual filtering behavior while the sheet is protected. Choose a slicer item that should visibly reduce the results, then choose another item or clear the filter. Confirm that users can select the intended buttons without being able to alter protected formulas or headings.
It is also useful to test with a copy of the workbook and with the same Excel version your audience will use. For example, a dashboard may have a PivotTable slicer on one sheet and a table slicer on another. Each sheet has its own protection settings, and each slicer may need its own Locked property cleared.
If the slicer is connected to several PivotTables, check each report after filtering. A slicer only controls compatible reports to which it is connected. Use the slicer’s Report Connections or PivotTable Connections option, where available, to confirm the intended connections before protection is applied.
Common problems and how to fix them
- The slicer is visible but its buttons do nothing: Unprotect the sheet, clear the slicer’s Locked property, then protect the sheet again with the appropriate report or filter permission. Check every slicer, not just the first one.
- Excel says the sheet is protected when you click the slicer: The slicer may still be locked, or the needed PivotTable or AutoFilter permission may be missing. Recheck both settings.
- Users can move or resize the slicer: Review whether Edit objects was enabled. Turn it off if users only need to filter, then retest. Keep the slicer unlocked if the filtering control still needs to work.
- One report changes but another does not: Check the slicer’s report connections. The reports may not share a compatible source or may not be connected to that slicer.
- Filtering works, but refreshing fails: Filtering an existing report and refreshing its data are different actions. Sheet protection may prevent refresh or other structural operations. Unprotect the sheet for maintenance, or design a separate refresh workflow and test it with the workbook’s data connection.
- The password is unknown: Excel does not provide a normal password-recovery path for protected sheets. Ask the workbook owner or administrator for the password or an unprotected copy; do not assume the slicer itself can remove the protection.
Excel for Mac and Excel for the web
On Excel for Mac, the protection and object-formatting interfaces differ from Windows. You can protect a worksheet from the Review tab, but the location and presentation of the slicer’s Locked property may vary by release. Look in the slicer’s formatting or size-and-properties controls, clear Locked, and use the available protection permissions. If a specific permission is not shown, test the workbook on the target Mac version before distributing it.
Excel for the web can support interacting with slicers in workbooks, but its editing and protection controls do not always match the full Windows desktop interface. If you need to change the slicer’s object properties or configure detailed sheet permissions, open the workbook in desktop Excel, set it up there, save it, and then test the web experience separately. Avoid promising web users a particular protected-slicer behavior until it has been verified in their environment.
When another design is a better fit
If the goal is to let people explore a dashboard without changing its layout, an unlocked slicer on a protected sheet is often a practical compromise. Keep cells locked, limit permissions to the actions users need, and provide a short instruction such as “Select a button to filter; use the clear-filter icon to show all items.”
If users need to edit the underlying records, change PivotTable layouts, or refresh external data, worksheet protection may get in the way. Consider separating the dashboard from the data-entry or maintenance sheet, or provide an unprotected working copy to authorized editors. A slicer is a filtering control, not a substitute for a secure user interface or a database permission system.
Frequently Asked Questions
Can I protect a sheet but still use a slicer?
Yes. Clear the slicer’s Locked property before protecting the sheet, then allow the relevant PivotTable or filter actions. Test the slicer with protection turned on, because the necessary permission depends on its source.
Do I have to enable Edit objects?
Not necessarily. Edit objects is broader than allowing a slicer to filter and may let people change other objects. First unlock the slicer and enable the relevant PivotTable or AutoFilter permission. Only use Edit objects if the workbook’s required behavior still needs it, and check what else users can modify.
Can a slicer filter an Excel table on a protected sheet?
It can in supported configurations, but table slicers and PivotTable slicers do not rely on identical protection permissions. Unlock the slicer, check the available AutoFilter permission where applicable, and test filtering in the version of Excel your users will use.
Will sheet protection stop users from seeing the data?
No. Worksheet protection is mainly intended to prevent editing and accidental changes. It does not hide data securely or encrypt the workbook. Use appropriate file access controls when information must be restricted.
Why can users click the slicer but not refresh the PivotTable?
Filtering an existing PivotTable is separate from refreshing its source. Protection, data connections, or workbook permissions may block refresh even when slicer use works. Unprotect the sheet for authorized maintenance or configure and test a suitable refresh process.
Related
- Excel Power Query Date Format: Types, Changes, and Fixes
- Train Inventory Spreadsheet: What to Track and How
- Tool Inventory Spreadsheet: Setup, Tracking, and Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Clear Cache on the Outlook App: Safe Steps by DeviceTo clear the Outlook app’s cache, use the method for your device: on Android, open Settings > Apps…
- Excel Data Validation List: How to Add a Blank OptionYes. To add a selectable blank to an Excel data-validation drop-down, include a genuinely empty cell inside the…
- Lock Cells in Google Sheets: How Cell Protection WorksYes. In Google Sheets, use Data > Protect sheets and ranges to restrict editing of selected cells or…
- How to Create a Table Template in Word for Easy ReuseTo make a reusable table in Word, create and format the table, select it, then choose Insert >…
- Excel Custom Number Format: Show Zero as Blank CellsTo display numeric zeros as blank in Excel without deleting their values, apply the custom number format 0;-0;;@…
- How to Add Multiple Transitions in PowerPointTo add transitions to several PowerPoint slides, select the slides in the thumbnail pane, open the Transitions tab,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.