Instead of entering formulas & variables individually, to compare results, you can set up an Excel Data Table, with one or two variables.

Data Tables are one of Microsoft Excel"s What If Analysis tools, along with Scenario Manager & Goal Seek..

Bạn đang xem: Data table in excel


Compare Formula Results

Calculate Monthly Payments

Compare Multiple Options

Create Data Table With 1 Variable

Create Data Table With 2 Variables


Speed Up Workbook Calculations

Clear a Data Table

Video: Create a Data Table

Download the Sample File

More Function Tutorials


Compare Formula Results

In some workbooks, you might want to kiểm tra different scenarios, và compare the results. For example, if you"re buying a car, which loan repayment term would fit your monthly budget?

4-year loan term at 5% interest 3-year loan term at 4.5% interest other terms và interest options??

In this tutorial, you"ll see how to:

-- mix up loan payment calculation

-- Compare multiple options in formula

-- Compare loan payments 1 variable

-- Compare loan payments with 2 variables

Calculate Monthly Payments with PMT

With the PMT function, you can calculate the monthly payment for a loan, based on 3 variables:

interest rate number of payment periods amount of the loan

These side-by-side PMT formulas show what your monthly payments will be, based on changing one variable in the formula -- the number of payments.

The formula in cell C6 divides the annual rate by 12, to lớn calculate the monthly interest rate:

=PMT(C2/12,C3,C4)

For a 48-month term, the monthly payment is $230.29, & for a 36-month term, it"s $299.71 per month.

*

Compare Multiple Options

What if you want to chạy thử multiple options for one of your formula variables, lượt thích the number of payments variable, shown above?

You don"t have lớn set up each option individually, like I did for those PMT formulas. Use one of Excel"s built-in analysis features -- an Excel data table. Use a Data Table

A data table makes it easy to chạy thử one or two variables from a formula, & see the differences, side-by-side.

For example, in the screen shot below, the data table show results for 2 variables - interest rate and number of payments.

In the sections below, you"ll see how to set up Excel data tables, make them calculate efficiently, and clear them, if necded.

-- mix up a 1 variable Data Table

-- phối up a 2 variable Data Table

-- tốc độ up workbook calculations

-- Clear a Data Table

Create Data Table With 1 Variable

In this example, you will build a one-variable data table that shows the monthly payments for loan terms ranging from 1 lớn 6 years. The number of payments will range from 12 to lớn 72.

NOTE: The đầu vào cells have to be on the same sheet as the data table.

The loan information is in cells C2:C4, with the number of payments in cell C3.

To mix up the data table:

In cells B8:B13, type the number of payments for loans terms of 1 lớn 6 years In cell C7, enter a PMT function, referring to the loan information cells: =PMT(C2/12,C3,C4) Select cells B7:C13 - the heading cells và the cells for the results On the Ribbon"s Data tab, in the Data Tools group, click What-If Analysis In the drop down menu, click Data Table.

*

The Data Table dialog box opens, where you can enter the input cell details

Click in the Column đầu vào cell box Then, in the Excel workbook, click on cell C3 That cell contains the variable for the number of payments. Finally, click the OK button, to lớn close the Data Table dialog box.

*

Select the cells with the monthly payments, và format as Currency. In the screen shot below, the format is Currency, with negative numbers bracketed & in red.

*

Click on one of the calculated monthly payment cells, & the formula bar shows that the cell contains a TABLE function, with cell C3 as the second argument. The curly brackets at the start and end of the formula indicate that this is an array formula

Create Data Table With 2 Variables

In this example, you will build a two-variable data table. With the 2 variables, it will show the monthly payments for loan terms ranging from 1 to 6 years, and interest rates from 2% khổng lồ 6%.

NOTE: The đầu vào cells have to lớn be on the same sheet as the data table.

The loan information is in cells C2:C4, with the interest rate in C2, and the number of payments in cell C3.

To mix up the data table:

In cells B8:B13, type the number of payments for loans terms of 1 khổng lồ 6 years In cells C7:G7, enter the interest rates between 2% & 6% In cell B7, enter a PMT function, referring to lớn the loan information cells: =PMT(C2/12,C3,C4) Select cells B7:G13 - the heading cells & the cells for the results On the Ribbon"s Data tab, in the Data Tools group, click What-If Analysis, & then click Data Table. Click in the Row input đầu vào cell box, and then click on cell C2, which contains the variable for the interest rate. Click in the Column input đầu vào cell box, and then click on cell C3, which contains the variable for the number of payments.

*

Click OK, khổng lồ close the dialog box. Select the cells with the monthly payments, và format as Currency. In the screen shot below, the format is Currency, with negative numbers bracketed & in red.

*

Click on one of the calculated monthly payment cells, and the formula bar shows that the cell contains a TABLE function, with cell C2 as the first argument, and C3 as the second argument. The curly brackets at the start and end of the formula indicate that this is an array function.

Speed Up Workbook Calculations

To prevent the data table from slowing down your Excel workbook calculations, you can change the calculation option for your workbook.

Most Excel workbooks are set for Automatic calculation. With that setting, Excel automatically recalculates all of the mở cửa workbooks:

every time there is a change, in any of the mở cửa workbooks any time you xuất hiện another workbook Change Calculation Setting

To prevent Excel from automatically doing all the Data Table calculations each time, follow these steps:

On the Excel Ribbon, click the Formulas tab At the far right, click the Calculation Options command In the drop down list of options, click Automatic Except for Data Tables

After you change this setting, the Data Table calculations will not occur when a recalculation is done on the entire workbook.

*

Recalculate the Data Table

If you change the calculation mode lớn Automatic Except for Data Tables, the Data Tables will not update automatically.

To manually recalculate your data table, follow these steps:

Select the main formula cell in the Data Table Press the F9 key on the keyboard, to lớn force a calculation Use Excel VBA

You can also use macros khổng lồ get faster calculation with Data Tables. See this post by Excel calculation expert, Charles Williams: Excel What-If Data Tables: Faster calculation with VBA

Clear a Data Table

Because the data table values are in an array, you cannot edit or clear individual cells. If you try khổng lồ change one cell, you will see an error message - "Cannot change part of a data table." If you want to lớn remove the entire table, or the resulting values, follow the steps below.

Xem thêm: Tập Làm Văn Bảo Vệ Môi Trường (42 Mẫu)

To remove the data table from the worksheet:

Select all the cells in the data table, including the heading On the keyboard, press the Delete key

To clear out the resulting values only:

Select all resulting values in the data table. On the keyboard, press the Delete key

Video: Create a Data Table

In this video, Mynda Treacy shows how to lớn set up Data Tables in Excel, using multiple variables.

See how much a savings tài khoản will grow, with different amounts deposited monthly.

In the first example, there is a 1-variable Data Table, for different amounts. In the second example, the Data Table uses 2 variables - amount & interest rate. In the third example, a 2-variable Data Table calculates the breakeven point for a pizza shop. Conditional formatting highlights the data, so it"s easier lớn read

Download the Sample File

Click here to tải về the Data Tables sample file. The zipped Excel workbook tệp tin is zipped, & in xlsx format. There are no macros in the workbook