Several months ago, Jeremy Gottlieb wrote an excellent blog on Moz called ‘One Formula to Rule Them All: SEO Data Analysis Made Easy in Excel‘. The article had terrific advice on using the Excel ISNUMBER formula to sort Excel data to create insights. After using the formula so much, I decided to create a formula generator to help myself and others be an SEO Hero for their clients.
You can use the tool above, or the ISNUMBER formula tool on its stand alone page.
How to use the ISNUMBER formula
Jeremy’s article has some terrific uses on how you can use this powerful formula. Below is a quick and dirty step through of I would use the isnumber generator with google analytics. In this example I’m using search console data for an imaginary Golf Course that also hosts weddings.
In this example, the client wants to know what ‘wedding’, ‘golf course’ and ‘general golf’ phrases are bringing in clicks. This is three search terms in total.
In the generator, I will click the three ‘number of terms’ button. This lets me fill in the three search terms, and how I want them to appear.
The ISNUMBER sorts by appearance, so in this case I put wedding first, so any search phrase that had golf and wedding in it would be categorized as wedding.
I took the same approach by putting course as my second search term. Had I used golf, any term that had golf and course in it would be sorted under golf.
From there I then added in a new column called ‘Category’ in the excel sheet and copied the formula into cell F2. Duplicating the formula down the F column, everything can be quickly sorted.
Using the ISNUMBER Excel formula you can categorize hundreds of rows of data in a matter of minutes. You’ll notice there’s a fourth category called ‘other’ and that simply puts anything that isn’t being searched for into a catch-all category.
You can boil it down even further by creating a pivot table, and then using an appropriate chart to display the pivot table data.
I’ve set the limit of search terms for Twelve as I rarely find myself using anything past seven or eight. If you need more than twelve terms, you should be able to see the pattern in the formula to tack on more expressions.
One way you may go past twelve is if you are categorizing many search term into a single category. For example you could have, in this case, added ‘club’, ‘driver’ and ‘birdie’ to the golf related category.
If you find this tool helpful, or have any glitches/problems with it, feel free to comment below.