Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

Protecting Excel Data

Learn how to lock and group data in Excel

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.

 

Protecting Excel Data

 

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.

 

Protecting Data - Example 1

 

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.

 

Protecting Data - Example 1

 

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.

 

Protecting Data - Example 2

 

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.

 

Protecting Data - Example 3

 

Grouping Cells

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.

 

Grouping Data - Example 1

 

Next, on the “Data” ribbon, select the “Group” drop-down, and then “Group” again.

 

Grouping Data - Example 2

 

Additional Resources

  • Building a Financial Model in Excel
  • Advanced Excel Formulas Course
  • Financial Modeling Best Practices
  • Types of Financial Models

Financial Modeling Certification

Become a certified Financial Modeling and Valuation Analyst (FMVA)® by completing CFI’s online financial modeling classes!