Data Tables in Excel

Quickly analyze your options with Excel & The What-If Analysis Data Table feature solves for multiple variables of equations.

Download the support worksheet here.

If you would like an alert when the latest video debuts, you can subscribe to our YouTube Channel.

How to create Data tables in Excel video transcript:

In our last quick tip, we used the What If Analysis Goal Seek function to arrive at a specific number to find how many water bottles we needed to sell in order to break even or reach a net profit of zero.

That was assuming that we know what our unit price was. What if we don’t? What happens if we sold twenty thousand water bottles at eight dollars? What if we sold thirty-five thousand water bottles at seven dollars? What if we sold forty thousand water bottles at six dollars and fifty cents. What if we wanted to see several different quantities sold at several different unit prices all at the same time?

The What If Analysis Data Table does just that.

We’re going to learn how to create a Data Table with various quantities sold at various unit prices. And we’ll even give our chart a fantastic look with the Conditional Formatting feature in our next quick tip.

First, let’s review this data so we can see how our formulas interact with each other.

And I’ve already pre-built the skeleton of a table with quantities ranging from five thousand to forty-five thousand in five thousand unit increments and unit prices ranging from five dollars to ten dollars in fifty-cent increments.

This table will give us the various net profits from these quantities and unit prices.

In order to make this table populate net profits, we need to make sure that the upper lefthand corner of these axes is referencing the Net Profit – so set this cell equal to C13.

Now indicate the boundaries of the table by dragging from one corner of the table to the other so that all the cells of the proposed table are highlighted.

Next, Go up to the Data tab, under the Data Tools click on What If Analysis, and select Data Table.

We need to tell the Data Table tool that the row inputs in our table represent various quantities, so mark the Row Input Cell as Quantity, C2. And the column inputs represent various unit prices, so mark the Column Input Cell as Unit Price, C5. Click O.K.
The calculations are performed instantly with the table being populated.

Now we can see different net profits resulting from different unit prices and quantities sold.

And we can even see where the values cross from negative profits and positive profits, giving us a general break-even curve. We’ll learn how to make this curve more visible while transforming this chart’s appearance with the Conditional Formatting feature in our next quick tip.

Now you know how to use the What If Analysis Data Table tool in excel to show what various inputs will do helping to make more complete decisions - like unit pricing.