When creating Excel templates for budgets, quotations, or sales reports, you often need users to enter their own data without changing the formulas that calculate the results.
For example, a quotation template might allow users to update quantities and unit prices while keeping the total amount formulas protected. If someone accidentally overwrites a formula, the calculation may become incorrect without being immediately noticeable.
In C#, you can control which Excel cells remain editable, lock the cells containing formulas, and optionally hide those formulas from the formula bar.
This tutorial demonstrates how to create an Excel quotation template where users can modify input values but cannot edit the calculated results.
Prerequisites
Before getting started, install the required Excel processing library in your C# project.
In Visual Studio, go to Tools > NuGet Package Manager > Package Manager Console and run:
Install-Package FreeSpire.XLS Alternatively, install it using the .NET CLI:
dotnet add package FreeSpire.XLS The example uses Free Spire.XLS for .NET, which allows you to create and modify Excel workbooks without requiring Microsoft Excel to be installed.
How Excel Cell Protection Works
Before writing the code, it's important to understand how Excel handles locked cells.
By default, Excel cells have the Locked property enabled. However, this setting does not prevent editing until worksheet protection is activated.
This means that protecting only certain cells involves two main operations:
- Unlock the cells that users should be able to edit.
- Protect the worksheet so that the remaining locked cells cannot be modified.
You can also enable the Hidden property for formula cells. Once worksheet protection is applied, their calculated results remain visible, but their formulas no longer appear in the formula bar.
These settings let you create a worksheet that users can fill out without accidentally modifying its calculation logic.
Create an Excel Template with Protected Formulas in Csharp
Suppose we're building a simple sales quotation template with the following structure:
| Column | Content | Editable? |
|---|---|---|
| A | Item descriptions | No |
| B | Quantities | Yes |
| C | Unit prices | Yes |
| D | Calculated amounts | No |
Rows 4–6 contain the quotation items, while cell D7 calculates the grand total.
We'll create the workbook, add the formulas, configure cell protection, and save the result.
Step 1: Create a Workbook and Add Sample Data
Start by creating a Workbook object and accessing the first worksheet.
Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; sheet.Name = "Quotation"; Next, add the column headers and sample values.
sheet.Range["A3"].Text = "Item"; sheet.Range["B3"].Text = "Quantity"; sheet.Range["C3"].Text = "Unit Price"; sheet.Range["D3"].Text = "Amount"; sheet.Range["A4"].Text = "Cloud Service"; sheet.Range["B4"].NumberValue = 3; sheet.Range["C4"].NumberValue = 199; sheet.Range["A5"].Text = "Support Package"; sheet.Range["B5"].NumberValue = 2; sheet.Range["C5"].NumberValue = 149; sheet.Range["A6"].Text = "Training Session"; sheet.Range["B6"].NumberValue = 1; sheet.Range["C6"].NumberValue = 299; Now add formulas to calculate each item's amount and the grand total.
sheet.Range["D4"].Formula = "=B4*C4"; sheet.Range["D5"].Formula = "=B5*C5"; sheet.Range["D6"].Formula = "=B6*C6"; sheet.Range["A7"].Text = "Grand Total"; sheet.Range["D7"].Formula = "=SUM(D4:D6)"; At this point, the worksheet contains the necessary data and formulas, but no editing restrictions have been applied.
Step 2: Unlock the Input Cells
In our quotation template, users should be able to change only the quantities and unit prices.
These values are stored in the range B4:C6.
To make that range editable, set its Style.Locked property to false.
sheet.Range["B4:C6"].Style.Locked = false; This allows users to update the values in those cells after the worksheet is protected.
The other cells retain their default locked state.
One important detail is that setting Locked = false does not activate worksheet protection. It simply specifies which cells will remain editable once protection is enabled.
Step 3: Lock and Hide the Formula Cells
The calculated amounts are stored in D4:D7.
We want to prevent users from modifying these formulas and hide them from the formula bar.
CellRange formulaRange = sheet.Range["D4:D7"]; formulaRange.Style.Locked = true; formulaRange.Style.FormulaHidden = true; These two properties serve different purposes:
-
Locked = trueprevents users from editing the cells when the worksheet is protected. -
FormulaHidden = trueprevents the underlying formulas from appearing in the formula bar while protection is active.
For example, cell D4 contains:
=B4*C4 After worksheet protection is applied, Excel still displays the calculated result, but users cannot view or edit the formula through the normal worksheet interface.
This is useful when sharing templates that contain calculations you don't want users to change accidentally.
Step 4: Protect the Worksheet and Save the File
Finally, enable worksheet protection using Protect().
sheet.Protect("Quote2026"); workbook.SaveToFile( "Protected_Quotation.xlsx", ExcelVersion.Version2016 ); workbook.Dispose(); The password is used to remove worksheet protection when further changes to the locked cells are needed.
With protection enabled, users can modify quantities and unit prices while the formulas and other locked cells remain protected.
Complete C# Example: Create a Protected Excel Quotation Template
The following program combines all the steps into a single example.
It creates the quotation workbook from scratch, so you don't need an existing Excel file to run it.
using System; using Spire.Xls; class Program { static void Main() { // Create an Excel workbook Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; sheet.Name = "Quotation"; // Add a title sheet.Range["A1"].Text = "Sales Quotation"; // Add column headers sheet.Range["A3"].Text = "Item"; sheet.Range["B3"].Text = "Quantity"; sheet.Range["C3"].Text = "Unit Price"; sheet.Range["D3"].Text = "Amount"; // Add sample quotation data sheet.Range["A4"].Text = "Cloud Service"; sheet.Range["B4"].NumberValue = 3; sheet.Range["C4"].NumberValue = 199; sheet.Range["A5"].Text = "Support Package"; sheet.Range["B5"].NumberValue = 2; sheet.Range["C5"].NumberValue = 149; sheet.Range["A6"].Text = "Training Session"; sheet.Range["B6"].NumberValue = 1; sheet.Range["C6"].NumberValue = 299; // Add calculation formulas sheet.Range["D4"].Formula = "=B4*C4"; sheet.Range["D5"].Formula = "=B5*C5"; sheet.Range["D6"].Formula = "=B6*C6"; sheet.Range["A7"].Text = "Grand Total"; sheet.Range["D7"].Formula = "=SUM(D4:D6)"; // Format the worksheet sheet.Range["A1"].Style.Font.IsBold = true; sheet.Range["A3:D3"].Style.Font.IsBold = true; sheet.Range["A7:D7"].Style.Font.IsBold = true; sheet.SetColumnWidth(1, 24); sheet.SetColumnWidth(2, 14); sheet.SetColumnWidth(3, 16); sheet.SetColumnWidth(4, 16); // Allow users to edit quantities and prices sheet.Range["B4:C6"].Style.Locked = false; // Protect and hide formulas CellRange formulaRange = sheet.Range["D4:D7"]; formulaRange.Style.Locked = true; formulaRange.Style.FormulaHidden = true; // Enable worksheet protection sheet.Protect("Quote2026"); // Save the protected workbook workbook.SaveToFile( "Protected_Quotation.xlsx", ExcelVersion.Version2016 ); workbook.Dispose(); Console.WriteLine( "Protected quotation template created successfully." ); } } After running the program, a file named Protected_Quotation.xlsx is generated.
When you open it in Microsoft Excel, the quotation looks like a normal worksheet, but its editing behavior is different:
- Cells B4:C6: Users can change the quantities and unit prices.
- Cells D4:D7: The calculated amounts remain visible, but their formulas cannot be viewed or edited through the protected worksheet.
- Other cells: The title, item descriptions, and headers remain locked.
For example, if the quantity in B4 changes from 3 to 5, the formula in D4 still calculates the updated amount based on the new input.
Users can enter different quotation values without having to recreate the formulas.
Apply Cell Protection to an Existing Excel File
The previous example creates a new workbook, but the same approach works with existing Excel templates.
Suppose you already have a file named Quotation_Template.xlsx with the same cell layout.
Instead of creating a new workbook, load the existing file and configure its protection settings.
using Spire.Xls; class Program { static void Main() { Workbook workbook = new Workbook(); workbook.LoadFromFile("Quotation_Template.xlsx"); Worksheet sheet = workbook.Worksheets[0]; // Lock all cells in the used range sheet.Range.Style.Locked = true; // Allow editing only in the input area sheet.Range["B4:C6"].Style.Locked = false; // Lock and hide the calculation formulas sheet.Range["D4:D7"].Style.Locked = true; sheet.Range["D4:D7"].Style.FormulaHidden = true; // Protect the worksheet sheet.Protect("Quote2026"); // Save to a new file workbook.SaveToFile( "Quotation_Protected.xlsx", ExcelVersion.Version2016 ); workbook.Dispose(); } } Notice that this example explicitly locks all cells in the used range before unlocking B4:C6.
This is helpful when working with an existing workbook because some cells may already have custom protection settings.
The example assumes that the worksheet is not already protected and that the input and formula ranges match the specified addresses. If your template uses different columns or rows, update those ranges accordingly.
Also, if you want some additional fields to remain editable, such as a customer name or quotation date, you can unlock them individually:
sheet.Range["B1"].Style.Locked = false; sheet.Range["B2"].Style.Locked = false; Make these changes before calling Protect().
Common Issues When Protecting Excel Cells in C
1. Cells Are Still Editable After Setting Locked to True
This usually happens when worksheet protection has not been enabled.
For example:
sheet.Range["D4:D7"].Style.Locked = true; This marks the cells as locked but does not prevent editing by itself.
You must also protect the worksheet:
sheet.Protect("Quote2026"); Without worksheet protection, Excel allows users to modify cells regardless of their Locked property.
2. Formulas Are Still Visible in the Formula Bar
Setting Locked = true prevents editing but does not automatically hide formulas.
To hide formulas, enable the FormulaHidden property:
sheet.Range["D4:D7"].Style.FormulaHidden = true; Then protect the worksheet.
Both operations are required. If worksheet protection is removed, the formulas become visible again.
For more details about this behavior, refer to Microsoft's guide to displaying or hiding formulas.
3. Users Cannot Enter Data into the Input Cells
Check whether the input cells were unlocked before enabling worksheet protection.
For example:
sheet.Range["B4:C6"].Style.Locked = false; If these cells remain locked, Excel prevents users from changing their contents.
Also verify that the cell addresses match your actual template. If quantities are stored in another column, update the range accordingly.
4. Does Worksheet Protection Secure Sensitive Excel Data?
No. Worksheet protection is primarily intended to prevent unwanted changes to spreadsheet content.
It should not be treated as a security mechanism for protecting confidential information.
Although hiding formulas keeps them out of the normal Excel formula bar while worksheet protection is active, it does not provide strong protection against someone who deliberately attempts to inspect or modify the workbook.
If your goal is to prevent unauthorized access to sensitive information, consider file-level encryption or another appropriate security measure.
Microsoft also makes this distinction in its Excel worksheet protection documentation.
Conclusion
Protecting formula cells in Excel is useful when building templates that other people need to fill out without changing the underlying calculations.
In C#, you can achieve this by unlocking designated input cells, locking and optionally hiding formula cells, and enabling worksheet protection.
This approach works well for quotations, budgeting templates, financial calculators, and other spreadsheets where users should be able to enter data while keeping the calculation structure intact.
