top of page
Search

Data Tables in Excel



A data table is used to see how altering one variable in a formula affects the result of that formula e.g. calculating the amount of interest on a savings account at different interest rates. The monthly repayment amount stays the same but is calculated at different interest rates. Rather than typing in several formulas for each interest rate, a data table can be used to quickly calculate information. This is a more efficient way of calculating variable amounts using formulas.



  1. 1. Open the “Repayments on Loan” workbook

  2. 2. Highlight cells A9:A19

  3. 3. On the Home tab select Series


  1. 4. For Series In select Columns

  2. 5. For Step Value enter 0.005

  3. 6. Click OK

  4. 7. This will create a variable interest rate that increases by 0.5% for each calculation

  5. 8. In cell B9 type in =B6

  6. 9. Highlight cells A8:B19

  7. 10. On the Data tab select What-If Analysis and choose Data Table


  1. 11. Highlight cell D4 for the Column Input Cell

  2. 12. This uses the standard interest rate on the loan to calculate the variable interest rate in the data table

  3. 13. Click OK

  4. 14. Change the cell range B9:B19 to currency with two decimal places

  5. 15. The Data Table will calculate the amount monthly repayments will be based on variable interest rates

  6. 16. ave the workbook as “Loan”


Two Input Data Table

A two-input data table is used to see how changing two variables in a formula affects the result of that formula e.g. calculating the amount of interest on a savings account at variable interest rates at different periods of time. The interest rate variable changes and the length of the savings period also changes. This produces a different amount depending on changing interest rates and different periods of time. A two-input data table uses two changing variables compared with one changing variable in a one input data table.


  1. 1. Open the workbook “Loan”

  2. 2. Delete the contents of cells in the range B9:B18

  3. 3. Cut and paste the range B8:G8 to A8:F8

  4. 4. The numbers across the top represent the term of the loan in months

  5. 5. Highlight cells A8:F19

  6. 6. On the Data tab click on What-If Analysis and choose Data Table

7

  1. 7. Select cell B3 for the Row Input Cell

  2. 8. This includes the standard term of the loan to be included in the data table containing a variable loan term

  3. 9. Select cell B4 for the Column Input Cell

  4. 10. This included the standard interest rate of the loan to be included in the data table containing a variable loan term

  5. 11. Click OK

  6. 12. Apply a currency format to the cell range B9:F19

  7. 13. A Data Table will be created displaying the monthly repayment amount depending on the variable interest rate and the variable term of the loan

  8. 14. Save the workbook and close it


To learn more advanced Excel features, click on the book cover image below:



 
 
 

Comments

bottom of page