# Excel data table for what-if analysis of car costs

How to build a one-variable and two-variable Excel data table that tests car running costs against fuel price and annual mileage, and how to read the results.

An Excel data table is a what-if tool that recalculates one formula many times and shows every result side by side. You point it at an input cell, such as fuel price or annual mileage, and Excel fills a block of outcomes for your car cost estimate. The feature lets you compare scenarios in one glance instead of editing cells by hand. 

- A data table changes one or two input cells and records the formula result for each value you supply.
- A one-variable table tests a single input, such as fuel price, across a row or column of values.
- A two-variable table crosses two inputs, such as fuel price and annual mileage, in a grid.
- The inputs and the cost formula must sit in the same worksheet as the results table.
- Data tables are recalculated by Excel, so a large results table slows a workbook down.
 

## What a data table does 

A data table is a sensitivity analysis feature in Excel that runs one formula against many input values. You build a car cost formula once, then ask Excel to show the outcome for each value in a list. The result is a block of numbers you can read as a comparison. 

The tool works by substitution. Excel takes your formula, places each test value into the linked input cell, and writes down the outcome. Your original formula and input cell return to their earlier values after the run. Nothing in the live model changes. 

Car running cost suits this treatment because several inputs move at once in real life. Fuel price and annual mileage shift independently, and each shift changes the yearly total. A data table shows how far the total travels when those inputs move. 

- Set up a cost formula with clear input cells
- Keep inputs and formula on the same worksheet
- Choose one or two inputs to vary
- Read the result block as a range of outcomes
 

## Build the car cost model first 

A useful data table needs a clean cost formula before any table is created. Place each input in its own labeled cell, such as fuel price per unit, annual mileage, and fuel consumption. The formula then refers to those cells rather than to typed numbers. 

A simple annual fuel cost formula multiplies annual mileage by consumption per distance and by fuel price. Add other running costs, such as insurance, maintenance and registration, as separate input cells. Keep the total in one output cell that the data table can point to. 

Label every input and the output cell. A data table copies values into an input cell, so a mislabeled cell produces a correct number for the wrong question. Clear labels on the worksheet prevent that mix-up when you read the results later. 

## Run a one-variable data table 

A one-variable data table tests a single input, such as the fuel price input, against your cost formula. List the test values down a column or across a row, leaving the first cell of the series for the formula reference. Excel then fills the outcomes beside or below the values. 

Select the whole block, including the value list and the empty result cells, then open the Data Table command on the Data tab. Enter the cell address of the input you want to vary. Excel substitutes each listed value in turn and records the result of the formula. 

A one-variable table answers questions about one driver at a time. You can see how the annual fuel bill changes as the price per unit rises or falls. The same layout works for annual mileage as the varied input, with fuel price held still. 
| Varied input | Held constant | What the table shows | 
| Fuel price | Annual mileage | Cost at each price level | 
| Annual mileage | Fuel price | Cost at each distance | 
| Maintenance budget | Fuel and mileage | Total with each budget |  

## Run a two-variable data table 

A two-variable data table crosses two inputs in a single grid, with one input down the side and one across the top. Fuel price can run across the columns while annual mileage runs down the rows. Each cell then shows the cost for that pair of values. 

Place the formula reference in the top-left corner of the grid, outside both value series. Select the full rectangle, open the Data Table command, and supply the row input cell and the column input cell. Excel fills every intersection with the matching outcome. 

The grid is the strength of the two-variable data table. A reader sees a range of running costs at once and spots where a high fuel price meets a long annual mileage. That view supports a decision about which cost level a budget can absorb. 

## Keep the layout readable 

A results table earns its place when a reader can find a number without effort. Format the header row and header column so the two varied inputs are obvious. Number formats with consistent decimal places make the grid easier to scan. 

Shade the result block lightly and keep the formula reference cell apart from the values. Add a short label above the table naming the output, such as total annual running cost. A reader then knows what every number in the grid represents. 

Separate the input area from the results area on the worksheet. Place the inputs, the cost formula and the results table in that order across the sheet. That order mirrors the logic of the analysis and helps another person follow it. 

## Avoid the usual setup errors 

The Data Table command fails when the input cell and the formula sit on different worksheets. Excel needs both on the same sheet, so move the formula or the input before you build the table. A reference to another sheet returns an error in the result block. 

Test values must be numbers, not text that looks like a number. A column formatted as text can leave the result cells blank or filled with errors. Check the cell format and retype the values if the table does not fill. 

Recalculation is the other trap. Every change to the workbook recalculates the data table, and a large grid of results takes time. Reduce the number of test values or set the workbook to manual calculation while you edit the model, then recalculate when you are done. 

## Read the results as a range of costs 

A data table answers with a spread of outcomes rather than a single number. The smallest and largest values in the block show the range a car cost estimate can occupy. Use that range when you weigh two listings against a budget. 

Look for the point where a cost crosses a level you set for yourself. That crossing marks the fuel price or annual mileage at which the yearly total stops fitting your plan. The table turns a limit into a visible threshold on the sheet. 

Keep the data table beside the listing details rather than in a separate file. A reader can then compare one car against another using the same input assumptions. The site subject of this category is exactly that side-by-side view of different listings. 

## Adapt the table to other inputs 

The same structure works for any input that drives a cost estimate. Maintenance budget, tire cost per distance, or insurance premium can each become the varied input. The formula and the output cell stay in place while the tested input changes. 

A two-variable grid can pair any two of those inputs, such as fuel price against maintenance budget. Decide which input belongs on the row axis by asking which comparison a reader needs first. The axis order changes the look but not the underlying outcomes. 

Keep one data table per question so the sheet stays legible. A workbook with several small tables reads better than one wide grid that mixes questions. Name each table after the input it varies, using the TABLE function wording in the sheet's own notes. 

## Questions about Excel data tables for car costs 

### What is a what-if analysis data table in Excel? 

A what-if analysis data table is an Excel tool that recalculates one formula for many input values and shows every result in a block. You supply the values and point Excel at the input cell to vary. The table then displays the outcome of your car cost formula for each value. 

### What is the difference between a one-variable and a two-variable data table? 

A one-variable data table varies a single input, such as fuel price, and returns one list of results. A two-variable data table varies two inputs at once, such as fuel price and annual mileage, and returns a grid. The grid shows the cost at every combination of the two values. 

### Why does my data table show errors or stay empty? 

A data table shows errors when the input cell or the formula sits on a different worksheet, because Excel requires both on the same sheet. Text-formatted values can also leave the result cells empty. Check the worksheet location and the cell format, then run the command again. 

### Which inputs should I vary for a car cost estimate? 

Fuel price and annual mileage are the two inputs that move a running cost estimate the most. You can also vary maintenance budget or insurance premium. Choose the inputs whose change would alter your decision about a listing. 

### How many test values should a data table hold? 

The count depends on how much detail you need and how fast the workbook must respond. Each added value increases recalculation time. A short series of values spaced far apart often shows the range of costs clearly enough. 

### Can a data table compare two cars at once? 

A single data table covers one formula, so it compares cost outcomes for one car. To compare two listings, build a matching table for each car using the same input values. Place them side by side and read the two result blocks against each other.
