This question came from a student during an Excel course.
I share a workbook with my co-workers. I have some very complex functions in this workbook. From time to time, I find that my functions no longer work as intended because someone has tried to change them. I don’t want to protect the entire worksheet or workbook because my co-workers do need to be able to change some cells. Is there any way to prevent people from changing my functions?
Answer – Protect Specific Cells
Yes, you have several options. You can use Visual Basic for Applications to create your own functions. You can also hide the functions so that users see your results but not the functions. Your simplest path, however, is to probably protect specific cells. Users will be able to view, but not edit the protected cells in your worksheet.
Step 1 – Select the Unprotected Cells
The first step in the process of protecting cells in a worksheet is to select the areas you want people to be able to edit. You can select individual cells or entire columns.
(Quick Tip: To select non-contiguous areas, select the first area and press the CTRL key while selecting other areas).
Step 2 – Unlock the Selected Cells
Right-click the selected cells, then click Format Cells.
When the Format Cells dialog box pops up, select the Protection tab.
Remove the check mark next to Locked.
Step 3 – Protect the Worksheet
Now that you’ve unlocked the cells you do not want to protect, you are going to protect your worksheet. This will, in effect, protect everything except for the cells you selected in the previous step.
Click the Review tab on your ribbon.
In the Changes group, select Protect Sheet.
When the Protect Sheet dialog box pops up, type your password (optional), then click the OK button. You will be prompted to enter your password again.
Note: If you do not create a password before clicking the OK button, users will be able to turn the protection off by simply clicking a button.
Do you have an Excel question?
Comment below and it just might make it to a blog post!
About the Author
Jackie Kiadii and her team of subject matter expert (SME) trainers provide Microsoft software training that delivers measurable results – increasing productivity, reducing inefficiency, and improving the bottom line. Topics include: Project, Access, Excel, Excel VBA, PowerPoint, Word, Outlook and Publisher. Upcoming Excel courses are on our calendar, click here.
Need onsite group training? Call 770.498.7333 or email us. Our courses can be customized to your needs. Jackie has provided software training for employees of The Coca-Cola Company, Emory University, Novelis, City of Atlanta and more. This training is available for groups of 4 or more students.
Click to Grab Your Excel Ebook – Free!
