Excel cell Lock

Source: Internet
Author: User

Excel is a commonly used data processing software that is commonly used by office workers. Regular office time need to some data or templates need to be protected, lest the staff accidentally delete some important data. In this case, you must lock the cell data that needs to be protected. Then the small knitting will be how to flexibly use the "Excel cell lock" related content. In this way, we can protect the important data and cannot edit and modify it. will not cause data damage or loss.

Excel cell Locking Method:

Step One: Ctrl + a All select all the cells, and then right-click in the Pop-up list box, select "Format Cells", go to the Cell Format window, select "Protection", uncheck "lock" can be. Click OK. As shown in the figure:

Step two: Continue to select one or more of the cells you want to lock, and then right-click to select "Format Cells", the same way as step one in the pop-up window select "Protection" to "lock" tick. Click OK.

Step three: Continue to select the cells you want to lock, and then click Tools-Protect-Protect worksheet on the menu bar. Enter the locked password on the Protection sheet window that pops up, uncheck the system by default, select locked cells, select unlocked cells, and then the worksheet is locked. Of course, this is all locked worksheet operation, if you need to lock the section, you can then drop down the window to select the cells you need to protect, according to the user needs to set. As shown in the figure:

Step four: We can also make changes to the locked range of cells, select "Tools"-"protection"-"allow users to edit the area", you can set the area. As shown in the figure:

Worksheet Excel cells are locked. So when we enter the data text, we will be prompted not to enter, as shown in the figure:

Excel cell Unlock Method:

Unlock the method is very simple, select the cell, directly click on the menu bar "tools"-"protection" under "remove sheet protection" can be, and then enter the window on your locked worksheet set the password to unlock the worksheet.

How Excel cell data is hidden:

How to hide data in cells, and then edit the area can be displayed again. Select the cell directly, right-click "Format Cells", select "Custom" on the "number" option on the pop-up window, and then enter;;; Can. Click OK, so the input text on the cell, it is not shown oh. As shown in the figure:

Of course in Excel you can also set some of the cells locked, the method is similar. Only according to the user's need to choose.

How to hide data in a locked-protected cell by first formatting the Excel cell. Lock important data and hide it for protection. Then you can change the cell data that is not protected, how do you do it? Small series through the test results of the case to analyze it. As shown in the figure: you need to set the red to a locked area, and black is the data entry area.

To set an unprotected zone method:

First, select the data entry area, that is, the B3:f10 area, in the right mouse selected "format Cells" in the Select the "Protection" option to uncheck the "lock" and "hidden" items, so the B3:f10 area is the cost locked area. In this way, the data can be modified. As shown in the figure:

To set a method for protecting a zone:

Through the above method, first will need to hide the protection of the area selected, the above figure: check "lock and Hide", you can select "hidden" to make the data in the cell does not appear in the formula bar, so as to avoid too long and obscure the following table content.

Let's take a look at how to automatically calculate and display the corresponding data:

In A3 cell, enter the formula "=if" (OR (b3<> ",c3<>"), COUNTBLANK ($A $2:a2) +counta ($A $2:a2), ""), and drag the fill handle in the lower right corner of the A3 to fill down to A10. In the case of B3, C3 cells as a reference, as long as one of them has data, then the corresponding ordinal number in the A3 grid, otherwise not displayed.

In H3 cell, enter the formula "=if" (OR (b3<> ",c3<>"), (E3+F3)/2, ""), and drag the fill handle in the lower right corner of the H3 to the H10, showing the average score per student

In cell G3, enter the formula "=if" (OR (b3<> ",c3<>"), SUM (E3:F3), ""), and fill down to G10 to display the total score.

In I3 cells, enter the formula "=if" (OR (b3<> ",c3<>"), Rank (H3, $H $: $H $10,0), ""), and fill down to G10 to dynamically display the results of the order.

When the operation is complete, the cell is protected. Select the cell, select tools-Protection-protect sheet on the menu bar, and set the password as shown in the figure:

In that case, a spreadsheet template is made. The red area cannot be changed. The two black areas are free to set changes. As shown in the figure:

Methods of reading and writing protection for Excel cells

Method One: We do not want the contents of the cell to be modified by others, you need to read and write the cell data of Excel protection. Lock on an Excel cell. The above explanation is very detailed.

Method Two: Select the one or more Excel cells that you want to lock, and then select the Effectivity option in the Data menu, and then set the valid conditions to write-protect the cells. If someone wants to change our Excel cell, then the content that is not allowed in this Excel cell will not be entered in Excel cells.

We've learned to write protection for Excel cells, so it's just read-protected. Reading protection means that people don't see what we write in Excel cells.

To set the reading of a cell read-protected by setting the color of the cell, the text color is the same as the back color. Or use other images to cover the cells that need to be protected, to cover the original of the cell, for read protection purposes. You can also read protection by setting the cell's row height and column widths, hiding the selected cells, and then protecting the worksheet so that users cannot directly access the cells that are hidden.

The most effective read protection is to encrypt the Excel file and turn it off, so that people who don't know the password can not open the Excel file, and can not talk about reading the data in Excel cells.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.