5 Excel and Google Sheets tricks that every SEO should know

Amid the recognition of generative AI instruments, spreadsheets are nonetheless important instruments for advertising and marketing analytics. In terms of organizing, cleansing, and analyzing information, Microsoft Excel and Google Sheets have stood the check of time.

All of the well-known SEO instruments export most of their information in CSV format. And whereas you should utilize genAI for information evaluation, usually you’ll nonetheless want an excellent previous spreadsheet to get began.

So let’s dive again into the fundamentals and see how one can make your life as an web optimization and digital marketer simpler with these 5 tricks to enhance your Excel and Google Sheets abilities.

1. Merge a number of Excel recordsdata

It is a fundamental function, however you can be stunned how many individuals don’t realize it.

Image this case: You’re serving to a well being firm with key phrase analysis, and so they’re in a extremely aggressive area of interest. At first, you’re given a listing of 10 illnesses to discover additional.

In web optimization, that equates to 10 long-tail phrases, every with a longtime abbreviation. The tip result’s 40 recordsdata (as a result of there was a time when Semrush wouldn’t allow you to export each the broad match and the associated phrases into one file). 

If you wish to mix these into one huge file, what most customers will do shall be to open every file after which copy and paste – 40 instances! If the recordsdata are huge and take time to open, this will simply take greater than two hours, with out you having the ability to even begin analyzing.

As a substitute of that time-consuming strategy, do this. 

• Create a brand new folder and put all of your recordsdata in it. (In my case, I had two folders, one for broad match key phrases and one for associated ones). 
• Then, open a brand new Excel file, and go to the Information tab.
• Click on on the Get Information dropdown, and select the From Folder possibility. 

Excel will mechanically merge all of your recordsdata into one so long as they’ve the identical columns. 

One of the best half is that Excel will immediate you for the encoding sort earlier than combining the info. Similar to that, in about 5 minutes, you’ll have a single giant file with, let’s say, 50,000 rows.

Fify thousand rows is quite a bit for key phrase analysis, particularly when the stakeholders are often anticipating no more than 50 to 100 concrete concepts. 

So that you’ll in all probability wish to begin cleansing your file. After eradicating all key phrases with out search quantity information out of your spreadsheet, you’ll be able to then take away all misspelled ones.

2. Spellcheck 1000’s of phrases with a click on

There’s a option to test the spelling of enormous Excel recordsdata, and this technique ought to work for each English and different languages.

The reply to this job is to make use of macros. By enabling macros and including one devoted to highlighting all misspelled phrases with concrete colour, you’ll be able to shortly see the errors and take away them out of your file. You may see the code I used within the picture beneath.

I’ve eliminated greater than 20,000 phrases from my 50,000 record with the above instance. It wants a while to run in case your file is huge (so you could wish to take a espresso break whereas ready).

Take into account that the code just isn’t excellent. When you have abbreviations or names (model, nation, individuals), all these shall be highlighted as misspelled if they aren’t written with the proper capital letters.

You have to yet another step to double-check and unhighlight all these. A easy IF method would possibly do the trick on this case.

Now our 50,000 key phrases are narrowed to round 10,000, however extra work is required, earlier than you’ll be able to really begin performing some evaluation. 

Dig deeper: 11 Google Sheets formulas SEOs should know

3. Detect supply language

One other potential step is to outline the language of each key phrase in your record. Take this step if you wish to exclude key phrases that aren’t from a particular language. 

Let’s say I’m engaged on bettering a web site in Bulgaria. In that case, I ought to provide you with concepts for brand new content material in Bulgarian, not in English. Although some Bulgarian customers would possibly use English when looking, it’s important to concentrate on content material of their native language.

For this one, you will have Google Sheets and the method:

• DETECTLANGUAGE(text_or_range)

This offers you an excellent begin. Nevertheless, I might advise an additional guide evaluation, because the outcomes from this method will not be excellent.

In my Bulgarian instance, many Bulgarian phrases have been marked as Ukrainian, Russian, Macedonian and even Chinese language. Outcomes for languages utilizing the Latin alphabet are extra correct and dependable.

After including yet another layer of knowledge to your information, you can begin analyzing. 

4. Counting coloured cells? There’s a macro for that

Each individual has alternative ways of working and analyzing information. In my case, I’m fascinated by colours. 

Coloration formatting in a single column is simple, as there are handy options to filter and kind information by colour. But it surely turns into a problem when you have got 30 or 50 columns and wish to know how you can depend all of the cells with concrete colour from all of the columns. 

As an illustration, I used to be engaged on an inner linking mission the place I wanted to make use of Screaming Frog. On this particular case, we have been in search of 40 completely different phrases so as to add inner hyperlinks to twenty completely different pages. 

After finishing the mission, I needed to know what number of inner hyperlinks my group had really carried out.

In case you’ve adopted alongside to this point, you would possibly guess that I didn’t wish to filter all 40 columns one after the other manually. That is very true as a result of we used colour coding at any time when we positioned an inner hyperlink.

To assist with this job, you should utilize macros once more. Yow will discover some good examples in this article, and the method you will have after implementing these is actually easy – CountCellsByColor(data_range, cell_color). You may outline the cell colour through the use of one among your cells. 

The excellent news for all followers of Google Sheets is that there’s additionally a manner to do that with out utilizing Excel. You simply want a Google Apps script.

In case you’re unfamiliar with this software, it’s the colourful icon (what a coincidence) underneath the Extensions menu. Although I’m an actual fan of Excel, this selection is much simpler. 

5. Scale back a number of method steps with Google Sheets’ ArrayFormula

One other Google Sheets trick that I discover helpful is the ArrayFormula. Sadly, Google’s documentation just isn’t the perfect, which may be why individuals underestimate the facility of this method.

This method allows you to write different formulation concurrently and can mechanically be expanded for a concrete array. 

An actual-life instance for which I take advantage of this method is to calculate the ROI of our web optimization efforts. I obtain invoices in a number of currencies, however the finances of my firm is outlined in a single. 

• So first, I have to convert the quantities of all invoices into one concrete forex with the assistance of:
  • =GOOGLEFINANCE(“CURRENCY:<Forex Code 1><Forex Code 2>”)
• The second step is to sum all these for a particular interval, in my case, a month.
• The third step is to calculate the ROI primarily based on the worth generated by the natural channel

The tip consequence will look one thing like this:

• Put in column D :
  • =ArrayFormula(IF(NOT(ISBLANK(A2:A)), A2:A * GOOGLEFINANCE(“CURRENCY:” & B2:B & “USD”), “”))
    
• Put in one other cell:
  • =(  E1 –   SUMIFS( D2:D,  C2:C, “>=” & DATE(2023,1,1),  C2:C, “<=” & DATE(2023,1,31)  ) / SUMIFS(  D2:D, C2:C, “>=” & DATE(2023,1,1),   C2:C, “<=” & DATE(2023,1,31))

The place:

• Bill quantities are in column A.
• Forex codes for conversion are in column B.
• Bill dates are in column C.
• Within the above method, I’m changing the bill quantities into U.S. {dollars}.
• The worth generated by the natural channel is in a particular cell, say E1.
• I’m summing for a particular month (e.g., January 2023).

The method seems lengthy, however bear in mind, you’ll solely have to enter it as soon as.

Plus, you’ll be able to ask ChatGPT to write down it for you. All you might want to do is to explain the specified consequence and the enter data you have got. 

Analyzing information for fast insights utilizing spreadsheets and genAI

ChatGPT can present fast insights from information evaluation. Nevertheless, you could first put together your information to be clear and well-structured. Then, craft an in depth immediate explaining the evaluation you need step-by-step and ask for particular outcomes.

To entry ChatGPT’s Advanced Data Analysis capabilities, you want ChatGPT Plus. This permits quantitative evaluation primarily based on the info, however bear in mind that qualitative outcomes will not be totally correct.

Keep in mind, ask for issues that may very well be quantified primarily based in your information, not for solutions needing qualitative outcomes, as you threat ending with not very correct insights. 

One factor I like about information evaluation with ChatGPT is the info visualization. When creating studies in your group, you should utilize AI’s energy for extra superior duties – like regression, speculation testing, and extra.

It’s also possible to do most of those duties with the assistance of Python or quickly with Excel + Python, however if you’re not a Python energy, then ChatGPT is the best way to go.

Should-know Excel and Google Sheets suggestions

At a time when generative AI dominates our each day lives, don’t overlook the worth of honing your spreadsheet abilities.

Mastering Excel and Google Sheets strategies builds essential analytical muscle tissues that allow you to grasp the “why” behind the info.

Whether or not you’re an web optimization analyzing key phrase lists, a PPC specialist reconciling advert spend, or a CMO deciphering marketing campaign analytics, leveling up your spreadsheet abilities is time nicely spent.

With the correct strategies, you’ll be able to wrangle even the biggest datasets into clear, actionable insights.