We’ve all been in a situation where we needed to see the results of an equation based on a range of values. Maybe it was “what if we spent X amount or Y amount” or “what are the potential impacts of another ad test?”
Calculating by hand is slow and cumbersome. Thankfully, data tables allow you to quickly compare values and run calculations multiple times.
Rather than manually calculating a range of values, this overlooked tool will help you quickly answer these types of questions.
Getting Started
You can find the data table menu on your ribbon. Under Data > What if Analysis > Dropdown > Data Table.
No need to click it now. If you already clicked it, you’ve no doubt ended up with a poorly explained menu with no idea how to proceed. Table that worry and we’ll jump into the examples.
Single Input Data Tables
Single input data tables change the value of a single variable. This is perfect when you only need to change one thing, such as budget, profit margin, or CTR. Let’s start with a simple example. How many clicks will we get at different CTRs?
In this example, we have 100,000 impressions and 2,500 clicks. This gives us a 2.5% CTR. What would happen if this CTR increased or decreased (beyond the obvious increase and decrease in clicks)?
Along the left, we’ll list out our CTRs in 0.5% increments. We know that total clicks can be calculated by multiplying the impressions by the CTR. Let’s input that equation in a cell, in this case, F4.
Next, we need to create the table. Highlight the entire range of cells. For the example below, we used E4 to F10. This covers our CTR variable and the output cells.
Now go back to the ribbon and select the data table. Then we’ll be back to the data table menu. This menu will have two options, one to modify the row input and another to modify the column input.
In our example, we need to modify the values based on the left-side column. We’ll input the CTR cell, C7, as the input cell. This sets up the table to calculate the values by inserting the column values in place of C7.
Click the OK button to run and the table will populate with the new values.
With a few clicks we now have click projections for a range of CTRs. Despite the simple example, you can easily expand this functionality to more complex formulas with additional factors.
Updating Multiple Columns Based On A Single Input
Despite a single variable input, you aren’t limited to a single output column. Imagine you are running a simple account with the prospect of heavy competition. The influx of competition will lead to CPC volatility. We need to estimate costs and revenue at different CPCs.
We’ll set up the table almost exactly the same as the first example but we’ll add three columns. One for profit, revenue, and cost. After we set up the calculations along the top row and the different CPCs along the left-hand column, we select the whole table again, set the column input to the CPC cell, and run.
Each column is now calculated and displaying the specified metric for the given CPC.
The only holdback here is that you are stuck with using a single input. In this case the CPC. Nonetheless, it’s an easy way to gauge a range of metrics based on a single change and much more useful than running multiple calculations by hand.
Two Input Data Tables
Not everything can be slimmed down to one input. Even if it can, it isn’t as fun. Despite having a row and column input we’ve stuck to the column input so far. We’re about to change that.
Imagine we need to estimate performance for a range of CTRs and CVRs. We’re currently running a heavy round of ad testing and CRO testing. Without any certainties, we may see wins in losses in one or both metrics. What happens if CTR drops but CVR increases, what if both decrease by 10% during the test phase?
To answer these questions, we will once again lay out a table like we did earlier except this time we will utilize the rows and the columns. To get started, we’ll place CVR along the vertical axis with values for -20% to +30% and we’ll do the same for CTR except we’ll place along a horizontal axis.
Where the two groups intersect we’ll place our revenue formula.
Then we’ll select the whole table and open the Data Table menu.
Once that is open we’ll choose our CVR cell as the column input, our CTR cell as a row input, and click OK. Excel then calculates all the combinations and delivers a table that shows projected revenue for each increase and decrease in both CTR and CVR.
Wrapping Up
While data tables won’t solve all your problems, they are an invaluable tool for analysis. Once you get the hang of It, you can generate multiple tables in minutes and quickly gauge scenarios and outcomes.
It not only helps in giving ranges for projections but can also serve as a sanity check. Receive an extremely aggressive goal? Pull up a table, throw in some moonshot metrics, and see if you even come close based on current performance.
Data tables can also model more complex scenarios such as changing demand, price changes, and more. If you can put something in a formula, you can use it in a data table.
If you are currently using data tables, what is your favorite use case? Have any clever tricks or big wins? Feel free to tweet us.
