The following Excel formula is useful for keyword research, perhaps one of the most important steps in search engine optimization. You should always conduct keyword research prior to writing content for your website. By identifying relevant keywords with high traffic and low competition, you stand a greater chance of attracting visitors interested in your content.
The word search formula enables us to sort a spreadsheet depending on whether words exist in an adjacent cell. Think of it as a full text search on a column. But instead of just seeing which cells match, we can also interact with the results.
Search Matching Words Using Excel
This formula will find the existence of a word or words in a cell:
How to Use the Excel Word Search Formula
- Create a new column next to the column containing the words you are searching for.
- Click the cell next to the cell you want to search and paste the formula as shown in the screenshot below. In the formula, replace “social media” with the text you would like to search and press Enter. The formula searches the adjacent cell and writes “true” or “false” depending on whether the search term was found. **Replace A2 with the cell reference you want to count if it does not match the image below.
- Duplicate the formula in the remaining cells by “dragging” the formula down the spreadsheet. (For illustration and details, see Tips and Tricks – Excel and SEO:Word Count Formula)
- Now you can sort the spreadsheet by the results of the word search. Select column B and sort from Z to A to see all the results that were true. Excel may show a Sort Warning dialog box. Make sure you set it to “Expand the selection” to keep your columns in sync.
IMPORTANT– The formula is case-sensitive. That means a search for “Social media” and “social media” will return different results. The formula will search for whatever you enter between the quotes, so be careful with spaces as well.
Excel Word Search Formula and Search Engine Optimization
Suppose we are a social media marketing firm in the process of researching keywords to improve our search engine optimization for keywords associated with social marketing. We are using a tool, like SEMRush, Wordtracker or the Google Keyword Tool, to find keywords related to two phrases: social media marketing and social network marketing. We complete the keyword search and get back the following 10 results:
Just by looking at the list, it is not clear how many phrases include the term “social media” and how many include the term “social networking.” In the real world, you are likely to have hundreds or even thousands of results. The Excel word search formula helps us sort the list so we can move the relevant keywords to a new spreadsheet and run further analysis.
This is a somewhat poor example. Simply sorting the column would group our keywords so we could pick out cells that match “social media” easily. This is generally not the case in practice. Keywords can be “sandwiched” between other words, rendering the simple column sort useless. The word search formula helps ensure you do not miss those important phrases.