Data Tables in Excel

<div dir="ltr" style="text-align: left;" trbidi="on"> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> In Excel, a Data Table is a way to see different results by altering an input cell in your formula. As an example, we're going to alter the interest rate, and see how much a £10,000 loan would cost each month. The interest rate will be our input cell. By asking Excel to alter this input, we can quickly see the different monthly payments. Want to know how much we'd pay back each month if the interest was 24 percent per year. But other banks may be offering better deals. So we'll ask Excel to calculate how much we'd pay each month if the interest rate was 22 percent a year, 20 percent a year, and 18 percent a year.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The formula we need is the Payment one you met in a previous section - PMT( ). Here it is again:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>PMT(</b>rate, nper, pv, fv, type<b>)</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We only need the first three arguments. So for us, it's just this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>PMT(</b>rate, nper, pv<b>)</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>Rate</b> means the interest rate. The second argument, <b>nper</b>, is how many months you've got to pay the loan back. The third argument, <b>pv</b>, is how much you want to borrow.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Let's make a start then. On a new spreadsheet, set up the following labels:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Create this Excel 2007 Spreadsheet" height="140" src="http://www.homeandlearn.co.uk/excel2007/images/2Tables.gif" width="406" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So we'll put our starting interest rate in cell B3 (<b>rate</b>), our loan length in cell B4 (<b>nper</b>), and our loan amount in cell B5 (<b>pv</b>).</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Enter the following in cells B3 to B5:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Payment Terms Spreadsheet" height="139" src="http://www.homeandlearn.co.uk/excel2007/images/2Tables2.gif" width="407" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So you need to enter 24.00% in cell B3, 60 in cell B4, and £10,000 in cell B5.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We'll enter our formula now. Click inside cell D2 and enter the following:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>=PMT(</b>B3 / 12, B4, -B5<b>)</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Cell B3 is the interest rate. But this is for the entire year. In the formula, we're diving whatever is in cell B3 by 12. This will get us a monthly interest rate. B4 in the formula is the number of months, which is 60 for us. B5 has a minus sign before it. It's a minus figure because it's a debt.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> When you press the enter key on your keyboard, Excel should give you an answer of £287.68.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Now that we have our function in place, we can create an Excel Data Table. First, though, we need to tell Excel about those other interest rates. It will use these to work out the new monthly payments. Remember, Excel is recalculating the PMT function. So it needs some new values to calculate with.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So enter some new values in cells C3, C4, and C5. Enter the same ones as in the image below:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The new Interest Rates" border="1" height="139" src="http://www.homeandlearn.co.uk/excel2007/images/2Tables3.gif" width="426" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We have put the PMT function in cell D2 for a reason. This is one Row up, and one Column to the right of our first new interest rate of 22%. The new monthly payments are going to go in cells D3 to D5. Excel needs the table setting out this way.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So that Excel can work out the new totals, you have to highlight both the new values and the Function you're using.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So highlight the cells C2 to D5. Your spreadsheet should look like this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Highlight the cells C2 to D5" border="1" height="140" src="http://www.homeandlearn.co.uk/excel2007/images/2Tables4.gif" width="443" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> As you can see, the cells C2 to D5 are now highlighted. This includes our new interest rate values in the C column, and our PMT function in cell D2. We can now create an Excel Data Table. This will work out new monthly payemnts for us. So do this:</div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>From the Excel menu bar, click on <b>Data</b></li> <li>Locate the <b>Data Tools</b> panel</li> <li>Click on the "What if Analysis" item:</li> </ul> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Data Tools Panel in Excel 2007" height="85" src="http://www.homeandlearn.co.uk/excel2007/images/2DataToolsWhatIf.gif" width="326" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> When you click on the "What if Analysis" item, you'll see the following menu:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The What-if Analysis menu" height="136" src="http://www.homeandlearn.co.uk/excel2007/images/2DataToolsWhatIf2.gif" width="161" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Click on <b>Data Table</b>, and you'll see this small dialogue box:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The Data Table dialogue box in Excel 2007" height="123" src="http://www.homeandlearn.co.uk/excel2007/images/2DataToolsWhatIf3.gif" width="213" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> In the dialogue box, there is only a <b>Row input cell</b> or a <b>Column input cell</b>. We want Excel to fill downwards, down a column. So we need the second text box on the dialogue box "Column input cell". If we were filling across in rows, we would use the "Row input cell" text box.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The Input Cell for us is the one that contains our original interest rate. This is the cell you want Excel to substitute.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So click inside the Column input cell box and enter B3:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img height="123" src="http://www.homeandlearn.co.uk/excel2007/images/2DataToolsWhatIf4.gif" width="213" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Click OK. When you do, Excel will work out the new monthly payments:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The New Interest Rates" border="1" height="140" src="http://www.homeandlearn.co.uk/excel2007/images/2Tables5.gif" width="459" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So if we could get an 18 percent interest rate, our monthly payments would be £253.93.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> If you click inside any of the cells D3 to D5, then look at the formula bar, you will see this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>{=TABLE(,B3)}</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> That's Excel's way of telling you that a Table has been created, based on the input cell B3</div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We'll try one more Data Table in the next part. We'll try an easier formula, this time</div> <div> <br /></div> </div>

Data Tables in Excel

In Excel, a Data Table is a way to see different results by altering an input cell in your formula. As an example, we're going to alte...

Read more »