Generating Sitemap Links with Excel

We have become good friends with the Ysais family of Kyle, Texas.  They visited us in Colorado last summer and we all had a great time.  Corrina stays in touch with Marie almost every day, our daughter Skypes and plays Minecraft with the two boys, and Michael taunts me with mouthwatering photos of his smoked brisket.

Marie runs Ysais SEO, which helps businesses optimize their search engine results.  She asked me to help with an Excel workbook to create Google sitemap links.  I took the file she sent and worked it into a tool that can create several links for each specified website.

I thought I would share the workbook because it makes a good example of how to use Excel’s table feature and its HYPERLINK function.  With Marie’s consent, here is the workbook. Click on the image to open the file.

sitemap generator

There are four sheets, each with its own table (note that these are not “data tables”).  The first three tables provide the building blocks for the links: the Google sitemap text (the “prefix”), the domain names, and part specifiers (the “suffix”).  On “SiteMaps”, the last tab, a table generates hyperlinks for all possible combinations of the three text blocks.

To use the workbook, make sure that the prefix text meets your needs.  As with all inputs in this workbook, the prefix text is colored blue.  You can insert rows above if you need more than one prefix. Next, enter the domain names.  There is room for 500 domain names.  Blanks are fine, and you can always insert more rows if you need more.  The final input step is to review the suffix text.  You can insert more suffix items if you need more.

Now go to the sitemap generator on the last sheet.  Click on the “Good?” filter and select “TRUE” only.  This will hide the blank links.  Be sure that the bottom of the “Last?” column shows a “TRUE” value.  This insures that the last possible combination of your text blocks has been reflected in the sitemap list.  If not, you will need to insert more rows into this table.

Marie reports that the workbook also made it easy for her to create Bing sitemaps.

Note: a good way to insert rows into any of these tables is to click on the cell labelled “Count” at the bottom of the first column.  Insert the number of needed rows and copy down any formulas from above as needed.

Please let me know if you have any questions.

Advertisements
This entry was posted in Spreadsheet Tips and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s