TRY OUR PACKAGES
Dealing with large amounts of data it’s all too easy to find yourself with excessive formatting in cells that appear to be blank. One way of finding out where your end cell is located is by pressing CTRL+SHIFT+END on the keyboard. This is the point at which Microsoft will save your data too. If you find a lot of the cells between hear and your actual data are blank then you might want to think about getting this tool.
Here’s the MyVlookup formula used beneath a standard Vlookup. You’ll notice that the original Vlookup has failed to indicate that Sarah has worked in HR and Marketing. The MyVlookup will on the other hand return all results from column 2 relating to Sarah.
Whilst there are formulas that enable you to pull out the root domain of a given URL, they are complicated and arent always fool proof. Once installed URL Tools lets you enter a variety of simple formulas to use, but the only one you’re ever really going to need is the =wwwsubdomain function.
Another brilliant feature in SEO Tools (and there are too many to list them all here) is the Google Analytics extension. This allows you to pull in visitor data, traffic source data, Adwords data and much more straight into your Excel spreadsheet. The latest version (v4.1.1) also features integration with Google AdWords.
The keen eyed Vlookup savvy of you out there will have noticed that the formula syntax for the MyVlookup is slightly different. Instead of selecting the entire table array to return a value from, you only need to designate the column (in this case A1:A12) before counting the rows across you want the formula to return a value from (in this case row 2).
5. XS Format Cleaner 1.1.
One of the problems with working on huge list of URLs in Excel is the inability to easily identify duplicate root domains. Whether importing, exporting or pasting URLs directly into your spreadsheet, you’re likely to end up with a whole variety of URLs structures. Some URLs may feature the http:// or www. prefix whilst others dont. Some may include subdomains, some wont.
Of course the obvious solution to the problem of conflicted copies is to invest in a server based network, on which two or more people can edit the same document at the same time. This costs money though, which isnt always worth it if you’re a smaller businesses running on a tight budget.
First of all you need to save your Excel file as a Macro Enabled spreadsheet. Then reopen the file and go into the Developer tab (or press Ctrl +F11). Go to the Insert Menu and click Module and paste in the following piece of code.
Much of the major functionality you can do on Majestic’s main site can be replicated straight into SEO Tools for Excel. From there, you can augment any data with your own, giving you potentially limitless information about any URL or link.
Much of the day to day activity of digital marketing departments and professionals involves handling large amounts of data. The modern digital marketer or SEO is bombarded with tools for collecting, collating and crunching a whole range of data and metrics, from web page analytics and backlinks to SERP rankings. From website analytics to keyword research, bets are you’ll find several competing tools out there that can produce it, track it and report on it.
Unsurprisingly Microsoft have a load of Excel videos and tutorials available for the general user, ranging from complete beginner stuff like locking panes and adding columns to more advanced operations like recording macros.
The Collaboration Macro (or CoMac) effectively stops two people from going in and editing the same spreadsheet at the same time, by essentially checking out the spreadsheet once a user opens it on his / her local machine. Anyone then trying to open the same spreadsheet will be greeted with an on-screen message telling them the spreadsheet has been checked out and by whom. All that’s involved from your part is copying and pasting a bit of VBA code.
Many of us have been there: You’ve spent an entire morning labouring away at a spreadsheet only to discover that one of your colleagues has been hard at work on the very same spreadsheet and Dropbox has created a conflicted copy file, which you now have to go about amalgamating with your colleague’s copy (that’s your plans for the afternoon out the window).
As an SEO in a small to medium sized company, Excel is probably the most versatile and powerful reporting and analysis tool you’re ever likely to use. As an outreach tool it is, in my opinion, without equal in its versatility. It is this versatility that really sets the Microsoft flagship apart, allowing it to handle any kind of dataset, filtering, pivoting and presenting it in any number of useful ways.
There are more professional looking solutions out there to solve collaboration issues on Dropbox but, unlike the CoMac code, they all seem to cost money. Conflicted Copy Pro is one of the more popular Ive found and comes with a free 30 day trial, after which it costs $7 USD a month for the licence.
4. The MyVlookup Macro.
Majestic SEO describes SEO Tools on its own blog thus:
Whilst the Our Community News website might not look the most obvious forum for VBA coders, I can assure you the code does work. OCN has also written similar VBA code for collaborative Word documents on Dropbox.
One of Excel’s greatest assets is its ability to use macros, integrate with add-ons and connect with APIs. Below are a few of my favourites.
SEO Tools also allows you to connect Excel directly to your SEOlytics or Majestic SEO account as well and pull in data directly from there. These API plugins will inevitably save you so much time by allowing you to import the data you need directly into your pre-formatted spreadsheet.
Last but certainly not least we have the XS Format Cleaner.xla , an add-in that will clear up any excess formatting you might have lurking in the recesses of your Excel worksheet. Recommended by Microsoft this little bit of software will cut file size and address problems you might have with memory issues or printing out too many pages.
Whilst Dropbox isnt the only cloud networking solution for small businesses out there , it certainly is one of the most popular. But it does have its drawbacks. One of the most infuriating of these is the dreaded conflicted copy issue.
The holy grail of Excel formulas, the Vlookup typifies why Excel is such a trusted piece of software for SEOs and digital marketers when interrogating large amounts of data. Many of you will have undoubtedly have used the Vlookup formula before but few of you will have encountered the MyVlookup function before. This is a variant on the standard Vlookup that uses a simple bit of VBA code and allows the lookup to return multiple values instead of a single one.
2. URL Tools.
SEO Gadget have put together a very useful Excel tips video featuring SEO Tools and Find My Blog Way has created a more detailed video tutorial on using SEO Tools, which runs for over half an hour and is also well worth a watch.
This is without doubt the simplest way Ive found of taking a huge list of URLs and getting the root domain of each really quickly without any fuss. After this it’s a simple case of copying and pasting as values and then applying conditional formatting to highlight duplicates.
The absolute daddy of SEO plugins. If you work in digital marketing and you ever download anything Excel related it should be Niels Bosma’s SEO Tools . This add-on is free and easy to install, bolting on a whole suite of SEO utilities, lookup and scraping tools into your Excel ribbon. These include onsite and offsite lookups that return everything from meta descriptions and keywords to PageRank and Link Count.
The CoMac does have a few issues and bugs associated with it but hey, it’s free right? You can download the VBA code here and then paste it directly into the Visual Basic Editor in Excel (open it through the Developer tab or Ctrl+F11).
If you’re still learning the basics of Excel, then there are a host of excellent tips, tools and resources out there to help you get to grips with it. One of the best Ive found written specifically for SEOs is Distilled’s comprehensive Excel for SEOs guide .
After the power and versatility of SEO Tools comes a much more modest add-in for Excel. URL Tools is an add-in by James Taylor of iCrossing that I’ve found invaluable over the last couple of years. It may not have the multitude of functions that SEO Tools boasts but what it does do it does very well.
Whatever your data looks like it’s likely that at some point you’ll need to export this data to manipulate it further and when you do this you’re almost certainly going to be using Excel.
Save and exit the VBA Editor and go back into your spreadsheet. You should now be able to use the MyVlookup function (if you can’t try saving, exiting and re-entering the spreadsheet).
When taking a universal approach to SEO, the fee-based add-in SeoTools for Excel by Niels Bosma will have you covered for multiple SEO integrations. The tool offers different programing interfaces or APIs (application programming interfaces) for typical SEO analysis tools. These include Ahrefs, Google AdWords, Google Analytics, Majestic, Moz, Searchmetrics, SEMrush, SISTRIX, and SEOlyitics. Through its toolbar, the add-in allows users to access the program’s individual components and determine via commands the CELL functions for analyzing SEO data. The tool can be downloaded free of charge upon registering on Seo-Tools-for-Excel’s website. But the complete, ad-free version is only available to users who first purchase the fee-based Pro version. For help with installation, there’s a tutorial video on the product’s website.
It’s worth carrying out analyses on performance factors or KPIs (key performance indicators) in order to implement the best-possible online marketing strategy for your website. The most important KPIs for on-page and off-page SEO are the insights on a domain’s visibility, reach. To be included in the KPI analysis are the popularity of the domain itself as well as its various subpages. User metrics, like the number of page visitors, the length of time spent on a site, and conversion rates are of particular interest for companies to know. But without an appropriate auxiliary program, processing such data with SEO analysis tools proves to be both a cumbersome and time-consuming affair. How then, does one take values from SEO tools and synchronize these directly into Excel without having to spend unnecessary time converting CSV files and importing or exporting datasets? This is where SEO add-ins for Excel come into play: businesses, small companies, online marketers, and web analysts are all able to maintain an overview, while also controlling the SEO performance of their pages . In the following paragraphs, we’ll present a selection of the most useful SEO extensions for excel.
Excel with Microsoft 365 and IONOS!
Effective search engine optimization (SEO) is the be-all end-all when it comes to making sure your website remains visible on Google, Bing, Yahoo! etc. The better you optimize your website’s subpages – in terms of both structure and content – the better your site will be evaluated by search engines. Eventually this optimization will pave way to a higher rank in the search engine result pages (SERPs) . The goal of search engine optimization is to increase the reach of your product, brand, or business and so to ultimately drive up turnover. Microsoft Excel is a great option for running different types of SEO data analyses. But despite the fact that there are plenty of browser-based SEO tools on the market, only very few of them can be connected and synchronized directly with Excel. For this reason, those looking to process SEO data need special extensions (add-ins) that have been created specifically for Excel. These enable a direct connection between the tool and Excel’s spreadsheet calculations.
Use Excel to create spreadsheets and organize your data – included in all Microsoft 365 packages!
How SEO tools in excel work and who they work best for.
SeoTools for Excel allows semantic labeling (page titles, headlines, meta descriptions, and keywords) to be clearly displayed in Excel. This provides users with a compact overview of the content structure of their own website.