Insert Checkboxes and Lock Cells

 

Process Flow to Insert Checkboxes and Lock Cells Using Data Validation in Excel

This process allows you to insert checkboxes for evaluation criteria (Punctuality, Job Knowledge, etc.) and lock cells using Data Validation with the formula =$G3<>1.

---

🔹 Step 1: Insert Checkboxes

1. Enable Developer Tab:

   • Open Excel → Go to File → Options → Customize Ribbon → Check Developer → Click OK.

2. Insert Checkboxes:

   • Go to Developer Tab → Click Insert → Select Checkbox (Form Control).
   • Click on the cell where you want the first checkbox (e.g., B2).
   • Resize and adjust the position.
   • Right-click → Edit Text → Delete text (optional).
   • Copy and paste checkboxes into other cells under Punctuality, Job Knowledge, etc.

---

🔹 Step 2: Link Checkboxes to Cells

1. Right-click on each checkbox → Select Format Control.
2. Go to the Control tab → Set Cell Link to the adjacent cell (e.g., H2 for B2 checkbox).
3. Repeat for all checkboxes under the relevant columns.
4. When checked, the linked cell will show TRUE; otherwise, it will show FALSE.

---

🔹 Step 3: Apply Data Validation to Lock Cells Based on Status

1. Select the range where checkboxes are inserted (e.g., B2:F10).
2. Go to Data → Data Validation → Custom.  Enter the formula: 

3. =$G3<>1
   

   • This formula prevents users from making changes if the Status (Column G) is 100%.
4. Click OK → Now, when a row's Status = 100%, checkboxes cannot be unchecked.

---

🔹 Step 4: Conditional Formatting (Optional)

1. Select the entire table (B2:G10).
2. Go to Home → Conditional Formatting → New Rule.
3. Select Use a formula to determine which cells to format. Enter the formula: 

4. =$G2=1
   

   • This highlights rows where Status = 100%.
5. Click Format → Choose a color → Click OK.

---

🔹 Summary

✔ Insert Checkboxes → Link to Cells
✔ Use Data Validation Formula =$G3<>1 to Lock Cells
✔ Use Conditional Formatting for Better Visibility