Expanding your Excel skills will make you a better marketer. Get started with this step-by-step guide to using Goal Seek in Excel.

As a marketer, you‘re constantly making decisions, setting goals, and figuring out how to allocate resources effectively. But without data to guide you, planning marketing strategies and determining budgets feels like throwing darts while blindfolded.

You don‘t have to make decisions based on guesswork. Excel‘s Goal Seek feature is a powerful tool that helps you use real data to set realistic targets and create achievable plans.

With Goal Seek, you can calculate backwards from a desired result to determine the input value needed to reach that result. For example, you can use Goal Seek to:

  • Determine how many leads you need to hit a sales target
  • Calculate how much web traffic you need to generate a certain number of conversions
  • Set the optimal price for your product to maximize revenue
  • Figure out how much to spend on ads to produce your target ROI

Rather than estimating what you need to put in to hit your goals, Goal Seek gives you a precise, data-backed answer.

In this guide, we‘ll walk through what Goal Seek is, why it‘s valuable for marketers, and how to use it step-by-step. We‘ll also look at several examples of Goal Seek in action for marketing planning and decision making.

By the end, you‘ll be ready to harness the power of Goal Seek in your own marketing workflows. Let‘s get started!

What is Goal Seek in Excel?

Goal Seek is an analysis tool in Excel that allows you to find the input value needed to achieve a desired output that you specify.

In other words, it enables you to calculate backwards. You provide the end result you want, and Goal Seek determines what input is needed to obtain that result.

For example, say you want to generate 500 leads from your website this month. Historically, you‘ve converted 10% of website visitors into leads on average.

Using Goal Seek, you can set your target result at 500 leads. Goal Seek will then calculate that you need to drive 5,000 website visits to generate 500 leads at a 10% conversion rate.

Goal Seek is essentially doing this calculation:
Target leads ÷ conversion rate = website visits needed
Or, plugging in the numbers:
500 leads ÷ 0.10 = 5,000 website visits

This type of "backward calculation" is extremely useful for marketing planning, budgeting, forecasting, and strategy. It eliminates guesswork and helps you make decisions based on concrete data.

Why Goal Seek is valuable for marketers

According to research from Allocadia, 83% of marketers say the pressure to prove marketing ROI is increasing. Yet only 21% are completely confident in their ability to measure ROI.

This is where Goal Seek comes in. By enabling marketers to set goals and determine plans based on data, Goal Seek improves decision making, accountability, and alignment with business objectives.

Some key benefits of using Goal Seek for marketing include:

  • Setting achievable goals: Goal Seek ensures your targets are realistic and grounded in data, not just aspirational. You can use Goal Seek to determine what‘s actually possible based on your historical performance.

  • Creating data-backed strategies: With Goal Seek, you can build marketing plans and budgets based on concrete numbers. This improves your chances of hitting your goals and justifying your strategies to stakeholders.

  • Optimizing resource allocation: Goal Seek helps you determine exactly how to allocate your marketing budget, time, and headcount to maximize results. You can calculate the optimal investment levels across channels and activities.

  • Forecasting potential outcomes: You can use Goal Seek to model different scenarios and predict results. This helps you prepare for uncertainties, spot potential issues, and make proactive pivots.

  • Measuring and proving ROI: By tying your marketing inputs to desired outputs, Goal Seek demonstrates how your efforts translate into bottom-line impact. This makes it easier to prove the value of your work.

Now that you understand the power of Goal Seek for marketing, let‘s dive into how to actually use it.

How to use Goal Seek in Excel

Using Goal Seek is a fairly straightforward process once you get the hang of it. We‘ll walk through it step-by-step using a simple marketing example.

Let‘s say you want to drive $100,000 in revenue this month. Your average order value is $500 and your sales team typically closes 10% of the leads they receive. You want to calculate how many leads you need to generate to produce $100,000 in revenue.

Step 1: Organize your data and set up formulas

Before you use Goal Seek, you need to get your data in order and set up the formulas to calculate your desired outcome.

In this case, the formula to calculate revenue is:
Leads x conversion rate x average order value = revenue

In your spreadsheet, lay out your data like this:

Month Leads Conversion rate Average order value Revenue
January 100 10% $500 $5,000
February 150 12% $500 $9,000
March 200 10% $500 $10,000

Then, set up your revenue formula in the revenue column. It should look like this:
=B2*C2*D2

Where B2 is leads, C2 is close rate, and D2 is average order value.

Step 2: Open Goal Seek

With your data and formulas ready, you can now run Goal Seek.

In the Excel menu, go to the Data tab and click "What-if Analysis." From the dropdown menu, choose "Goal Seek."

Excel what-if analysis menu with Goal Seek option

This will bring up the Goal Seek dialog box:

Goal Seek dialog box

Here‘s what each field means:

  • Set cell: This is the cell where your desired result is calculated. In our example, this is the revenue cell.
  • To value: Here, you put the actual value you want to achieve. We want $100,000 in revenue.
  • By changing cell: This is the input metric you want to solve for. In this case, we want to know how many leads we need, so we select the leads cell.

Step 3: Run Goal Seek and interpret results

Once you‘ve filled out the Goal Seek dialog box, click OK to run the analysis.

Goal Seek will calculate the input value needed to reach your desired result and update the input cell automatically.

In our example, Goal Seek determines that we need 2,000 leads to generate $100,000 in revenue at a 10% close rate and $500 average deal size.

Goal Seek results

Goal Seek also helpfully updates the formula cell with your desired result. This shows that if you bring in 2,000 leads, you‘ll end up with $100,000 in revenue based on your other assumptions.

Keep in mind that Goal Seek‘s calculations are only as good as the data you feed it. If your input data or formulas are off, Goal Seek‘s output won‘t be reliable. Always double check your numbers before acting on Goal Seek‘s results.

It‘s also important to remember that Goal Seek is directional, not prescriptive. It tells you the input needed to achieve an output mathematically. But it‘s up to you to determine if that input is realistically achievable and figure out how to tactically reach it.

Advanced examples of using Goal Seek for marketing

To further illustrate the value of Goal Seek for marketing, here are a few more examples of strategic questions you can answer with this tool:

1. How much should you spend on ads?

The question:

  • To generate 500 downloads of your app this month, how much do you need to spend on ads given your current cost-per-install (CPI)?

The setup:

  • Target downloads: 500
  • CPI: $5
  • Formula: Spend ÷ CPI = downloads

Goal Seek settings:

  • Set cell: Downloads cell
  • To value: 500
  • By changing cell: Spend cell

The result:

  • To get 500 app downloads at a $5 CPI, you need to spend $2,500 on ads this month.

2. What conversion rate do you need to hit your revenue target?

The question:

  • Your sales target for the quarter is $250,000 and your average deal size is $10,000. What lead-to-customer conversion rate do you need to achieve to reach your revenue goal?

The setup:

  • Target revenue: $250,000
  • Average deal size: $10,000
  • Number of leads: 100
  • Revenue formula: Leads x conversion rate x average deal size

Goal Seek settings:

  • Set cell: Revenue cell
  • To value: $250,000
  • By changing cell: Conversion rate cell

The result:

  • To generate $250,000 in revenue from 100 leads with a $10,000 average deal size, you need a 25% lead-to-customer conversion rate.

3. How many customers do you need to reach your average revenue per user target?

The question:

  • If your goal is to achieve $100 average revenue per user (ARPU) and your total revenue is currently $50,000, how many users do you need?

The setup:

  • Target ARPU: $100
  • Current revenue: $50,000
  • ARPU formula: Total revenue ÷ total users

Goal Seek settings:

  • Set cell: ARPU cell
  • To value: $100
  • By changing cell: Total users cell

The result:

  • To reach an ARPU of $100 with $50,000 in total revenue, you need a user base of 500 people.

Tips for getting the most out of Goal Seek

As you start using Goal Seek more, keep these best practices in mind:

  1. Organize and clean your data before analyzing. Goal Seek‘s outputs will only be as reliable as the data you feed it. Make sure your data is accurate, up-to-date, and formatted correctly before running Goal Seek.

  2. Double check that your formulas are set up right. Goal Seek relies on your formulas to calculate results. If your formulas are constructed incorrectly, Goal Seek‘s answers will be off. Always test your formulas and make sure they‘re working as expected.

  3. Run multiple scenarios to pressure test goals. Use Goal Seek to see what inputs are needed to achieve different levels of your desired outcome. This will give you a sense of what‘s realistically achievable.

  4. Sense-check Goal Seek‘s outputs. If the result seems way off base, don‘t take it at face value. Scrutinize your inputs and formulas to pinpoint potential issues. Extraordinary claims require extraordinary evidence.

  5. Pair Goal Seek with other Excel tools for deeper insights. Goal Seek is even more powerful when used along with Excel‘s other analysis and forecasting capabilities. Combine it with tools like Solver, PivotTables, and XLOOKUP to further slice and dice your data.

  6. Validate your Goal Seek with real-world experiments. Goal Seek can guide your planning, but you still need to test and iterate in the real world. Let Goal Seek inform your hypotheses, then validate and refine through experimentation.

Conclusion

Excel‘s Goal Seek feature is a powerful tool to add to your marketing tool belt. With Goal Seek, you can:

  • Set realistic, achievable goals
  • Create data-backed strategies and plans
  • Optimize your marketing mix and budget allocation
  • Forecast outcomes and prepare for different scenarios
  • Measure and prove the impact of your marketing efforts

By calculating backward from desired outputs to required inputs, Goal Seek replaces guesswork and assumptions with data and precision. This leads to better decision making, more predictable results, and clearer ties between marketing activities and business impact.

While Goal Seek is an advanced Excel feature, it‘s worth the effort to master it. The more you can harness data in your marketing planning and execution, the more effective you‘ll be in hitting your targets and demonstrating value.

You now have everything you need to start using Goal Seek in your own work. Try it out in your next marketing planning session and see the difference it makes.

To continue leveling up your Excel skills, check out the resources below:

  • 10 Essential Excel Functions for Marketers
  • Advanced Excel Formulas and Functions for Marketing
  • Data Analysis Tools in Excel for Marketers
  • Pivot Tables: A Beginner‘s Guide for Marketers
  • 33 Excel Tips for Becoming a Spreadsheet Pro

Here‘s to unlocking the full power of Excel to supercharge your marketing!

Tags: