Creating a Comping Spreadsheet From Scratch: A Step-by-Step UK Tutorial

▍Creating a Comping Spreadsheet From Scratch: A Step-by-Step UK Tutorial

This is a build-it-yourself tutorial. You start with a blank Google Sheet, and by the end of about 20 minutes you'll have a working comping tracker with formulas, dropdown validation, conditional formatting and a Statistics tab. Every cell is explained.

If you'd rather just copy the finished thing without understanding it, our sister post comping spreadsheet template guide hands you the ready-made column structure to paste in. This post is for the compers who want to understand every formula, build the muscle memory for editing it later, and end up with something they can genuinely modify rather than a black box they're afraid to touch.

▍Before you start: what you need

A Google account (free), a laptop or desktop for the initial build (mobile works for daily use but the setup is much easier on a real keyboard), and about 20 minutes of focused time. Bring a cup of tea — there's a lot of small clicking.

This tutorial uses Google Sheets because it's free, syncs to your phone automatically, and the formula syntax is identical to Excel for everything we'll do. If you'd rather use Excel or Apple Numbers, the structure transfers — only minor syntax differences (Numbers uses slightly different separators in some formulas).

▍Step 1: Create the spreadsheet

Open Google Drive (drive.google.com) → New → Google Sheets → Blank spreadsheet. You'll see an empty grid with a default name like "Untitled spreadsheet".

Click the title at the top-left, rename it to "Comping Tracker [your name] 2026". The year matters because most compers archive yearly and start a fresh sheet each January — keeps file sizes manageable and gives a clean "this year's wins" view.

While you're naming things, click the + button at the bottom-left to add a second sheet tab. Rename the default one to Tracker and the new one to Stats. We'll fill the Tracker tab now and build the Stats tab in step 6.

▍Step 2: Build the column headings

Click on cell A1 in the Tracker tab and type the following column headings, pressing Tab between each (Tab moves you right one cell — much faster than clicking):

• A1: Comp name
• B1: Source
• C1: Entry method
• D1: Entry date
• E1: Closing date
• F1: Days left
• G1: Status
• H1: Prize description
• I1: Prize value (£)

That's the core nine columns most UK compers settle on after a few weeks of trial and error. Resist the urge to add extras now — we'll talk about optional columns in step 9.

Now make the headings stand out. Select row 1 (click the "1" on the left), then on the toolbar: bold (Ctrl+B / Cmd+B), then a fill colour (paint bucket icon, pick a light grey). The visual cue helps when you're scrolling on mobile.

▍Step 3: Freeze the header row

This is the one setup step beginners universally forget and regret.

Click View → Freeze → 1 row. Now when you scroll down past row 30, the headings stay visible at the top. Without this, by row 50 you'll be guessing which column is which.

While you're at it: View → Freeze → 1 column as well, so column A (Comp name) stays visible when you scroll right on a phone. Two small tweaks, hours saved over the next year.

▍Step 4: Format the date columns

Date columns D (Entry date) and E (Closing date) need date formatting, otherwise Sheets might treat your input as text or a random number.

Click the D column letter to select the whole column → Format → Number → Date → pick the UK format (DD/MM/YYYY). Repeat for column E.

While you're in Format: column I (Prize value) → Format → Number → Custom currency → £ symbol with 0 decimal places. Now any number you type into column I displays as £150 rather than 150 or $150.

▍Step 5: Build the Days-left formula in column F

This is the formula that makes the whole tracker useful.

Click cell F2 (row 2, column F — the first data row). Type:

=IF(ISBLANK(E2), "", E2-TODAY())

Press Enter. What this does, broken down:

• E2-TODAY() subtracts today's date from the closing date in cell E2, giving a number of days
• IF(ISBLANK(E2), "", …) wraps it so empty rows don't fill with negative numbers (Sheets treats blank cells as date zero — 1899 — without this guard, you'd see -46,000 in every empty row)

Now we need this formula in every row. Click cell F2 to select it, then hover over the small blue square in its bottom-right corner until your cursor becomes a crosshair. Drag down to row 500 (or wherever you want to plan ahead to). Every row F2:F500 now has the formula, automatically referring to its own E column cell.

For the visual learners: imagine the screenshot shows a tracker where row 2 has Closing date 30/05/2026 and today's date is 23/05/2026 — column F now shows 7. Row 3 with Closing date 25/05/2026 shows 2. Row 4 with no closing date shows blank. The formula does this for every row, recalculating every time you open the sheet.

▍Step 6: Add dropdown validation for Source, Method and Status

Dropdowns prevent the great spreadsheet plague: typing "Instagram" in one row and "insta" in the next and "IG" in the third. To a COUNTIF formula those are three different sources and your analytics become nonsense.

Source dropdown (column B)

Click the B column letter to select the whole column → Data → Data validation → + Add rule → Apply to range B2:B500 → Criteria: Dropdown → enter these options (one per line):

• Sweepzy
• Instagram
• Facebook
• X (Twitter)
• TikTok
• Magazine
• On-pack
• Brand newsletter
• Other

Under "If the data is invalid": Show a warning. Click Done. Now every cell in column B (from row 2 onwards) has a small dropdown arrow.

Entry method dropdown (column C)

Same process — Data → Data validation → + Add rule → Range C2:C500 → Dropdown options:

• Web form
• Comment
• Email
• Postal
• SMS
• DM / Direct message
• Like and share
• Creative (slogan/photo)

Status dropdown (column G)

Same again — Range G2:G500 → Dropdown options:

• Pending
• Entered
• Won
• Not won
• Disqualified

Three minutes of clicking, and you've just protected six months of future analytics. Worth it.

▍Step 7: Conditional formatting on column F (the killer feature)

This is the visual alerting system. Without it, your tracker is a spreadsheet. With it, your tracker actively tells you what to do today.

Click the F column letter → Format → Conditional formatting. The sidebar opens. Add these rules in order (top rule wins, so set them up in this sequence):

Rule 1: Already closed (grey)

• Apply to range: F2:F500
• Format rules: Custom formula is
• Value: =F2<0
• Formatting style: light grey background, dark grey text
• Click Done

Rule 2: Entering today / tomorrow (red)

• Click + Add another rule
• Custom formula: =F2<=2
• Formatting style: red background, white text
• Done

Rule 3: This week (orange)

• • Add another rule
• Custom formula: =F2<=7
• Orange background
• Done

Rule 4: Next fortnight (yellow)

• • Add another rule
• Custom formula: =F2<=14
• Yellow background
• Done

Now visualise the result: a tracker where rows closing today scream red, rows closing this week glow orange, rows closing within the fortnight are amber, anything further out is neutral white. Sort column F ascending each morning and your priority list builds itself.

Add one more set of rules on column G (Status): green fill for "Won", grey fill for "Not won" or "Disqualified", white for everything else. Now your weekly review ("how did last week go?") is a single glance.

▍Step 8: Build the Stats tab

Click across to the Stats tab you created in step 1. We'll build four formulas — the only four most compers ever need.

In cell A1, type the heading Metric. In B1, type Value.

Total entries this month

• A2: Entries this month
• B2: =COUNTIFS(Tracker!D:D, ">="&EOMONTH(TODAY(),-1)+1, Tracker!D:D, "<="&EOMONTH(TODAY(),0))

Breakdown: EOMONTH(TODAY(),-1)+1 is the first day of the current month. EOMONTH(TODAY(),0) is the last day. COUNTIFS counts how many entries in column D of the Tracker tab fall between those two dates. Roll over into June, the formula updates itself — no manual maintenance.

Win rate (percentage)

• A3: Win rate
• B3: =IFERROR(COUNTIF(Tracker!G:G, "Won") / COUNTIFS(Tracker!G:G, "<>Pending", Tracker!G:G, "<>Entered", Tracker!G:G, "<>"), 0)

Format cell B3 as percentage (Format → Number → Percent). The IFERROR(…, 0) wrapper prevents a divide-by-zero error when you first set this up with no data. The key bit is dividing wins by settled entries only (excluding Pending, Entered and blank rows). Without this, your win rate looks fake-low because it counts every still-open competition as a loss.

A healthy committed UK comper sits around 0.5-2% (1 win per 50-200 settled entries). New compers often sit at 0.2-0.5% — that's normal and improves with experience, source curation and the tips in maximising your chances of winning.

Total prize value won (£)

• A4: Total prize value won
• B4: =SUMIFS(Tracker!I:I, Tracker!G:G, "Won")

Format as currency (£, 0 decimals). This is the number that surprises every new comper. Add it up over a year and even casual compers usually land in the £200-£500 range; committed compers regularly top £1,000-£3,000.

Wins by source

• A6: Wins by source (heading)
• A7: Sweepzy, A8: Instagram, A9: Facebook, etc — match your Source dropdown options
• B7: =COUNTIFS(Tracker!B:B, A7, Tracker!G:G, "Won")
• Drag the B7 formula down to match each source

This is where the analytics earn their keep. After three months you'll see which sources actually produce wins versus which are pure noise. Drop the noise.

▍Step 9: Optional columns for power users

Resist these for the first month. If you genuinely miss a feature after 4 weeks of real use, add it:

The rule: add a column only when you've actually wished it existed three times in real use. Speculative columns get abandoned and clutter the sheet.

▍Step 10: Mobile setup

Download the Google Sheets app (iOS or Android). Open your tracker. Tap the three dots → Add to home screen so it's a one-tap launch.

Mobile-specific tweaks:

• Hide power-user columns on mobile if you added them. Tap the column letter → "Hide column". They still exist; they just don't waste screen space
• Use the dropdowns — typing on a phone keyboard is slow; tapping is fast. This is why we set up validation in step 6
• Sort by Days left as your default mobile view — Data → Sort range → column F ascending

The tracker should be usable with your thumb at a bus stop. If it's not, something in setup needs fixing. For mobile-first comping more broadly, see comping routine and time management.

▍Step 11: Sharing settings

If you're running a solo tracker, skip this section. If you share with a comping partner:

1. Click Share (top-right blue button)
2. Enter their email
3. Set permission: Editor (they can add entries) or Viewer (read-only)
4. Tick Notify people so they get an email link
5. Untick "Anyone with the link" unless you specifically want a public sheet — there's no reason a stranger needs your comping data

Agree the column structure upfront. The fastest way to break a shared tracker is one partner silently adding a column the other doesn't know about. If you're collaborating regularly, add a small "Last updated" cell at the top of the Stats tab so you can see when your partner last touched it.

▍Step 12: Backup strategy

Google Sheets has version history (File → Version history → See version history — lifesaver if you delete the wrong row), but for a real backup:

• Monthly: File → Download → Microsoft Excel (.xlsx). Save to your own drive with a date in the filename ("Comping Tracker 2026-05.xlsx"). Why Excel format and not Sheets? Because if Google's auth ever goes wrong, you want a file you can open in another tool.
• Quarterly: Also export to PDF for archival snapshots. File → Download → PDF document.
• Annually: Start a fresh Sheet for the new year. Archive the old one to a folder.

Takes 60 seconds a month. Saves you when something inevitably goes wrong.

▍Step 13: Daily and weekly routines

The spreadsheets that survive have routines. The ones without routines die in week 6.

Daily (2 minutes):

• Add any new comps you entered today (one row each)
• Update column G for anything you heard back about (won, not won)
• Sort by column F to see what's closing tomorrow — those get entered first thing in the morning

Weekly (10 minutes):

• Look at any rows where F is blank or zero and decide: archive, delete or chase
• Glance at the Stats tab — anything weird? Win rate cratered? Look at which sources you're using
• Move "Not won" rows older than 4 weeks to an Archive tab, or delete them — clutter kills usability

Monthly (15 minutes):

• Recalculate the Stats tab manually as a sense-check
• Look at the Wins by source breakdown — which sources are pure noise? Stop using them
• Consider whether any optional columns from step 9 are now genuinely worth adding

▍When to stop building and start using

A risk of any "build it yourself" tutorial is that you spend three weeks perfecting the spreadsheet and zero time entering competitions. Don't.

The minimum viable tracker is steps 1-7 (about 15 minutes). Steps 8 onwards add value but they don't have to happen on day one. Build the bare bones, start entering, and iterate over the next month based on what you actually miss. Most compers find they never need columns J-P at all.

For the broader question of how this tracker fits into your overall comping setup — auto-fill extensions, the dedicated comping email, the device choice — see essential comping tools and resources and comping supplies and equipment.

▍When the spreadsheet stops being enough

Honest admission: spreadsheets break around 50 active entries. Three failure modes:

1. Mobile editing gets painful. Pinching and scrolling on a bus stops being viable past 50 rows.
2. No push reminders. A spreadsheet can't ping your phone the night before a deadline — you have to remember to open it. Plenty of compers lose £200 vouchers this way.
3. Duplicate-entry detection breaks down. Past 100 active comps, scrolling to check "did I already enter this one?" stops working. People enter twice and get disqualified.

When you hit that wall, Sweepzy's free competition tracker handles unlimited entries, pushes deadline reminders to your phone, auto-detects duplicates, and gives you analytics that would take an hour a month to build manually in the Stats tab above. You can import your spreadsheet in one click on day one. Create a free account here — no credit card, free forever plan.

But don't switch tools prematurely. If you're at 10-20 entries a day and the spreadsheet you just built feels fine, you're done. The point of this tutorial was to make a tracker that fades into the background of your hobby. If it does that, mission accomplished.

▍Common build mistakes

The five mistakes that consistently break custom-built comping spreadsheets:

1. Skipping the freeze panes step. By row 50 you can't tell which column is which. Two clicks in View → Freeze fixes it forever.
2. No data validation. "Instagram" / "insta" / "IG" treated as three different sources. Your Wins-by-source analytics become useless after a month.
3. Forgetting the conditional formatting on Days left. Without the visual alerts, you'll miss closing dates. This is the single most valuable feature of the entire spreadsheet.
4. Building 20 columns on day one and abandoning the sheet in week three. Build the 9-column core. Add columns only when you've actually wanted them three times.
5. No backup. Google version history isn't a real backup. Export to .xlsx monthly. Takes 60 seconds.

For more on the tracking question generally — pen and paper vs spreadsheets vs apps, and when each is right — see how to track competition entries and the master ultimate guide to comping.

▍Where to go from here

Three options once you've finished building:

• Use the tracker for 4-6 weeks, then come back and add power-user columns based on what you actually missed
• Compare against the ready-made template in comping spreadsheet template guide — same destination, but useful as a sanity-check that you didn't miss anything
• Upgrade later when you hit the 50+ active entries wall — Sweepzy's tracker handles the mobile and reminder problems a spreadsheet can't

Whatever you do, get out of the spreadsheet and back to entering comps as soon as it's working. The tracker is admin; the entries are the hobby. Start tracking entries free with Sweepzy if you'd rather skip the spreadsheet entirely — same column structure under the hood, but mobile-first with push reminders. Free forever, no credit card.