Protecting Excel Data
Protecting Excel data in financial modeling is critical. Users prefer to secure and protect cells that they will not tweak or change while leaving certain cells free to adjust assumptions and inputs. By doing so, users are protected from potential inconsistencies and mistakes that may result from typos and unwanted changes in output cell functions.
The first thing we should recognize is that the default setting in Excel is for individual cells to be locked. Therefore, when a worksheet is protected, all cells will be locked down. Although your model’s output cells must always remain locked, input cells need to be unlocked to allow for changes in assumptions and inputs.
The Excel shortcut to unlock and format cells is Ctrl + 1. The “Format Cells” window includes a separate “Protection” tab, where you can lock or unlock individual cells.
How to Protect a Worksheet
Once you’ve unlocked the input cells in your financial model, you can now protect your worksheet by going to the “Review” ribbon and selecting “Protect Sheet.” It is important that we unlock the input cells prior to protecting the worksheet as users will need to make changes to the model inputs.
How to Protect Data
Now that we’ve learned how to lock and unlock cells and protect a worksheet, let’s try it ourselves in an easy, step-by-step procedure.
First, let’s highlight the cells that we need to unlock. In this case, we will highlight the input cells “CurrentSales,” “SalesGrowthPercent,” and “GrossMargin” in blue font to indicate they are input cells. In fact, blue fonts are commonly used to mark input cells.
Now that we highlighted the input cells in blue font, let’s enter Ctrl + 1 to format these cells. In the “Format Cells” window, we will click on the “Protection” tab to ensure that the “Locked” box is ticked off. Press OK to return to the spreadsheet.
Finally, we will now lock the entire worksheet after unlocking our input cells. In order to lock the worksheet, we will press the “Review” ribbon and then click on the “Protect Sheet” function. In this example, we will leave all the default settings and not use a password as it is optional.
When we click OK, we will now see that all cells are locked and unable to change, except for the three input cells that we’ve unlocked prior to protecting the worksheet.
After going through the instructions for protecting Excel data when building a financial model, let’s learn how to use the grouping function in Excel to create multiple sections in a worksheet. Grouping cells is an extremely useful and handy way to audit a model by making it easily expandable and contractible. Moreover, grouping cells allow big corporations with multiple operating divisions to conveniently display and organize data in a single worksheet.
Here are the step-by-step instructions to group cells:
First, select the rows you want to group while leaving the title row at the top unselected.
Next, on the “Data” ribbon, select the “Group” drop-down, and then “Group” again.