Excel Tutorial

Excel Tutorial Shortcut Keys in Excel Formatting in Excel Notes in Excel Formats in Excel Cells and Ranges in Excel Excel Function and Formulas Conditional Formatting in Excel Data Validation in Excel Charts in Excel Excel Ribbon Toolbar Basics of Excel Spell Check in Excel Data Analysis in Excel AutoFill in Excel Goal Seek in Excel Solver in Excel Pivots Table in Excel Go-To Special function in Excel Blank cells in Excel Count Cells with Text in Excel Date and Time in Excel-VBA Dependent Drop-down List in Excel Operators in Excel Dependent Combo box in Excel VBA Error Bar in Microsoft Excel Excel Axes Excel File using Password Excel Unique Values Frequency Distribution in Excel Gauge Chart in Excel Histogram in Excel Sum Every Nth Row in Microsoft Excel SumIF Formula in Microsoft Excel Multiplication in Excel Unique Values in Excel Trendline in Excel Excel Themes Copying formula in Excel Check Marks in Excel Calculating the Last Day of the Month in Excel Calculating Age in Excel Insert Row in Excel

Functions

Excel MAX() Function Excel INT() Function Excel MOD() Function Excel ROUND() Function Excel ROUNDUP() Function Excel AVERAGE() Function Excel COUNT() Function Excel COUNTA() Function Excel COUNTBLANK() Function Excel MIN() Function Excel EDATE() Function Excel EOMONTH() Function Excel HOUR() Function Excel MINUTE() Function Excel SECOND() Function Excel TIME() Function Excel WORKDAY() Function Excel WORKDAY.INTL() Function Excel DAYS() Function Excel WEEKNUM() Function Excel WEEKDAY() Function Excel SMALL() Function Excel LARGE() Function Excel LEFT() Function Excel RIGHT() Function Excel MID() Function Excel FIND() Function Excel SEARCH() Function Excel EXACT() Function Excel SUBSTITUTE() Function Excel TEXT() Function Excel VALUE() Function Excel AND() Function Excel OR() Function Excel IFERROR() Function Excel IF() Function Excel Nested IF’s Function Excel IFNA() Function Excel COUNTIFS() Function Excel VLOOKUP() Function Excel HLOOKUP() Function Excel INDEX() Function Excel MATCH() Function Excel OFFSET () Function Averageif Function in Excel

How To

How to import Microsoft Access data into the Microsoft Excel How to use TODAY function in Excel How to Alphabetize in Excel How to remove duplicate values from excel How to lock cells in Excel How to create drop down in excel How to Delete Row in Microsoft Excel How to Highlight Duplicates Words in the Microsoft Excel How to print titles in Excel How to make use of the Wildcard in Excel How to Make Use of the F-Test in Excel How to make use of the Excel Autofit in Excel How to generate random numbers in Excel How to apply Advanced Filter in Excel How to use Index and Match in Excel

Misc

Absolute Value in Excel Adding Column in Excel Converting Units in Excel Count Characters in Excel Custom Sort Order in Excel Decimals in Excel Division in Excel Locate Maximum Values in Excel Nearest Multiple in Excel Paste Options in Excel Quarter Dates in Excel Row Difference in Excel Separate Strings in Excel Reverse List in Excel Array Formula in Excel What if Analysis Data Table in Excel Excel Shortcut Keys What is a spreadsheet in Excel?

What if Analysis Data Table in Excel

What is What-If Analysis?

Suppose you have a cell A15 which is formula driven i.e. has a formula and the formula is based on values in different cells (A1, A2, A3, A4), what-if analysis helps us in understanding the changes to A10 when values in cells A1, A2, A3 or A4 are altered.

Let’s say person ABC has bought a car which is worth $40,000. Now ABC gave 19% of this amount as a down-payment and agreed to 48 months’ loan term at an interest rate of 5%. These 4 variables (Purchase Price, Down payment, Loan Term and Interest Rate) will have direct impact on the following variables (dependent variables) –

  • Loan Amount – If the customer paid 19% as down-payment, it implies that he took loan for remaining 90%
  • Monthly Payments – Monthly EMI costs based on loan taken, interest rates and loan term
  • Total Interest – A monthly payment is a combination of interest payments and the actual loan amount – this variable tells us how much interest rate we are paying on the loan amount
  • Total Payments – Interest rate plus loan amount

Now you can use formulas to calculate last 4 variables – make sure to give cell location rather than individual values. So, for example, Purchase Price is in cell A6 and Down Payment is in A7, to calculate ‘Loan Amount’ you can use the formula A6*(1-A7) rather than 80000*(0.8).

Similarly, you can calculate rest of the fields. Once this is done, using the following steps, you can incorporate ‘What-if Analysis’ in your work –

  • Go to Data Tab and click on What-if Analysis and then select Scenario Manager
  • Click on Add and a new pop-up will appear – put a Scenario name such that you can easily understand what it stands for
  • Next is changing cells i.e. which cells you want to change to see the impact on ‘Loan Amount’, ‘Monthly Payments’, ‘Total Interest’ and ‘Total Payments’ – let’s say we want to see the impact of changes to down-payment on these 4 variables
  • I have put Scenario Name as DP 20% which is current scenario
  • In ‘Add Scenario’ – I select the cell consisting of the value corresponding to Down Payment
  • And click on OK – a prompt will appear in which excel asks for the value you want to see – currently it is 0.2 – click on OK
  • Add 2 more scenarios DP 30% and DP 40% following the same process and corresponding values will be 0.3 and 0.4 and then click on OK
  • Now you can see 3 different scenarios – you can select anyone and see the difference it leads to the 4 values ‘Loan Amount’, ‘Monthly Payments’, ‘Total Interest’ and ‘Total Payments’

Now what if you want to see the impact of these changes to Down Payments in one table itself side-by-side? For this, we will introduce a new functionality ‘Data Tables’.

What is a Data Table?

A data-table is a range of cells which give us values of formula driven cells (dependent variables) when values in input cells are changed. In our example, input cells are Purchase Price, Down payment, Loan Term and Interest Rate.

One Variable Data Table

A data table which gives different values of dependent cells corresponding to different values of 1 input variable. In our example, let’s say, we want to look at the changes to dependent variables when we use different values of down-payment. Do note that a data table is different from a normal table -

  • To create a One Variable Data Table, write different values of down-payment (input variable) in a column
  • Use equal to operator to get the values of dependent variables as a header for this new table as shown in the diagram below
  • Once done, select the entire table and go to ‘Data’ tab and then ‘What-if Analysis’ and then select ‘Data Table’
  • A new pop-up will appear – since the different values of down-payment are in a column, select ‘Column Input Cell’ and give the location of the Down-Payment value in the original table – let the ‘Row Input Cell’ remain empty and click on OK
  • All the values will be populated in the table and now you can easily compare how the 4 dependent variables will change with changing down payment value

Two Variable Data Table

Similar to One Variable Data Table, just that in here we can see the impact on only 1 dependent variable (instead of many) when we make changes to 2 different input variables at the same time - 

  • Put different values of input variable in a row and for the second input variable in a column
  • Use the equal to variable to get value of dependent variable on top corner of the table

Select the table and click on ‘Data Table’ and for Row, select the value corresponding to input variable in the first table and for column select the value corresponding to second input variable and click on OK