Crunching big marketing data? Brush up on tips for merging, analyzing, and visualizing Excel and Google Sheets files to extract key insights.
Amid the popularity of generative AI tools, spreadsheets are still essential tools for marketing analytics. When it comes to organizing, cleaning, and analyzing data, Microsoft Excel and Google Sheets have stood the test of time.
All the famous SEO tools export most of their data in CSV format. And while you can use genAI for data analysis, often you’ll still need a good old spreadsheet to get started.
So let’s dive back into the basics and see how you can make your life as an SEO and digital marketer easier with these five tips to boost your Excel and Google Sheets skills.
1. Merge multiple Excel files
This is a basic feature, but you will be surprised how many people don’t know it.
Picture this situation: You’re helping a health company with keyword research, and they’re in a highly competitive niche. At first, you’re given a list of 10 diseases to explore further.
In SEO, that equates to 10 long-tail phrases, each with an established abbreviation. The end result is 40 files (because there was a time when Semrush wouldn’t let you export both the broad match and the related phrases into one file).
If you want to combine these into one big file, what most users will do will be to open each file and then copy and paste – 40 times! If the files are big and take time to open, this can easily take more than two hours, without you being able to even start analyzing.
Instead of that time-consuming approach, try this.
- Create a new folder and put all your files in it. (In my case, I had two folders, one for broad match keywords and one for related ones).
- Then, open a new Excel file, and go to the Data tab.
- Click on the Get Data dropdown, and choose the From Folder option.
Excel will automatically merge all your files into one as long as they have the same columns.
The best part is that Excel will prompt you for the encoding type before combining the data. Just like that, in about five minutes, you’ll have a single large file with, let’s say, 50,000 rows.
Fify thousand rows is a lot for keyword research, especially when the stakeholders are usually expecting not more than 50 to 100 concrete ideas.
So you’ll probably want to start cleaning your file. After removing all keywords without search volume data from your spreadsheet, you can then remove all misspelled ones.
2. Spellcheck thousands of words with a click
There’s a way to check the spelling of large Excel files, and this method should work for both English and other languages.
The answer to this task is to use macros. By enabling macros and adding one dedicated to highlighting all misspelled words with concrete color, you can quickly see the errors and remove them from your file. You can see the code I used in the image below.
I have removed more than 20,000 words from my 50,000 list with the above example. It needs some time to run if your file is big (so you may want to take a coffee break while waiting).
Keep in mind that the code is not perfect. If you have abbreviations or names (brand, country, people), all these will be highlighted as misspelled if they are not written with the correct capital letters.
You will need one more step to double-check and unhighlight all these. A simple IF formula might do the trick in this case.
Now our 50,000 keywords are narrowed to around 10,000, but more work is needed, before you can actually start doing some analysis.
3. Detect source language
Another potential step is to define the language of every keyword in your list. Take this step if you want to exclude keywords that are not from a specific language.
Let’s say I’m working on improving a website in Bulgaria. In that case, I should come up with ideas for new content in Bulgarian, not in English. Even though some Bulgarian users might use English when searching, it’s essential to focus on content in their native language.
For this one, you will need Google Sheets and the formula:
- DETECTLANGUAGE(text_or_range)
This will give you a good start. However, I would advise an extra manual review, as the results from this formula are not perfect.
In my Bulgarian example, many Bulgarian words were marked as Ukrainian, Russian, Macedonian and even Chinese. Results for languages using the Latin alphabet are more accurate and reliable.
After adding one more layer of information to your data, you can start analyzing.
4. Counting colored cells? There’s a macro for that
Every person has different ways of working and analyzing data. In my case, I am fascinated by colors.
Color formatting in one column is easy, as there are convenient features to filter and sort data by color. But it becomes a challenge when you have 30 or 50 columns and want to know how to count all the cells with concrete color from all the columns.
For instance, I was working on an internal linking project where I needed to use Screaming Frog. In this specific case, we were looking for 40 different terms to add internal links to 20 different pages.
After completing the project, I wanted to know how many internal links my team had actually implemented.
If you’ve followed along so far, you might guess that I didn’t want to filter all 40 columns one by one manually. This is especially true because we used color coding whenever we placed an internal link.
To help with this task, you can use macros again. You can find some nice examples in this article, and the formula you will need after implementing these is really simple – CountCellsByColor(data_range, cell_color). You can define the cell color by using one of your cells.
The good news for all fans of Google Sheets is that there is also a way to do this without using Excel. You just need a Google Apps script.
If you’re unfamiliar with this tool, it is the colorful icon (what a coincidence) under the Extensions menu. Even though I am a real fan of Excel, this option is far easier.
5. Reduce multiple formula steps with Google Sheets’ ArrayFormula
Another Google Sheets trick that I find useful is the ArrayFormula. Unfortunately, Google’s documentation is not the best, which might be why people underestimate the power of this formula.
This formula enables you to write other formulas simultaneously and will automatically be expanded for a concrete array.
A real-life example for which I use this formula is to calculate the ROI of our SEO efforts. I receive invoices in multiple currencies, but the budget of my company is defined in one.
- So first, I need to convert the amounts of all invoices into one concrete currency with the help of:
- =GOOGLEFINANCE(“CURRENCY:<Currency Code 1><Currency Code 2>”)
- The second step is to sum all these for a specific period, in my case, a month.
- The third step is to calculate the ROI based on the value generated by the organic channel
The end result will look something like this:
- Put in column D :
- =ArrayFormula(IF(NOT(ISBLANK(A2:A)), A2:A * GOOGLEFINANCE(“CURRENCY:” & B2:B & “USD”), “”))
- =ArrayFormula(IF(NOT(ISBLANK(A2:A)), A2:A * GOOGLEFINANCE(“CURRENCY:” & B2:B & “USD”), “”))
- Put in another 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))
Where:
- Invoice amounts are in column A.
- Currency codes for conversion are in column B.
- Invoice dates are in column C.
- In the above formula, I am converting the invoice amounts into U.S. dollars.
- The value generated by the organic channel is in a specific cell, say E1.
- I am summing for a specific month (e.g., January 2023).
The formula looks long, but remember, you will only need to enter it once.
Plus, you can ask ChatGPT to write it for you. All you need to do is to describe the desired result and the input information you have.
Analyzing data for quick insights using spreadsheets and genAI
ChatGPT can provide quick insights from data analysis. However, you must first prepare your data to be clean and well-structured. Then, craft a detailed prompt explaining the analysis you want step-by-step and ask for specific results.
To access ChatGPT’s Advanced Data Analysis capabilities, you need ChatGPT Plus. This allows quantitative analysis based on the data, but be aware that qualitative results may not be fully accurate.
Remember, ask for things that could be quantified based on your data, not for answers needing qualitative results, as you risk ending with not very accurate insights.
One thing I like about data analysis with ChatGPT is the data visualization. When creating reports for your team, you can use AI’s power for more advanced tasks – like regression, hypothesis testing, and more.
You can also do most of these tasks with the help of Python or soon with Excel + Python, but if you are not a Python power, then ChatGPT is the way to go.
Must-know Excel and Google Sheets tips
At a time when generative AI dominates our daily lives, don’t overlook the value of honing your spreadsheet skills.
Mastering Excel and Google Sheets techniques builds critical analytical muscles that enable you to understand the “why” behind the data.
Whether you’re an SEO analyzing keyword lists, a PPC specialist reconciling ad spend, or a CMO interpreting campaign analytics, leveling up your spreadsheet skills is time well spent.
With the right methods, you can wrangle even the largest datasets into clear, actionable insights.
Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.
