Protecting Excel Data

Learn how to lock and group data in Excel

Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets. Start Free

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.

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

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 doing so is optional.

Protecting Data - Example 2

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.

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

Grouping Data - Example 1

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

Grouping Data - Example 2

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:

Analyst Certification FMVA® Program

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.


Financial Analyst certification curriculum


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:


0 search results for ‘