Explore the Possibilities of the What-If Analysis Scenario Manager in Excel Using C#
Tags: fileformats, csharp, excel, whatsnew
Published: 2023-10-04 15:40:10

---
title: Explore the Possibilities of the What-If Analysis Scenario Manager in Excel Using C#
published: true
date: 2023-10-03 11:13:28 UTC
tags: fileformats, csharp, excel, whatsnew
canonical_url: https://www.syncfusion.com/blogs/post/what-if-analysis-scenario-manager-excel-csharp.aspx
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p1554rj1xru2jrvshl80.png
---
What-if analysis in Excel is a powerful tool for individuals and organizations to use to explore various scenarios, predict the impact of choices, and make decisions confidently. It allows you to create scenarios with different sets of values for the same cells in a worksheet. With this feature, a user can view multiple forecasts of data in a single Excel document.

From the [2023 Volume 3](https://www.syncfusion.com/forums/184588/essential-studio-2023-volume-3-main-release-v23-1-36-is-available-for-download "Essential Studio 2023 Volume 3") release onward, the [Syncfusion Excel Library (XlsIO)](https://www.syncfusion.com/document-processing/excel-framework/net ".NET Excel Library") supports a [what-if analysis scenario manager](https://help.syncfusion.com/file-formats/xlsio/what-if-analysis "What-if Analysis Scenario Manager in Excel") feature in Excel documents using C#.

Users can perform the following actions using the what-if analysis scenario manager:

- [Create scenarios](https://help.syncfusion.com/file-formats/xlsio/what-if-analysis#create-scenario "Creating scenarios in Excel") with different sets of values and apply them to view the forecast results.
- [Apply scenarios](https://help.syncfusion.com/file-formats/xlsio/what-if-analysis#apply-scenario "Applying scenarios in Excel") and save the results as separate Excel documents.
- [Protect](https://help.syncfusion.com/file-formats/xlsio/what-if-analysis#protect-scenario "Protect scenarios in Excel") or [hide](https://help.syncfusion.com/file-formats/xlsio/what-if-analysis#hide-scenario "Hide scenarios in Excel") the scenarios to prevent users from changing them.

Let’s see how to perform these actions in an Excel document using C#.

**Note:** If you are new to our Excel Library, following our [getting started guide](https://help.syncfusion.com/file-formats/xlsio/create-excel-file-csharp-vbnet "Getting started with .NET Excel Library") is highly recommended.

## Creating scenarios

You can create multiple scenarios in an Excel document, and each scenario can have up to 32 variable cell ranges with values.

The following code example illustrates how to create what-if analysis scenarios in an Excel document.

```csharp
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;

    FileStream inputStream = new FileStream("WhatIfAnalysisTemplate.xlsx", FileMode.Open, FileAccess.Read);
    IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
    inputStream.Dispose();

    IWorksheet worksheet = workbook.Worksheets[0];

    // Access the collection of scenarios in the worksheet.
    IScenarios scenarios = worksheet.Scenarios;

    //Initialize list objects with different values for scenarios.
    List<object> currentChangePercentage_Values = new List<object> { 0.23, 0.8, 1.1, 0.5, 0.35, 0.2};
    List<object> increasedChangePercentage_Values = new List<object> { 0.45, 0.56, 0.9, 0.5, 0.58, 0.43};
    List<object> decreasedChangePercentage_Values = new List<object> { 0.3, 0.2, 0.5, 0.3, 0.5, 0.23};
    List<object> currentQuantity_Values = new List<object> { 1500, 3000, 5000, 4000, 500, 4000 };
    List<object> increasedQuantity_Values = new List<object> { 1000, 5000, 4500, 3900, 10000, 8900 };
    List<object> decreasedQuantity_Values = new List<object> { 1000, 2000, 3000, 3000, 300, 4000 };

    //Add scenarios in the worksheet with different values for the same cells.
    scenarios.Add("Current % of Change", worksheet.Range["F5:F10"], currentChangePercentage_Values);
    scenarios.Add("Increased % of Change", worksheet.Range["F5:F10"], increasedChangePercentage_Values);
    scenarios.Add("Decreased % of Change", worksheet.Range["F5:F10"], decreasedChangePercentage_Values);
    scenarios.Add("Current Quantity", worksheet.Range["D5:D10"], currentQuantity_Values);
    scenarios.Add("Increased Quantity", worksheet.Range["D5:D10"], increasedQuantity_Values);
    scenarios.Add("Decreased Quantity", worksheet.Range["D5:D10"], decreasedQuantity_Values);

    //Saving the workbook as a stream.
    using (FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite))
    {
        workbook.SaveAs(stream);
    }

}
```

The following is the output image.
<figure>

  <img src="https://www.syncfusion.com/blogs/wp-content/uploads/2023/10/Creating-scenarios-using-what-if-analysis-feature-in-an-Excel-document-1.png" alt="Creating scenarios using what-if analysis feature in an Excel document" style="width:100%">

  <figcaption>Creating scenarios using what-if analysis feature in an Excel document</figcaption>

</figure>

## Applying scenarios

You can get a cell value after applying specific scenarios in the worksheet. You can save each scenario’s results as a separate Excel document. This way, the resultant document can be shared with other users.

The following code example illustrates how to apply scenarios in a worksheet and resave the results in separate Excel documents.

```csharp
//Access the collection of scenarios in the worksheet.
IScenarios scenarios = worksheet.Scenarios;

for (int pos =0; pos < scenarios.Count; pos++)
{
    //Apply scenarios.
    scenarios[pos].Show();

    IWorkbook newBook = excelEngine.Excel.Workbooks.Create(0);

    IWorksheet newSheet = newBook.Worksheets.AddCopy(worksheet);        

    newSheet.Name = scenarios[pos].Name;

    //Saving the new workbook as a stream.
    using (FileStream stream = new FileStream(scenarios[pos].Name + ".xlsx", FileMode.Create, FileAccess.ReadWrite))
    {
        newBook.SaveAs(stream);
    }

    //To restore the cell values from the previous scenario results.
    scenarios["Current % of Change"].Show();
    scenarios["Current Quantity"].Show();
}
```

Refer to the following images. Here, we’ve applied the **Current % of Change** and **Current Quantity** scenarios. 
<figure>

  <img src="https://www.syncfusion.com/blogs/wp-content/uploads/2023/10/Excel-document-before-applying-scenarios-2.png" alt="Excel document before applying scenarios" style="width:100%">

  <figcaption>Excel document before applying scenarios</figcaption>

</figure>
<figure>

  <img src="https://www.syncfusion.com/blogs/wp-content/uploads/2023/10/Excel-document-after-applying-the-scenarios.png" alt="Excel document after applying the scenarios" style="width:100%">

  <figcaption>Excel document after applying the scenarios</figcaption>

</figure>
## Protect scenarios

You can protect a scenario in an Excel document to keep other users from modifying it. By default, the scenarios are protected when the sheet is protected. This can be disabled by making the **IScenario.Locked** property false.

The following code example illustrates how to protect scenarios in an Excel document.

```csharp
IWorksheet worksheet = workbook.Worksheets[0];

//To make a scenario editable after protecting the sheet set scenarios[0].Locked = false;

//Enable worksheet protection.
worksheet. Protect("scenario");
```

The following is the output image. You can see that the edit option is disabled.
<figure>

  <img src="https://www.syncfusion.com/blogs/wp-content/uploads/2023/10/Protecting-scenarios-in-an-Excel-document-1.png" alt="Protecting scenarios in an Excel document" style="width:100%">

  <figcaption>Protecting scenarios in an Excel document</figcaption>

</figure>
## Hiding scenarios

You can also hide a scenario in an Excel document, so only specific users can view the scenario results. The following code example illustrates how to hide a scenario in an Excel document.

```csharp
//Access the collection of scenarios in the worksheet.
IScenarios scenarios = worksheet.Scenarios;

//Disable the protection for a specific scenario.
scenarios["Increased % of Change"].Hidden = true;

worksheet. Protect("Scenario");
```

Refer to the following output image. In it, we’ve hidden the **Increased % of Change** scenario **.**
<figure>

  <img src="https://www.syncfusion.com/blogs/wp-content/uploads/2023/10/Hiding-a-scenario-in-an-Excel-document-1.png" alt="Hiding a scenario in an Excel document" style="width:100%">

  <figcaption>Hiding a scenario in an Excel document</figcaption>

</figure>

## GitHub samples

You can download examples of the C# what-if analysis scenario manager for Excel from [this GitHub page](https://github.com/SyncfusionExamples/XlsIO-Examples/tree/master/What-If%20Analysis "What-If analysis scenario manager in Syncfusion Excel Library GitHub demos").

## Conclusion

Thanks for reading! In this blog, we’ve explored the new C# [what-if analysis scenario manager](https://help.syncfusion.com/file-formats/xlsio/what-if-analysis#scenario-manager "What-if Analysis Scenario Manager in Excel Library") feature in the [Syncfusion Excel Library](https://www.syncfusion.com/document-processing/excel-framework/net ".NET Excel Library"). This tool allows you to modify cell values and observe the resulting impact on worksheet formulas.

Take a moment to peruse the [documentation](https://help.syncfusion.com/file-formats/xlsio/overview "Getting started with .NET Excel Library"), where you’ll find other Excel Library options and features like [conditional formatting](https://help.syncfusion.com/file-formats/xlsio/working-with-conditional-formatting "Conditional Formatting in Excel"), [tables](https://help.syncfusion.com/file-formats/xlsio/working-with-excel-tables "Tables in Excel"), [pivot tables](https://help.syncfusion.com/file-formats/xlsio/working-with-pivot-tables "Pivot tables in Excel"), and [charts](https://help.syncfusion.com/file-formats/xlsio/working-with-charts "Charts in Excel"). Using this library, you can also export Excel data to  [PDF](https://help.syncfusion.com/file-formats/xlsio/excel-to-pdf-conversion "Convert Excel to PDF"), [image](https://help.syncfusion.com/file-formats/xlsio/worksheet-to-image-conversion "Worksheet to image conversion"), [data table](https://help.syncfusion.com/file-formats/xlsio/working-with-data#exporting-from-worksheet-to-data-table "Exporting from Worksheet to Data Table"), [CSV](https://www.syncfusion.com/kb/9092/convert-excel-file-to-csv-in-c-vb-net "Convert Excel file to CSV"), [TSV](https://www.syncfusion.com/kb/8556/does-xlsio-support-tsv-files "Convert Excel to TSV"), [HTML](https://help.syncfusion.com/file-formats/xlsio/working-with-excel-worksheet#save-worksheet-as-html "Save Worksheet as HTML"), [collections of objects](https://help.syncfusion.com/file-formats/xlsio/working-with-data#exporting-from-worksheet-to-clr-objects "Working with Excel data"), [ODS](https://help.syncfusion.com/file-formats/xlsio/excel-to-ods-conversion "Convert Excel to ODS"), [JSON](https://help.syncfusion.com/file-formats/xlsio/excel-to-json-conversion "Convert Excel to JSON"), and more file formats.

Are you already a Syncfusion user? You can download the product setup [here](https://www.syncfusion.com/account/downloads/studio/ "Essential Studio Licences and Downloads page"). If you’re not a Syncfusion user yet, you can download a free 30-day trial [here](https://www.syncfusion.com/downloads/fileformats/confirm "Get free evaluation of the Essential Studio products").

Please let us know in the comments section below if you have any questions about these features. You can also contact us through our [support forum](https://www.syncfusion.com/forums "Syncfusion Support Forum"), [support portal](https://support.syncfusion.com/ "Syncfusion Support Portal"), or [feedback portal](https://www.syncfusion.com/feedback/excel "Syncfusion Feedback Portal"). We are always happy to assist you!

## Related blogs

- [Easiest Ways to Highlight Data in Excel Using C#](https://blog.syncfusion.com/blogs/post/highlight-data-in-excel-using-csharp.aspx "Blog: Easiest Ways to Highlight Data in Excel Using C#")
- [6 Easy Ways to Export Data to Excel in C#](https://www.syncfusion.com/blogs/post/6-easy-ways-to-export-data-to-excel-in-c-sharp.aspx "Blog: 6 Easy Ways to Export Data to Excel in C#")
- [Export Data from Collection to Excel and Group It in C#](https://www.syncfusion.com/blogs/post/export-data-from-collection-to-excel-and-group-csharp.aspx "Blog: Export Data from Collection to Excel and Group It in C#")
- [Export Data to a Predefined Excel Template in C#](https://www.syncfusion.com/blogs/post/export-data-to-a-predefined-excel-template-in-c.aspx "Blog: Export Data to a Predefined Excel Template in C#")
- [Easy Steps to Export HTML Tables to an Excel Worksheet in C#](https://www.syncfusion.com/blogs/post/easy-steps-to-export-html-tables-to-an-excel-worksheet-in-c.aspx "Blog: Easy Steps to Export HTML Tables to an Excel Worksheet in C#")