In addition to the familiar option of protecting a spreadsheet or an entire workbook, Excel also offers the option of only locking cells for editing containing certain formulas. This ensures that formulas are protected while still allowing normal working in the rest of the spreadsheet. Want to know how this works? Then keep reading.
Protecting Excel formulas is easier than you think
You have created a spreadsheet, maybe from
web imported data, with overviews in the form of tables in which cell contents are generated via complex formulas and now want to make this available to team members for further processing. However, it is important to you that you can work in the spreadsheet without changing your formulas.
- Select the entire worksheet (Ctrl + A) and then right-click to open the "Format Cells" window.
- Select the "Protection" tab and uncheck the "Locked" box.
- Now activate all cells of the spreadsheet that contain formulas by selecting the "Go to" dialog box via "Ctrl + G" and clicking on "Contents" there.
- Another window will open. You activate the item "Formulas" and select all cells containing formulas you want to lock and protect.
- Now open the "Format Cells" dialog box again and select the Protection tab. Check the "Locked" box again, which you unchecked before. Because you have only selected the cells that contain formulas, this format will only be applied to these cells and all other cells will be left out.
- In the last step, you now lock the worksheet via the "Review" "Protect Sheet" tab.
A dialog box opens again. Keep the checkmarks that have been preset here. Now only cells that contain formulas are locked instead of the whole spreadsheet. Find more tips and tricks for your work with Excel and the other Microsoft applications in our Microsoft 365 guide.
Do you spend a lot of time working with Excel and waste a lot of time formatting charts? Then read more about our Chart Creation solution! Our practical Office add-ins will help you create documents and charts faster and you will not have to lift a finger to adjust them to your corporate design. Our solution will take the annoying and time-consuming formatting works out of your hands, not only in Excel but also in Word, PowerPoint and even Outlook!
Interested? Contact us for further information. Our experts will be happy to answer any questions you may have!