Last February I gave you some handy Excel tips – here’s some more! Today I’m talking about array formulae.

It’s a formula that takes in a bunch of cells (ie an array) where a normal formula would take in just one cell. An array formula will look a bit like this:

{=SUM(*Stuff You Can Usually Only Do to One Cell*)}

It can do operations over an array of cells (even if the operation usually only works with one cell), then sums (or averages, or counts, etc) over these.

Unlike normal formulae, you can’t just click away from the cell or press enter – you have to press control, shift and enter (on Windows) or command and return (on Mac) to let Excel know it’s an array formula. Excel adds curly brackets around the formula to show it’s not normal.

Here’s a simple example – you’ve got the impressions and average positions for a bunch of queries, and you want the total.

Total impressions is easy – it’s just SUM(B:B). But the average position for your queries isn’t the sum of the positions (that would be 54.88, which is obviously wrong!) and it’s not just the average of the average positions – it needs to be weighted by number of impressions. So on each row you want to multiply position by impressions, then add those together, then divide by the total impressions.

You could add a column of ‘Avg. position * Impressions’ and sum over it. Or you could do the same thing in one cell with an array formula

=SUM(B3:B17*C3:C17)/SUM(B3:B17)

Press control, shift and enter (or command and return) to make it into an array formula. That gives you the average position for everything.

Note that array formulae won’t work with arrays like B:B – you have to specify just the cells you want to use.

Say you’re looking at search query reports for a long time period to look for long term trends, new positive or negative keywords, and so on. There’s now an Added/Excluded column which says ‘Excluded’ if there’s a negative keyword exactly matching a query, but there’s nothing to tell you if a search query is blocked by a broad or phrase negative keyword that only matches part of it.

Well now you can use array formulae to see if a cell contains words from a list of cells – which means you can see which queries your negatives are already blocking.

Alternatively, you could use array formulae to check that you aren’t accidentally blocking a positive keyword with a negative keyword. (Microsoft adCenter already offers Negative Keyword Conflicts reports, but AdWords doesn’t have this functionality.)

First download your search term report into Excel.

(If you want a negative keyword conflict checker, the steps are the same – put your positive keywords into Excel, with the keyword text in column A.)

Then add a second worksheet, name it ‘Negatives’, and paste in your negative keywords from AdWords Editor.

Go back to the ‘Search term report’ worksheet. The search terms will start in row three, so go to the first clear cell in that row, where you can enter the right formula.

First you write a statement to find out if the search term (in A3) was excluded by just the first negative keyword (in Negatives!C2).

=ISNUMBER(FIND(Negatives!C2, A3))

The FIND formula will see if Negatives!C2 is in A3. If it is, FIND() will return a number (the location of the negative keyword in the search term) and if it isn’t it will return an error. We don’t care about where the negative appears in the search term, just whether it appears at all – so we wrap the FIND() statement in ISNUMBER(), which will return TRUE if the negative is found and FALSE if it isn’t.

However, this method doesn’t work perfectly. The negative keyword ‘super’ will block search terms like ‘super fast’ and ‘super mario’ but not ‘superted’ or ‘supercalifragilisticexpialidocious’, even though the string ‘super’ is in all of them – it blocks terms with ‘super’ as a whole word.

So we can add spaces around the negative keyword and the search term. The string ‘ super ‘ is in ‘ super mario ‘ but not ‘ superted ‘, which is what we want.

=ISNUMBER(FIND(” “&Negatives!C2&” “,” “&A3&” “))

There’s still the problem that the negative keywords can be CaPItaLISed in any way, and the FIND() function won’t work if the negative has capitals where the search term doesn’t. So we force both negative and search term to be lower case using LOWER().

=ISNUMBER(FIND(” “&LOWER(Negatives!C2)&” “,” “&LOWER(A3)&” “))

But this still isn’t complicated enough! What if the negative is exact? If the negative is ‘super’ on exact match then it shouldn’t exclude ‘super mario’, only ‘super’. We need an IF() to check the match type of the negative.

=IF(RIGHT(Negatives!D2,5)=”Exact”,LOWER(Negatives!C2)=LOWER(A3),ISNUMBER(FIND(” “&LOWER(Negatives!C2)&” “,” “&LOWER(A3)&” “)))

Now we check if the negative match type is exact. If it is then we check if the search term and negative are precisely the same (when made lowercase). And if the match is phrase or broad, there’s the same ISNUMBER() statement we had from before.

Site note: Shouldn’t we treat broad match differently to phrase? Maybe, but:

a) We don’t know precisely how negative broad match behaves

b) *This thing is complicated enough already.*

So we finally have a formula that can check a search term against a negative. Now we turn it into a formula that checks the search term against *all* negatives.

Array formulae have to sum something, so we use an IF() to make the formula output a number rather than TRUE or FALSE.

=IF(IF(RIGHT(Negatives!D2,5)=”Exact”,LOWER(Negatives!C2)=LOWER(A3),ISNUMBER(FIND(” “&LOWER(Negatives!C2)&” “,” “&LOWER(A3)&” “))),1,0)

Then we change the references to cells in the Negatives worksheet into references to arrays, and put a SUM around the whole thing

=SUM(IF(IF(RIGHT(Negatives!$D$2:$D$12,5)=”Exact”,LOWER(Negatives!$C$2:$C$12)=LOWER(A3),ISNUMBER(FIND(” “&LOWER(Negatives!$C$2:$C$12)&” “,” “&LOWER(A3)&” “))),1,0))

Then press control, shift and enter (or command and return) – curly brackets should appear.

Voilà! If the number is 0 your search term isn’t blocked by any negatives, if there’s a number that’s how many negative keywords conflict with the term.

You can then copy and paste down the column to check each search query.

The main problem is that this is a bit calculation heavy, and Excel may keel over if you try it with too many search terms or negatives. Be careful if you’re using this on big ad groups!

Also, there’s nothing to deal with punctuation – if either your keywords or search terms have punctuation that could stop Excel finding a match. You may want to use Find and Replace to replace punctuation with spaces.