The Ultimate On-Page SEO Template

The first step to using the Ultimate On-Page SEO Template is installing the necessary add-in for Microsoft Excel. Bing Ads Intelligence, a pretty sweet keyword research tool, is recommended for this template but not necessary. However, the add-in that will make your life significantly easier is SEOtools for Excel, by Niels Bosma.

(Be sure to watch the installation video on the download page, as it can be a bit tricky if you haven’t worked with Excel add-ins before.)

Confirm that SEOtools for Excel is installed correctly:  If it is, you’ll see a “SEOtools” link on the primary navigation bar of Microsoft Excel.

  Next, download my template!   Or, watch the demo video


Step 1: Perform an SEO Audit

The first tab, “SEO Audit,” serves as a benchmark for your SEO project. Following the tool tips using your newly installed add-ins will provide key on-page and off-page information about your target domain.


You should have at least a smidgeon of an idea as to what the different header’s mean for SEO. If not, do a quick Google search and brush up on your know-how. Ultimately, this tab is to serve as a benchmark, upon which to be continually improved.

Step 2: Brainstorm Some Target Keywords.

The “Keyword Research [Brainstorm]” tab is the first of two tabs that will be used to develop the seed list of keywords for your optimization campaign. Follow the tooltips and use the referenced web applications to get the off-the-top list of keywords.


With the four columns in this tab, do your best to get a sum total of 200 brainstormed keywords.

Step 3: Add the Keywords Your Current Visitors Use.

There isn’t any sense in reinventing the wheel, especially when certain parts of the wheel work already. The third tab, titled “Keyword Research [Visitors],” allows you to compare your target keywords with the keywords that are already being used to find content on your website. The more visits, the better.


Of all the tabs in this workbook, this one is the least necessary for a successful campaign. If you have any bit of trouble navigating your way through it, don’t hesitate to place it on the backburner until you have more time to focus.

Step 4: Analyze Your Keywords.

Obviously, a seed list of 200 keywords is going to be tough to incorporate within your site in its entirety. Instead, we’ll use the “Keyword Analysis” tab to cut the keyword list down to a selective few. Though we’ll most likely need to find a middle ground, the ideal keywords are those that are heavily searched each month without many search results. That way, your pages will quickly index and rank properly, and you’ll be sure to gain a decent amount of traffic.


This tab calculates two scores for each keyword:  Keyword Efficiency Index (KEI) and a “Total Score.” The KEI is a once standard formula for calculating the effectiveness of a keyword; however, it’s a cinch to find half a dozen naysayers with a quick Google search. The “Total Score” can be combined with the KEI to improve the accuracy of determining keyword effectiveness.

Once you’ve filled-out the tab, highlight the information and sort by KEI. Delete any row with an index lower than 100. Next, sort by the Total Score. The top 10-15 keywords—barring any newly-discovered relevance—are your targets.

Step 5: Start Optimizing Your Content.

Tie your target keywords with URLs of the pages you’d like to optimize. Following the columns in the “Content Optimization” tab from left-to-right, fill out the information to properly update your page elements—including meta-descriptions, headers, and the like.


The goal of this tab is to incorporate your target keywords into the different on-page elements of your website. One rule of thumb is to use the keywords as early in the element as possible. In other words, for the keyword “cookies,” the header “We make delicious cookies!” isn’t as effective as “Cookies are our specialty!”

Step 6: Track Your Ever-Improving Rankings.

On to the “Tracking (Rank)” tab! This tab will be used on an ongoing basis to track and improve your SEO strategies.


The first column is for your target keywords. Just paste them, and use RankChecker once a month to fill out the rest of the information.

Step 7: Track Your SEO-Related Metrics.

The “Tracking (Metrics)” tab will also be used on an ongoing basis, populated with information from Google Analytics. Tracking-Metrics

Take a temperature check at the start of the project, set your goals, and do a quick 5-minute copy-and-paste from your analytics account as often as you’d like. (The more often, the more sincere your data will be.)

Now, implement your changes!

Once you’ve populated the Ultimate On-page SEO Template and pasted in your initial benchmark data for future tracking, you’re ready to implement your changes. We’re talking logging into your content management system (CMS) and pasting the new titles, headers, URLs, and H-tags in their appropriate locations. This can be an incredibly tedious process, but if you’ve done your due diligence, the payoff will be worthwhile.

Good luck, and let us know how it goes!

Watch the video to learn more

Fill out the form to download!


Search Engine Marketing
  • Written by: Ataur Rahman

    Hello admin, First thanks for your important post. It is very useful for us. I was very happy to have found this site.

    • Written by: Evan VanDerwerker

      Hey, Ataur. Thanks for the kind words and for stopping by. It’s a collection and enhancement of a number of different tools/processes I’ve used in the past. Let me know how it helps, or if you find any room for improvement. Thanks again!

  • Written by: Manggale Web

    This looks like an amazing tool. I immediately downloaded and install it on the computer. However, I am still confused, how to use it
    Do you have a source?

    • Written by: Evan VanDerwerker

      Hey. First of all, thanks for the kind words! I was thinking it might be worthwhile to create a few instruction videos for using the template. Would that be of interest, or would a different format be preferred?

  • Written by: Lucy


    thank you a lot for the template. I really like it. But could you give me some instructions for the keyword analysize?

    What do you meen with search volum for example and what is the relative search volume?

    • Written by: Evan VanDerwerker

      Hey, Lucy.

      Thanks for stopping by, and I’m glad to hear the template is useful. I’ve just recorded and uploaded an instructional video. It’s embedded on this page.

      Let me know if you have any other questions, and thanks again!

  • Written by: Morten Madsen

    Hi Evan
    It seems like a quite useful tool that I could be interested in testing. A few questions, though:
    1) Is there an option to only get results from a specific country?
    – For all of us that focus on non English markets, it will ruin the tool a bit, if it isn’t localized suggestions that will appear

    2) Reg. the visits from specific keywords (tab three in the video) – isn’t it possible to get that data from WMT instead?

    • Written by: Evan VanDerwerker

      Hey, Morten. Thanks for your kind words and interest!

      In reverse order, you are correct that you can get the keywords data from Webmasters Tools (Search Traffic > Search Queries).

      Regarding keyword localization, the two add-ins (SEOTools and BAI) do have that capability. Bing Ads Intelligence, for example, allows you to select a language and country/region that you would like to do research in. You will see this option after you download the add-in and sign-in with your Bing account. SEOTools for Excel can serve as a handshake between Excel and (basically) any information in Google Analytics. After you install the file, click “Google Analytics” in the runner and sign-in with your account. You will then be able to select the profile, the interval, and dimensions–country included.

      I haven’t done too much with these features myself, so let us know how it works!

  • Written by: Carter

    I’ve signed up and confirmed my e-mail but I’m not able to download the template. :/

    • Written by: Evan VanDerwerker

      Hey, Carter. Sorry for the trouble. Give it a go again, and let me know if you experience the same lack of e-mail!

  • Written by: Chris

    Hi Evan, I subscribed to check out your excel template, but it did not appear


    • Written by: Evan VanDerwerker

      Hey, Chris. Once you confirm your e-mail address, the link will be sent in a follow-up mail. However, I’ve just sent the template to you myself.

  • Written by: Nicholas Allen

    Great template!

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Subscribe to my blog?

    Join my mailing list to have my latest blog posts and free content delivered directly to your inbox.

    Post categories

    I seek to empower small businesses and start-ups with the services and skills needed to build and promote a successful online presence.

    Subscribe to my blog?

    Join my mailing list to have my latest blog posts and free content delivered directly to your inbox.

    Whaddaya say? Wanna subscribe?

    Whaddaya say? Wanna subscribe?

    Join my mailing list to have my latest blog posts and free content delivered directly to your inbox.

    You have successfully subscribed!