Competitor Analysis Template: A Spreadsheet Framework for Small Businesses
A practical competitor analysis template for small businesses: which competitors to include, the comparison grid, a weighted scoring sheet, a SWOT summary and how to turn findings into pricing and marketing decisions.
A competitor analysis template is a spreadsheet that lines up your business against three to eight competitors on the same set of criteria (offer, price, channels, reviews, positioning) so you can see where you win, where you lose and what to change. For a small business, the useful version fits in one workbook with four tabs: a competitor list, a comparison grid, a weighted score and a one-page summary with actions. This guide explains what goes on each tab, how to gather the data without paid tools, and how to keep the analysis from turning into a document nobody uses.
Step 1: choose the right competitors
List competitors in three groups, because each tells you something different:
| Type | Definition | Why include it |
|---|---|---|
| Direct | Same product or service, same customers | Sets the price and quality your customers compare you with |
| Indirect | Different product, same problem solved | Explains why some customers never consider you |
| Aspirational | A larger or better-known business in your space | Shows where the market is heading and what customers will soon expect |
A good way to find them is to search the way a customer would: the main search terms for your offer, the marketplaces you sell on, and the names customers mention when they say “we were also looking at…”. Three to five direct competitors plus one or two of each other type is enough.
Step 2: the comparison grid
Put competitors in columns (with your own business in the first column) and criteria in rows. Group the rows so the grid stays readable:
- Offer: core products or services, range, unique features, guarantees.
- Price: entry price, typical order value, discounts, shipping or setup fees.
- Customers: who they target, where they are, what language and tone they use.
- Channels: website, marketplaces, social platforms, physical locations, partners.
- Visibility: the search terms they appear for, how often they publish, advertising you can see.
- Reputation: average rating, number of reviews, the most common praise and complaint.
- Operations: delivery time, opening hours, booking or checkout experience.
Record facts, not impressions, and add a source and a date in a note or a separate column. Prices and offers change; an undated grid quickly becomes misleading.
Step 3: score with weights
Facts become decisions once you score them. On a scoring tab, list the five to eight criteria customers care about most, give each a weight that adds up to 100%, and score every business from 1 to 5.
The weighted score for each business is:
=SUMPRODUCT($B$2:$B$8,C2:C8)
where column B holds the weights and column C the scores for one business. Copy the formula across. Conditional formatting with a color scale shows at a glance where each business is strong or weak.
| Criterion | Weight | You | Competitor A | Competitor B |
|---|---|---|---|---|
| Price for the main offer | 25% | 3 | 4 | 2 |
| Quality and range | 20% | 4 | 3 | 4 |
| Delivery or turnaround time | 15% | 4 | 2 | 4 |
| Reviews and reputation | 15% | 2 | 4 | 5 |
| Ease of buying | 15% | 3 | 4 | 4 |
| Visibility in search | 10% | 2 | 3 | 4 |
| Weighted score | 100% | 3.10 | 3.40 | 3.65 |
In this example the business wins on delivery and quality but trails on reviews and visibility. The obvious first actions are a review request routine after every order and content for the search terms competitor B ranks for, not a price cut.
Step 4: a one-page SWOT and action list
Summarise the grid in a SWOT on its own tab:
- Strengths: criteria where you score highest.
- Weaknesses: criteria where you score lowest and customers care most.
- Opportunities: gaps no competitor fills well, such as an unserved customer group or a common complaint.
- Threats: a competitor’s recent move, a new entrant, a price war.
Below the SWOT, keep a short action table with the action, owner, due date and the metric you expect to move. Three actions per quarter that actually happen beat twenty ideas in a slide.
How to gather the data without paid tools
- Their website and checkout: prices, shipping, policies and the buying experience, up to the payment step.
- Marketplace listings: price, number of reviews, ratings and the questions customers ask.
- Review sites and map listings: read the most recent negative reviews; they show what customers wish were different.
- Search results: search your main terms in a private window and note who appears and with what kind of page.
- Social profiles: posting frequency, the content that gets engagement, how they answer comments.
Stick to publicly available information. Do not misrepresent yourself to obtain confidential information from competitors or their staff.
Keep the analysis alive
Review prices and offers monthly and the full grid every quarter. Add a “last checked” date to each column and a tab that logs changes, such as “Competitor A raised its entry price on 3 March”. Patterns in that log are often more useful than any single snapshot.
Turn findings into numbers
If the analysis suggests a price change, test it against your own costs first with the profit margin calculator. If it points to more marketing, set the spending in the marketing budget template and plan the content in the content calendar. Track whether sales actually move with the sales tracker spreadsheet. More guides are in Marketing Plans & Trackers.
Frequently asked questions
How many competitors should I analyse?
Three to five direct competitors and one or two indirect or aspirational ones. More than eight makes the grid hard to maintain.
What is the difference between a competitor analysis and a SWOT?
The competitor analysis compares businesses criterion by criterion. A SWOT summarises what that comparison means for your own business. The template uses both: the grid as evidence, the SWOT as the conclusion.
How often should I update it?
Check prices and offers monthly and refresh the full grid quarterly, or immediately when a competitor launches something new.
Can I build it in Google Sheets?
Yes. SUMPRODUCT and color scales work the same way, and a shared sheet lets the whole team add observations.