Bulk Generating Page Titles in Google Sheets (and Excel!)

Yeap, chances are that if you’re working with a large enough client that you’re contemplating bulk-generating page titles for, you’d be working with a dynamic system. Ideally, you can just give the developers your rules and they’ll build them in and you don’t need to manually generate anything other than some examples.

Ideally.

So, in the less-than-ideal world where you need to manually generate tens, hundreds, or thousands of page titles, here’s a potential solution to get you started in the right direction.

You’ll learn how to;

• Randomly pick between title ‘keyword’ templates
• Randomly select from CTR-centric templates
• Select a shorter fall back if the variables make your title too long
• Trim the variation off if the title tag is too long

You’ll essentially be able to bulk spin together unique page titles for each page.

The majority of people won’t ever use this, but, the principles behind it could be applied to many different bulk techniques.

Building out your variables

Initially, you’ll need to get all your data together by listing out the variables of each appropriate page. These are the different dynamic bits that get used in the new page title.

You might have this data already, but if everything you need is in the URL, then you could go ahead and extract it by using the formulas I mentioned in my migration guide to extract what you need.

If you’ve got a nice dataset already, then you can just get started with crafting your titles.

For my demo, I’ll be using a mix of the channel (buy or rent), location, and the property types (apartments, houses, etc).

You’ll see each variable I plan on using in a template is in a separate column, which will allow us to pull it into the page title as needed.

Above each of the variable types, I have a fallback option in case the variables are blank.

So if ‘location’ is blank, use ‘Australia’, and If ‘type’ is blank, we will default to ‘Properties’ for the titles.

Building your page title templates

Next, we’ll need to write out the different variations of page titles. These are what will take your variables into account, and then spit out the page title.

You could just want to use the exact same template for all your titles, which is just a nice simple substitution formula.

But that’s too easy. So, instead, we’re going to randomly select from multiple title elements.

This setup will randomly select from multiple choices of template, and then rotate through a few times if the selected template is going to be too long once the specified variants are pulled in.

Probably a bit confusing right now, but as an example, some locations could be 5 letters, like ‘Perth’.

You could then also run into locations that are rather long like ‘Eggs and Bacon Bay’ (legit place in Tasmania!). So having a single title structure to account for both, or even just the median length, could leave you at a loss by not being able to utilise the full character limit and maximise CTR.

There are plenty of ways SEOs choose to write their page titles, however, I normally stick with a tried and tested format of;

KEYWORD | SOMETHING FOR CTR

Following this format, I’ve split the title generator into two parts.

Keyword part of the page title

We have to write out the variations that will be used at the front of page titles.

For many bulk sites, you would essentially just look at using the exact same template for this depending on the keyword structure.

However, for example sake, I’ve gone with the 3 different variations.

You’ll see I’ve included the 3 potential variables that will be used within a template.

You can include as many, or as little variations here as you’d like, and with my template sheet you can do up to 5.

CTR-centric page title extension

The next part will be the extensions that get added after the keyword, with a primary aim of being for CTR, and maybe an additional keyword drop.

Again, I’ve added 3 variations our templates can use.

However, adding those two together for many locations & property types will create titles that will be way too long for us to use as they’d get truncated.

To combat this, we can add some fallback extension variations.

These are significantly shorter than our initial extensions, and they’ll get used if the Keyword + Extension variations getting generated are too long.

Generating your page titles

The magical formula to randomly select from your page titles is;

=INDEX($BA$12:$BE$12,1,TRUNC(( countA($BA$12:$BE$12)*RAND())+1))&” | “&INDEX($BA$13:$BE$13,1, TRUNC((countA($BA$13:$BE$13)* RAND())+1))

Where;

$BA$12:$BE$12 – Cell reference to the Keyword parts

$BA$13:$BE$13 – Cell reference to CTR page

This formula will randomly select from the keyword range, add a pipe in the middle, then randomly select from the CTR range.

So by having 3 of each template, there are a potential 9 options for the title structure.

Now you just need to substitute in your variables.

Adding the substitutions to the same formula as before, we get;

=substitute(substitute(substitute(substitute(substitute( INDEX($BA$12:$BE$12,1, TRUNC((countA($BA$12:$BE$12)* RAND())+1))&” | “&INDEX($BA$13:$BE$13,1, TRUNC((countA($BA$13:$BE$13)*RAND())+1)), $D$8,if(len($D12)>0,$D12,$D$9)), $E$8,if(len($E12)>0,$E12,$E$9)) ,$C$8,if(len($C12)>0,$C12,$C$9)), $F$8,if(len($F12)>0,$F12,$F$9)), $G$8,if(len($G12)>0,$G12,$G$9))

It might look a bit confusing, but the substitution formulas are wrapped around the initial formula, and then within the subsitution formulas I have an IF statement that lets me check if the cell is blank essentially.

So if a variable has a length of 1 or more, swap out that variable.

If the given variable has a length of 0, use the default values for that variable that I set.

For me, that will all spit out;

As you can see though, some of my shiny new page titles are too long!

To fix this, I am going to try to regenerate them again, and again, and again until I’m confident the variations can’t produce one that will fall under the length requirement.

Looping through the page title generation

Whilst it’s not exactly ‘looping’ due to the constraints of a formula, it’s pretty close.

Every time time something on the sheet changes, the page titles get regenerated. It can be turned off, but it’s still just easier to paste the initially generated titles in a new column.

We then throw another column on the end of where we just pasted the page titles, and run a new formula over it.

=if(M12<$C$7+1,L12, substitute(substitute(substitute( substitute(substitute(INDEX( $BA$12:$BE$12,1,TRUNC(( countA($BA$12:$BE$12)*RAND())+1))&” | “&INDEX($BA$13:$BE$13,1, TRUNC((countA($BA$13:$BE$13)* RAND())+1)),$D$8,if(len($D12)>0,$D12,$D$9)), $E$8,if(len($E12)>0,$E12,$E$9)), $C$8,if(len($C12)>0,$C12,$C$9)), $F$8,if(len($F12)>0,$F12,$F$9)), $G$8,if(len($G12)>0,$G12,$G$9)))

This is exactly the same as the earlier formula, except for an IF statement at the start. This IF just checks the length of the page title that was just pasted to the left.

If the pasted page title matches a requirement (set to be 59 characters or less), then copy that page title over.

If the page title is longer than 59 characters, generate it again!

So for this example, I have pasted my titles from the first generation on the left, and then run the new formula on the right, and you’ll see two more page titles matching the criteria that haven’t previously fallen within the length requirements.

Then we repeat that again;

And again, and again, and again, until there’s a pretty slim chance of hitting a match that will fall under the length requirement.

My example runs through this 7 times, and then it changes on the 8th time.

Using a fallback extension when the titles are too long

Once we’re happy that no more short titles will be generated, we move onto using the shorter fallback extensions.

=if(AK12<$C$7+1,AJ12, substitute(substitute(substitute(substitute( substitute(INDEX($BA$12:$BE$12,1, TRUNC((countA( $BA$12:$BE$12)*RAND())+1))&” | “&INDEX($BA$14:$BE$14,1, TRUNC((countA($BA$14:$BE$14)* RAND())+1)),$D$8,if( len($D12)>0,$D12,$D$9)), $E$8,if(len($E12)>0, $E12,$E$9)),$C$8,if(len($C12)>0, $C12,$C$9)), $F$8,if(len($F12)>0,$F12,$F$9)), $G$8,if(len($G12)>0,$G12,$G$9)))

This formula is the one we just used, that checks the length of the previously generated title, and then regenerates it if it’s too long.

However, there’s one small tweak.

It will look inside this range: $BA$14:$BE$14

That range has my fallback extensions, with the previous one looking at the primary extensions in $BA$13:$BE$13 – so it’s now looking one below.

Pulling in this shortened extension will give us back more page titles that fall within the range.

My example validates all of them, except one, in the first go, however, as before, I repeat the formula again just to confirm.

So we’re now left with one pesky page title that is too long before the location.

Albeit, not a real one, but this is still a scenario you could encounter.

So to fix this, the absolute final formula just strips the extension.

=if(AS12<$C$7+1,AR12, substitute(substitute(substitute(substitute( substitute(INDEX($BA$12:$BE$12,1, TRUNC((countA($BA$12:$BE$12)* RAND())+1)),$D$8,if(len($D12)>0, $D12,$D$9)), $E$8,if(len($E12)>0,$E12,$E$9)) ,$C$8,if(len($C12)>0,$C12,$C$9)), $F$8,if(len($F12)>0,$F12,$F$9)), $G$8,if(len($G12)>0,$G12,$G$9)))

That single page title, will now successfully validate once we don’t pull in the extension.

And then just pasting that final page title generation into a column near the start, we end up with all our unique page titles with all their variables swapped out.

Not exactly my best title copywriting in history… but you get the point!

You will just need to repeat the copy/pasting bit every time you edit your variables or templates, but you won’t need to touch those formulas!

So once setup, it’s a pretty easy process to bulk generate the titles going forward.

Or, just use my sheet to save you some time!

Using my page title generation template

I know this isn’t the easiest thing to follow along too, so I made a sheet that you can copy and use.

There are a few extra things in it to try and make the process a bit easier, but you need to be careful with it!

It’ll break pretty easy because of the macro that gets run to generate the titles. So please don’t insert/delete rows, or columns. Just input your existing data.

If you do want to modify things, just know that the ‘generate’ button will probably no longer work and that you’ll just need to manually copy the data around.

1. Copy the document

Click the link below, and copy the generator to your Google Drive.

2. Paste your data in

I’ve included 2 extra variable columns in case you want to use them, allowing you up to 5 different variables. Just paste your data in and follow the same format as the example data.

3. Fill out the variable details

Update your variable names, along with the defaults that should be used if the variable cell is blank.

You can even set the title length limits for the formulas if you want (it won’t change the conditional formatting on the cells though).

4. Fill out the page title template variations

You can include up to 5 of each version, but just make sure you have at least one in each row.

If you don’t want a different extension fallback, just include the same templates as the extension aboe it, and the formula will just strip them off if it’s too long.

5. Click generate!

I built a macro into the sheet that will work through all the columns, and copy all your title data as it goes.

This will save you the need to manually doing anything. Just fill out the template and then this button will do the hard work!

It’s going to ask you for access to the document, but this is just to run the macro itself. That macro is all self-contained, nothing leaves the document.

If you don’t want to give the macro permission to modify the document, just unhide the hidden rows, and then follow the process above of copying your page titles into each ‘pasted’ column. Then just paste the final 10th generation into the first page title column and you’ll be done.

Why?

A few different reasons why you’d be stuck trying to generate your titles.

My first scenario for this was to deliver 200 page titles to a client, across all their service pages, and safe to say that I really wish I had something like this back then!

Big shout out to the guys here though, as I got the original formula from them! Have tweaked it a bit, but the core thanks sits with them.

Let me know if everything works okay for you, and whether you have any issues or feedback!

I would love to hear if you’ve found it useful.