How to Make a Small Fortune With Google Sheets | Create a Headline Generator in a Google Sheet

In this tutorial, we're going to create a headline generator. The data or the information that I got from this is from LandingFolio. Go check them out. They have this great headline formulas e-book. I'm using the free version. If you want more formulas, they have 200 headlines.

What I'm doing here is a very light version of what I think Danny's doing at headline. But I'm not really going to much of what he's doing there. This is just to show you how to use Google Sheets to make something like this.

What do you get after reading this tutorial?

• Make something like sentence generators.
• If you have your own formulas, you can come up with topic ideas. This is actually a really interesting way if you have a couple of key terms or keywords and you want to generate ideas and you know the formulas and you just enter it.
• You can do something like this, which I created (something like a web app).
• You can even create a worksheet for your own internal processes or internal business if you want.

So here's the idea: We have these topics or named areas to fill in. And once we fill those in, we get headlines. The headlines are based on the formulas.

Let's get started!

In the image above, you see Audience, Undesirable Outcome, Product/Solution. A very typical headline might be about designers where your website is not working or developers, are your websites not working?

Here are some examples for you:

Once we enter these items, we're going to generate six headlines.

You can also generate many more. I'll show you how to make more than this in a while.

Using name ranges in Google Sheets

Here is the one key thing: We're going to do a very interesting thing today. We are going to name ranges and this is going to help us understand, help us generate more headlines or outcomes than we ever imagined, because we'll be able to easily remember what this cell is.

So that is D6 and we might not remember that it’s in the Settings tab, then !D6. We can actually name this range and then use that in formulas.

How do we do name the range and use it in formulas?

Let's go to the menu at the top and click on “Data” then select “Named ranges.” You’ll see that I already created a few: audience, outcome, product.

For our purposes for this particular case, it's a single cell. You wouldn't necessarily think like audience is that much different than “SETTINGS!D6”

If you know Audience is there, then I just have to type in SETTINGS!D6. But what ends up happening is once we know it's easy to know a word, the formulas are going to know it consistently. It will remember it much easier, so we can come back to this and be like, “Oh yeah. I can add 10 to 12 more headlines very easily by using this formula.”

The other two formulas I'm going to be using is concatenate and name range.

I'm going to show you concatenate and the proper way to get title case. We want all of our headlines capitalized, but the main thing we're going to do right now is named ranges.

Column B is just some text, but this result is concatenation of Audience, then the phrase “Are Your”, the name range of outcome, and then a question mark.

Let me build this one: {Get/Do Something} Like {Authority} - Without The {Pain Points}

If you look over here, we have Audience, Outcome, and Product. we don't have the other ones, so I'll show you how to name ranges.

We have D12 selected. Then on the right side of the page, “Named ranges”, click on “Add a range.” I’m going to type in “something.”

I'm going to go to D14, which is Authority. We’ll do the same thing: Add a range and call it “authority.”

Same steps for D16, Pain Points. Add a range and call it “pain.”

You can name them as short or as long as you want.

Now we go to the Results column. We're going to do =CONCATENATE and we’re going to add them together.

Just type “something” and now it changes color. We know it is grabbing the right thing.

This is the rest of the formula:

=CONCATENATE(something,“ like “,authority,“ without “,pain)

Now we have “Get a Body like Arnold Schwarzenegger without Going to the Gym.”

That was over here, in the Settings tab:

We can also add title case, because it’s a headline. Just type proper( after the equals sign and before the first parenthesis. Then we just wrap it in the summation points.

=proper(CONCATENATE(something,“ like “,authority,“ without “,pain))

Now we have an item we can copy and paste. One thing I would say is this: If I copy this cell and paste it within Google sheets, it's going to copy the formula. What I can do is insert to the right results. We just do the array formula:

=arrayformula(D2:D)

And now I just have the text.

I hate to do this. I don't do this very often, but you can hide column D.

What's going to happen is every single time you go and see this, you're going to be like ,"What's here?" You're going to open it up and you’ll remember.

I recommend that if you do it this way, you add the array formula and you want just text for someone else, maybe to copy it instead of having a copy and paste values.

This column can go on a separate sheet and you can name it what you want. Then you can make column E just the only formula on this entire sheet if you wish.

So that's a little bit of a better item that you can do, one thing to make this sheet a little bit better.

Now let's do the next one: How To

Let’s put in proper and concatenate.

=proper(CONCATENATE(“How To ”,something,” like “,authority))

Let’s apply the same formula for How Can You:

=proper(CONCATENATE(“How Can You “,something,” without “,pain))

Now we have literally three items here and we have any number of different formats that we can do. We can add ten more with the same items (in Settings tab).

We only have to enter six items and we can get dozens and dozens of headlines based on this (Headlines tab).

This is just using named ranges it's using concatenate and proper.

One extra thing you can do to this is you can create drop-down menus here:

I hope you enjoyed this tutorial! Don’t make any sheets. Make Better Sheets.

Watch the video version:

Learn more about making money with Google Sheets:

Get more Google Sheets Tutorials at BetterSheets.co

We love Google Sheets here. If you love Google Sheets as well, you might want to consider becoming a member of Better Sheets. For only $19/month you can access over 200 Better Sheets tutorials. Learn Apps Script in under 40 minutes. Design better dashboards. Make your sheets faster and yourself more confident in sheets.