How To Utilize Excel Data Tables For Better Projections
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.
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.
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.
Browse By Category
You can’t have an outstanding team without an outstanding culture
There's a reason why our team thinks we are a great place to work and no, its not because we have a ping pong table set up. See more about Hanapin's latest certification + we'll let you in on a little secret!
Tracking UAC Performance Through Third-Party App Analytics
Explore how to link and import app downloads and in-app events in Google Ads through third-party app analytics providers.
The 2019 Budget Season Doesn't Have to Be a Nightmare
You could just go off of a formula and call it a day, but in order to have a solid budget in place you need to take the time to prepare.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
How to Build an In-Platform Facebook Ads Funnel
Facebook has many tools to help you build your funnel right in the platform, and to nurture that funnel without taking users off-platform at all.
How We Gained $21,000 More in Revenue by Changing One Color
Testing button color, or the color of any element on your page can be extremely rewarding - if it is done correctly. See how you can turn a simple test into drastic results.
New Research! The State of PPC
This year's State of PPC annual report has arrived! Grab the download for the latest trends and benchmarks in the PPC industry.
Load More »