How to Do a Competitor Analysis Template in Excel

Building a competitor analysis in Excel is the perfect way to get organized, spot opportunities, and track what your rivals are doing without needing expensive subscription tools. This guide will walk you through exactly what data to collect and how to structure your Excel template, step by step.

What to Track in a Competitor Analysis

A good analysis isn't just about knowing competitor names, it's about understanding their strategies. Before you open Excel, you need a clear idea of what information you're trying to find. We'll break it down into four key categories.

1. High-Level Company Overview

Start with the basics to establish a profile for each competitor. This information provides essential context for everything else you'll analyze.

Company Name & Website: The official name and a direct link to their homepage.
Year Founded: How long have they been in business? An established player will have different strategies than a new startup.
Company Size: Headcount gives you a rough idea of their resources. You can usually find a good estimate on LinkedIn.
Location: Where is their headquarters? This can be relevant for local businesses or globally focused ones.
Positioning Statement: Look at their homepage headline or "About Us" page. How do they describe themselves in one sentence? This tells you who they think their customer is and what value they provide.

2. Product and Pricing Strategy

Next, dig into what your competitors are actually selling. Understanding their offer helps you differentiate your own.

Core Products/Services: List their main offerings. What is their flagship product?
Pricing Model: How do they charge? Is it a one-time fee, recurring subscription, freemium, or usage-based?
Price Points: Document their specific pricing tiers (e.g., Basic at $29/mo, Pro at $99/mo).
Target Audience: Based on their product, pricing, and messaging, who are they trying to sell to? (e.g., small businesses, enterprise, freelancers, etc.).
Key Features: What are the 3-5 unique features they highlight? Look for direct comparisons to your own product.

3. Marketing and Sales Channels

This is where you'll spend most of your time. You want to understand how they attract, engage, and convert customers. Divide this into a few sub-sections.

Website & SEO

Monthly Organic Traffic: Use free tools like Ubersuggest or pay for SEMrush/Ahrefs to get an estimate of how many visitors they get from search engines.
Top Keywords: What terms do they rank for on Google? This shows their content strategy's focus.
Content Strategy: Do they have a blog, podcast, YouTube channel, or webinars? Note the type and frequency of content they produce. Their blog topics can reveal new customer pain points or target keywords.
Backlink Strength: How many other websites link to them? A high number of quality backlinks indicates strong domain authority.

Social Media Presence

Platforms Used: Note which platforms they are active on (LinkedIn, Instagram, TikTok, Threads, etc.).
Follower Count: How large is their audience on each platform?
Engagement Rate: Don't just look at followers. Check the number of likes and comments relative to their follower count. A small, engaged audience is often more valuable than a large, passive one.
Posting Frequency & Content Type: How often do they post? What are they posting (e.g., videos, static images, case studies, behind-the-scenes content)?

Paid Advertising

Ad Platforms: Are you seeing their ads on Google, Facebook, LinkedIn, etc.?
Ad Copy & Messaging: Screenshot ads when you see them. What headline and offer are they pushing? Are they promoting a free trial, a demo, or a discount?
Landing Pages: Click their ads and see where they send traffic. The design and copy on their landing pages tell you a lot about their conversion strategy.

4. SWOT Analysis Summary

Once you've gathered your data, organize it using the classic SWOT framework. This is where data turns into insight, helping you formulate a response.

Strengths: What do they do exceptionally well? (e.g., huge brand recognition, low pricing, superior product feature).
Weaknesses: Where do they fall short? (e.g., poor customer support, outdated website, inactive on social media). This is where your opportunities live.
Opportunities: Based on their weaknesses, what market gaps can you fill? (e.g., Target the customer segment they ignore, create the content they lack).
Threats: What are they doing that could harm your business? (e.g., launching a new product that competes with yours, running a massive ad campaign).

Building Your Competitor Analysis Template in Excel (Step-by-Step)

Now, let’s organize this information in a clean, functional Excel spreadsheet. Using separate tabs keeps your analysis tidy and easy to navigate.

Step 1: Open a New Workbook and Create Tabs

Open a new blank workbook in Excel. At the bottom, you’ll see one worksheet, likely named "Sheet1." Rename it to Summary. Then, click the ‘+’ icon to add more sheets and rename them as follows:

Product & Pricing
Marketing
SWOT

Step 2: Set Up the 'Summary' Tab

This tab is for the high-level company information. It provides at-a-glance context.

In cell A1, type “Metric.” In cell B1, type “Your Company”. In C1, type “Competitor 1,” in D1, type “Competitor 2,” and so on for as many competitors as you're tracking. Then, list the metrics from our "Company Overview" section down column A.

Your setup should look something like this:

Step 3: Build the 'Product & Pricing' Tab

Use the same layout as the Summary tab. List your product and pricing metrics down column A and each company across row 1. This allows for easy side-by-side comparisons.

Emphasis: Having these details in one place is incredibly useful for writing marketing copy, training new sales hires, and identifying gaps in your product roadmap.

Step 4: Design the 'Marketing' Tab

This tab will likely be the most data-rich. Use the categories we defined earlier as subheadings to keep things organized. You can bold the main categories in Column A.

Step 5: Lay Out the 'SWOT' Tab

For the SWOT tab, a different layout works better. We want to see how each competitor stacks up. Set up columns for "Company," "Strengths," "Weaknesses," "Opportunities," and "Threats."

In the cells, use bullet points (Alt + Enter) to list your findings. The Opportunities column is the most important one for you - it's your action plan.

Step 6: Enhance Your Template with Excel Features

Now that the structure is in place, you can use some of Excel’s built-in tools to make it more dynamic.

Conditional Formatting: On your ‘Marketing’ tab, highlight the column with the highest Monthly Traffic. To do this, select all the traffic data (e.g., cells C3:E3), go to Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items... and set it to highlight the top 1. This gives you an instant visual cue of who's leading in search.
Data Validation (Drop-downs): To ensure consistent data entry, use drop-down lists. For Pricing Model on your ‘Product’ tab, select the cell, go to Data > Data Validation. In the "Allow" dropdown, choose "List," and in the "Source" box, type your options: Subscription, One-time, Freemium, Usage-Based.
Add Charts for Visualization: Data is great, but visualizations are better. On your 'Marketing' tab, you can create a simple bar chart to compare social media followers. Highlight the company names and their follower counts, then go to Insert > Recommended Charts and pick the clustered bar chart. It instantly makes the differences more apparent.

Tips for an Effective Analysis

A template is just a file until you put it to work. Follow these simple rules to ensure your competitor analysis provides real value.

Schedule Regular Updates: The market changes quickly. Set a reminder in your calendar to update this spreadsheet at least once per quarter.
Focus on Insights, Not Just Data: The point isn't to fill in every single cell. The goal is to answer the question, "So what?" What does it mean that a competitor has a high follower count but low engagement? It might mean they have an opportunity for more genuine community-building that you can capitalize on.
Share it With Your Team: Your competitor analysis shouldn’t be a secret. Share it with your marketing, sales, and product teams. It can inform ad campaigns, sales battle cards, and future product decisions.

Final Thoughts

Building a competitor analysis template in Excel streamlines your research and puts critical business insights right at your fingertips. By tracking company fundamentals, product offerings, marketing strategies, and synthesizing them into a SWOT analysis, you move from guesswork to a data-informed strategy.

Manually updating the metrics on paid ad performance, website traffic, or sales activity can be the most time-consuming part of keeping this analysis fresh. At my company, we built Graphed to remove exactly that kind of friction. Instead of exporting CSVs from dozens of platforms, Graphed connects directly to your data sources like Google Analytics, Shopify, and Facebook Ads. You can ask for a dashboard comparing channel performance in plain English and get a real-time report automatically, turning hours of data-pulling into a 30-second task.