Protecting Excel Data

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 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.

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 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.

Additional Resources

CFI is the official provider of the Financial Modeling & Valuation Analyst designation and on a mission to help you advance your career.

To continue learning and developing your skills, these additional free CFI resources will be helpful:

• Building a Financial Model in Excel
• Advanced Excel Formulas Course
• Financial Modeling Best Practices
• Types of Financial Models
• See all financial modeling resources
• See all Excel resources