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 are 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 have 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 doing so is optional.
When we click OK, we will now see that all cells are locked and unable to be changed, except for the three input cells that we 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 enables 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.
Below is a break down of subject weightings in the FMVA® financial analyst program. As you can see there is a heavy focus on financial modeling, finance, Excel, business valuation, budgeting/forecasting, PowerPoint presentations, accounting and business strategy.
A well rounded financial analyst possesses all of the above skills!
Additional Questions & Answers
CFI is the global institution behind the financial modeling and valuation analyst FMVA® Designation. CFI is on a mission to enable anyone to be a great financial analyst and have a great career path. In order to help you advance your career, CFI has compiled many resources to assist you along the path.
In order to become a great financial analyst, here are some more questions and answers for you to discover:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.