Google Sheets is an incredible tool that many SEOs don’t fully utilise, due to a lack of knowledge, experience, or simply not realising just how many tasks it can help with. If you’re unaware of what Google Sheets can do for your work processes, here are some tricks that will help simplify your day and save you valuable time. You’ll even discover some bonus ideas for ways to use Sheets.
What is Google Sheets?
Much like Excel spreadsheets, Google Sheets is an app that looks and functions in the same way as any spreadsheet tool but with added benefits. Firstly, because it’s online, it can be accessed and used anywhere without the worry of leaving your file on your laptop at home. The web-based aspect of Google Sheets also means that you can gather data from anywhere on the web and quickly update your spreadsheet. All you need to get started with Google Sheets is an internet connection, a web browser and a Google account. Then you’ll be good to go.
Google Sheets works from any device, both iOS and Android, and it’s free along with Google Drive, Docs and Slides, meaning you can incorporate it with other Google features for an immersive experience. There are some great add-ons for this app but there’s also the option to write custom code and create your own, too.
Google Sheets requires no installation and comes with some great online benefits. These include sharing documents between teams, online storage or real-time editing and shared files that multiple members of the team can work on at once.
Five Google Sheets tricks to increase productivity
🔎 The ultimate tool in any SEOs arsenal is the vlookup formula which allows you to search for a range using a string which you can then return matching values from a specific cell within that range. The syntax is =vlookup(string, range, index_key) which asks Google Sheets to look for a value in the column of a table you’ve specified and then retrieve a value from that data.
🔎 In addition to finding data, it can also be used to update information. For example, if you have a list of keywords and you want to compare rankings six months later for a client, you can add a column and use vlookup for each keyword to gather the previous and current ranking positions. It’s an easy way of searching through large tables of data quickly to find exactly what you’re looking for in moments.
📩 The =importxml(url, xpath_query) formula allows you to import data using an XPath query without you needing to leave the spreadsheet. This is brilliant for SEOs as it means you can pull in various different types of information to work with it within Sheets. For example, you could have a list of URLs in one column and then pull in metadata to use as a reference for optimising those pages.
📩 Of course, there are other tools that will pull in data from crawling sites, but the benefit of this formula is that once the sheet is set up, the most up to date metadata will be pulled in. So, you don’t need to crawl and recrawl every time – Google Sheets will just provide the most relevant information.
OR, ISNUMBER, SEARCH & IF
📚 Great for categorisation, the syntax for this formula is =or(isnumber(search(term, string))). It allows SEOs to identify different terms by testing whether multiple conditions are true or false, check if the value is a number returning true or false, and then returning the location of a substring within a string. If you have a vast list of keywords that you want to assign categories to or to use for specific product pages on your site to reduce cannibalisation, this formula is a great help.
📚 The formula can be used for as many as over 200 terms and the way that it’s set up means that if a keyword matches in multiple locations, all of the applicable labels would be listed in the results. This is particularly helpful for product pages as it helps you to see where a keyword features.
🕵🏼 This is a more advanced version of the VLOOKUP tool using Structure Query Language or SQL. With this formula, the syntax for which is =query(range, sql_query, you can put multiple conditions in place and go into greater depth with your searches. Perhaps you want to compare competitors by exporting backlink profiles. This might result in thousands of URLs, but using the SQL query, you can quickly produce a list of active links or identify where the Domain Rating is less than 50 to help you with your competitor research.
📜 It provides the opportunity to write custom functions, implement bespoke menus and sidebars, publish web apps that interact with other Google tools like Analytics, and even with third parties, plus much more. For specific problems where a standard formula won’t quite work the way you want it to, the Script Editor is a handy way of working around a problem to create a custom solution.
Google Sheets isn’t just useful for organising data. It can also be used to create dashboard reports, feedback forms, content planners and content audits. It’s also popular within team projects for creating Gantt charts that help to clearly illustrate how tasks overlap in large ongoing assignments.
This often-under-utilised app can help SEOs streamline their tasks and work more powerfully, while also getting more from their data in an efficient and time-saving way. A well-designed spreadsheet in Google Sheets can help teams become more organised, clarify their goals more easily and even map out complex processes in an easy-to-follow way.