Campaign spend in Excel: actual against the plan you typed
A campaign spend sheet is one row per campaign in the period you are closing. You type the campaign, the channel, the planned amount, and the actual amount. Both amounts are numbers you typed, in the currency of this file. The variance is actual minus planned, and it stays blank if either amount is missing. The flag is a word: over when actual is above planned, under when actual is below planned, on when they match. SUMIFS adds actual by the channel you typed. COUNTIFS counts the rows marked over. This sheet is not a performance report. Clicks, revenue, and a result story answer whether the campaign worked. Those numbers belong on a campaign analysis sheet. Here the question is the money. A note can mention a result. It does not change the variance. Do not put revenue in the actual column. Actual is spend. The sheet does not pull figures from an ad platform, an agency, or a bank. You type the actual amount, or you paste it as a value from an export or an invoice you have already checked. IF only subtracts the two amounts on the row. If a second invoice arrives, add it into the actual total on the same row, or replace actual with the new total you trust. Do not add a row because another invoice arrived. A line-by-line invoice log can live on another sheet. This sheet keeps one total per campaign. A blank planned or a blank actual does not become on. Blank means an amount is missing. The flag stays blank until both amounts are typed. Zero actual against a planned amount above zero is under. That is a real gap, not a missing cell. Before you tell someone a channel is over, read three campaigns you can check against the export or the invoice. Planned must be the amount you agreed. Actual must be the spend total you are willing to add. Elyx can write the variance and the sums. You still decide what to stop. The trial lasts 7 days, with no card.
The Problem
The sheet fails when actual is a sentence. "Roughly on track, waiting on one invoice" cannot be subtracted from a plan. Put the sentence in the note. Put a number in actual. The flag reads the number. It also fails when each invoice starts a new row for the same campaign. You then have several actuals, and a sum of the column double-counts the campaign if you also kept a total row. One campaign, one row, for this period. You edit actual in place. The invoice log, if you need every line, is a different sheet. A third failure is mixing a company budget with campaign spend. A company budget has rent, payroll, and lines that are not campaigns. This sheet is campaigns only. If a cost is not a campaign, it does not get a row here. Putting it here makes the channel sum a story about the whole company. A fourth failure is treating the variance as a return. Over means you typed a spend above the plan you typed. It does not say the campaign failed, and under does not say it worked. Results live on the analysis sheet. The sum of actual is the sum of amounts you typed. It is not a platform balance you did not paste.
Benefits
One row per campaign in the period, edited in place when the total changes.
Over, under, and on compare two amounts you typed. They do not judge the result.
A missing amount stays blank, so an unknown invoice is not marked on.
SUMIFS adds actual by the channel word you typed.
Invoice lines stay on a log, so the sheet does not grow a row per bill.
Step-by-Step Tutorial
Lay out one row per campaign
Name the sheet Spend. In a cell named Period, type the period this sheet closes, in words you will recognise later. Headers in row 1: Campaign, Channel, Planned, Actual, Variance, Flag, Note. Campaign is the name you already use, unique on this sheet for this period. Channel, Planned, Actual, and Note are entries. Variance and Flag are formulas. One campaign occupies one row until you close the period. Add a row when a campaign joins the period. Do not add a row because a new day of spend arrived, and do not add a row because an invoice arrived. The next period is a new sheet, or a copy you archive. Do not stack two periods on one sheet and then forget to filter. If you truly need two periods in one file, give each row a period column and make every sum include that column. This guide uses one period per sheet, named in Period, so the sums at the top are the period.
Sort by Campaign and scan for duplicates before you rely on the total. Two rows for one campaign will be added twice.
Restrict the channel words
On a sheet named Lists, type the channel words you will actually use. Three is enough to start: search, social, and other. Apply data validation to the Channel column so only those words can be entered. Search means the spend you class as search. Social means the spend you class as social. Other means you have not given it a tighter word yet. If you need email, add email to the list and add a SUMIFS that uses that exact word. Do not invent a channel in the note and expect the sum to find it. The sum reads the channel cell. Agree the list before the first close. A capital letter or a trailing space will miss the sum.
Validation stops new typos. Clean any channel that was typed before the list existed.
Type the planned amount you agreed
Planned is the amount you are measuring against, for this campaign, in this period, in the currency of the file. Type it as a number. Leave it blank if the plan is not agreed. Do not type "TBD" or a range such as "2 to 3". A range is two numbers, and this column holds one. If the plan changes, replace the number and say so in the note. The old plan is not kept on the row. Do not store the plan in a different currency from actual. Convert before you type, and write the currency once above the sums. The formula will not convert.
If the plan is a monthly cap and the sheet is the month, type the cap. Do not type a daily cap and then compare it with a month of spend.
Type the actual spend you have checked
Actual is the spend total you trust for this campaign in this period. It comes from an invoice you checked or from an export you pasted as values, not as a live link the sheet maintains. Leave it blank if you have not checked a figure. Zero means you typed zero, which is a claim that there was no spend. Do not type a platform name in the amount cell. Do not type revenue here. If a later invoice belongs to the same campaign, add it to the total or replace the total. Keep the invoice identity in the note or on the log. Format Actual as a number. Text in the column will be ignored by SUMIFS, and the row will not warn you with a flag until both amounts are numbers.
Paste exports as values. A formula that points at a file you do not still have will show a number you cannot defend at the close.
Show the variance only when both amounts exist
With Planned in C2 and Actual in D2, the variance is =IF(OR(C2="",D2=""),"",D2-C2). A missing amount stays blank. A positive variance means actual is above planned. Zero means they match. A negative variance means actual is below planned. Format Variance as a number in the currency of the file. Do not format it as a percent unless you add a separate column that divides the variance by planned, and only when planned is not zero. The flag in the next step reads the amounts, not a percent. Keep the percent, if you want one, out of the count.
=IF(OR(C2="",D2=""),"",D2-C2)A percent of a blank plan is not a useful flag. Leave the flag on the two amounts.
Flag over, under, or on
The flag is =IF(OR(C2="",D2=""),"",IF(D2>C2,"over",IF(D2<C2,"under","on"))). Both amounts must be present. Actual above planned is over. Actual below planned is under. Equal amounts are on. The word on does not mean the campaign worked. It means the two amounts you typed match. The count of campaigns over budget is =COUNTIFS(F:F,"over"). Read that count as a count of rows, not as a sum of money. The money is the next step. Copy the flag down every campaign row. A hand-typed word will not follow the amounts when you update actual.
=IF(OR(C2="",D2=""),"",IF(D2>C2,"over",IF(D2<C2,"under","on")))If you only want to chase rows that are over, filter Flag to over. Leave the under rows visible in a second view so an unspent plan is not forgotten.
Add actual by channel, and add the overage
Actual for a channel is =SUMIFS(D:D,B:B,"search"), with the word changed to the channel you typed. Build one sum per channel word on your list, and label each sum with that word. Total actual is =SUM(D:D) only if every number in the column is a campaign actual and you have no subtotal sitting in the column. Safer: =SUMIFS(D:D,B:B,"<>") so you add rows that have a channel. The overage, the sum of positive variances, is =SUMIFS(E:E,F:F,"over"). That sum is only the rows marked over. It is not total actual, and it is not total planned. Put the channel sums, the over count, and the overage at the top, each with a label. A single unlabeled total will be read as whatever the reader fears.
=SUMIFS(D:D,B:B,"search")If you add a channel word, add its SUMIFS in the same pass. A channel with no sum is spend you will not see at the top.
Check three campaigns before you publish the sums
Pick three rows. For each one, look at the export or the invoice you trust. Planned must be the amount you agreed for this period. Actual must match the spend total, not the revenue, and not a single invoice if more than one belongs to the row. Variance must equal actual minus planned when both are filled, and blank when either is empty. Flag must be over, under, or on in line with those amounts, and blank when an amount is missing. If one row fails, do not send the channel sums. Fix the cell. The sums are the sums of this sheet. They are not a platform's own total unless you have checked that the pasted figures add up to it. A results sheet can hold clicks and revenue. Do not paste those columns into Actual.
Read the period name in the Period cell before you send the sheet. A sum without a period is a number from whichever month was left open.
Template Features
One actual per campaign
Actual is the spend total you typed for this campaign in this period. Invoices, if you keep them line by line, stay on a log.
Variance only when both amounts exist
Actual minus planned is blank if either cell is empty. A missing invoice does not become a full underspend or a match.
=IF(OR(C2="",D2=""),"",D2-C2)Over, under, or on
Over means actual is above planned. Under means it is below. On means the amounts match. None of the three is a verdict on results.
=IF(OR(C2="",D2=""),"",IF(D2>C2,"over",IF(D2<C2,"under","on")))Actual by channel
SUMIFS adds actual for one channel word. Each word on your list needs its own labeled sum.
=SUMIFS(D:D,B:B,"search")Overage as its own sum
SUMIFS adds variance only where the flag is over. That is the money above plan, not the whole spend.
=SUMIFS(E:E,F:F,"over")Concrete Examples
A campaign above the amount you typed as planned
You agreed 800 in the currency of the file. The export you checked shows 950 of spend. You want the row marked over.
Channel: search. Planned: 800. Actual: 950.
Result: Variance is 150. Flag is over. The over count includes the row. The search sum includes 950. The overage sum includes 150. The 150 is spend above plan. It is not revenue and it is not a rate.
A campaign with no actual yet
Planned is filled. You have not checked an invoice. You do not want the row marked on, and you do not want a variance equal to the whole plan as if it were unspent.
Channel: social. Planned: 400. Actual: empty.
Result: Variance is blank. Flag is blank. The over count does not include the row. The social sum does not include a number from this row. Type actual when you have a figure you have checked.
A second invoice for the same campaign
Actual was 950. A later invoice of 50 belongs to the same campaign in the same period.
You replace Actual with 1000 on the same row. Planned stays 800.
Result: Variance becomes 200. Flag stays over. You did not add a second row. The channel sum rises by 50 because the cell changed, not because a new campaign appeared.
Pro Tips
Campaign name is the key
People shorten names. If two teams share a name, add a code you already use. The sum cannot tell the rows apart from a nickname.
One currency in the amount columns
Convert before you type. Write the currency above the sums. A mixed column adds numbers that are not the same unit.
Fees you care about are part of actual, or they are a column
If the figure you type is media only, say so in the header. If it includes a fee you checked, say that too. Do not change the meaning halfway down the sheet.
Results stay on the analysis sheet
Clicks and revenue answer a different question. Link them by campaign name if you must, but do not subtract revenue from spend on this flag.