Where does an email list actually come from?
Ask a shop owner how the newsletter list is doing and you will usually hear one number: the size of the list. It is the least useful number for deciding what to do next. A list grows through a handful of sign-up sources, each with its own form in its own place: a field in the footer, a pop-up, a tick box at checkout, a landing page that offers a free guide, a QR code next to the till. Some of these work hard and some barely work at all, and the total hides which is which.
This free lesson shows how to take the list apart by source in a spreadsheet. You will calculate the form conversion rate of each source, the overall rate for the month and each source's share of all sign-ups. These three numbers are the foundation for everything else in list building: you cannot improve a form, plan growth or judge the quality of a source before you know how many people saw each form and how many of them signed up.
The example company is Quillfern Paper Co., a fictional online shop for planners, pens and stationery with one small shop on the high street. All sources, people and numbers are invented for learning, and every rate you see here is illustrative, not a benchmark for your own business. The data are the same ten rows you work with in the lab above.
Two counts per source: form views and sign-ups
Every sign-up source produces two counts. Form views say how many people had the form in front of them during the period: the pop-up was displayed, the landing page was loaded, the checkout page with the tick box was reached. Sign-ups say how many of them submitted an email address. Most email tools and form plug-ins report both per form; for a QR code or a printed card you may have to count scans or visits to the page behind it.
Be strict about what counts as a view. If the footer form sits at the bottom of every page, every page view technically includes it, although most visitors never scroll that far. That is fine as long as you count the same way every month: the rate of the footer form will be low, and you compare it with its own past, not with a pop-up. What you should avoid is mixing definitions, for example sessions for one form and page views for another, and then ranking them against each other.
The form conversion rate
The form conversion rate answers one question: of the people who saw this form, what share signed up? Form conversion rate = sign-ups ÷ form views. If 1,000 people see a form and 30 sign up, the rate is 30 ÷ 1,000 = 0.03, shown as 3 %. The rate describes the form and its placement, independent of how much traffic it receives, which is exactly why it is useful: it lets you compare a form seen by tens of thousands with one seen by a few hundred.
In a spreadsheet the rate is a single division. With form views in column B and sign-ups in column C, the formula for the first source in row 4 is =C4/B4. Format the cell as a percentage and copy it down the column. Because the references are relative, the row numbers move along as you copy: the formula in row 5 reads =C5/B5, in row 6 =C6/B6, and so on. You write the logic once and the spreadsheet repeats it for every source.
A fully worked example: Quillfern Paper Co. in March 2026
Here are the ten sign-up sources of Quillfern Paper Co. for March 2026, with the two counts from the lab and the two calculated columns. In total the forms were seen 97,040 times and collected 1,495 sign-ups.
| Sign-up source | Form views | Sign-ups | Form conversion rate | Share of all sign-ups |
|---|---|---|---|---|
| Footer form | 58,300 | 212 | 0.36 % | 14.2 % |
| Exit pop-up | 11,400 | 344 | 3.02 % | 23.0 % |
| Checkout tick box | 3,900 | 351 | 9.00 % | 23.5 % |
| Blog inline form | 12,800 | 141 | 1.10 % | 9.4 % |
| Planner guide landing page | 2,600 | 156 | 6.00 % | 10.4 % |
| Welcome quiz | 1,850 | 111 | 6.00 % | 7.4 % |
| Instagram link in bio | 3,400 | 68 | 2.00 % | 4.5 % |
| In-store QR code | 950 | 38 | 4.00 % | 2.5 % |
| Refer-a-friend page | 1,200 | 42 | 3.50 % | 2.8 % |
| Webinar registration | 640 | 32 | 5.00 % | 2.1 % |
| Total | 97,040 | 1,495 | 1.54 % | 100.0 % |
Walk through a few rows. The exit pop-up was shown 11,400 times and collected 344 sign-ups: 344 ÷ 11,400 = 3.02 %. The checkout tick box was seen by only 3,900 people, the buyers who reached the last step of an order, and 351 of them ticked it: 9.00 %, the highest rate in the table. The footer form sits at the other extreme: 58,300 views, 212 sign-ups and a rate of 0.36 %. That does not make the footer form bad. It is always there, costs nothing and still supplied 14.2 % of the month's sign-ups.
The chart sorts the sources by rate, and a pattern appears that you will find on most lists. Forms shown to people who are already committed, such as buyers at checkout, readers who asked for a guide or guests registering for a webinar, convert at several percent. Forms shown to everyone, such as the footer field or an inline form in blog posts, convert at a fraction of that. The intent of the audience matters more than the design of the form.
Totals with SUM
To describe the whole month you need totals. The function SUM adds all numbers in a range: =SUM(B4:B13) returns the 97,040 form views and =SUM(C4:C13) the 1,495 sign-ups. A range is written as its first and last cell with a colon between them. In the lab you type the first formula in B14 and copy it one cell to the right; the column letter shifts from B to C in the same way row numbers shift when you copy down.
The overall rate comes from the totals, not from the average
What is the form conversion rate of all sources together? The correct answer divides the totals: 1,495 ÷ 97,040 = 1.54 %, in the lab =C14/B14. It is tempting to take the average of the ten rates in column D instead. That gives 4.00 %, more than twice the true figure.
The reason is weight. A plain average treats every source alike: the webinar form with 640 views counts exactly as much as the footer form with 58,300. In reality 60 % of all form views happened in the footer, at a rate of 0.36 %, and that drags the overall figure down. Dividing total sign-ups by total views weights each source by its views automatically. Remember the rule for every rate in this course: add up the counts first, then divide.
The same trap appears whenever someone reports an "average conversion rate" across forms, campaigns or months without saying how it was computed. If the groups differ in size, the average of their rates and the rate of their totals can be far apart, and only the second describes what actually happened.
Share of all sign-ups and the absolute reference
The rate tells you how well a form works. It does not tell you how much it contributes. For that you need the share of all sign-ups: sign-ups of the source ÷ total sign-ups. For the checkout tick box that is 351 ÷ 1,495 = 23.5 %; for the exit pop-up 344 ÷ 1,495 = 23.0 %. Together these two sources supply 46.5 % of the month's sign-ups.
The formula needs one new idea. If you write =C4/C14 and copy it down, row 5 becomes =C5/C15, and C15 is empty. The total has to stay put while the source moves. Dollar signs do that: =C4/$C$14. A reference with a dollar sign before both the column letter and the row number is an absolute reference; it points to the same cell wherever the formula is copied. In most spreadsheet programs you can press F4 while typing a reference to add the dollar signs.
- Form conversion rate (D4):
=C4/B4, copied down to D13. Both references are relative. - Totals (B14, C14):
=SUM(B4:B13), copied right to C14. - Overall rate (D14):
=C14/B14, total sign-ups ÷ total form views. - Share of all sign-ups (E4):
=C4/$C$14, copied down to E13. The total is locked with dollar signs.
A quick check for the share column: the ten shares must add up to exactly 100 %. If they do not, the total in the denominator is wrong or the reference to it has slipped.
Reading rate and share together
Neither number is enough on its own. Put them side by side and each source falls into one of four situations, and each situation suggests a different action.
- High rate, large share (checkout tick box: 9.00 % and 23.5 %). The backbone of the list. Protect it: any change to the checkout page should be checked against this number.
- High rate, small share (webinar registration: 5.00 % and 2.1 %). The form works; it simply is not seen often. More traffic to it is the lever, if that traffic can be found at a sensible cost.
- Low rate, large share (footer form: 0.36 % and 14.2 %). It contributes through sheer volume. Even a small improvement of the rate is multiplied by a very large number of views.
- Low rate, modest share (blog inline form: 1.10 % and 9.4 %). A candidate for a rethink: a different offer, a different position in the article, or accepting that this audience came to read.
The comparison of the two shares makes the point visually. The footer form takes 60.1 % of all form views and delivers 14.2 % of the sign-ups. The checkout tick box takes 4.0 % of the views and delivers 23.5 % of the sign-ups. Whenever the second bar is taller than the first, the source converts above the overall rate; whenever it is shorter, below.
What a what-if looks like
Once the table exists, simple questions can be answered in seconds. Suppose a clearer headline lifted the footer form from 0.36 % to 0.50 %. With the same 58,300 views that would be about 292 sign-ups instead of 212, roughly 80 more per month. Suppose instead the in-store QR code doubled its rate from 4.00 % to 8.00 %: with 950 views that adds 38 sign-ups. The same effort produces very different results depending on how many people see the form, which is why rate and volume must always be read together. These are scenarios, not forecasts; a later lesson shows how to test a form change properly.
Common mistakes
Most errors in this kind of table are small and easy to spot once you know them.
- Dividing the wrong way round. Form views ÷ sign-ups gives numbers like 33 instead of 3 %. A conversion rate can never exceed 100 %, so any larger value is a sign that the division is upside down.
- Averaging the rates. The average of the source rates is not the overall rate. Divide the totals.
- Forgetting the dollar signs. Without
$C$14the share formula drifts to empty cells below the total and shows a division error. - Typing results instead of formulas. A typed 3 % will not change when next month's counts are pasted in. Every calculated cell should start with an equals sign.
- Mixing periods. Views from the whole quarter and sign-ups from one month produce a rate that means nothing. Both counts must cover the same days.
- Judging a source on a handful of views. A form with 40 views and 4 sign-ups shows 10 %, and one more or one fewer sign-up moves that figure a great deal. Wait for more data before drawing conclusions.
How to read the result
For Quillfern Paper Co. the March table says three things. First, the list depends on two sources: checkout and the exit pop-up deliver 46.5 % of all sign-ups, so a technical fault in either would be felt at once. Second, the best-converting forms are those shown to people with clear intent, and several of them (the planner guide, the welcome quiz, the webinar) are seen by few visitors; sending more of the right traffic there is the obvious experiment. Third, the overall rate of 1.54 % is dominated by the footer form and says little about any single form, which is the reason to track rates per source.
Notice what the table does not say. It does not say which source brings subscribers who later open, click and buy, and it does not say how many of the sign-ups became real subscribers. A sign-up is the start of the path, not the end.
What comes after the sign-up
The rest of the course follows the path in Figure 3 and then turns to the people already on the list. Each lesson is a lab like this one, with a new data set from the same fictional shop.
- Lessons 2 to 4 measure what is lost at the double opt-in step, net the new subscribers against unsubscribes, bounces and complaints, and turn the result into a list growth rate and a churn rate.
- Lessons 5 and 6 compare sources on cost and on the engagement of their subscribers after 90 days, and evaluate tests of form variants with fewer fields, incentives and different timing.
- Lessons 7 to 10 build engagement segments from the days since the last click or purchase, summarise them with COUNTIF and SUMIFS, and analyse a preference centre: topics, frequency and how many people opt down instead of unsubscribing.
- Lessons 11 to 13 apply a sunset rule, measure what a sunset changes before anyone is removed, and evaluate a re-permission campaign.
- Lessons 14 to 19 cover incentive economics, a growth forecast, a plan from goal to sources, a hygiene audit of a sign-up log, the decision between a segmented send and a blast, and a what-if grid of sign-ups against churn.
- Lesson 20 combines the skills in a quarterly list scorecard with a target flag and a combo chart.
Try it yourself
The lab at the top of this page contains the ten sources with empty columns for the rate, the totals and the share. Type the four formulas, copy them down and let the Coach check each step. If a result is off, the hint shows the formula for the first row. When all four tasks are green you have built the table from this article yourself, and you can paste your own form views and sign-ups over the fictional ones to see where your list really comes from.
Frequently asked questions
How do I calculate the conversion rate of an email sign-up form?
Divide the number of sign-ups by the number of people who saw the form in the same period: sign-ups ÷ form views. In a spreadsheet that is a formula such as =C4/B4, formatted as a percentage. Use form views, not visits to the whole site, when you want to judge the form itself.
What is a good sign-up form conversion rate?
There is no single good number, because the rate depends on where the form sits and who sees it. A footer form that every visitor scrolls past converts far below a tick box at checkout or a dedicated landing page. Compare each source with its own history and with similar placements rather than with one benchmark.
Why is the average of my source conversion rates different from the overall rate?
A plain average gives every source the same weight, however few people saw it. The overall rate weights each source by its form views. Calculate it from the totals: total sign-ups ÷ total form views.
What is the difference between conversion rate and share of sign-ups?
The conversion rate compares a source's sign-ups with its own form views and tells you how well the form works. The share of sign-ups compares a source's sign-ups with the total of all sources and tells you how much of the list growth it supplies. A source can be strong on one and weak on the other.
Why do I need dollar signs in =C4/$C$14?
Dollar signs make a reference absolute, so it stays on the same cell when the formula is copied. C4 is relative and becomes C5, C6 and so on down the column, while $C$14 keeps pointing at the total in every row.
Are form sign-ups the same as new subscribers?
Not always. With double opt-in a sign-up joins the list only after clicking the link in a confirmation email, and some people never do. Sign-ups measure how well the form works; confirmed subscribers measure how fast the list really grows.
Keep going: Email List Building & Segmentation: Sign-Up Sources, Form Conversion, Double Opt-In Loss, List Growth Rate, Engagement Segments, Preference Centres & Sunset Rules
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Double Opt-In Loss: Confirmation Rate, Unconfirmed Sign-Ups per Source and the Weighted Loss of the Whole Month
- Net List Growth: New Subscribers Minus Unsubscribes, Bounces and Complaints, a Running List Size and the Replacement Ratio
- List Growth Rate and Churn Rate: Monthly Rates, the Compound Average Growth Rate and a Twelve-Month Projection
- Source Cost and Quality: Cost per Confirmed Subscriber, 90-Day Engaged Rate, Cost per Engaged Subscriber and a Ranking
- Form Conversion Tests: Fewer Fields, Incentives and Timing, Relative Lift over the Control and Extra Sign-Ups per Month
- Engagement Segments: Days Since the Last Click or Purchase, Active, Warm, Cooling and Dormant with IFS, and Segment Counts
- Segment Summary: Subscribers, Revenue and Clicks per Segment with COUNTIF and SUMIFS, Revenue per Subscriber and a Revenue Chart
- Preference Centres: Topic Choices, Frequency Options, Audience Size per Topic and Emails per Subscriber with XLOOKUP
- Unsubscribe or Opt Down: Save Rate of the Preference Page, Subscribers Kept, Revenue Retained and a Stacked Chart of Choices
- Sunset Rules: Days Inactive, a Two-Condition Rule with IF and AND, Re-Permission Candidates and the Share of the List Affected
- What a Sunset Changes: Volume Removed, Clicks Lost, Click Rate and Complaint Rate Before and After, and Sending Cost Saved
- Re-Permission Campaigns: Re-Confirmation Rate by Source, Addresses Removed, List Size Afterwards and a Weighted Average with SUMPRODUCT
- Sign-Up Incentives: Confirmed Subscribers, First Orders, Net Margin after the Incentive and the Best Incentive per Sign-Up
- Forecasting List Growth: SLOPE as Subscribers per Month, FORECAST.LINEAR for the Next Six Months and Months to a Goal
- From Goal to Plan: New Subscribers per Source from Views × Conversion × Confirmation, Expected Losses, Net Growth and Months to the Goal
- List Hygiene at the Door: Duplicate Addresses, Missing Consent Records, Unconfirmed Sign-Ups and the Mailable List
- Segmented Send or Blast: Expected Margin, the Cost of Unsubscribes, Net Result per Segment and a Send or Skip Decision
- What-If Grid: List Size in Twelve Months for Every Mix of Monthly Sign-Ups and Churn, a Heat Map and the Ceiling of a List
- Final Project: A Quarterly List Scorecard with Sign-Ups, Form Conversion, Confirmation Rate, Net Growth, a Target Flag and a Combo Chart