Excel Cheat Sheet for Keyword Marketers

27th November 2018 - 35 minutes read  Data & AnalyticsTips & Tricks
Richard Lewis - SEO Lead

Excel Formulas & VBA for Keyword Marketers

Here’s a list of some of the less well known Excel formulas and VBA modules that regularly come in handy for keyword marketers. That could be SEOs, PPCs or anyone who works with large spreadsheets containing keywords and associated data like search volume, CPC & categories. Think of it as an excel cheat sheet for marketers.




Coming soon: Numbered index, excel gifs.

Get domain from URL:


This works by bringing back everything to the left of the first trailing slash found after the initial 2 in ‘http..://’, which in a URL is the slash occurring after the TLD.


Get subdomain from URL:


When you just need the subdomains in a big list from a bunch of differently formatted URLs. This formula works regardless of the presence of the protocol. What it lacks in elegance, it more than makes up for in usefulness.


Remove first X characters from cell:


If there’s something consistent that you want to remove from the front of data in your cells, such as an html tag like <title>, you can use this to remove it by specifying its length in characters in this formula, so X would be 7 in this case.


Remove last X characters from a cell:


You might use this to remove the trailing slash from a list of URLs, for example, with X as 1.


Group keyword phrases automatically based on words they contain:


This little chap deserves a blog post all its own. In SEO you have to put keywords into groups to target them properly on landing pages. It takes ages and is actually quite hard. The intuitive method is to use Excel’s filtering function to check for the presence of a word in a keyword e.g. ‘blue’ in ‘light blue shirts’, but for large keyword sets this has been known to jeapordise employee sanity.

With this formula, you can fill out your ‘group’ column with instances of the formula, referring to the list of possible groups which you have in a separate table outside your main keyword data table, and the group column gets populated all at once with the matching groups based on what the keyword contains. It’s like you had teams of people using the filtering method all at the same time. With AI natural language processing still falling wide of the mark as of 2019, this formula remains the fastest way to do it, cutting the time needed to accurately group keywords by a wide margin.


Using the formula to group your keywords:

  • $K$6:$K$20 is your string-to-search-for range (the list of all the possible words you want to check for in the keyword).
  • $L$6:$L$200 is your Label to return when string found, put it in the next column along lined up (this can just be the word you’re checking for if you want – same as above)
  • B5 is the cell containing the keyword string which you are searching to see if it contains any of the listed strings so you can label it as such
  • “/” is what gets returned when none of the strings are matched

Using the formula


For geeks: How the formula works.

The remarkable thing about this formula is that it takes an unlikely combination of functions to do something for which Excel was never intended, brilliantly well.

Let’s say we want to find out which colour category a keyword belongs in. We have a spreadsheet laid out like this, and want the result of the formula in B2 to be the group ‘blue’:


Here’s the formula again:


Now we’re going to take it apart and re-build it, step by step. Let’s ignore the IFERROR and concentrate on the core functions, starting from the middle:


SEARCH – Returns the starting location of a text string inside another text string:

=SEARCH(find_text, within_text)
=SEARCH(get the position number of the start of this, within this)

For example, the SEARCH formula below returns ‘4’ because ‘def’ begins from the 4th character in ‘abcdef’:


Nice & simple.

SEARCH is a great way of telling if something is in something else, which is the core purpose of what we’re trying to do. If it brings back a number, we know what we’re looking for is in there.

But in our grouping formula, find_text isn’t a single value or cell reference – it’s a range of cells. So let’s try that now, but with SEARCH on its own, with C2:C4 as colour categories ‘red,’ blue’, ‘green’ and A2 as the keyword ‘light blue shirt’:


If this worked like before, we’d expect to get a ‘7’ as the result because matching ‘blue’ starts at position 7 in the keyword. Instead we get a #VALUE! error because the search function on its own doesn’t take ranges – at least not on its own. It needs a friend.


LOOKUP – There’s a reason you haven’t used it before – it’s a bit weird and not that useful. It’s like VLOOKUP’s ne’er-do-well brother who has lucked out with a better name. But it turns out that LOOKUP was born with a special talent:

When lookup_value (what we’re looking for) is greater than all values in lookup_vector (where we’re looking for it) and therefore cannot be found, LOOKUP matches the last non-error value in lookup_vector.

=LOOKUP(lookup_value, lookup_vector)
=LOOKUP(what we're looking for, where we're looking for it)
=LOOKUP(2^15, C2:C4)

Don’t expect to get this now, all will be explained in due course. All you need to know for now is that it just so happens that this ability of LOOKUP is exactly what we need.

The lookup_value we are going to use is 2^15, becaue this is the lookup_value which satisfies the criteria mentioned above of ‘can never be found’. I’ll explain why shortly.

Where exactly are we looking for this lookup value which must never be found? This is where search comes in.

In the full grouping formula, the SEARCH formula is itself the lookup vector (the place we’re looking for the lookup value):


The beauty of the teamwork between SEARCH and LOOKUP is that once it’s placed within LOOKUP, SEARCH starts to up its game and can evaluate using a range argument rather than being restricted to a single value or cell reference like it was in our example earlier. The result of the SEARCH formula within LOOKUP is now not a single number, but an array of numbers and error values, which can be seen using the formula evaluation tool in Excel.

1. The lookup value 2^15 is evaluated to 32,768

This is why we used 2^15 – it’s simply mathematical notation for 32,768, the maximum number of characters Excel will permit inside 1 cell, so no starting position returned by SEARCH can ever be higher and therefore the lookup value can never be found. This is what kicks in the ‘matches the last non error value’ ability of LOOKUP.

2. The SEARCH range is evaluated to an array of the values in the range, as seen in the {curly brackets}:

3. Then, the SEARCH function is performed on ‘light blue shirt’ for each item in the array, with the result of this in each case either being a number representing the starting position of the array item in the keyword, or a #VALUE! error indicating the item was not found:

So what’s the result of this formula? It’s 7, because of LOOKUP’s special talent – matching the last non-error value.

But ‘7’ isn’t much help in our keyword grouping. Luckily, LOOKUP has another special talent – you can add a ‘result_vector’ range to the end of it, and the value returned will be the item in this range which is on the same row as the value which LOOKUP matches from the SEARCH array. That was a cool sentence. We haven’t got much time in our example so let’s just use the same range as the lookup_vector that contains the colours:


Then we wrap an iferror around it to tidy it up and hey presto, we have quite possibly the cleverest formula in any digital marketer’s bag of tricks:


Now when your boss asks you how it works, you can tell them rather than just pretending to know.


Bonus: Want to keep adding strings and labels to the bottom of your group list as you go along, without editing the formula ranges? You can do it using the below variant of the formula – it will let you add new strings and labels for the formula to search for on the fly. If you imagine your strings list is in column G and your labels list is in column H:



Alternatively, you can fill your lookup vector ranges with nonblanks that won’t match any keywords and overwrite them as you go.


Word count:

See how many words are in your keyword to identify if it’s long tail and get a measure of potential intent.

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)


The simplest way to get TRUE / FALSE for ‘value in list’ or ‘not in list’, instead of using vlookup:

This is my favourite, so often we just need to know if URLs in list A are contained within list B. No need to count vlookup columns or iferror.



Get TRUE or FALSE if a word or string is in a cell:

If you fancy a break from using the ‘contains’ filter, this can be a way to get things done faster and in a more versatile way:



Remove first word from cell (all before & including 1st space):

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

To remove the last word instead, just use LEFT instead of RIGHT.


Replace the first word in a cell with another word:

=REPLACE(A2,1,LEN(LEFT(A2,FIND(" ",A2)))-1,"X")

“X” is the word you want to replace the incumbent first word with, or this can be a cell reference.



Super trim – more reliable trimming of spaces from cells:

Sometimes using =TRIM() fails because of an unconventional space character from something we’ve pasted into Excel. This gets them all:



Text to columns with space as the delimiter (can be anything) using a draggable formula instead, so it can be used in a template:

=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:A2)-1)*LEN($A2))+1,LEN($A2)))






Get the last path from a URL (everything after the last slash, not counting the trailing slash if present):

Good for when you need to get just the last portion of a URL, that pertains to the specific page:



Get the first folder from a URL:

Good for extracting language folder:




Remove all text after the Xth instance of a specific character:

Say you want to chop the last folder off a URL, or revert a keyword cluster to a previous hierarchy level. The “/” is the character where the split will occur, change it to whatever you want. The “-0” at the end chops off everything after the last instance. Changing it to -1 would chop off everything after the penultimate instance, and so on:



Create an alphabetical list of column letters for use in other formulas A,B,C…AA,BB etc:



Count the number of given character instances in a cell:

For when countif “*x*” doesn’t cut it:


example is for ” ” space character


Count the number of times a specific word appears in a cell:

The formula above works for individual characters, but if you need to count whole words this will work – handy for checking keyword inclusion in landing page copy for SEO:


In the example, B2 should contain the word you are counting the instances of within A2.


Show if there are no numbers in a string:

Change the end to >0 to reverse it.



Get the current column letter for use in other formulas:



Put your keywords into numbered batches for pulling seasonal search volume data:

To save you having to count out 2,500 keywords each time. This batches them up so you just have to filter for the batch number, ctrl A, ctrl C, ctrl V.


43 is the number of keywords in your list divided by 2500, which is the keyword planner limit

Use the blank row insertion macro to make the batches easily selectable


Word order flipper:

Excel is not built for string parsing and manipulation, but if you have to do it in Excel you can try this. Turns ‘dresses white wedding’ into ‘white wedding dresses’. Use it in steps inside itself to further rearrange words in a different order.

=TRIM(MID(F18,SEARCH(" ",F18)+1,250))&" "&LEFT(F18,SEARCH(" ",F18)-1)


Find the maximum numerical value in a row range, and return the column header:

Useful for when you need to know which month has the highest search volume (a2:f2 = the values, a1:f1 = the headers)



Find position of nth occurrence of character in a cell:





To use these:

  1. Save your workbook as .xlsm
  2. Reopen it and hit alt + f11
  3. In the menu, insert > module
  4. Paste in the code
  5. Press the play button

There’s no need to understand the code. But be careful to save a backup copy of your workbook before running any of these – they can’t be undone with ctrl + z!


Convert all non-clickable URLs in your spreadsheet to  clickable hyperlinks:

So you can visit the URLs easily if you need to e.g. for optimisation of a lot of pages, so you don’t have to mess about double clicking each one to get it ready.

Sub HyperAdd()

For Each xCell In Selection
 ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
 Next xCell

End Sub


Conditional formatting by row value:

So the colour intensity is relative to each row only, rather than the entire range. You need to use this to complete the search landscape document seasonality tab.

Sub NewCF()
 For Each r In Selection.Rows
 r.PasteSpecial (xlPasteFormats)
 Next r
 Application.CutCopyMode = False
 End Sub


Remove duplicates individually by column:

If you have a lot of columns, each of which needs duplicates removing individually e.g. if you have a series of category taxonomies to clean – you can’t do this from the menu:

Sub removeDups()
 Dim col As Range
 For Each col In Range("A:Z").Columns
 With col
 .RemoveDuplicates Columns:=1, Header:=xlYes
 End With
 Next col
 End Sub


Merge adjacent cells in a range based on identical value:

To save you doing it individually when you need to make a spreadsheet look good:

Sub MergeSameCell()
 Dim Rng As Range, xCell As Range
 Dim xRows As Integer
 xTitleId = "KutoolsforExcel"
 Set WorkRng = Application.Selection
 Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
 xRows = WorkRng.Rows.Count
 For Each Rng In WorkRng.Columns
 For i = 1 To xRows - 1
 For j = i + 1 To xRows
 If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
 Exit For
 End If
 WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge
 i = j - 1
 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
 End Sub


Remove all instances of any text between and including 2 characters from a cell (in this example, the < and >):

Especially good for removing HTML tags from screaming frog extractions, kind of a stand in for regex.

Public Function DELBRC(ByVal str As String) As String
 While InStr(str, "<") > 0 And InStr(str, ">") > InStr(str, "<")
 str = Left(str, InStr(str, "<") - 1) & Mid(str, InStr(str, ">") + 1)
 DELBRC = Trim(str)
 End Function


Highlight mis-spelled words:

This can help you identify garbled / nonsense keywords from a large set, or just to spellcheck in Excel if you need to.

Sub Highlight_Misspelled_Words()
 For Each cell In ActiveSheet.UsedRange
 If Not Application.CheckSpelling(Word:=cell.Text) Then cell.Interior.ColorIndex = 3
 End Sub


Lock all slicers in position:

If you send Excel documents to clients with slicers in them, you might worry that they’ll end up moving the slicers around while trying to use them – a poor experience which makes your document feel less professional.  But there’s a way around it – run this code and your slicers will be locked in place across all worksheets, while still operational. This effect persists when the document is re-saved as a normal .xlsx file.

Option Explicit

Sub DisableAllSlicersMoveAndResize()

 Dim oSlicerCache As SlicerCache
 Dim oSlicer As Slicer
 For Each oSlicerCache In ActiveWorkbook.SlicerCaches
 For Each oSlicer In oSlicerCache.Slicers
 oSlicer.DisableMoveResizeUI = True
 Next oSlicer
 Next oSlicerCache
End Sub


Slice’n dice – Split delimited values in a cell into multiple rows with key column retained:

It’s easy to put a delimited string (Keyword,Volume,CPC…) into columns using text-to-columns but what if you want it split vertically instead, into rows? This can help:

Sub SliceNDice()
 Dim objRegex As Object
 Dim X
 Dim Y
 Dim lngRow As Long
 Dim lngCnt As Long
 Dim tempArr() As String
 Dim strArr
 Set objRegex = CreateObject("vbscript.regexp")
 objRegex.Pattern = "^\s+(.+?)$"
 'Define the range to be analysed
 X = Range([a1], Cells(Rows.Count, "b").End(xlUp)).Value2
 ReDim Y(1 To 2, 1 To 1000)
 For lngRow = 1 To UBound(X, 1)
 'Split each string by ","
 tempArr = Split(X(lngRow, 2), ",")
 For Each strArr In tempArr
 lngCnt = lngCnt + 1
 'Add another 1000 records to resorted array every 1000 records
 If lngCnt Mod 1000 = 0 Then ReDim Preserve Y(1 To 2, 1 To lngCnt + 1000)
 Y(1, lngCnt) = X(lngRow, 1)
 Y(2, lngCnt) = objRegex.Replace(strArr, "$1")
 Next lngRow
 'Dump the re-ordered range to columns C:D
 [c1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y)
 End Sub


Make multiple copies of a worksheet at once:

If you are making a reporting template for example, and want to get the sheets for all 12 weeks created in one go:

Sub swtbeb4lyfe43()

ThisWS = "name-of-existing-worksheet"
 '# of new sheets
 s = 6
 '# of new sheets
 For i = 2 To s
 Worksheets("name-of-existing-worksheet-ending-with-1").Copy After:=Worksheets(Worksheets.Count)
 ActiveSheet.Name = ThisWS & i
 Next i
 End Sub


Add a specific number of new rows based on cell value:

Saves repeatedly using insert row, pressing F4 etc:

Sub test()
 On Error Resume Next
 For r = Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1
 For rw = 2 To Cells(r, "E").Value + 1
 Cells(r + 1, "E").EntireRow.Insert
 Next rw, r
 End Sub


Column stacker:

This one’s great when you have lots of columns of information that you want to be combined all into one master column:

Sub ConvertRangeToColumn()
 Dim Range1 As Range, Range2 As Range, Rng As Range
 Dim rowIndex As Integer
 xTitleId = "KutoolsforExcel"
 Set Range1 = Application.Selection
 Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
 Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
 rowIndex = 0
 Application.ScreenUpdating = False
 For Each Rng In Range1.Rows
 Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
 rowIndex = rowIndex + Rng.Columns.Count
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
 End Sub


Superfast find and replace for huge datasets:

To match partial cell, change to ‘X1Part”.

Sub Macro1()

Application.EnableEvents = False
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual

' fill your range in here
 ' choose what to search for and what to replace with here
 Selection.Replace What:="0", Replacement:="/", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Application.EnableEvents = True
 Application.ScreenUpdating = True
 Application.Calculation = xlCalculationAutomatic

End Sub


Paste all cells as values in a worksheet in the active range:

For when your spreadsheet is too slow to do it manually

Sub ToVals()
 With ActiveSheet.UsedRange
 .Value = .Value
 End With
 End Sub


Format all cells to general format or whatever you like, without having to select them:

Another good one for when your spreadsheet is too slow.

Sub dural()
 ActiveSheet.Cells.NumberFormat = "General"
 End Sub


Formula activation – Insert equals at the beginning for a range of cells:

If you’re making something complex with a lot of formulas that you don’t want switched on yet, but you want to be able to use other formulas at the same time (i.e. can’t turn off calculations) this can help. It’s also good for just adding things to the start of cells:

Sub Insert_Equals()

Application.ScreenUpdating = False

Dim cell As Range

For Each cell In Selection
 cell.Formula = "=" & cell.Value
 Next cell

Application.ScreenUpdating = True

End Sub


Consolidate all worksheets from multiple workbooks in a folder on your computer into a single workbook with all the worksheets added into it:

If you have a big collection of workbooks which you want consolidated into one, you can do it in a single step using this macro. Especially good for when the workbooks you need to consolidate are big and slow.

Sub CombineFiles()

Dim Path As String
 Dim FileName As String
 Dim Wkb As Workbook
 Dim WS As Worksheet

Application.EnableEvents = False
 Application.ScreenUpdating = False
 Path = "C:\scu" 'Change as needed
 FileName = Dir(Path & "\*.xl*", vbNormal)
 Do Until FileName = ""
 Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
 For Each WS In Wkb.Worksheets
 WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
 Next WS
 Wkb.Close False
 FileName = Dir()
 Application.EnableEvents = True
 Application.ScreenUpdating = True

End Sub


Fast deletion of named columns in a spreadsheet which is responding slowly:

Sometimes, one does not simply ‘delete a column’. This is for those times.

Sub Delete_Surplus_Columns()

Dim FindString As String
 Dim iCol As Long, LastCol As Long, FirstCol As Long
 Dim CalcMode As Long

With Application
 CalcMode = .Calculation
 .Calculation = xlCalculationManual
 .ScreenUpdating = False
 End With

FirstCol = 1
 With ActiveSheet
 .DisplayPageBreaks = False

LastCol = .Cells(3, Columns.Count).End(xlToLeft).Column

For iCol = LastCol To FirstCol Step -1

If IsError(.Cells(3, iCol).Value) Then
 'Do nothing
 'This avoids an error if there is a error in the cell
 ElseIf .Cells(3, iCol).Value = "Value B" Then
 ElseIf .Cells(3, iCol).Value = "Value C" Then
 End If

Next iCol

End With

With Application
 .ScreenUpdating = True
 .Calculation = CalcMode
 End With

End Sub


Find and replace based on a table in another worksheet:

Use X1Part for string match & replace within a cell, or X1 whole for whole cell match & replace:

Sub Substitutions()

Dim rngData As Range
 Dim rngLookup As Range
 Dim Lookup As Range

With Sheets("Sheet1")
 Set rngData = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
 End With

With Sheets("Sheet2")
 Set rngLookup = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
 End With

For Each Lookup In rngLookup
 If Lookup.Value <> "" Then
 rngData.Replace What:=Lookup.Value, _
 Replacement:=Lookup.Offset(0, 1).Value, _
 LookAt:=xlWhole, _
 SearchOrder:=xlByRows, _
 End If
 Next Lookup

End Sub



  • If you have a slow spreadsheet that’s locked up Excel while it’s calculating, but you still need to use Excel for other stuff, you can open a completely new instance of Excel by holding Alt, clicking Excel in the taskbar, and answering ‘yes’ to the pop up box. This isn’t just a new worksheet – it’s a totally new instance of Excel.
  • To open a new workbook in the same instance of Excel a bit more quickly than usual when you already have workbooks open, you can use a single middle mouse click on Excel in the taskbar.




We’ve written some other blog posts about excel, you can find them here: