After applying What-If Analysis, Excel will often prevent the deletion of data from the source data or the output. In this article, we will explain how to delete What-If Analysis data in Excel using 2 simple and quick methods.
Suppose we have the dataset below of a company’s monthly Sales and Profit Margin and have applied What-If Analysis from the Scenario Manager to this data. Now we want to delete some of the data.
Method 1 – Deleting What If Analysis by Selecting the Whole Table
Sometimes after obtaining forecast data from What-If Analysis in Excel, we cannot delete data from the cells. Here’s how to do it:
Step 1:
In the image below, we have applied What-if Analysis to our dataset in columns E:F to obtain the increase in product quantity.
- Select a cell (for example F7) and press Delete.
A window will pop up saying “Can’t change part of a data table”.
- Click OK.
- Now select cells F6:F11 and press Delete.
The blank cells confirm that the What-If Analysis has been deleted.
Step 2:
To delete just some data, not the whole dataset:
- Select the range F6:F11 and press Ctrl+C to copy.
- Press Alt+E+S+V to paste as values.
- Now select any cell (for example F10) and click Delete.
The cell is deleted.
Read More: What If Analysis Data Table Not Working
Method 2 – Using the Scenario Manager to Delete What If Analysis
The Scenario manager feature is mostly used for financial calculations and forecasts in Excel, delivering analysis in Scenarios. Here, we will explain how to remove these Scenarios from our worksheet.
Steps:
Suppose that in our dataset we have already applied 3 Scenarios from the What-if Analysis feature in Excel.
- Go to Data > Forecast > What-if Analysis > Scenario Manager.
- In the Scenario Manager dialog box that opens, the 3 Scenarios for our data table are listed.
- To delete a Scenario, select it and click Delete.
- Press Close.
The corresponding What-if Analysis is deleted from our sheet.
Things to Remember
- Directly open the Scenario Manager by holding down the Alt key and pressing T+E .
Download Practice Workbook
Related Articles
- What-If Analysis in Excel with Example
- How to Do Sensitivity Analysis in Excel
- How to Get Sensitivity Report from Solver in Excel
- How to Perform Sensitivity Analysis for Capital Budgeting in Excel
- Sensitivity Analysis for NPV in Excel
- How to Build a Sensitivity Analysis Table in Excel
- How to Do IRR Sensitivity Analysis in Excel
<< Go Back to What-If Analysis in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!