An introduction to using pivot tables in Excel with slicers to be able to better visualise your data.
This is perfect for anyone who is just starting out in SEO, and particularly in Excel, that wants to understand some foundations.
Creating a pivot table in Excel
You’ll need to get your data setup properly, and in a format that is appropriate for a table, and then you just create your pivot table off the back of it.
They’re super easy to create, once you get shown how the first time, as there are a couple different settings / options for them.
Adding a slicer to a pivot table in Excel
Once your pivot table is setup, you can move onto creating a slicer for the table.
These slicers essentially act like filters for the table, but they’re a lot easier to interact with as they’re just buttons.
Hopefully this video is useful to you, and you get some value out of it.
Estimating your organic traffic will allow you better assess your current performance for a set of keywords, but also allows you to directly compare that performance with your competitors.
In this video, I run through how to properly set up your data and run the formula across a set of categorised keywords. This will be the basis of future videos, on how we can better use this data.
The estimated traffic is being worked out by multiplying the search volume of the keywords, with the expected CTR at their current rankings.
=IFERROR([*CELL FOR SEARCH VOLUME*]*(IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(*CTR MODEL TABLE RANK COLUMN*,*CURRENT RANK*),*CTR MODEL TABLE CTR AT RANK COLUMN*),”N/A”)),0)
Removing the bulk of it, we essentially have the search volume, along with a lookup for the CTR at that rank. The lookup could be simplified with a vlookup if you want to, however, I choose to just use my categorisation formula for this.
This can be simplified to just a vlookup for the rank table, especially if you’re using whole rankings rather than GSC average rankings that have a decimal place.
Estimating SEO Traffic in Google Sheets
You can duplicate a Google sheet below which is 95% the same as the Excel above to get you started.
Discover the easiest ways to generate keywords in bulk using Excel. These formulas are the basis of so much work, and keyword generation really will just give you a top-level understanding of the formula’s use.
Concatenate – Join as many cells together as needed
=CONCATENATE(*First CELL to join*,*Second CELL to Join*)
Concatenation is a method that many people are using, and its just adding one cell to another. This can be useful to start things off, but there ampersand (&) does this too, a lot cleaner with a lot more flexibility.
So use this if you’re used to it, but I’d recommend switching to using the below ampersand (&) method.
& – The better way of joining items together
=*CELL to Join*&*CELL to Join*&”Some text to join on the end”
My preferred method, you literally just put an ‘&’ between each cell you want to work with.
So =A2&” “&B2 will output A2, a space, and B2. Nothing really complex about it, giving you full freedom.
Substitute – Swap text in a cell, for other text
=SUBSTITUTE(*CELL for text swap / the template*,*Old text for replacing*,*New text to replace with*)
Giving a little more customizability through template elements, the substitution formula lets you bulk substitute out specific dynamic elements into a keyword template.
So you could have a list of categories, products, locations, or really anything else, and just swap them out in a keyword template.
You can create keywords following a specific rule, and this tends to generate less keywords overall, with a higher percent of them having volume as you know the formats to follow.
Merging keywords with MergeWords
Mergewords offers a simple version of the formulas above, allowing you to just paste in the different parts of the keywords and output the merged keywords.
Ever wanted to see the top-ranking domains / URLs for a particular keyword set?
This analysis will let you do exactly that, and give you more refined performance data, allowing you to dig into the true top performers.
There are a few tools out there that let you see the top competitors and their estimated traffic.
However, many of these tools fail to give good insight into category-specific performance. This holds you back from being able to extra true competitive performance insights, as you’re only seeing the wider picture.
You’re missing out on the drilled-in numbers
Watching the video above, you’ll learn how to build out your own analysis, using a combination of Excel & SerpRobot.
Steps to extract the top-ranking domains
Export SERPs data from SerpRobot
Format the data appropriately in Excel
Extract the domains from the ranking URLs
Run estimated traffic formula based on ranking & search volume
Categorise the keywords into required categories
Pivot the data based on the domains, with slicers/filters for categories
And there you will have it. Your own analysis, with estimated traffic figures at the category level, so that you can get the direct competitor insights you’re after.
Merging Multiple CSV Files Together with CMD (Windows Command Prompt)
Doing keyword research, and have a million CSV files that you need to extract data from?
Well, this is where a handy, and rather tiny, merge CSV files SEO hacks that has saved me plenty of time over the years comes in.
The ability to merge as many CSVs as you want into a single monster file, allowing you to then just dedupe it and use it how you need.
Built into Windows, the command prompt is not something the average user will open up.
I’ll show you how to open it, and most importantly, how to merge csv files together in CMD.
Opening the windows command prompt
You open the command prompt by;
Clicking start/windows in the bottom left corner
Typing ‘CMD’ (just type it, don’t need to press anything)
Clicking on ‘Command Prompt’
You’ll be presented with a blank command prompt, that will default to your user directory and look similar to the below;
Change the directory to the location of your CSV files
The next step is to change the directory in CMD, to where you have the CSV files that you want merged, stored.
To do this, you type CD, and then the folder location.
So for me, I will be typing in;
CD C:\Users\sam\Box Sync\Clients\Sample CSV files
And then hit enter, and it will look like the below;
CMD has successfully changed the location to where you have your CSV files.
If you don’t know the location of this, you can just browse through your folders and then click in the top of the screen.
Browsing to my folder, I will see;
You then click in the bar at the top where I have highlighted, and you will be able to copy the folder location, like this;
Merging your CSV files
Now that you’ve updated the location, you can merge your CSV files in the command line.
To merge the files, you need to type copy *.csv filename.csv
the wildcard *.csv will select all the csv files in the folder, and the filename can be anything you want.
So for me, I will type the following;
copy *.csv all-keywords.csv
The command prompt will then run through and merge them all, and output a new csv file based on the name you gave it.
Depending on how many files, and how big they are, will depend on how long this takes to run.
For my small batch, it took 2 seconds. Even with a large one it shouldn’t take more than 10-15 seconds to run though.
Deduplicating your new CSV
After you’ve merged your files, you will just need to open the csv, and dedupe them. You could do this in Excel, or open it in Google docs.
Select the column of keywords
Click on ‘remove duplicates’ under the data tab
Ensure ‘expand the selection’ is selected, and then click ‘remove duplicates’
Click ‘unselect all’ and then tick on the keyword column only so that it only dedupes the keywords
Excel will then duplicate all the keywords, and you can then throw them into google sheets or whatever you use to manage your keywords, or look at the data.
Successfully merged your CSV files
And that is how to merge csv files in cmd with the Windows command prompt.
Couldn’t be easier!
Bulk Generating Page Titles in Google Sheets (and Excel!)
Yeap, chances are that if you’re working with a large enough client that you’re contemplating bulk-generating page titles for, you’d be working with a dynamic system. Ideally, you can just give the developers your rules and they’ll build them in and you don’t need to manually generate anything other than some examples.
Ideally.
So, in the less-than-ideal world where you need to manually generate tens, hundreds, or thousands of page titles, here’s a potential solution to get you started in the right direction.
You’ll learn how to;
Randomly pick between title ‘keyword’ templates
Randomly select from CTR-centric templates
Select a shorter fall back if the variables make your title too long
Trim the variation off if the title tag is too long
You’ll essentially be able to bulk spin together unique page titles for each page.
The majority of people won’t ever use this, but, the principles behind it could be applied to many different bulk techniques.
Building out your variables
Initially, you’ll need to get all your data together by listing out the variables of each appropriate page. These are the different dynamic bits that get used in the new page title.
You might have this data already, but if everything you need is in the URL, then you could go ahead and extract it by using the formulas I mentioned in my migration guide to extract what you need.
If you’ve got a nice dataset already, then you can just get started with crafting your titles.
For my demo, I’ll be using a mix of the channel (buy or rent), location, and the property types (apartments, houses, etc).
You’ll see each variable I plan on using in a template is in a separate column, which will allow us to pull it into the page title as needed.
Above each of the variable types, I have a fallback option in case the variables are blank.
So if ‘location’ is blank, use ‘Australia’, and If ‘type’ is blank, we will default to ‘Properties’ for the titles.
Building your page title templates
Next, we’ll need to write out the different variations of page titles. These are what will take your variables into account, and then spit out the page title.
You could just want to use the exact same template for all your titles, which is just a nice simple substitution formula.
But that’s too easy. So, instead, we’re going to randomly select from multiple title elements.
This setup will randomly select from multiple choices of template, and then rotate through a few times if the selected template is going to be too long once the specified variants are pulled in.
Probably a bit confusing right now, but as an example, some locations could be 5 letters, like ‘Perth’.
You could then also run into locations that are rather long like ‘Eggs and Bacon Bay’ (legit place in Tasmania!). So having a single title structure to account for both, or even just the median length, could leave you at a loss by not being able to utilise the full character limit and maximise CTR.
There are plenty of ways SEOs choose to write their page titles, however, I normally stick with a tried and tested format of;
KEYWORD | SOMETHING FOR CTR
Following this format, I’ve split the title generator into two parts.
Keyword part of the page title
We have to write out the variations that will be used at the front of page titles.
For many bulk sites, you would essentially just look at using the exact same template for this depending on the keyword structure.
However, for example sake, I’ve gone with the 3 different variations.
You’ll see I’ve included the 3 potential variables that will be used within a template.
You can include as many, or as little variations here as you’d like, and with my template sheet you can do up to 5.
CTR-centric page title extension
The next part will be the extensions that get added after the keyword, with a primary aim of being for CTR, and maybe an additional keyword drop.
Again, I’ve added 3 variations our templates can use.
However, adding those two together for many locations & property types will create titles that will be way too long for us to use as they’d get truncated.
To combat this, we can add some fallback extension variations.
These are significantly shorter than our initial extensions, and they’ll get used if the Keyword + Extension variations getting generated are too long.
Generating your page titles
The magical formula to randomly select from your page titles is;
It might look a bit confusing, but the substitution formulas are wrapped around the initial formula, and then within the subsitution formulas I have an IF statement that lets me check if the cell is blank essentially.
So if a variable has a length of 1 or more, swap out that variable.
If the given variable has a length of 0, use the default values for that variable that I set.
For me, that will all spit out;
As you can see though, some of my shiny new page titles are too long!
To fix this, I am going to try to regenerate them again, and again, and again until I’m confident the variations can’t produce one that will fall under the length requirement.
Looping through the page title generation
Whilst it’s not exactly ‘looping’ due to the constraints of a formula, it’s pretty close.
Every time time something on the sheet changes, the page titles get regenerated. It can be turned off, but it’s still just easier to paste the initially generated titles in a new column.
We then throw another column on the end of where we just pasted the page titles, and run a new formula over it.
This is exactly the same as the earlier formula, except for an IF statement at the start. This IF just checks the length of the page title that was just pasted to the left.
If the pasted page title matches a requirement (set to be 59 characters or less), then copy that page title over.
If the page title is longer than 59 characters, generate it again!
So for this example, I have pasted my titles from the first generation on the left, and then run the new formula on the right, and you’ll see two more page titles matching the criteria that haven’t previously fallen within the length requirements.
Then we repeat that again;
And again, and again, and again, until there’s a pretty slim chance of hitting a match that will fall under the length requirement.
My example runs through this 7 times, and then it changes on the 8th time.
Using a fallback extension when the titles are too long
Once we’re happy that no more short titles will be generated, we move onto using the shorter fallback extensions.
That single page title, will now successfully validate once we don’t pull in the extension.
And then just pasting that final page title generation into a column near the start, we end up with all our unique page titles with all their variables swapped out.
Not exactly my best title copywriting in history… but you get the point!
You will just need to repeat the copy/pasting bit every time you edit your variables or templates, but you won’t need to touch those formulas!
So once setup, it’s a pretty easy process to bulk generate the titles going forward.
Or, just use my sheet to save you some time!
Using my page title generation template
I know this isn’t the easiest thing to follow along too, so I made a sheet that you can copy and use.
There are a few extra things in it to try and make the process a bit easier, but you need to be careful with it!
It’ll break pretty easy because of the macro that gets run to generate the titles. So please don’t insert/delete rows, or columns. Just input your existing data.
If you do want to modify things, just know that the ‘generate’ button will probably no longer work and that you’ll just need to manually copy the data around.
1. Copy the document
Click the link below, and copy the generator to your Google Drive.
I’ve included 2 extra variable columns in case you want to use them, allowing you up to 5 different variables. Just paste your data in and follow the same format as the example data.
3. Fill out the variable details
Update your variable names, along with the defaults that should be used if the variable cell is blank.
You can even set the title length limits for the formulas if you want (it won’t change the conditional formatting on the cells though).
4. Fill out the page title template variations
You can include up to 5 of each version, but just make sure you have at least one in each row.
If you don’t want a different extension fallback, just include the same templates as the extension aboe it, and the formula will just strip them off if it’s too long.
5. Click generate!
I built a macro into the sheet that will work through all the columns, and copy all your title data as it goes.
This will save you the need to manually doing anything. Just fill out the template and then this button will do the hard work!
It’s going to ask you for access to the document, but this is just to run the macro itself. That macro is all self-contained, nothing leaves the document.
If you don’t want to give the macro permission to modify the document, just unhide the hidden rows, and then follow the process above of copying your page titles into each ‘pasted’ column. Then just paste the final 10th generation into the first page title column and you’ll be done.
Why?
A few different reasons why you’d be stuck trying to generate your titles.
My first scenario for this was to deliver 200 page titles to a client, across all their service pages, and safe to say that I really wish I had something like this back then!
Big shout out to the guys here though, as I got the original formula from them! Have tweaked it a bit, but the core thanks sits with them.
Let me know if everything works okay for you, and whether you have any issues or feedback!
I would love to hear if you’ve found it useful.
Stripping and Rebuilding a URL Structure for Migration Testing
Some clients have recently required a migration mapped out for 10’s to 100’s of thousands of URLs.
To do this, we needed to capture all the different URLs, build out the migration rules, and then test the redirects.
Mapping out and building the rules is one thing, but then how do we bulk generate what the URLs should look post-migration so that we can actually test the redirects?
How can we test the implemented redirect rules, to ensure they match what we originally mapped out?
No one wants to manually create and map 1,000+ URLs to compare the redirect path too, and I personally prefer to not just test the redirects then give them ‘after’ the once over.
I’d like something to compare the implementation to.
Using Google sheets (or Excel) you can strip down the existing URL, extract the parts the redirection rules will use, and then rebuild the existing URL into their new formats.
This will allow you to do a bulk migration test against the implemented redirect rules, and confirm everything redirects where it should.
There are a couple of ways of achieving this, and I will just go through the different methods I use below.
A quick note though, this has nothing to do with building the actual redirection rules themselves. Whilst it might help you build better examples of what you need to redirect, this post is purely about building the expected URLs so you can confirm the redirects match.
Stripping a URL down
There are a few main types of URLs we can account for here.
The standard hierarchical folder-based structure, the flat URL structure with everything to load the page in the base URL, and the ugly query parameter based structure.
They can all be stripped down relatively easily, but firstly we can strip out the URL path from the domain to simplify issue patching later.
Removing the domain from a URL Path
A handful of different ways to strip a domain from a URL are available, but this formula is possibly the easiest;
This will strip everything up to and including ‘domain.com’ allowing us to account for all https, http, www, and non-www variations of a URL leaving us with just the URL path.
It will also cull subdomains, so just keep that in mind. If you need subdomains left, you can still use the same formula, but each subdomain will need it’s own.
I’d recommend extracting the subdomain in a separate column, and then referencing the subdomain’s cell, rather than plain text domain. That should save you a bit of time!
Extracting query parameter values from a URL
Let’s go over this one first, as it is slightly simpler than breaking down a ‘pretty’ URL.
There’s just one formula you need, and that’s regex extraction.
That will extract the value for the ‘parent’ query parameter, and leave the cell blank if the parameter doesn’t exist, or is blank.
You then just need to repeat that formula, for each query parameter value you need to extract.
Stripping a hierarchical ‘pretty’ URL structure
Stripping down a folder-based, or hierarchical is the next structure that could be at play here – and generally my favourite to use.
The absolute simplest way is to use the “split text to columns” method.
Highlight your text, click ‘tools’, and then click on ‘Split text to columns’.
You’ll then get asked for a separator. In this case, the separator is a ‘/’, so you’ll need to click ‘custom’ and enter the ‘/’.
After which, you’ll immediately get the following;
Each folder split across the different cells, giving you all the different folder levels all accounted for.
Works perfectly! If you don’t mind splitting any new URLs like this going forward any time you add new ones.
I personally prefer to try and work with formulas where possible, so that we can keep expanding the URL list, along with tweaking it as we go if modifications are required.
Provided you did the URL path column above, the following formula is what you’ll need;
=iferror(LEFT(B6,FIND(“/”,B6)–1),“”)
That will just strip off everything after the first slash, leaving you with the parent folder.
Next, you’ll do a similar thing to get the child folder, but first, you’ll just subtract the parent folder from the URL path.
You essentially rebuild the URL that came before it, based on what you had stripped out, and then you’re left with the ‘location’ that is on the end.
If your location isn’t on the end or doesn’t have something that can be clearly substituted out after it, you’ll need to modify the formula used for the child folder.
Rebuilding the New Post-Migration URL Structures
Once you’ve stripped down the old URL to its main elements, you can start building the new one!
If you’re merely converting query parameters to a clean structure, with no folder changes, you’re super lucky.
Many of us though, are not that lucky.
Converting query parameters to pretty SEO URLs in Google Sheets
Okay you lucky people, this is super easy.
=”https://www.domain.com/”&C2&”/”&D2&”/”
You just piece the URL together using the ampersand, ‘&’.
Include your domain with a slash, then the parent folder and a slash after it, and then the child folder and another slash.
Some people like to use concatenate, but you complicate things. Complications through concatenations.
Annnnnd you’re done! You’ve now turned your query parameters into a pretty URL structure, in bulk, so that you can test these out.
It’s rarely that easy though.
Rebuilding a URL structure where folders change names or get merged
Whilst it’s easier when things don’t change, it really isn’t that hard when they do now that you’ve stripped the URL down.
The first step is to paste all your parent, child, and any other folders into a separate column, and then remove all their duplicates.
This will leave you with a unique set of folder values.
You then just need to add their new value next to them.
If the value doesn’t change, just paste the same value in.
If the folders are getting merged, just paste the folder you’re merging into here.
If it is a completely new value, type out the replacement.
The mapping of the values might take time, as some websites could have hundreds and hundreds.
The benefit of doing it this way is that you aren’t dealing with all the possible variations & combinations.
Separating the child folders from the parent folders lets us independently map them to their new values.
Now we need to bring these new values into the mix, with a good old vlookup (if you don’t know how to do it, check out my SEO Excel formulas post).
=VLOOKUP(C2,M:N,2,0)
Repeat the process for the child folders, and you’ll get all the shiny new folder values.
You’ll just need a slight tweak to the child formula, due to the possibility of there being no child.
=iferror(VLOOKUP(D2,P:Q,2,0),“”)
If no child exists, the cell will remain blank.
You should now have completely mapped the old to new folder values, so we can move on to constructing the URL.
You’ll have to account for your own variations of the template, and whether a child may or may not exist.
Rebuilding a Structure that Previously Used ID’s
What if you’re just stuck with a heap of parameter IDs, and no ‘pretty’ URL to start with?
The benefit to the above vlookup style URL rebuilding is that you can extract the query parameter values, and then just map the old values to their new pretty folder name.
A quick rebuild, and you’ve gone from the old ugly URLs, to what could be thousands of crawlable new versions of the URL!
Start by getting the path out of the URL, and then extract all the parameter values, like I showed you earlier.
Add your new parameter values to the parent/child vlookup lists;
Then just vlookup the parameter value, to the new parent & new child values, and build your new URL!
Everything was covered earlier, so just grab all the formulas from there.
That covers it though!
You’ve gone from the ugly parameter version to a shiny new & testable pretty URL.
Bulk testing your new URL structure
You can now run the old URLs through screaming frog (or your preferred bulk testing tool) and copy out the final URL in the redirect path.
Match it up to your expected ‘new URL’ and confirm whether it is the same or whether they’re different.
If they’re different, is it the redirect rule that doesn’t match? Is there an error in the expected URL? Are the redirects just not implemented?
This is guaranteed to save you time bulk testing your migration.
Testing a URL migration on staging before going live
In an ideal world, for a migration this size, you should be able to test out your redirection rules on a staging environment.
Swap out the website’s domain in the formula, for the URL of the staging environment, and do everything else the same.
You’ll get thousands of staging URLs ready to load screaming frog up with!
Download the sample
You can download all my sample data and formulas below if you want to jump right in.
As an SEO, you really can’t escape Excel. Almost every month a new SEO tool pops up, that can do so much, but will never seem to replace those little things you can do in Excel.
You find yourself using a tool for 75% of the job, but will then always jump back into Excel to finish it off.
Well, that’s me anyway. If it’s not you, then you probably just don’t know many of the things that Excel can do!
These excel formulas for SEO will really help you out.
I run through VLOOKUPs, IF statements, LEN, TRIM, PROPER / UPPER / LOWER, COUNTIFs & SUMs and show you some basic usage examples. I’ve also added a few extras to the bottom that aren’t in the video!
Basic Excel Formulas for SEOs
Whether you’re starting out, or an experienced SEO, these are the basic building blocks to so many advanced uses of Excel for SEO.
Whilst you can use something like SEO tools for Excel, knowing these formulas will really help you skill up.
VLOOKUP – Find data for a value, within a range
The simplest summary of what a VLOOKUP will do is to match two sets of data where you have something to match them with.
So is long as one column in each data set has some sort of unique identifier, then you can VLOOKUP between the two datasets and bring the data together.
If you don’t have a unique identifier, will you have one if you match two columns together?
You might have a date that isn’t unique, but if you add it to another column (like colour) you will get a unique value that you can use to match the data sets.
=VLOOKUP(*CELL of what you want to search for*,*RANGE of where you want to look for it*,*COLUMN NUMBER the data is in you want to extract*,*TRUE (1) or FALSE (0) but 99% will be FALSE*)
LEN – Count character length of a cell
Nice and simple formula. The LEN formula is great for counting the lengths of page titles and meta descriptions!
Plenty of other uses, but that’s what the majority of people will use it for.
=LEN(*CELL to count*)
TRIM – Remove spaces before & after a cell
Sometimes when trying to match data up, the cell might visually look the same as another cell, but you can’t seem to match it up!
The amount of times I have run into this is crazy, and the majority of times it happens there is a pesky space before or after the text.
The TRIM formula will clean these up for you and make sure there isn’t something hidden there wasting your time!
=TRIM(*CELL to trim*)
SUM – Add all numbers in a range together
Just adds numbers. It’s that simple. Just select the range you’d like to add together and away it goes.
=SUM(*RANGE to add together*)
COUNT – Display a count of cells with numbers in them
Out of your selected range, how many of the cells have a number in them?
=COUNT(*RANGE to count for numbers*)
COUNTA – Display a count of cells with numbers and letters in them (so not empty)
Out of your selected range, how many of the cells have a number of letters in them? This is what you need to use if you are trying to count cells with characters rather than numbers.
=COUNTA(*RANGE to count for numbers*)
COUNTIF – Count all the cells when a specific criterion is met
Only count a cell that matches your criteria. This is great for being able to select cell as your criteria.
So if you have a column of ‘Colour’ and a cell that has the text of ‘Blue’, you could select that ‘Blue’ cell and then the range that has all the colours, and the formula will count how many times ‘Blue’ appears in the range.
=COUNTIF(*RANGE to count for numbers*,*CRITERIA for when to count*)
PROPER – Capitalise the first letter of every word in a cell
Great for cleaning up a list of titles, or any words, where you’d like to capitalise the first letter of every word.
=PROPER(*CELL to capitalise first letters*)
UPPER – Capitalise every letter of every word in a cell
This will format it AND MAKE IT LOOK LIKE YOU’RE SHOUTING. Just use the upper formula when you need to make sure everyone knows you’re SERIOUS.
=UPPER(*CELL to capitalise everything*)
LOWER – Make every letter of every word in a cell lowercase
The lower formula will make everything lower case. Great to run through your keywords with to ensure there isn’t any weird capitalisation going on.
=LOWER(*CELL to lowercase*)
LEFT & RIGHT – Extract x characters from the left or right of a cell
This is a simple way of extracting a certain amount of characters from the left or the right of some text.
Great if you want to cull the end of a title, remove a domain, or trim some trailing slashes from a URL.
=LEFT(*cell reference for text to look in*, *number of characters*)
=RIGHT(*cell reference for text to look in*, *number of characters*)
However, if you are looking trim everything before or after a certain character, instead of a number of characters, check below for a more advanced formula!
IF – Only do something when a certain condition is met
The IF formula lets you create rules for your cells. Maybe you need to filter out titles that are too long.
You could do an IF formula in a new column that says =IF(length>70,”LONG”,”OKAY”). This will give you back a cell that says LONG if the title is too long, or OKAY if everything is okay.
Probably not the best use case, but gives you the idea!
=IF(*The condition to run*,*What to do if it’s true,*What to do if it’s false*)
Does Cell Contains Text – Run condition if the cell contains specific text
A quick formula to use if you want to do something off the back of a cell containing a specific piece of text.
This formula will return TRUE or FALSE, which is what I normally use with this one, but you can expand it do use IF. So IF bla = TRUE, do this.
Great way to quickly segregate data by whether something is in a URL, or keyword.
=ISNUMBER(SEARCH(“text to look for”,*Cell to look in*))
& – Yeap, the ampersand bad boy is rather useful
If you’re trying to merge stuff together in Excel, you need the ampersand. This thing will let you attach anything, to anything else.
It will let you run multiple formulas, then just stick them all together to form what is essentially a sentence.
=”Stick this “&”,with this “&”, and merge all “&2+2&” sentences together”
Which will output:
Stick this, with this, and merge all 4 elements together
SUBSTITUTE – Swap out any text, with any other text
This formula is invaluable if you are doing any sort of text editing or manipulation.
You select the cell you’d like to manipulate, enter the text that currently exists, then enter the text you wish to change it to.
=SUBSTITUTE(*text to look in*,”old text here”,”new text here”)
Trim URL to Domain / Subdomain
This is something that you will find incredibly useful if you’ve never done it before. Some extra tools do it, but you can achieve almost perfect trimming with just a formula – SOOOO much easier!
Quick and Dirty Trim Domain Prefixes Method
If you are just looking to trim the HTTP part, here is a hacky formula that will just substitute out the HTTP part. It’ll leave subdomains / domains, and all their folders intact however.
Spend a bit more time playing with a formula, and you can get something that will do a much better job though!
This formula will trim any URL back to its subdomain. It will remove all prefixes, or any suffixes (aka folders) from a URL so that you are just left with the subdomain & domain combo.
With A2 being the cell reference to where you have a URL to trim.
Trim URL to Root Domain
If you are wanting to get rid of the subdomain too, so you are just left with the root domain, you can use this formula. It will remove folders, subdomains, prefixes, and anything else so that you are just left with the root domain stripped out.
This is something I have only picked up in the last couple of years, and these formulas allow you to strip out the text before or after a character or a set of characters.
I will leave this up to your imagination with how they work, but a great usage is stripped title tags of anything after a | (pipe).
Yes, you could find/replace this stuff out, or substitute it, but maybe there are 50 different items to swap out. That will take ages!
If they all have a pipe right before them, you can strip them all out with one formula.
This formula will remove everything before the pipe. Just edit the pipe out with any character, word, phrase, anything, and it will remove all the text before it.
=RIGHT(A1,LEN(A1)-FIND(“|”,A1))
Strip Text After a Character
Again, just replace the pipe with anything you want to have this formula strip all the text that is after it.
=LEFT(A1,FIND(“|”,A1)-1)
Insert Row Every Nth Row in Google Sheets
Got a list of keywords you need to break up? This formula is magic and will split your rows into lists of x values. This one in particularly breaks the keywords into groups of 20.
The biggest thing to remember with Excel formulas is that chances are someone else has tried what you’re doing before.
Just break down what you’re doing, and Google it piece by piece. You might end up putting something together yourself, through what you find from others.
Hopefully, you’ve found some new ways of using Excel for SEO.
An extremely large table of all their keywords, isn’t going to give the insight some clients would want. Some would like more a bit more insight into the data they’ve paid thousands for.
Building a keyword research dashboard in Excel is a great way to allow a client to interact with their keyword data.
In this short video, I will show you how you can take a categorised keyword list and turn it into a client-friendly dashboard!
The video is a couple years old now, and I may eventually update it, however, for the most part, it still holds true today.
Now, this won’t be great for some clients. You could end up giving them way too much information, and have them asking way too many questions.
However, I personally don’t mind that. I love when clients have an interest in SEO as it gets them more involved in the project, and more excited when the results kick in.
How to build a keyword research dashboard in Excel
Categorise up your keyword research data
Create pivot tables for each category level
Attach slicers to the categories so you can filter the data
Design the tables & slicers to match your client’s branding
And there you have it, you will have an interactive Excel keyword research dashboard to send to your clients.
Creating an online keyword research dashboard
You can save this Excel dashboard to Onedrive and then share with your clients via a link instead of sending a file.
For dashboards under a certain size this means they will be able to view the dashboard online instead of having to download the file. You can then simply update the keyword research file, and they will be able to see the update when they refresh their browser.
This becomes a massive help when trying to send updated data to a client. No more v53 tacked onto the end of a file because you’ve updated it 53 times!
Download the Excel Keyword Research File
If you watched the video and can’t be bothered doing the work yourself, you can just have my file!
Or if you’re doing keyword research for car hire you can also just steal my keywords – chances of that have to be slim.
