A sheet records your backlinks but can't tell you when one is lost or turns nofollow. What to check, a Google Sheets formula, and backlink monitoring options.
A spreadsheet is a fine place to record the links you build. It is a bad place to find out whether they still exist, because a sheet only knows what you typed into it. Keep the sheet if it works for you, and add a check that opens each linking page on a schedule. Here is what goes wrong when nobody checks, what each check should look for, and three ways to run it, from a formula in your sheet to a monitor that does it for you.
Why links in a sheet go stale
Pages on the web disappear all the time, and the sheet never notices. Pew Research Center's link rot study checked a sample of pages from each year between 2013 and 2023. Of the pages from 2013, 38% were no longer accessible in 2023, and for pages from the 2021 snapshot "about one-in-five were no longer accessible just two years later."
Pew measured pages in general, not pages carrying your backlinks. Still, a page you earned a link on can be deleted, moved or edited like any other, and your row stays green whatever happens to it.
What a check needs to look at
A row in your sheet should answer four questions, each confirmed by opening the live page.
- Is the page still there? If the page is gone for good, the link on it is gone too.
- Is your link still on it? Editors update old posts and cut paragraphs. The page can live while your link does not.
- Is it still followed? Google's guide to qualifying outbound links says links marked
sponsored,ugcornofollow"will generally not be followed." The publisher can add one of those values after you log the link, so check it each time. - Does it still point where you meant? If your target URL now redirects, check that the chain ends on the page you want.
Log the anchor text too. It is most useful across the whole profile, so read your anchor mix as a pattern rather than row by row.
Option 1: check it inside Google Sheets
If you track a small number of links, you can run a basic check with a formula. Google's IMPORTXML function pulls data from a web page with an XPath query, using the syntax IMPORTXML(url, xpath_query, locale).
Put the linking page's URL in column A. Then type two XPath queries on each row, using your own domain in place of example.com. In column E, type //a[contains(@href,'example.com')]/@href and in column F, type //a[contains(@href,'example.com')]/@rel.
In column C, put =IMPORTXML(A2, E2) to see whether your link is on the page. In column D, put =IMPORTXML(A2, F2) to see its rel value. Copy all four columns down the sheet.
Read the results this way. If column C shows your link, it is on the page. If column C shows an error instead, either the link is missing or Sheets could not load the page, so open it by hand. If column D shows nofollow, sponsored or ugc, the link carries that value. If it shows an error next to a found link, open the page by hand: the link may have no rel value, which is how Google's guide says a regular link is marked, or Sheets may have failed to load the page that time.
A rule of thumb we use: any row that is not a found link with no rel value gets opened by hand before you email anyone. Open it by hand as well when a page links to you more than once, since the formula then returns several values and the columns stop lining up, and when the page builds its links with JavaScript, which the formula may not see.
This works, but someone has to remember to look at it, and it tells you nothing when you are not in the sheet.
Option 2: run the sheet through a checker
If you would rather not build formulas, upload the sheet you already have. SerpCrest's Backlink Sheet Checker takes a link-tracking spreadsheet and shows which placements are live, nofollow or lost, with sensitive columns removed in your browser first. It works without an account. For a one-off audit of a list of URLs, the Bulk DoFollow / NoFollow Checker checks whether each backlink is live, dofollow and indexed.
Each run shows the state of your links at the moment you run it. For a fresh answer, run it again.
Option 3: have it checked on a schedule
The step a sheet cannot take on its own is telling you when a link is lost. With an account, our Backlink Monitor takes the spreadsheet you already track links in (.xlsx or CSV) and checks every row on a schedule you pick, from daily to monthly, with a notification when a link is confirmed lost. Passwords, keys and payment details are stripped before the file is saved.
If you would rather move off the sheet, the Backlink Manager, also with an account, keeps one project per site or client. When you save a live URL, it opens the page and checks whether the link is there, whether it is followed or nofollow, whether the anchor text is there, and whether it reaches you through an old domain that now redirects. Project links are checked again every week, and a missing link gets a second look a few hours later before you are notified.
When a link goes missing
Act on a lost link soon after the check finds it, while your last email to the editor is still recent.
- Page gone, site alive: ask whether the content moved, and send the new URL if it did.
- Link removed in an edit: reply to your original thread, quote the paragraph, and ask if it can go back. Keep it to one short email.
- Changed to nofollow or sponsored: if you paid for the placement, Google's spam policies expect
sponsoredornofollowon it, so leave it alone. If it was editorial, it is fair to ask why, once. - Spammy links you never built: usually do nothing. Google's disavow help page says "In most cases, Google can assess which links to trust without additional guidance, so most sites will not need to use this tool" and limits disavowing to cases with a considerable number of spammy links and a manual action, actual or likely. Our post on when the disavow tool still applies goes further.
Reporting to clients from the sheet
If you send clients a monthly sheet, the check is what makes it worth reading: which links are live and which are lost, not just a list of everything ever built. Our White-Label Backlink Report turns a project or a spreadsheet into a branded PDF of live and lost links, the anchor mix and every placement. If you check anchor balance before the report goes out, the Anchor Text & Spam Risk Analyzer shows the distribution and over-optimisation risk.
Start this week with the twenty links you would least like to lose. Run them through one of the three options above, and note what changed since you logged them. If most of your link value sits on a handful of pages, our post on backlink concentration risk shows how to spot that before one of them disappears.
