Creating Instant Tables in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

The Data table tool in Excel has been around for years and makes data tables very quickly and easily.

By Helen Bradley

Consider this situation, you’re about to borrow money for a house and you need a quick reference of loan amounts, interest rates and payments. You want it on paper where you can reference it, so you crank up Excel and then start working on the task. Sound like a good idea? If you weren’t planning on creating a data table, right now I suggest you stop and let’s see if we can simplify what you’re about to do. You’ve happened upon a perfect job for Excel’s Data table tool. It makes data tables and it does it very quickly and easily. I’ll show you how.

The Data table tool has been around for years so, regardless of whether you’re using your old faithful Excel 97 or the spanking new Excel 2003 you can make data tables.


ONE WAY OR TWO?

There are two types of data tables you can create in Excel – one way tables and two way tables. Data tables are useful for comparing values where one or two variables change. A one variable change occurs in our loan example if the amount you’re planning to borrow changes. In that case you’ll want to know the new payments at the interest rate you’ve been quoted.

A two variable change will occur if you’re unsure what amount you’ll be borrowing and if you aren’t sure what the interest rate will be. There are two things that might change – amount and rate – these are called variables because their values can vary!

Where one variable changes you want a one way data table and where two change, you need a two way data table to have all the information you need at your finger tips.

The benefit of using data tables rather than punching numbers into a formula is that all the information is displayed on the worksheet in front of you and you can print it and take it with you and look up any value in it at any time, without having to return to the computer.

CREATE THE DATA TABLE STRUCTURE

Enough of the theory, it’s time to get to work and see just how easy these data tables are. We’ll create the two way table and the variables will be amount borrowed and interest rate. We’ll use the PMT formula to calculate the repayment amount and we’ll calculate it all using a 30 year loan term.

Type this data into a new Excel worksheet

A2   Amount borrowed
B2   100000
A3   Interest Rate
B3   5%
A4   Term in years
B4   30
A5   Monthly Repayment
B5   =PMT(B3/12,B4*12,B2)

This worksheet uses the PMT (payment) function to calculate the amount you need to repay each month if you borrow a fixed amount for a fixed term at a fixed rate of interest. Here we’re starting with 100,000 borrowed at 5% for the 30 years. The result is a negative number because we’re paying out money – as if we’re likely to forget.

To create the data table we need to enter the two sets of values that we’ll test – interest rate and amount. We’ll put amount down a column and interest rate across a row but they could be reversed if you wanted to.

To enter the amounts, begin in cell B9 and enter 100000. Select the range from cell B9 to cell B33 and choose Edit, Fill, Series, type a step value of 10000 and click Ok. This enters the values from 100,000 to 340,000 with a step of 10,000.

To enter the interest rates start in cell C8 and enter 4.75%. Select the range from C8 to J8 and choose Edit, Fill, Series and type a step value of .25%. If necessary, format the range to display percentage values and two decimal places. The range now displays the values 4.75% to 6.50% in increments of .25%.

The final element before actually creating the table is the formula into which you put these figures to get the result you’re interested in – the payment per month. When you write the formula, and create the table, Excel will take all possible combinations of interest rate and loan amount and calculate the monthly payments for each. Your table is currently 8 columns x 25 rows in deep so the single formula will make 200 calculations.

Excel expects the formula used for the calculations to appear in the top left cell of what will become the data table. In our case this is cell B8 – this might not look like a sensible place to put it, but it’s how the thing works, so who’s arguing?

So, into cell B8 type this formula:

=PMT(B3/12,B4*12,B2)

Coincidentally (or not), this is the same formula we typed into cell B5, however, you can’t copy it from there because that formula contained relative references which won’t copy correctly so you’re stuck with retyping it. Of course, you should still get the same result – if you don’t, check your formula because the result should be – $536.82.

CREATE THE TABLE

Now we’re ready to create the actual data table formulas. So, highlight the range of cells from B8 to J33 – this area encompasses all the cells which will form the data table as well as the formula and the variables. Choose Data, Table to display the Data table dialog. The Row Input Cell is a reference to the cell containing the value you will be altering using the data in the top row of the data table. The top row contains interest rate which is also contained in cell B3 in the original data area. Enter B3 in the Row Input Cell in the dialog.

The second cell reference you need is the Column Input Cell. This is the amount data and the amount is stored in cell B2 of our original data area so enter B2 in this area of the dialog. Click Ok to create the table.


HOW TO READ IT

Once you’ve finished going wow! at what just happened, you can clean up the table so it’s easier to read. To do this, format the values in the table to whole numbers without distracting cents. While you need to leave the formula in cell B8 it’s unsightly and distracting so I like to format it with white text so disappears into the cell background (you need to use a number format as well as white text to do this and remove the red negative number formatting).

You can now read the payment amounts from the table. To see how much you’ll pay if you borrow $200,000 at 5.25% look at the figure at the intersection of the row with the heading $200,000 and the column headed 5.25%. It’s $1,104 per month – perhaps it’s time to ask the boss for a rise?

One thing to note, when you’re working with data tables – you can’t change any of the cells in the data area (the range from cell C9 to J33) because these cells contain a set of array formulas {=TABLE(B3,B2)} which is what creates the table.

 

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address