Tabellab

Campaign Budget Tracker: Planned vs Spent

Lesson 1 of 20Lab

All lessons
  1. Campaign Budget Tracker: Planned vs SpentLabFree
  2. Paid Channel Metrics: CTR, CPC, CPM, Conversion Rate, CPA and ROASLab Locked
  3. Email Campaign Stats: Delivered, Open, Click and Unsubscribe RatesLab Locked
  4. Social Media Content Calendar: Posts per Platform and WeekLab Locked
  5. Lead Funnel: Step Conversion and Visitors NeededLab Locked
  6. A/B Test: Rates, Lift and a Sanity CheckLab Locked
  7. Cleaning a Lead List: TRIM, PROPER, LOWER and DuplicatesLab Locked
  8. UTM Link Builder: Joining Text into Tracking LinksLab Locked
  9. Event and Webinar ROI: Costs, Show-up Rate and ReturnLab Locked
  10. Monthly Marketing Report: Growth %, Conversion and a Trend ChartLab Locked
  11. Q3 Marketing Dashboard: ROAS by Channel, Chart and HighlightsLab Locked
  12. From Raw Export to Summary: SUMIFS by Channel and MonthLab Locked
  13. Mapping Table: Grouping Messy Source Names with XLOOKUP and IFNALab Locked
  14. Campaign Timeline: Durations, Creative Deadlines with WORKDAY and Campaigns Live per WeekLab Locked
  15. Vendor Invoices: Matching to Purchase Orders with XLOOKUP and Days OverdueLab Locked
  16. Social Post Performance: Engagement Rate, Averages by Format and the Top PostLab Locked
  17. Email Segments: Opt-Ins, Segment Rules and Contacts per RegionLab Locked
  18. Event Kit Inventory: Needed Quantities, Shortfalls and Orders in Full PacksLab Locked
  19. Event Feedback Survey: Score Groups, the Score Distribution and NPSLab Locked
  20. Final Project: The Quarterly Marketing Pack – Budget Use, CPA, ROAS, Review Flags and a Combo ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Read first

In Excel

  • Select C8:E8 and press Alt + = (Windows) or Cmd + Shift + T (Mac) to insert the three sums at once.
Loading the spreadsheet…

The question every marketing coordinator gets asked

If you work in marketing, sooner or later someone will ask you: “How much do we have left?” It might be your manager before a planning meeting, the finance team at the end of the quarter, or an agency that wants to know whether it can book more ads. A marketing coordinator who can answer that question in ten seconds, campaign by campaign, with numbers everyone trusts, becomes the person the whole team relies on. A coordinator who has to dig through invoices and ad dashboards every time does not.

This free lesson is for marketing coordinators, marketing assistants, interns, small-business owners who run their own campaigns and anyone moving into a marketing operations role. You will join Fernleaf Outdoor, a fictional outdoor gear brand, as its new marketing coordinator. Six campaigns are running in Q2, across search ads, social video, email, influencers, a printed catalogue and a webinar. Your job: track planned against spent, see what is left, flag what is over budget and report the total. All companies, campaigns and numbers in the course are fictional.

The good news: you only need a handful of formulas, SUM, simple subtraction and division, and IF. The skill is less in the formulas than in setting the sheet up so it stays correct when invoices arrive every week. This article walks you through the logic, a fully worked example with the exact formulas, the Excel steps, two charts and the mistakes that most often turn a budget tracker into a source of confusion.

The core idea: planned, spent, remaining and % used

A budget tracker compares two numbers for every campaign. The planned budget is what was approved before the campaign started. The spent amount is what has actually been used so far, taken from invoices, ad platform billing and expense reports. From these two numbers you calculate three things.

  • Remaining = planned − spent. A positive number is budget still available, a negative number means the campaign has spent more than it was given.
  • % used = spent ÷ planned. It makes campaigns of very different sizes comparable: 107% on a search campaign and 111% on a catalogue are both over, whatever their size in money.
  • Status is a word, “Over” or “OK”, produced by IF. People scan words faster than numbers, and a status column can be filtered and coloured.

The order of the subtraction matters. Planned minus spent gives a positive number when there is money left, which is how most people think about a budget. If you write spent minus planned, the signs flip and a healthy campaign looks negative. Pick one convention, label the column clearly (“Remaining”) and keep it in every report.

The percentage needs context. 95% used in the last week of a campaign is exactly what you want. 95% used after the first week of a twelve-week campaign is a warning that the money will run out long before the end. That is why experienced coordinators look at % used next to % of time elapsed, a check called pacing, which we come back to below.

Use “greater than”, not “greater than or equal”, for the over-budget test. A campaign that spent exactly its budget, like Trail Influencers with 5,000 of 5,000, is on plan, not over.

Worked example: Fernleaf Outdoor’s Q2 budgets

Here are the six Fernleaf campaigns from the lab, with every result filled in. Columns C and D are the inputs. Columns E to G and the total row are what you calculate.

CampaignChannelPlanned (C)Spent (D)Remaining (E)% used (F)Status (G)
Spring Sale SearchGoogle Ads6,0006,420−420107.0%Over
Hiking Boots ReelsMeta Ads4,5003,98052088.4%OK
Newsletter Q2Email9008604095.6%OK
Trail InfluencersInfluencer5,0005,0000100.0%OK
Summer CatalogPrint3,2003,540−340110.6%Over
Gear Care WebinarWebinar1,20076044063.3%OK
Total20,80020,56024098.8%

Two campaigns are over budget: Spring Sale Search spent 420 more than planned and Summer Catalog 340 more. Three campaigns have money left, and Trail Influencers landed exactly on budget. In total Fernleaf planned 20,800 and spent 20,560, so the marketing team as a whole is 240 under budget and has used 98.8% of its money. That is a useful headline, but it also hides the two overruns, which is exactly why the campaign-level view matters.

Look closely at the total % used. It is total spent ÷ total planned, 20,560 ÷ 20,800 = 98.8%. If you averaged the six percentages in F2:F7 instead, you would get 94.2%. The average gives the 900 newsletter the same weight as the 6,000 search campaign, and the small, underspent webinar pulls the figure down. The pooled 98.8% is the one finance will recognise, because it matches the money.

Fernleaf Outdoor Q2: planned vs spent (fictional)PlannedSpent, within budgetSpent, over budgetSpring Sale Search6,0006,420Hiking Boots Reels4,5003,980Newsletter Q2900860Trail Influencers5,0005,000Summer Catalog3,2003,540Gear Care Webinar1,20076002,0004,0006,0008,000
Planned and spent budget for each fictional Fernleaf campaign. Peach bars mark campaigns that spent more than planned.

These are the formulas behind the table. The first five are exactly what the Coach in the lab checks, and the last two are useful additions for a summary line in your report.

CellWhat it calculatesFormula
E2Budget remaining (copy down to E7)=C2-D2
F2Share of the budget used (copy down to F7)=D2/C2
G2Status word (copy down to G7)=IF(D2>C2,"Over","OK")
C8:E8Totals of planned, spent and remaining=SUM(C2:C7), copied right
F8Share of the total budget used=D8/C8 (same as =SUM(D2:D7)/SUM(C2:C7))
extraNumber of campaigns over budget=COUNTIF(G2:G7,"Over") → 2
extraTotal overspend of the over-budget campaigns=SUMIF(E2:E7,"<0") → −760

% of budget used per campaign (fictional)dashed line = 100% of the planned budget0%50%100%107.0%Spring SaleSearch88.4%Hiking BootsReels95.6%NewsletterQ2100.0%TrailInfluencers110.6%SummerCatalog63.3%Gear CareWebinar
Share of budget used per campaign. Everything above the dashed 100% line is over budget. Fictional numbers.

The second chart answers a different question from the first. The planned-versus-spent bars show where the big money is. The % used columns show which campaigns are furthest off plan, regardless of size. Gear Care Webinar stands out at 63.3%: either it was cheaper than expected, or some invoices have not arrived yet. Finding out which is part of the coordinator’s job, because an unbooked invoice will turn up later and change the picture.

How to build it in Excel, step by step

You can rebuild the tracker in any blank workbook in about ten minutes. The steps use the same cells as the lab.

  1. Type the headers in A1:G1: Campaign, Channel, Planned, Spent, Remaining, % used, Status. Make them bold with Ctrl + B (Cmd + B on a Mac).
  2. Enter the six campaigns with their channels, planned and spent amounts in A2:D7, and the word Total in A8.
  3. In E2 type =C2-D2. Select E2:E7 and press Ctrl + D to copy the formula down, or double-click the fill handle at the bottom-right corner of E2.
  4. In F2 type =D2/C2, copy it down to F7 and format F2:F8 as percent with Home → Number → Percent Style (Ctrl + Shift + %). Add one decimal with Increase Decimal.
  5. In G2 type =IF(D2>C2,"Over","OK") and copy it down to G7. Text inside a formula always needs straight double quotes.
  6. Select C8:E8 and press Alt + = (Windows) or Cmd + Shift + T (Mac). Excel inserts =SUM(C2:C7), =SUM(D2:D7) and =SUM(E2:E7) in one go.
  7. In F8 type =D8/C8 for the share of the total budget used. Do not use AutoSum or AVERAGE here.
  8. Select G2:G7 and choose Home → Conditional Formatting → Highlight Cells Rules → Text that Contains, type Over and pick the light red fill. Over-budget campaigns now light up on their own.
  9. For the charts, select A1:A7 and C1:D7 (hold Ctrl or Cmd for the second range) and choose Insert → Charts → Clustered Bar. For the % chart, select A1:A7 and F1:F7 and choose Clustered Column.
Turn the range into an Excel Table with Ctrl + T before you add more campaigns. New rows then get the formulas in E, F and G automatically, and a Total Row can be switched on under Table Design → Total Row.

Going one step further: pacing

The tracker you built tells you where each budget stands today. Pacing tells you whether it will last. For each campaign, compare the share of the budget used with the share of the campaign’s time that has passed. Suppose, for example, a campaign runs for 13 weeks and 8 weeks have passed. Then 8 ÷ 13 = 61.5% of the time is gone. If the campaign has used 61.5% of its budget, it is on pace. If it has used 85%, it will run out early.

In Excel you can add a column with the time elapsed, for example =(TODAY()-StartDate)/(EndDate-StartDate), capped at 100% with =MIN(1,…), and a pacing column with =F2-H2: the difference between % used and % of time elapsed. A positive gap means spending is running ahead of the calendar. Many teams flag anything more than ten points ahead, but the right threshold depends on the channel, because a print catalogue is often paid in one invoice while search ads are billed continuously. Start and end dates are not part of this lab, so treat this as an idea for your own tracker.

Keeping the Spent column honest

A tracker is only as good as its Spent column. In practice the numbers come from several places: the billing pages of the ad platforms, invoices from the print shop and the influencer agency, the email tool’s monthly fee and expense claims for things like webinar equipment. Each source updates at a different speed. Ad platforms show spend almost in real time, while a printer may invoice weeks after delivery.

Three habits keep the sheet reliable. First, agree on one update day, for example every Monday morning, and write the “last updated” date at the top of the sheet. Second, keep a simple log on a second sheet with one row per invoice or billing export (date, campaign, supplier, amount) and calculate Spent from it with =SUMIF(Log!B:B,A2,Log!D:D), so that every number can be traced back to a document. Third, never overwrite the Planned column when a budget changes: add a note or a separate “Revised plan” column, so the original approval stays visible and the change can be explained later.

These habits sound administrative, but they are what makes a coordinator’s numbers trusted. When a manager asks why Summer Catalog is 340 over, you can open the log, point to the second print invoice and answer in one sentence instead of promising to check.

Excel for marketing coordinators course: campaign budget tracker with planned vs spent
Excel for Marketing Coordinators: from the Q2 budget tracker in lesson 1 to a Q3 marketing dashboard in lesson 11.

Common mistakes and how to spot them

Budget trackers rarely fail because of hard formulas. They fail because of small inconsistencies that nobody notices until a meeting. Here are the usual suspects.

  • Remaining calculated the wrong way round. =D2-C2 makes healthy campaigns negative. Spot it: the campaign with the most money left shows the most negative number.
  • % used upside down. =C2/D2 returns planned ÷ spent. Spot it: the over-budget campaigns show less than 100%.
  • Using >= in the status test. Spot it: Trail Influencers, exactly on budget, shows “Over”.
  • Averaging the percentages. =AVERAGE(F2:F7) gives 94.2% instead of 98.8%. Spot it: the total % used does not equal total spent ÷ total planned.
  • Typed numbers in formula columns. Someone overwrites a formula with a value, and it stops updating. Spot it: press Ctrl + ` (Show Formulas, under Formulas → Show Formulas) and look for numbers in E, F or G.
  • Mixing tax-inclusive and net amounts. If some invoices include sales tax or VAT and the plan does not, spending looks higher than it is. Spot it: a campaign with one supplier jumps by a suspiciously round percentage. Decide on one basis with finance and note it on the sheet.
  • Commitments not counted. A booked catalogue print run that has not been invoiced yet is money that is already gone. Spot it: % used suddenly jumps late in the quarter. Some teams add a “Committed” column next to Spent.

Quick reference: functions and shortcuts

Everything used in this lesson at a glance:

Function or shortcutWhat it does hereExample
SUMAdds a column of budgets=SUM(C2:C7)
IFReturns one word if a test is true, another if not=IF(D2>C2,"Over","OK")
COUNTIFCounts cells that match a condition=COUNTIF(G2:G7,"Over")
SUMIFAdds cells that match a condition=SUMIF(E2:E7,"<0")
Alt + = (Windows) / Cmd + Shift + T (Mac)AutoSum for the selected cellsSelect C8:E8, press the shortcut
Ctrl + D / Cmd + DCopies the top formula down the selectionSelect E2:E7, press Ctrl + D
Ctrl + Shift + %Percent formatF2:F8
Home → Conditional Formatting → Highlight Cells RulesColours cells automaticallyText that Contains “Over” → light red fill
Ctrl + T / Cmd + TTurns the range into an Excel Table with automatic formula fillClick in A1:G7, press Ctrl + T

Real-world uses

The same planned-versus-spent logic appears in almost every marketing role.

  • Weekly team meeting. The coordinator shares the tracker with the status column filtered to “Over”, so the discussion starts with the campaigns that need a decision.
  • Agency billing check. When an agency invoice arrives, it goes into the Spent column first. If a campaign suddenly shows “Over”, the invoice is checked before it is approved.
  • Quarter-end close. Finance asks for the total marketing spend against plan. The total row answers it, and the campaign rows explain the difference.
  • Reallocating money. The webinar has 440 left, the search campaign is 420 over. The tracker makes it easy to propose moving budget between campaigns, with numbers.
  • Small-business owners. One person running ads, email and a printed flyer can use the same six columns to see whether marketing stays within what the business can afford.

What you will build in the rest of this course

Lesson 1 is free. The other nineteen lessons are part of the paid course (a one-time 9.90 USD) and follow Fernleaf Outdoor through the rest of the year, so each lab adds a new tool to your marketing toolkit.

Paid Channel Metrics: CTR, CPC, CPM, Conversion Rate, CPA and ROAS. You turn a paid media export into the six numbers every marketing meeting asks for, across four ad channels, and learn which ones divide by clicks, impressions or conversions.

Email Campaign Stats: Delivered, Open, Click and Unsubscribe Rates. You report five newsletters with the right denominators and find the email that cost the most subscribers.

Social Media Content Calendar: Posts per Platform and Week. You count planned posts per platform and week with COUNTIFS and highlight the weeks that fall below the posting target.

Lead Funnel: Step Conversion and Visitors Needed. You follow visitors through the funnel, find the step where most people drop out and work back to the traffic needed for next month’s sales target.

A/B Test: Rates, Lift and a Sanity Check. You compare two subject lines, calculate the lift of variant B and run a simple check on whether there is enough data to trust the result.

Cleaning a Lead List: TRIM, PROPER, LOWER and Duplicates. You clean a real-looking sign-up sheet with TRIM, PROPER and LOWER and remove duplicates before it goes into the email tool.

UTM Link Builder: Joining Text into Tracking Links. You join text with formulas to create consistent tracking links, so web analytics can tell which source and campaign brought each visitor.

Event and Webinar ROI: Costs, Show-up Rate and Return. You add up all costs of an in-store talk streamed as a webinar, measure the show-up rate and work out whether it paid back.

Monthly Marketing Report: Growth %, Conversion and a Trend Chart. You prepare a monthly report with month-over-month growth, the conversion rate, a quarter comparison and a revenue trend chart.

Q3 Marketing Dashboard: ROAS by Channel, Chart and Highlights. The finale: a one-screen dashboard with spend, revenue and ROAS by channel, a comparison chart and flags for channels below target.

Every lab is a real spreadsheet in your browser with step-by-step tasks and a Coach that checks each cell and explains typical mistakes, so you learn the formulas by using them on realistic marketing data.

Summary

A campaign budget tracker needs only a few formulas: remaining = planned − spent (=C2-D2), % used = spent ÷ planned (=D2/C2), a status with =IF(D2>C2,"Over","OK"), totals with SUM, and a total % used from the totals, not from an average. For Fernleaf that shows two campaigns over budget and 98.8% of the total budget used. Add conditional formatting and, if you can, a pacing column, and the tracker answers “how much do we have left?” before anyone asks.

Now scroll back up to the lab and build the tracker yourself: fill E2:G7 and the total row and let the Coach check every cell. It takes about fifteen minutes, and it is the first sheet most marketing coordinators are asked to own.

Frequently asked questions

How do I track a marketing budget in Excel?

List each campaign with its planned and spent amount, then add columns for remaining (=C2-D2), % used (=D2/C2) and a status with IF. Total the columns with SUM and divide total spent by total planned.

What is the formula for budget remaining in Excel?

Subtract spent from planned, for example =C2-D2. A negative result means the campaign is over budget.

How do I show “Over” when a campaign is over budget?

Use =IF(D2>C2,"Over","OK"). Use > rather than >= so that a campaign that spent exactly its budget shows OK.

How do I calculate the percentage of budget used?

Divide spent by planned, =D2/C2, and format the cell as a percentage. For the total, divide total spent by total planned instead of averaging the campaign percentages.

How can I highlight over-budget campaigns automatically?

Select the status column and use Home, Conditional Formatting, Highlight Cells Rules, Text that Contains, then type Over and choose a fill colour.

What is budget pacing in marketing?

Pacing compares the share of a budget already spent with the share of the campaign’s time that has passed. Spending far ahead of the calendar means the budget may run out before the campaign ends.

Keep going: Excel for Marketing Coordinators: Budgets, Campaign Metrics & Reports

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.

  1. Paid Channel Metrics: CTR, CPC, CPM, Conversion Rate, CPA and ROAS
  2. Email Campaign Stats: Delivered, Open, Click and Unsubscribe Rates
  3. Social Media Content Calendar: Posts per Platform and Week
  4. Lead Funnel: Step Conversion and Visitors Needed
  5. A/B Test: Rates, Lift and a Sanity Check
  6. Cleaning a Lead List: TRIM, PROPER, LOWER and Duplicates
  7. UTM Link Builder: Joining Text into Tracking Links
  8. Event and Webinar ROI: Costs, Show-up Rate and Return
  9. Monthly Marketing Report: Growth %, Conversion and a Trend Chart
  10. Q3 Marketing Dashboard: ROAS by Channel, Chart and Highlights
  11. From Raw Export to Summary: SUMIFS by Channel and Month
  12. Mapping Table: Grouping Messy Source Names with XLOOKUP and IFNA
  13. Campaign Timeline: Durations, Creative Deadlines with WORKDAY and Campaigns Live per Week
  14. Vendor Invoices: Matching to Purchase Orders with XLOOKUP and Days Overdue
  15. Social Post Performance: Engagement Rate, Averages by Format and the Top Post
  16. Email Segments: Opt-Ins, Segment Rules and Contacts per Region
  17. Event Kit Inventory: Needed Quantities, Shortfalls and Orders in Full Packs
  18. Event Feedback Survey: Score Groups, the Score Distribution and NPS
  19. Final Project: The Quarterly Marketing Pack – Budget Use, CPA, ROAS, Review Flags and a Combo Chart

Buy course — $9.90 See the full course