Build a Simple Campaign Tracking Sheet

What you will make

A compact Excel campaign tracker with input columns, three calculated metrics and totals for one reporting week. Allow about 30 minutes. The practice figures are fictional and do not represent expected marketing performance.

Before you start

Use Excel for Microsoft 365 on Windows. Download campaign-tracker.xlsx below and save a copy. The supplied workbook is a worked example. To practise building it, create a blank workbook and follow the same cell positions. Examples use English formula names and commas; a regional installation may require semicolons.

1. Set up the input columns

On a sheet named Campaigns, use row 4 for headers. A4 is Campaign, B4 Spend GBP, C4 Impressions, D4 Clicks and E4 Leads. Enter these four rows starting at row 5: Search, 200, 10000, 400, 20; Social, 150, 8000, 240, 12; Email, 50, 2000, 100, 10; Trial, 25, 1000, 0, 0. All rows represent the same reporting week. Use numeric values, with zero for a known zero and a blank for an unknown figure.

2. Define the calculations

Add F4 Click-through rate, G4 Lead rate and H4 Cost per lead GBP. For this exercise, click-through rate is clicks divided by impressions, lead rate is leads divided by clicks and cost per lead is spend divided by leads. Compare campaigns only when reporting periods and the definitions of their inputs are consistent.

3. Enter the formulas

Enter this in F5:

=IF(OR(C5="",D5=""),"",IF(C5=0,"",D5/C5))

Enter this in G5:

=IF(OR(D5="",E5=""),"",IF(D5=0,"",E5/D5))

Enter this in H5:

=IF(OR(B5="",E5=""),"",IF(E5=0,"",B5/E5))

Copy F5:H5 down through row 8. These formulas leave a rate blank when a required input is missing or its denominator is zero. A blank rate is not a measured 0%. Trial therefore has 0% click-through rate, but blank lead rate and cost per lead.

4. Format and inspect

Format F5:G8 as percentages with one decimal place. Format spend and cost per lead as GBP with two decimal places. Search should show 4.0%, 5.0% and £10.00. Social should show 3.0%, 5.0% and £12.50. Email should show 5.0%, 10.0% and £5.00. Distinguish the editable A:E inputs from F:H formula cells with a light fill and clear labels.

5. Calculate matching totals

Label A10 Total. In B10 enter =SUM(B5:B8) and copy it across to E10. Then enter F10 =IF(C10=0,"",D10/C10), G10 =IF(D10=0,"",E10/D10) and H10 =IF(E10=0,"",B10/E10). Totals should be £425, 21,000 impressions, 740 clicks and 42 leads. Overall rates are 3.5%, 5.7% and £10.12 after display rounding. Calculate these rates from totals; averaging row percentages would answer a different question.

6. Test changes and missing information

Change Search leads in E5 to 25. Its cost per lead should become £8.00 and overall cost per lead £9.04. Set E5 to zero: Search cost per lead should become blank. Clear E5: both its lead rate and cost per lead should be blank. Restore 20. The simple Total row sums available values, so complete every input before interpreting aggregate results.

7. Extend carefully

To add another campaign, insert a row immediately above Total, add its inputs and copy formulas from a campaign row. Inspect the SUM ranges and verify that the new record is included. Check for duplicate campaign-period records and negative inputs before reporting. The sheet does not calculate revenue return or attribute the same lead across platforms.

If the formulas fail

• A formula appears as text: use a normal cell format, then re-enter it starting with =.
• A syntax error appears: check quotes, parentheses and your regional separator.
• Totals disagree: verify the four source rows and SUM ranges.
• A rate looks too large: confirm the numerator, denominator and percentage format.

Check your learning

You should be able to reproduce the starting totals, explain the zero-denominator behaviour and make a change that updates the overall result. Save your tracker and keep the reporting period and metric definitions with it.

Official guidance

Microsoft: IF function

Microsoft: SUM function

Cover image: AI-generated illustrative scene created specifically for this resource.

Download and complete the exercise

Download the worked campaign tracker and editable practice sheet (Excel XLSX)

Practice files in the formats shown. Version 1.0, 8 October 2026. Personal and internal organisational adaptation permitted with Jefferson Otonbara Imgbi author credit retained.