Slicer: how to lock slicer to one option only?

Bpuad

New Member
Joined
Feb 17, 2012
Messages
36
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I have a sheet with individual information for employee that I have created a pivot table from and added slicers, so far so good.

Now to the problem, one of the columns in the individual data sheet is BA (Business Area) and one is BU (Business unit / under BA). I would like to be able to send this out to each BA and lock the slicer to just show the BA in question and they can then filer/use slicer to see e.g. information about each of their BU's.

Is this possible? If I can't lock it, can I just sort it in some way so that they can't themselves add a slicer and see other BA's information?

I've tried to search a lot but can't seem to find a solution to this, or perhaps I'm just a rubbish searcher :)

/Per
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
If you don't want them to be able to see the other data, remove it from the data source. No other way will be truly secure.
 
Upvote 0
Hi,

I have a sheet with individual information for employee that I have created a pivot table from and added slicers, so far so good.

Now to the problem, one of the columns in the individual data sheet is BA (Business Area) and one is BU (Business unit / under BA). I would like to be able to send this out to each BA and lock the slicer to just show the BA in question and they can then filer/use slicer to see e.g. information about each of their BU's.

Is this possible? If I can't lock it, can I just sort it in some way so that they can't themselves add a slicer and see other BA's information?

I've tried to search a lot but can't seem to find a solution to this, or perhaps I'm just a rubbish searcher :)

/Per
SOLUTION HERE! (After 5 years):

To achieve this, you should use the "Protect sheet" functionality.

1. Make sure you select all the cells that you want to lock (Select cells and go to Home - Alignment -> Protection). If you just want to Lock the slicer, unselect Lock for every other cell.
2. Right-click slicer > Size & properties > Properties (section) > Select "Locked"
3. Go to Review (tab on the ribbon) -> Protect Sheet > choose Password and other options and click Ok.

<<< VOILA! Ultimate solution! >>>

Feel free to reply to this comment if you need help! I'll try to answer as fast as I can.
 
Upvote 0
Excel‘s worksheet protection is easily broken though.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top