Skip to main content

Protect Formula Cells


The use of worksheet protection in Excel is a little strange. Using the steps below, you can quickly protect just the formula cells in your worksheet.

It seems unusual, but all 16 billion cells on a worksheet start out with their Locked property set to True. You need to unlock all of the cells first:

  1. Select all cells by using the icon above and to the left of cell A1.
  2. Press Ctrl+1 (that is the number 1) to open the Format Cells dialog.
  3. In the Format Cells dialog, go to the Protection tab. Uncheck Locked. Click OK.
Select all cells and change the Locked property to off.

While all cells are still selected, select Home, Find & Select, Formulas.

At this point, only the formula cells are selected. Press Ctrl+1 again to display the Format Cells dialog. On the Protection tab, choose Locked to lock all of the formula cells.

With only the formula cells selected, set Locked property to On.

Locking cells does nothing until you protect the worksheet. On the Review tab, choose Protect Sheet. In the Protect Sheet dialog, choose if you want people to be able to select your formula cells or not.

Note

Scroll down a few rows in the Protect Sheet dialog box to reveal popular choices: Use AutoFilter and Use PivotTable & PivotChart. If you want allow people to interact with these features, scroll down and select them.

Caution

Don’t bother putting in a password.

Use the Protect Sheet icon on the Review tab. You can choose what people can do to a protected sheet. In this screenshot, only Select Unlocked Cells is available.
Labels:

Comments

Post a Comment

Popular posts from this blog

20 Time Intelligence Dax Measures

20 Time Intelligence DAX measures in Power BI with examples: Year-to-Date Sales: css Copy code YTD Sales = TOTALYTD( [Total Sales] , Calendar [Date] ) Month-to-Date Sales: css Copy code MTD Sales = TOTALMTD( [Total Sales] , Calendar [Date] ) Quarter-to-Date Sales: css Copy code QTD Sales = TOTALQTD( [Total Sales] , Calendar [Date] ) Previous Year Sales: mathematica Copy code Previous Year Sales = CALCULATE ( [ Total Sales ] , SAMEPERIODLASTYEAR ( Calendar [ Date ] ) ) Year-over-Year Growth: css Copy code YoY Growth = DIVIDE( [Total Sales] - [Previous Year Sales] , [Previous Year Sales] ) Rolling 3-Month Average Sales: sql Copy code 3 M Rolling Avg Sales = AVERAGEX(DATESINPERIOD(Calendar[ Date ], MAX (Calendar[ Date ]), -3 , MONTH ), [Total Sales]) Cumulative Sales: scss Copy code Cumulative Sales = SUMX (FILTER(ALL(Calendar), Calendar [Date] <= MAX (Calendar[Date])), [Total Sales] ) Running Total Sales: scss Copy code Running Total Sales = SUMX (FILTER(ALL(Calendar), Calen...
Post a Comment
Read more

40 Power Query Editor features in Power BI

40 Power Query Editor features in Power BI along with examples: 1. Filter Rows: Remove rows based on conditions. Example: Remove rows with a null value in the "CustomerName" column. 2. Remove Duplicates: Eliminate duplicate rows. Example: Remove duplicate entries based on the "OrderID" column. 3. Sort Rows: Arrange rows in ascending or descending order. Example: Sort data by "Date" column in descending order. 4. Replace Values: Substitute one value with another. Example: Replace "N/A" with "Unknown" in the "Status" column. 5. Split Columns: Divide a column into multiple columns. Example: Split "FullName" into "FirstName" and "LastName." 6. Merge Queries: Combine data from multiple sources. Example: Merge customer and order data based on the "CustomerID." 7. Group By: Aggregate data based on a specific column. Example: Group sales data by "ProductCategory" and calculate the sum ...
Post a Comment
Read more

Data Cleaning Functions in Excel

  The CLEAN function Using the CLEAN function removes nonprintable characters text. For example, if the text labels shown in a column are using crazy nonprintable characters that end up showing as solid blocks or goofy symbols, you can use the CLEAN function to clean up this text. The cleaned‐up text can be stored in another column. You can then work with the cleaned text column. The CLEAN function uses the following syntax: CLEAN(text) The text argument is the text string or a reference to the cell holding the text string that you want to clean. For example, to clean the text stored in Cell A1, use the following syntax: CLEAN(A1) The CONCATENATE function The CONCATENATE function combines, or joins, chunks of text into a single text string. The CONCATENATE function uses the following syntax: CONCATENATE(text1,text2,text3,...) The text1, text2, text3, and so on arguments are the chunks of text that you want to combine into a single string. For example, if the city, state, and ZIP co...
Post a Comment
Read more