12 Surprisingly Easy Excel Tricks for SEOs

Search engine optimizers — don’t fall for the myth that Excel is aged and lifeless.

This classic slice of Microsoft Office holds a ton of potential. Spreadsheets can make your keyword research (among other tasks) more efficient, help you hone in on your targets, and guide you toward mega optimization.

Here are 12 super simple Excel tips and tricks you can use to evolve your SEO skills.

1. Have a Massive Spreadsheet? Use the XLOOKUP Formula

As an SEO, you probably have a ton of keywords and data to sift through. As a result, you’re bound to have some staggering spreadsheets in your repertoire. That’s where the XLOOKUP formula comes into play.

One of the most useful Excel tips, XLOOKUP is an evolution of VLOOKUP. The newer formula, XLOOKUP, lets you search your entire spreadsheet across tabs and in any direction. The formula lets you:

• Find not found (or N/A) items
• Look for partial and approximate matches in addition to exact matches
• Horizontal lookups, instead of just vertical
• Two-way lookups if you need both

So here’s an example breakdown of the formula:

Example: Excel XLOOKUP

The term you use can be a word, category, or even a cell label. XLOOKUP automatically searches for exact matches, but you can gear it to find near matches using “TRUE”.

2. Delimiters to Turn Text Into Columns

If you have a series of terms separated by commas, semicolons, spaces, tabs, or any other character you use to separate items, you can use Excel’s delimiting feature to turn this “list” of sorts into sequential columns.

Here’s how to get there:

• Select the cells you want to convert.
• Click Data, then Text to Columns.
• Click the Convert Text to Columns Wizard and then head to Delimited and Next.
• Choose which delimiter you want to use (in the Other section, you can type in any character). Click Next.
• Pick Column Data Format, or stick with the automatic selection from Excel.
• Pick the Destination or the cells you want your data to appear across. When you’re finished, select Finish.

Example: Excel Delimiters

3. LEN Lets You Manage Character Count Easily

The latest Excel tips and tricks include LEN, a tool to help you keep your character counts in check.

This is important for SEOs because there’s often the need to keep meta content (like titles and descriptions) in the lines.

You can also use LEN to reorganize strings of text (putting the longest one at the top, for instance).

According to Microsoft, LEN is a String/Text Function with a really simple syntax:

=LEN(cell)

Excel Tips and Tricks

When it comes time to transport data from your spreadsheet to your CMS, you’ll minimize time spent editing online. That’s a pretty valuable return.

4. Use the PROPER Formula to Adjust Text Style En Masse

Say you transported your data onto your excel spreadsheet, but most of your keywords are mixed between lowercase and uppercase letters. You want to add a column with keyword versions that have the first letter of each word capitalized.

The PROPER formula lets you select a range of data and change the text so it’s capitalized. It’s pretty cool and surprisingly useful for an SEO professional. Here’s what it looks like in action:

=PROPER(cell) or =PROPER(cell:cell)

Excel PROPER Formula for SEOs

The second example above works for a range of cells. Whichever you use, just highlight the cells you want to adjust, and apply the formula in another area.

This tool has come in handy for me more than once, especially when I’m trying to clean up a document to make it worthy of the client’s eyes. Excel tricks like this don’t get much easier.

5. Drag Formats Across Cells

All this talk of formats, formats, formats may have you wondering: Is it tedious to implement this syntax across the spreadsheet?

No. Thankfully, you can drag formats across cells to apply them in bulk. If you want to use a format across rows or columns, it’s quite simple.

Here’s a basic example to help you get the gist of the trick:

Example of Excel Drag Format

You have columns E and F, and you want to multiply them by one another in 20 rows. Type the formula into the top of another column (in this case, H).

Then, click the cell you wrote in to highlight it. Click and hold, and drag down the rows you want to apply the formula to. When you unclick, you’ll have formulas across the board!

Consider using this for the formula we just mentioned, LEN — which counts characters. You can have your meta title lengths in two shakes of a lamb’s tail.

6. REGEXTRACT Is One of the Most Useful Excel Tips

The REGEXTRACT formula is a more efficient alternative to finding and copying every cell in a robust spreadsheet.

Use this formula and you can pick and choose all URLs before plucking the clean domain. The point is that it’s way quicker than clicking a million times. (Spoiler alert: That was an exaggeration.)

While REGEXTRACT isn’t quite as painless as some other formulas — namely the PROPER trick we explained above — it’s doable. But instead of breaking down the hilariously long string of code for you here, I’m going to lead you to a really great resource.

It’s an Ahrefs Excel spreadsheet solely dedicated to showcasing how REGEXTRACT works. If you click on any of the cells in the REGEXTRACT column, you’ll see the formula listed beside fx.

Example: Excel REGEXTRACT

Yes, it’s lengthy, but take this chance to copy it. And save the spreadsheet if you need to. I’m all about doing what you have to do to level up your SEO skills.

7. Transpose to Switch Rows Into Columns (or Vice Versa)

So you’re developing a spreadsheet, plugging along — who knows, maybe even enjoying it — when you realize you’ve got it all wrong. You have a row of data that needs to switch axes asap.

That’s where transposing rows into columns comes into play.

Here’s how you do it:

• Select the range of data you want to transpose, and copy it.
• Find the top of the row or column you want to switch to, and after right-clicking, click Paste Special.
• Hover over the options to find Transpose and click it.

Excel Tips to Transpose

Excel must-know tricks like this come in handy for SEOs. You may encounter the need to reorganize backlink catalogs, deduplication pages, and more — and a simple row-to-column (or the other way around) can make a world of difference.

8. Excel Must Know Tricks Include Using Pivot Tables to Your Advantage

Not all the data in your spreadsheets belongs there. Unwelcome outliers can throw off your SEO analysis for an entire domain. What if there was a trick to help you spot those outliers as well as trends within the work?

That’s where pivot tables come into play. It’s pretty easy to do, too.

Highlight any cell on your spreadsheet. Click Insert, then Pivot Table. Then you just have to fill out this dialogue box:

Excel Pivot Table Tricks

For example, in the Table/Range section, you can write “cell:cell”. You can select New Worksheet if you want your pivot table to go in a separate spreadsheet, or you can choose Existing Worksheet for the pivot table to go in your highlighted cell. That’s what the above screenshot shows.

Then, once you click OK, Excel will prompt you to build a report. Choose which fields you want to include from the PivotTable Field List on the right by dragging and dropping them where you want.

In the end, the functioning pivot table offers you insight on potential outliers, plus it gives you new options for organizing your data that you may not have thought of!

Example: Functioning Pivot Table

9. Eliminate Duplicates with the COUNTIF Formula

Deduplicating content is part of an SEO expert’s job description. It’s a necessary step if you want to clean up someone’s site and set a clean slate for content rollout.

The COUNTIF formula directly relates to this. It lets you eliminate duplicates of content within your spreadsheet. So if you’re tracking what’s already up on a website — or you’re making certain to avoid mistakes before you publish anything at all — COUNTIF will come in handy.

It works for exact duplicates, so you don’t have to worry about semantic keywords.

Here’s an example formula:

=COUNTIF(A2:A5, A5)

You’re determining if there are any exact duplicated within the specified range of cells.

Run through Microsoft’s how-to for COUNTIF . Keep in mind that this type of formula (statistical functions) aren’t case sensitive. Also, erroneous characters won’t work; if you’re concerned about this, use the CLEAN function to eliminate them.

10. You Can Open Multiple .xlsx Files At Once — and Shift Between Them

If you’re an SEO, you probably have a ton of spreadsheets that you’re working on, all at the same time. This quick Excel must-know trick lets you open them all at once, so you can get on with your day!

Here’s how to do it:

• Highlight all the .xlsx files you want to open in bulk.
• Press Enter.
• All files will open at the same time!

Believe it or not, that’s all there is to it. In the short-term, it may save you mere seconds — but every SEO knows that we’re in it for the long game. Just imagine how much time it will save you!

11. Make Your Own Shortcuts

If you use Excel all the time, there will come a time when you want to customize your shortcuts for your unique needs.

SEOs know this feeling all too well.

While the traditional shortcuts (namely Save, Undo Typing, and Repeat Typing) are handy, they don’t encompass all of your frequent requirements.

You can, however, create new shortcuts to build your very own shortcut menu. For example, if you want to add Copy to the shortcut menu, simply follow this path:

File → Options → Quick Access Toolbar → Add Copy to Right Column from Left → Save

If you go about this path yourself, you’ll see that there’s no shortage of options for you to add to your shortcut menu. We’re talking freeze panes, conditional formatting, add or remove filters, pivot table and more.

I don’t know about you, but I love handy Excel tips and tricks like this one!

12. The Last of the Excel Tips and Tricks: Use WildCards

The CTRL+F option to find something specific is an old trick of just about every trade, but it’s precise. Sometimes, you’re not so sure about what you’re looking for — and you need something a bit more vague.

That’s where wildcards come into play. Specifically, we’re talking about these: ? and *

The question mark and asterisk let you search for something close to what you’re actually looking for. Specifically, the question mark means one character, and asterisk means at least one character.

The Latest Excel Tips and Tricks, At Your Fingertips

According to The 2020 SEO Jobs Report by Backlinko, analytics is one of the top skills that recruiters search for in SEOs. Meanwhile, 63.4% of SEO job postings require candidates to have experience with a specific tool. Believe it or not, Excel is definitely one of those tools. Not only will the most useful Excel tips help you land a job in search engine optimization, but they will also help you thrive in your career.

As for me, I’m all about anything that makes life easier — and anything that will help link searchers with the content they’re seeking.

  This article was written for Business 2 Community by John Lincoln.
Learn how to publish your content on B2C

---

Author: John Lincoln

Follow @johnelincoln

John Lincoln (MBA) is CEO of Ignite Visibility, a digital marketing instructor at UCSD and author of the book “Digital Influencer, A Guide to Achieving Influencer Status Online,” Lincoln has worked on over 400 marketing campaigns including clients such as FOX, USA Today and BTO Sports. He … View full profile ›

---

More by this author:

• How to Elevate Your Strategy With Pinterest Business
• 5 Tips for Improved Social Media Account Management
• Amazon Ad Types: What You Need to Know to Successfully Boost Your Sales