Your spreadsheet is already an SEO tool. You just need the right formulas.
If you do SEO, you live in spreadsheets. Keyword lists, rank tracking, content calendars, link audits -- it all ends up in a grid of rows and columns. The problem is that the interesting work (analyzing competitors, writing meta descriptions, building content briefs) usually happens somewhere else: ChatGPT in another tab, Ahrefs in a third, a Google search in a fourth.
SheetMagic collapses that workflow into one place. The five methods below are not abstract ideas -- they are exact formulas you can paste into your sheet right now. Each one replaces a manual process that would otherwise take hours.
If you are new to SheetMagic, start with the getting started guide to install the add-on and run your first formula.
1. Pull live SERP data for any keyword with =SERP()
Most keyword research starts with a Google search. You type a term, scan the results, and manually record titles, URLs, and snippets. With =SERP(), you skip the manual part entirely.
Set up your sheet:
- Column A: your seed keywords
- Column B: the SERP formula
=SERP(A2)Returns up to 20 structured search engine results for the keyword in A2, including titles, URLs, snippets, and ranking positions.
That single formula returns structured data for up to 20 results per keyword. Drag it down column B and you have SERP data for your entire keyword list without opening a browser.
Take it further with =BULKSERP():
=BULKSERP(A2)Returns search results in a horizontal layout -- one row per keyword, with results spread across columns. Easier for comparing multiple keywords side by side.
=BULKSERP() lays results out horizontally instead of vertically, which makes it easier to compare multiple keywords in a compact view.
Each SERP query uses integration credits, not AI tokens. The free tier includes 50 integration credits per month -- enough to research about 50 keywords. Paid plans start at 1,000 credits. Check the pricing page for details.
Cluster keywords by search intent with =AITEXT()
Once you have SERP data, use =AITEXT() to classify each keyword by intent:
=AITEXT("Classify this keyword by search intent. Return exactly one label: informational, navigational, transactional, or commercial investigation. No explanation.", A2)Returns a single intent label for the keyword in A2. The no-explanation constraint keeps the output clean for filtering.
Now you can filter your keyword list by intent. Sort by "transactional" to find money keywords. Filter "informational" to plan blog content. This turns a flat keyword list into a prioritized content strategy.
2. Analyze competitor content without leaving your sheet
Here is the workflow: scrape the top-ranking pages for your target keyword, extract their content structure, and use AI to find the gaps your content should fill.
Pull the top-ranking URLs
Start with =SERP() to get the URLs ranking for your target keyword. You already have these from step 1.
Extract each page's meta title and description
Use the dedicated meta extraction formulas. Put competitor URLs in column A:
=GETMETATITLE(A2) in column B
=GETMETADESCRIPTION(A2) in column C
Pull the heading structure
Extract the H1 and H2 tags to understand how competitors structure their content:
=GETH1(A2) in column D
=GETH2(A2) in column E
Scrape the full page content
For deeper analysis, pull the full page text:
=VISIT(A2) in column F
This retrieves up to 50,000 characters of page content.
Run AI analysis on the competitor data
Now use =AITEXT() to analyze what you have collected:
=AITEXT("Based on this page content, list the 5 main topics covered and identify one topic gap that a competing article could fill. Be specific.", F2)
The result is a competitor analysis spreadsheet that would normally take a full afternoon to build manually. Every cell is formula-driven, so you can rerun the entire analysis for a different keyword by changing one cell.
=AITEXT("Compare these competitor meta descriptions and identify what makes the strongest one effective. Then write a better one that is under 155 characters. Descriptions: "&C2&" | "&C3&" | "&C4&" | "&C5, B2)Feeds multiple competitor meta descriptions into one prompt and generates a superior version based on the page title in B2.
3. Generate meta descriptions and title tags at scale
Writing unique meta descriptions for hundreds of pages is one of the most tedious tasks in technical SEO. Here is how to do it in minutes.
Sheet layout:
- Column A: page URL
- Column B:
=GETMETATITLE(A2)(pull existing title) - Column C:
=GETH1(A2)(pull the H1 for context) - Column D: target keyword (enter manually or from your keyword sheet)
- Column E: new meta description (the formula below)
=AITEXT("Write a meta description for this page. Rules: under 155 characters, include the keyword ""&D2&"" naturally, end with a clear call to action. Page title: "&B2&". Main heading: "&C2, "")Generates a keyword-optimized meta description using the existing page title and H1 as context. The double quotes around D2 ensure the keyword appears as a quoted term in the prompt.
Why this works better than generic meta description generators: The formula uses the actual page title and H1 as context, so the AI writes descriptions that are relevant to the specific page content -- not just the keyword.
For title tags, use a similar approach:
=AITEXT("Write an SEO title tag under 60 characters for a page about "&C2&". Include the keyword ""&D2&"" near the beginning. Make it compelling for search results.", "")Generates a click-worthy title tag that fits within Google's display limits.
Test one row first. Review the output. Tweak the prompt if needed. Then drag down to generate descriptions for your entire site. For more prompting strategies, see the complete AI formulas guide.
4. Build content briefs that writers can actually follow
The gap between "we need an article about X" and a published piece is usually a content brief. Most briefs are either too vague (a keyword and a word count) or take too long to write. SheetMagic can generate detailed briefs in seconds.
Step 1: Gather the raw inputs.
Set up columns for the target keyword, the top-ranking URLs (from =SERP()), and the heading structures of competing articles (from =GETH2()).
Step 2: Generate the brief.
=AITEXT("Create a content brief for an article targeting the keyword ""&A2&"".\n\nInclude:\n- Target audience (be specific)\n- Search intent\n- Recommended word count\n- 6-8 suggested H2 headings\n- 3 key points each heading must cover\n- Internal linking suggestions\n- One unique angle competitors are not covering\n\nCompetitor headings for reference: "&D2, "")Generates a comprehensive content brief using the target keyword and competitor heading data as inputs. The newline characters (\n) make the prompt readable inside the cell.
Each brief lands in a single cell. You can wrap the column to display the full text, or copy the cell contents into a Google Doc for your writers.
Step 3: Generate a meta description and title tag for the planned content.
Use the formulas from section 3 above, pointing them at the brief content instead of an existing page. This means your writers have SEO-ready metadata before they start writing.
Content briefs work best with models that handle long output well. Claude and GPT-4o both produce strong briefs. You can switch models in the SheetMagic settings under Extensions > SheetMagic without changing your formulas. See all supported providers.
5. Monitor your rankings and track changes over time
This is where the spreadsheet format really shines. Because =SERP() returns live data, you can build a rank tracking system that updates on demand.
Sheet layout:
- Column A: your target keywords
- Column B: your domain (e.g., "sheetmagic.ai")
- Column C:
=SERP(A2)(pull current results) - Column D:
=AITEXT("From these search results, find the ranking position of the domain "&B2&". If it does not appear in the results, return Not ranking. Return only the position number or Not ranking.", C2)
=AITEXT("From these search results, find the ranking position of the domain "&B2&". If it does not appear in the top results, return Not ranking. Return only the position number.", C2)Extracts your ranking position from the SERP data. Returns a clean number for easy tracking.
To track changes over time:
- Create a new sheet tab for each week or month
- Copy your keyword list and formulas
- Use
=AITEXT()to compare this week's positions to last week's
=AITEXT("Compare these two ranking positions and describe the change. Current: "&D2&". Previous: "&E2&". Return format: up/down/unchanged and the number of positions moved.", "")Compares current and previous ranking positions. Put last weeks data in column E.
This is not a replacement for dedicated rank tracking tools if you need daily monitoring of thousands of keywords. But for tracking 50 to 200 priority keywords on a weekly basis, it is faster to set up and costs nothing beyond your existing SheetMagic plan.
The complete SEO spreadsheet system
Here is what makes this approach powerful: everything connects. Your keyword research (step 1) feeds into your competitor analysis (step 2). Your competitor analysis informs your meta descriptions (step 3) and content briefs (step 4). Your rank tracking (step 5) tells you whether the content you published is working, which feeds back into your keyword research.
It is a closed loop, and it all lives in one spreadsheet.
Formulas used in this guide:
| Formula | Purpose | Uses |
|---|---|---|
=SERP() | Pull live search results | Integration credits |
=BULKSERP() | Horizontal SERP layout | Integration credits |
=GETMETATITLE() | Extract page title tags | Integration credits |
=GETMETADESCRIPTION() | Extract meta descriptions | Integration credits |
=GETH1() / =GETH2() | Extract heading structure | Integration credits |
=VISIT() | Pull full page content | Integration credits |
=AITEXT() | Generate and analyze text | AI tokens |
Web scraping formulas (=SERP(), =VISIT(), etc.) use integration credits. AI formulas (=AITEXT()) use AI tokens. These are separate quotas tracked independently in your plan. The free tier includes 5,000 AI tokens and 50 integration credits per month. Check pricing for paid plan limits.
For a deeper dive into web scraping formulas, read the complete web scraping guide. For more AI formula techniques, see how to use AI formulas in Google Sheets.