Skip to Content

EPM cell locking and worksheet protection

EPM Cell locking and Worksheet Protection                                                                               

Applies To:

SAP Business Objects and Consolidation for NetWeaver 10.0


This document explains how to prevent a user from changing, moving, or deleting important elements of the report using dynamic formatting sheet and EPM worksheet protection.

Author: Dolly Katyal

Company Name: Wipro Technologies

Author Bio: Dolly is a SAP BI/BPC developer.  Worked in an end to end SAP BI/ABAP/BO implementation, with domain knowledge of various utilities modules Meter reads, meter data collection, finance, enquiries, new connections, fieldwork etc. Currently working as SAP BPC developer on various planning modules like HR, Benefits etc. for a US customer.


A requirement specifies that the user should be able to enter the data in an input form based on property of a dimension. Based on that property values, some cells should be locked for input and user should not be able to enter data against them. We can accomplish this requirement using cell locking and EPM worksheet protection.

Worksheet Protection

To prevent a user from changing, moving, or deleting important elements of the report, you can protect the current worksheet or the whole workbook. If you protect a worksheet containing reports in which a user will enter data, you can lock certain and unlock other cells of the report.

Dynamic formatting sheet

Dynamic formatting is defined in a dedicated sheet. A specific format (such as font, color, or size) can

be defined for levels of a hierarchy, types of members, odd/even rows or columns and page axis


Rules for dynamic formatting

Below are some rules that should be taken care of while formatting:

1.      If you check more than one formatting section, the format settings in lower sections override the ones in upper sections in case of conflicts (for example: inconsistency between a member format and a level format).

2.      Inside each section, if you check several Apply checkboxes, the format settings are applied from

the lowest area to the upper area.

Steps for locking cells

Step 1: Firstly create a report

Step 2: Apply locking in formatting sheet

1.     To restrict single member, you can directly chose the member and restrict.

Go to EPM formatting sheetMember selection tab

2.     To restrict multiple members on the basis of a property, maintain any property in Account dimension.

Example: DISPLAY in this case. Mention the property values as “N” for which you want the lock the cells.

Then go to — EPM formatting sheet –Property selection — make selections as shown below.

It looks like this once the property selection is done:

Step 3: Lock in formatting sheet

Now we have selected the property on the basis of which we want to lock the data.

Right click on the cell in front of the property selection cell Go to EPM Lock selected cells.

STEP 4: Apply Worksheet protection

Dynamic formatting locks will not work without applying work sheet locking

Note: The Microsoft Excel sheet protection always overrides the EPM add-in sheet protection. You can unprotect a sheet using the Microsoft Excel feature or the EPM add-in feature. However, to protect a sheet, you must use the EPM add-in feature.

How to protect the workbook using EPM Add-in

To protect the sheet, the workbook or only certain elements, and to enter a password:

Select EPM — Options — Sheet Options — select the Protection tab.


Choose a password that is easy to remember, because if you lose the password, you can no longer

access the protected worksheet or workbook.

Step 5: Verify locking

Once you are done with protection.  Try to insert data in the cells that we have already locked using EPM protection.


To edit your report further you will first have to un-protect the workbook and then edit the report. Steps are mentioned below:

Select EPM — Options — Sheet Options.

A message appears in which you can enter the protection password. In the Protection tab of the Sheet Option  select NoProtection.


We have learnt how to lock the cells in EPM workbook by using dynamic formatting template and EPM workbook protection. We can lock and unlock the workbooks using EPM options.


1 Comment
You must be Logged on to comment or reply to a post.
  • Thank you for the step by step instructions.  Very helpful.  Do you also have any discussion on how to lock time periods dynamically based on the forecast category selected?

    Many thanks.