How to Do Conjoint Analysis in Excel
Trying to figure out which features your customers actually value can feel like a guessing game. Conjoint analysis offers a structured method to stop guessing and start understanding what drives purchase decisions. This article will walk you through, step-by-step, how to conduct a basic conjoint analysis right in Microsoft Excel.
What Exactly Is Conjoint Analysis?
Conjoint analysis is a survey-based market research technique that helps you understand how people make complex decisions. Instead of just asking "Is price important?", which most people would say "yes" to, it presents respondents with a series of distinct product choices (or "profiles") and asks them to rate or rank their preferences.
By analyzing these choices, you can figure out the perceived value, or utility, shoppers place on individual product features - like price, color, brand name, warranties, and more. This method reveals what attributes customers are willing to trade off for others.
You can use the insights from a conjoint analysis to:
- Develop new products with the most sought-after features.
- Optimize your pricing strategy based on perceived value.
- Identify target market segments with different preferences.
- Project market share for potential product configurations.
While specialized software exists for conjoint analysis, running your first one in Excel is a fantastic way to learn the ropes. It forces you to understand the underlying mechanics and is a powerful tool you likely already own.
The Components of a Conjoint Study
Before diving into Excel, it's important to grasp the three main building blocks of any conjoint study.
Attributes
Attributes are the primary features or characteristics of a product or service. If you were building a new SaaS product, your attributes might include:
- Support Level: The type of customer service included.
- Feature Set: The collection of features included in a plan.
- Price: The monthly cost of the subscription.
The key is to choose attributes that are both important to your customers and actionable for your business.
Levels
Levels are the specific variations available for each attribute. Each attribute must have at least two levels. For our SaaS example, the levels could be:
- For Support Level: Email Only, Email & Phone Support.
- For Feature Set: Basic, Advanced, Premium.
- For Price: $29/month, $49/month, $79/month.
Free PDF · the crash course
AI Agents for Marketing Crash Course
Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.
Profiles
A profile (sometimes called a concept or card) is a specific combination of levels that describes one hypothetical product. You'll show a series of these profiles to your survey respondents. A sample profile for our SaaS product might be:
- A plan with Advanced features and Email & Phone Support for $49/month.
Your Step-by-Step Guide to Conjoint Analysis in Excel
Once you've identified your attributes and levels, you're ready to jump into Excel and build your analysis. Let's stick with our SaaS product example.
Step 1: Design Your Product Profiles
With three attributes (one with two levels, one with three, and one with three), you have a total of 2 × 3 × 3 = 18 possible unique combinations. Testing all combinations is a "full-factorial design" and can quickly become unmanageable if you have more attributes.
For this exercise, we'll use a "fractional-factorial design," which selects a smaller, statistically representative subset of profiles to test. Manual creation for this is fine. You want to create about 8–12 profiles that are balanced, meaning each level appears a reasonably equal number of times.
In Excel, you would build a table like this to organize your profiles:
Step 2: Collect Survey Data
Next, you’ll present these profiles to your survey participants. Using a tool like SurveyMonkey, Google Forms, or Typeform, create a survey where each profile is shown one by one, and respondents are asked to rate it on a consistent scale, such as 1 to 10 (where 1 is "Not at all likely to buy" and 10 is "Very likely to buy").
Let's say you surveyed 200 people. You would then calculate the average rating for each of the 9 profiles and add that data to your Excel sheet. That Average Score will become the dependent variable in our analysis.
Step 3: Structure Your Data for Regression Analysis
This is the most crucial data preparation step. Linear regression models require numerical input, so we need to convert our text-based levels into numbers using a technique called "dummy coding."
For each attribute, you will create a dummy variable column for all but one of its levels. The level you leave out becomes the "baseline" or reference point.
- For Support (2 levels): We'll create one dummy column:
Support: Email & Phone. "Email Only" will be our baseline. If a profile has "Email & Phone" support, this column gets a 1, otherwise, it gets a 0. - For Features (3 levels): We'll create two dummy columns:
Features: Advanced,Features: Premium. "Basic Features" is the baseline. - For Price (3 levels): We'll create two dummy columns:
Price: $49/mo,Price: $79/mo. "$29/mo" is the baseline.
Your spreadsheet should now look something like this. Notice how you can determine the baseline level by seeing all zeros for that attribute's dummy columns (e.g., Profile 1 has Basic Features).
Step 4: Run a Linear Regression
Excel's Data Analysis Toolpak is perfect for this. If you don't have it enabled, go to File > Options > Add-ins > Manage: Excel Add-ins > Go... and check the box for "Analysis Toolpak."
- Navigate to the Data tab and click on Data Analysis on the far right.
- Select Regression from the list and click OK.
- In the regression pop-up:
- Click OK. Excel will generate a new sheet with your regression output.
Step 5: Calculate Part-Worth Utilities
The goal is to determine the "part-worth utility" for every level of every attribute. The regression output gives us most of these. The numbers in the Coefficients column are the part-worth utilities for the levels you created dummy variables for.
To find the utility for your baseline levels, remember that the sum of utilities for all levels within an attribute must equal zero. Therefore, a baseline level's utility is the negative sum of the other utilities in its group.
Here's how we'd calculate them:
- Support (Email Only):
-1.26 - Features (Basic):
-(0.93 + 1.81) = -2.74 - Price ($29/mo):
-(-0.76 + -2.59) = 3.35
Create a summary table of these utilities. Higher values mean more preference.
From this, we see that price is a huge driver ('$29/mo' has a massive utility of 3.35), and premium features are highly valued (1.81). We also see that people really dislike the lowest support tier (-1.26).
Free PDF · the crash course
AI Agents for Marketing Crash Course
Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.
Step 6: Putting Your Analysis to Work
The real value is in using these utilities to make strategic decisions. You can now predict the preference for any product combination by simply adding up the utilities of its levels.
Attribute Importance
You can quickly see which attributes matter most by calculating the range of utilities within each. Simply take the highest utility minus a given attribute's lowest utility.
- Price Importance: 3.35 - (-2.59) = 5.94
- Features Importance: 1.81 - (-2.74) = 4.55
- Support Importance: 1.26 - (-1.26) = 2.52
By a significant margin, Price is the most important factor in this decision, followed by Features, and then Support.
Market Simulation
You can create a simple market simulator. Imagine you're considering two potential offerings:
- Plan A (Budget Saver): Basic Features (-2.74) + Email & Phone Support (1.26) + at $29/mo (3.35) = Total Utility: 1.87
- Plan B (Power User): Premium Features (1.81) + Email & Phone Support (1.26) + at $79/mo (-2.59) = Total Utility: 0.48
In a head-to-head comparison, the budget-friendly "Plan A" would be significantly more preferred based on this analysis, suggesting it may capture more initial market share.
Final Thoughts
By following these steps, you can execute a conjoint analysis directly in Excel, transforming abstract customer opinions into a quantitative framework for making smarter product and pricing decisions. It provides a robust alternative to simply asking people what they want, revealing their true preferences through trade-offs.
Building these analyses manually in Excel is a fantastic way to grasp the mechanics, but the process can become time-consuming with larger data sets. As you integrate survey results with live data from different marketing, sales, and analytics sources, tasks can become more complex. We built Graphed to address this specifically. After connecting your sources in a few clicks, you can use plain English to analyze data across platforms, turning days of work into seconds of conversation and helping you act on insights instantly.
Related Articles
How to Create Facebook Ads API for Claude Code
Create a Meta Marketing API access token that never expires, using a system user, so Claude Code can pull Facebook ad reports and manage campaigns.
ClickHouse vs Postgres: When to Switch to Columnar
Comparing ClickHouse vs PostgreSQL means matching storage architecture to workload. Row storage wins for transactions. Columnar wins for scan-heavy analytics.
Google Ads for Orthodontists: Get More Booked Consults
Most orthodontic Google Ads campaigns waste money on low-intent searches. The problem is structure, not the platform.