What-If Analysis Scenarios Data Tables

What-If Analysis

What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. The following example helps you master what-if analysis quickly and easily.

Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20.

If you sell 60% for the highest price, cell D10 calculates a total profit of 60 * $50 + 40 * $20 = $3800.

Create Different Scenarios

But what if you sell 70% for the highest price? And what if you sell 80% for the highest price? Or 90%, or even 100%? Each different percentage is a different scenario. You can use the Scenario Manager to create these scenarios.

Note: You can simply type in a different percentage into cell C4 to see the corresponding result of a scenario in cell D10. However, what-if analysis enables you to easily compare the results of different scenarios. Read on.

1. On the Data tab, click What-If Analysis and select Scenario Manager from the list.

The Scenario Manager dialog box appears.

2. Add a scenario by clicking on Add.

3. Type a name (60% highest), select cell C4 (% sold for the highest price) for the Changing cells and click on OK.

4. Enter the corresponding value 0.6 and click on OK again.

5. Next, add 4 other scenarios (70%, 80%, 90% and 100%).

Finally, your Scenario Manager should be consistent with the picture below:

Note: to see the result of a scenario, select the scenario and click on the Show button. Excel will change the value of cell C4 accordingly for you to see the corresponding result on the sheet.

Scenario Summary

To easily compare the results of these scenarios, execute the following steps.

1. Click the Summary button in the Scenario Manager.

2. Next, select cell D10 (total profit) for the result cell and click on OK.

Result:

Conclusion: if you sell 70% for the highest price, you obtain a total profit of $4100, if you sell 80% for the highest price, you obtain a total profit of $4400, etc. That's how easy what-if analysis in Excel can be.

Goal Seek

What if you want to know how many books you need to sell for the highest price, to obtain a total profit of exactly $4700? You can use Excel's Goal Seek feature to find the answer.

1. On the Data tab, click What-If Analysis, Goal Seek.

The Goal Seek dialog box appears.

2. Select cell D10.

3. Click in the 'To value' box and type 4700.

4. Click in the 'By changing cell' box and select cell C4.

5. Click OK.

Result. You need to sell 90% of the books for the highest price to obtain a total profit of exactly $4700.

Data Tables

One Variable Data Table | Two Variable Data Table

Instead of creating different scenarios, you can create a data table to quickly try out different values for formulas. You can create a one variable data table or a two variable data table.

Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20. If you sell 60% for the highest price, cell D10 below calculates a total profit of 60 * $50 + 40 * $20 = $3800.

One Variable Data Table

To create a one variable data table, execute the following steps.

1. Select cell B12 and type =D10 (refer to the total profit cell).

2. Type the different percentages in column A.

3. Select the range A12:B17.

We are going to calculate the total profit if you sell 60% for the highest price, 70% for the highest price, etc.

4. On the Data tab, click What-If Analysis and select Data Table from the list.

5. Click in the 'Column input cell' box (the percentages are in a column) and select cell C4.

We select cell C4 because the percentages refer to cell C4 (% sold for the highest price). Together with the formula in cell B12, Excel now knows that it should replace cell C4 with 60% to calculate the total profit, replace cell C4 with 70% to calculate the total profit, etc.

Note: this is a one variable data table so we leave the Row input cell blank.

6. Click OK.

Result.

Conclusion: if you sell 60% for the highest price, you obtain a total profit of $3800, if you sell 70% for the highest price, you obtain a total profit of $4100, etc.

Note: the formula bar indicates that the cells contain an array formula. Therefore, you cannot delete a single result. To delete the results, select the range B13:B17 and press Delete.

Two Variable Data Table

To create a two variable data table, execute the following steps.

1. Select cell A12 and type =D10 (refer to the total profit cell).

2. Type the different unit profits (highest price) in row 12.

3. Type the different percentages in column A.

4. Select the range A12:D17.

We are going to calculate the total profit for the different combinations of 'unit profit (highest price)' and '% sold for the highest price'.

5. On the Data tab, click What-If Analysis and select Data Table from the list.

6. Click in the 'Row input cell' box (the unit profits are in a row) and select cell D7.

7. Click in the 'Column input cell' box (the percentages are in a column) and select cell C4.

We select cell D7 because the unit profits refer to cell D7. We select cell C4 because the percentages refer to cell C4. Together with the formula in cell A12, Excel now knows that it should replace cell D7 with $50 and cell C4 with 60% to calculate the total profit, replace cell D7 with $50 and cell C4 with 70% to calculate the total profit, etc.

8. Click OK.

Result.

Conclusion: if you sell 60% for the highest price, at a unit profit of $50, you obtain a total profit of $3800, if you sell 80% for the highest price, at a unit profit of $60, you obtain a total profit of $5200, etc.

Note: the formula bar indicates that the cells contain an array formula. Therefore, you cannot delete a single result. To delete the results, select the range B13:D17 and press Delete.

Quadratic Equation

A quadratic equation is of the form ax2 + bx + c = 0 where a ≠ 0. A quadratic equation can be solved by using the quadratic formula. You can also use Excel's Goal Seek feature to solve a quadratic equation.

1. For example, we have the formula y = 3x2 - 12x + 9.5. It's easy to calculate y for any given x. For x = 1, y = 0.5

2. For x = 2, y = -2.5

3. But what if we want to know x for any given y? For example, y = 24.5. We need to solve 3x2 - 12x + 9.5 = 24.5. We can solve the quadratic equation 3x2 - 12x + 9.5 - 24.5 = 0 by using the quadratic formula.

3x2 - 12x -15 = 0
a = 3, b = -12, c = -15
D = b2- 4ac = (-12)2 - 4 * 3 * -15 = 144 + 180 = 324

x =	-b + √D	or	x =	-b - √D
	2a			2a

x =	12 + √324	or	x =	12 - √324
	6			6

x =	12 + 18	or	x =	12 - 18
	6			6

x =	5	or	x =	-1

4. You can use Excel's Goal Seek feature to obtain the exact same result. On the Data tab, in the Data Tools group, click What-If Analysis, Goal Seek.

The Goal Seek dialog box appears.

5. Select cell B2.

6. Click in the 'To value' box and type 24.5

7. Click in the 'By changing cell' box and select cell A2.

8. Click OK.

Result.

Note: Excel returns the solution x = 5. Excel finds the other solution if you start with a x-value closer to x = -1. For example, enter the value 0 into cell A2 and repeat steps 4 to 8. To find the roots, set y = 0 and solve the quadratic equation 3x2 - 12x + 9.5 = 0. In this case, set 'To value' to 0.

Protect

Protect

Encrypt an Excel file with a password so that it requires a password to open it.

1. Open a workbook.

2. On the green File tab, click Save As.

3. Click on the Tools button and click General Options.

4. In the Password to open box, enter a password and click OK.

5. Reenter the password and click OK.

Note: this feature also encrypts your Excel file. If you lose or forget the password, it cannot be recovered.

6. Enter a file name and click Save.

It requires a password to open this Excel file now. The password for the downloadable Excel file is "easy".

Protect Workbook

Structure | Windows

This example teaches you how to protect the workbook structure and windows in Excel.

Structure

If you protect the workbook structure, users cannot insert, delete, rename, move, copy, hide or unhide worksheets anymore.

1. Open a workbook.

2. On the Review tab, click Protect Workbook.

3. Check Structure, enter a password and click OK.

4. Reenter the password and click on OK.

Users cannot insert, delete, rename, move, copy, hide or unhide worksheets anymore.

Windows

If you protect the workbook windows, users cannot move, change the size and close windows anymore.

1. Open a workbook.

2. On the Review tab, click Protect Workbook.

3. Check Windows, enter a password and click OK.

4. Reenter the password and click on OK.

Users cannot move, change the size and close windows anymore.

To unprotect the workbook, click Protect Workbook and enter the password. The password for the downloadable Excel file is "easy". The structure and window of this workbook are protected.

Protect Sheet

When you share an Excel file with other users, you may want to protect a worksheet to help prevent it from being changed.

1. Right click a worksheet tab.

2. Click Protect Sheet.

3. Enter a password.

4. Check the actions you allow the users of your worksheet to perform.

5. Click OK.

Note: if you don't check any action, users can only view the Excel file!

6. Confirm the password and click OK.

Your worksheet is protected now. To unprotect a worksheet, right click on the worksheet tab and click Unprotect Sheet. The password for the downloadable Excel file is "easy".

Lock Cells

You can lock cells in Excel if you want to protect cells from being edited. In this example, we will lock cell A2.

Before you start: by default, all cells are locked. However, locking cells has no effect until you protect the worksheet. So when you protect a worksheet, all your cells (=worksheet) will be locked. As a result, if you want to lock a cell, you have to unlock all cells first, lock a cell, and then protect the sheet.

1. Select all cells.

2. Right click, and then click Format Cells.

3. On the Protection tab, uncheck the Locked check box and click OK.

4. Right click cell A2, and then click Format Cells.

5. On the Protection tab, check the Locked check box and click OK.

Note: if you also check the Hidden check box, users cannot see the formula in the formula bar when they select cell A2.

6. Protect the sheet.

Cell A2 is locked now. To edit cell A2, you have to unprotect the sheet. The password for the downloadable Excel file is "easy". You can still edit all other cells.

Read-only Workbook

This example shows you how to make your workbook read-only. Users can still save changes by creating a new copy of the workbook. For a better protection, protect a sheet.

1. Open a workbook.

2. On the green File tab, click Save As.

3. Click on the Tools button and click General Options.

4. In the Password to modify box, enter a password and click OK.

Note: If you only want to recommend users to open the Excel file as read-only, without protecting it, don't enter a password and check Read-only recommended.

5. Reenter the password and click on OK.

Note: this feature does not encrypt your Excel file. Malicious users can edit the file and remove the password.

6. Enter a file name and click Save.

Your workbook is read-only now. The password for the downloadable Excel file is "easy".

Mark as Final

Mark a workbook as final in Excel to indicate that this is the final version of your workbook. Only use this feature to discourage editing. Users can still edit the workbook.

1. Open a workbook.

2. On the green File tab, click Info, Protect Workbook, Mark as Final.

3. Click OK.

Result.

Note: users can still edit the workbook by clicking on Edit Anyway. Also notice the Marked as Final icon in the status bar.

Conditional Formatting

Highlight Cells Rules | Clear Rules | Top/Bottom Rules

Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.

Highlight Cells Rules

To highlight cells that are greater than a value, execute the following steps.

1. Select the range A1:A10.

2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Greater Than...

3. Enter the value 80 and select a formatting style.

4. Click OK.

Result. Excel highlights the cells that are greater than 80.

5. Change the value of cell A1 to 81.

Result. Excel changes the format of cell A1 automatically.

Note: you can also highlight cells that are less than a value, between a low and high value, etc.

Clear Rules

To clear a conditional formatting rule, execute the following steps.

1. Select the range A1:A10.

2. On the Home tab, click Conditional Formatting, Clear Rules, Clear Rules from Selected Cells.

Top/Bottom Rules

To highlight cells that are above the average of the cells, execute the following steps.

1. Select the range A1:A10.

2. On the Home tab, click Conditional Formatting, Top/Bottom Rules, Above Average...

3. Select a formatting style.

4. Click OK.

Result. Excel calculates the average (42.5) and formats the cells that are above this average.

Note: you can also highlight the top 10 items, the top 10 %, etc. The sky is the limit!

Manage Rules

To view all conditional formatting rules in a workbook, use the Conditional Formatting Rules Manager. You can also use this screen to create, edit and delete rules.

1. Select cell A1.

2. On the Home tab, click Conditional Formatting, Manage Rules...

The Conditional Formatting Rules Manager appears.

Note: because we selected cell A1, Excel shows the rule applied to the range A1:A10.

3. From the drop-down list, change Current Selection to This Worksheet, to view all conditional formatting rules in this worksheet.

Note: click New Rule, Edit Rule and Delete Rule to create, edit and delete rules.

Data Bars

Data bars in Excel 2010 or later make it very easy to visualize values in a range of cells. A longer bar represents a higher value.

To add data bars, execute the following steps.

1. Select a range.

2. On the Home tab, click Conditional Formatting, Data Bars, and click a subtype.

Result:

Explanation: by default, the cell that holds the minimum value (0 if there are no negative values) has no data bar and the cell that holds the maximum value (95) has a data bar that fills the entire cell. All other cells are filled proportionally.

3. Change the values.

Result. Excel updates the data bars automatically. Read on to further customize these data bars.

4. Select the range A1:A10.

5. On the Home tab, click Conditional Formatting, Manage Rules.

6. Click Edit rule.

Excel launches the Edit Formatting Rule dialog box. Here you can further customize your data bars (Show Bar Only, Minimum and Maximum, Bar Appearance, Negative Value and Axis, Bar Direction, etc).

Note: to directly launch this dialog box for new rules, at step 2, click More Rules.

7. Select Number from the Minimum drop-down list and enter the value 100. Select Number from the Maximum drop-down list and enter the value 150.

8. Click OK twice.

Result.

Explanation: the cell that holds the value 100 (if any) has no data bar and the cell that holds the value 150 (if any) has a data bar that fills the entire cell. All other cells are filled proportionally.

Color Scales in Excel 2010 or later make it very easy to visualize values in a range of cells. The shade of the color represents the value in the cell.

To add a color scale, execute the following steps.

1. Select a range.

2. On the Home tab, click Conditional Formatting, Color Scales, and click a subtype.

Result:

Explanation: by default, for 3-Color scales, Excel calculates the 50th percentile (also known as median, middle value or midpoint). The cell that holds the minimum value (9) is colored red. The cell that holds the median (36) is colored yellow, and the cell that holds the maximum value (80) is colored green. All other cells are colored proportionally.

Read on to further customize this color scale.

3. Select the range A1:A7.

4. On the Home tab, click Conditional Formatting, Manage Rules.

5. Click Edit rule.

Excel launches the Edit Formatting Rule dialog box. Here you can further customize your color scale (Format Style, Minimum, Midpoint and Maximum, Color, etc).

Note: to directly launch this dialog box for new rules, at step 2, click More Rules.

6. Select 2-Color Scale from the Format Style drop-down list and select white and blue.

7. Click OK twice.

Result.

Icon Sets

Icon Sets in Excel 2010 or later make it very easy to visualize values in a range of cells. Each icon represents a range of values.

To add an icon set, execute the following steps.

1. Select a range.

2. On the Home tab, click Conditional Formatting, Icon Sets, and click a subtype.

Result:

Explanation: by default, for 3 icons, Excel calculates the 67th percent and 33th percent. 67th percent = min + 0.67 * (max-min) = 2 + 0.67 * (95-2) = 64.31. 33th percent = min + 0.33 * (max-min) = 2 + 0.33 * (95-2) = 32.69.  A green arrow will show for values equal to or greater than 64.31. A yellow arrow will show for values less than 64.31 and equal to or greater than 32.69. A red arrow will show for values less than 32.69.

3. Change the values.

Result. Excel updates the icon set automatically. Read on to further customize this icon set.

4. Select the range A1:A10.

5. On the Home tab, click Conditional Formatting, Manage Rules.

6. Click Edit rule.

Excel launches the Edit Formatting Rule dialog box. Here you can further customize your icon set (Icon Style, Reverse Icon Order, Show Icon Only, Icon, Value, Type, etc).

Note: to directly launch this dialog box for new rules, at step 2, click More Rules.

7. Select 3 symbols (Uncircled) from the Icon Style drop-down list. Select No Cell Icon from the second Icon drop-down list. Change the Types to Number and change the Values to 100 and 0. Select the greater than symbol (>) next to the value 0.

8. Click OK twice.

Result.

If the Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales and Icon Sets are not sufficient, you can create a new rule. For example, highlight the codes below that occur more than once in the range A2:A10and have a score greater than 100.

1. Select the range A2:A10.

2. On the Home tab, click Conditional Formatting, New Rule...

Note: Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales and Icon Sets above are shortcuts. They can also be found under New Rule.

3. Click on 'Use a formula to determine which cells to format'.

4. Enter the formula =AND(COUNTIF($A$2:$A$10,A2)>1,B2>100)

5. Select a formatting style and click OK.

Result. Excel formats cell A5 because code A occurs more than once in the range A2:A10 and the value 150 in cell B5 is greater than 100.

Explanation: COUNTIF($A$2:$A$10,A2) counts the number of codes in the range A2:A10 that are equal to the code in cell A2. If COUNTIF($A$2:$A$10,A2) > 1 and B2 > 100, Excel formats cell A2. Because we selected the range A2:A10 before we clicked on Conditional Formatting, Excel automatically copies the formula to the other cells. Thus, cell A3 contains the formula =AND(COUNTIF($A$2:$A$10,A3)>1,B3>100), cell A4 =AND(COUNTIF($A$2:$A$10,A4)>1,B4>100), etc. Notice how we created an absolute reference ($A$2:$A$10) to fix this reference.

This example teaches you how to find duplicates (or triplicates) in Excel. Go here to remove duplicates.

1. Select the range A1:C10.

2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Duplicate Values...

3. Select a formatting style and click OK.

Result. Excel highlights the duplicate names.

Note: select Unique from the first drop-down list to highlight the unique names.

As you can see, Excel highlights duplicates (Juliet, Delta), triplicates (Sierra), quadruplicates (if we have any), etc. Execute the following steps to highlight triplicates only.

4. First, clear the previous conditional formatting rule.

5. Select the range A1:C10.

6. On the Home tab, click Conditional Formatting, New Rule...

7. Select "Use a formula to determine which cells to format".

8. Enter the formula =COUNTIF($A$1:$C$10,A1)=3

9. Select a formatting style and click OK.

Result. Excel highlights the triplicate names.

Explanation: =COUNTIF($A$1:$C$10,A1) counts the number of names in the range A1:C10 that are equal to the name in cell A1. If COUNTIF($A$1:$C$10,A1) = 3, Excel formats the cell. Because we selected the range A1:C10 before we clicked on Conditional Formatting, Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =COUNTIF($A$1:$C$10,A2)=3, cell A3 =COUNTIF($A$1:$C$10,A3)=3, etc. Notice how we created an absolute reference ($A$1:$C$10) to fix this reference.

Shade Alternate Rows

This example shows you how to use conditional formatting to shade alternate rows. Shading every other row in a range makes it easier to read your data.

1. Select a range.

2. On the Home tab, click Conditional Formatting, New Rule...

3. Click on 'Use a formula to determine which cells to format'.

4. Enter the formula =MOD(ROW(),2)

5. Select a formatting style and click OK.

Result.

Explanation: the MOD function gives the remainder of a division. The ROW() function returns the row number. For example, for the seventh row, MOD(7,2) equals 1. 7 is divided by 2 (3 times) to give a remainder of 1. For the eight row, MOD(8,2) equals 0. 8 is divided by 2 (exactly 4 times) to give a remainder of 0. As a result, all odd rows return 1 (TRUE) and will be shaded.

Compare Two Lists

This example describes how to compare two lists using conditional formatting. For example, you may have two lists of NFL teams.

To highlight the teams in the first list that are not in the second list, execute the following steps.

1. First, select the range A1:A18 and name it firstList, select the range B1:B20 and name it secondList.

2. Next, select the range A1:A18.

3. On the Home tab, click Conditional Formatting, New Rule...

4. Select "Use a formula to determine which cells to format".

5. Enter the formula =COUNTIF(secondList,A1)=0

6. Select a formatting style and click OK.

Result. Miami Dolphins and Tennessee Titans are not in the second list.

Explanation: =COUNTIF(secondList,A1) counts the number of teams in secondList that are equal to the team in cell A1. If COUNTIF(secondList,A1) = 0, the team in cell A1 is not in the second list. As a result, Excel fills the cell with a blue background color. Because we selected the range A1:A18 before we clicked on Conditional Formatting, Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =COUNTIF(secondList,A2)=0, cell A3 =COUNTIF(secondList,A3)=0, etc.

7. To highlight the teams in the second list that are not in the first list, select the range B1:B20, create a new rule using the formula =COUNTIF(firstList,B1)=0, and set the format to orange fill.

Result. Denver Broncos, Arizona Cardinals, Minnesota Vikings and Pittsburgh Steelers are not in the first list.

Conflicting Rules

Sometimes multiple conditional formatting rules in Excel conflict. A higher rule always wins. This example illustrates two different results.

1. The value 95 is higher than 80 but is also the highest value (Top 1). The formats (yellow fill vs green fill and yellow text color vs green text color) conflict. A higher rule always wins. As a result, the value 95 is colored yellow.

Result:

2. Move the second rule up. The value 95 is the highest value (Top 1) but is also higher than 80. The formats (green fill vs yellow fill and green text color vs yellow text color) conflict. A higher rule always wins. As a result, the value 95 is colored green.

Result:

Note: only use the Stop If True check boxes for backwards compatibility with earlier versions of Microsoft Excel.

Checklist

This example teaches you how to create a checklist in Excel. First, turn on the Developer tab. Next, you can create a checklist.

To create this checklist, execute the following steps.

1. On the Developer tab, click Insert and then click Check Box in the Form Controls section.

2. Draw a check box in cell B2.

3. To remove "Check Box 1", right click the check box, click the text and delete it.

4. Select cell B2.

5. Click on the lower right corner of cell B2 and drag it down to cell B11.

6. Right click the first check box and click Format Control.

7. Link the check box to the cell next to it (cell C2).

8. Repeat step 7 for the other check boxes.

9. The count the number of items packed, insert a COUNTIF function into cell B14.

10. Hide column C.

11. Insert an IF function into cell B16.

Result:

Note: we created a conditional formatting rule to change the background color of cell B16 depending on the cell's value.