In this tutorial you will learn that how to use data table in excel. Use Data table in excel for evaluation of the changing results of the cells values which use in formula. If you are designing a sheet for the company that gives loan to clients so then data table is very useful for you. For calculation of loans you use financial functions. PMT function is the financial function which returns the payments of specific period of time. Data table is also used for calculation of profit of organization or company e.g. products of company are sold and some are sold on old rate and some are sold on new rate. So the profit for the company of sold things be the changed due to variations in the price or rate of products so need in this situation a data table.

use data table in excel example:-

For using data table in excel sheet you need to create a loan payment spread sheet in excel. Sheet having name of person, loan amount, No of payments, Duration in month, interest, present value and monthly payment.

loan-sheet

Monthly Payments calculated by the formula as =PMT(interest/12,duration,-total amount).

use of data table in excel 2013

Now you have new values of loan amount, no of payments, interest and formula for monthly payment calculation and create new sheet like as following sheet which is give below.

new-format-of-loan-sheet

Now select the required data for inserting the data table.

selection-of-data-for-data-table

For inserting the data table visit data menu of microsoft excel.

data-menu-of-excel

Now visit the data tools panel as like following.

use data table in excel

Click on What If Analysis  and some options are appeared including data table.

data-table-option

After selection of data table option data table dialog box appeared.

data-table-dialog-box

Type B6 in column input cell which is interest on loan.

interest-rate-input-column-cell

After pressing the OK button new values are calculated as like following sheet of microsoft excel.

new-values-in-excel-sheet

Hence you have learned how to use data table in microsoft excel spread sheet for the variable values of cells.

 

 

 

 

Please follow and like us:

ali hiader

Leave a Reply

Your email address will not be published. Required fields are marked *