I've been doing SEO for 15 years and its always changing. One thing that never changes is that often the tools you find to "make your life easier" are built in a way that is too complex for small to medium sized businesses OR the tool doesnt have the flexibility you need. One area where this has come into play is generating an XML sitemap for Google. Which is why I had someone on my team build this simple XML sitemap generator for excel.
First, you have to realize that an XML sitemap is completely optional. Many sites will see little or no benefit from adding one. In particular, small sites with a well done SEO navigation and hierarchy often don't require an XML sitemap. With that said, submitting an XML sitemap with your custom priority and change frequency values is a solid SEO technique that sends the right signal to Google and Bing about your website. So while they are not necessary, an XML sitemap is a technique that we at PIC handle for our clients.
Next, if your website is using a content management system (CMS) such as Wordpress, Drupal or Joomla, then you should be able to generate an XML sitemap with a simple plugin solution. I say should because we've seen some custom builds of these sites where the XML sitemap generator plugins didn't fill the needs of our clients. So if you are running a CMS, I would encourage you to look there first. That is the best way to keep your XML sitemap updated and accurate.
Our Free XML Sitemap Generator Spreadsheet
If you are still here, then you are determined to get an XML sitemap created and uploaded in a simple way. Hopefully this spreadsheet works for you. Simply download the excel file and follow the instructions in the tab. If you have questions, e-mail us. Then if this helps you out, please send a tweet to @walkwithpic or post a link to this XML sitemap tool to help others out.

How to Create an XML Sitemap
Easy Ways to Select URLs for your XML Sitemap:
Step 1: Compile Your Website's URLs
Begin by listing all the URLs of your website that you want search engines to index. Use our free spreadsheet to input your URLs in a single column. Check that each URL starts with the appropriate protocol (http:// or https://) and includes the full path.
Step 2: Add Optional Metadata
You can add the following optional columns to provide search engines with additional information about each URL:
- Last Modified (lastmod): Indicates the date when the URL was last updated. Use the YYYY-MM-DD format.
- Change Frequency (changefreq): Suggests how frequently the content at the URL is likely to change. Acceptable values include always, hourly, daily, weekly, monthly, yearly, and never.
- Priority (priority): Specifies the URL's priority relative to other pages on your site, ranging from 0.0 to 1.0, with 1.0 being the highest priority.
Step 3: Create the XML Structure
Once your data is organized, you'll need to construct the XML structure. Our free spreadsheet will help you build the XML tags for each URL easily. For example:
<url>
<loc>[URL]</loc>
<lastmod>[lastmod]</lastmod>
<changefreq>[changefreq]</changefreq>
<priority>[priority]</priority>
</url>
Replace [URL], [lastmod], [changefreq], and [priority] with references to the corresponding cells in your spreadsheet.
Step 4: Assemble the Sitemap
After generating the XML tags for each URL, combine them into a single XML document. At the beginning of the document, include the XML declaration and the opening <urlset> tag with the appropriate namespace:
xml
CopyEdit
<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
At the end, close the <urlset> tag:
xml
CopyEdit
</urlset>
Step 5: Save the Sitemap
Copy the entire XML content and paste it into a text editor. Save the file with a .xml extension, such as sitemap.xml. Confirm that the file is UTF-8 encoded.
Step 6: Validate the Sitemap
Before submitting your sitemap to search engines, you should validate its structure to ensure there are no errors. You can use online XML sitemap validators or tools like Google's Search Console to check for issues.
Step 7: Submit the Sitemap to Search Engines
To inform search engines about your sitemap:
- Google: Log in to Google Search Console, navigate to your website's dashboard, click on "Sitemaps," and submit the URL of your sitemap.
- Bing: Log in to Bing Webmasters, navigate to your website’s dashboard, click on “Sitemaps” and submit the URL of your sitemap.
By following these steps and best practices, you can effectively create and maintain an XML sitemap using our Excel spreadsheet.
Best Practices for XML Sitemaps
To maximize the effectiveness of your XML sitemap:
- Include Only Canonical URLs: Check that each URL in your sitemap is the canonical version to avoid duplicate content issues.
- Keep the Sitemap Updated: Regularly update your sitemap to reflect any changes in your website's structure or content.
- Adhere to Size Limits: A single sitemap file should not exceed 50,000 URLs or 50 MB (uncompressed). For larger sites, split the sitemap into multiple files and use a sitemap index file to reference them.
- Use Absolute URLs: Always include the full URL, including the protocol, to ensure proper indexing.
- Reference the Sitemap in robots.txt: Add the location of your sitemap to your robots.txt file to help search engines discover it.
User-agent: *
Disallow: /private/
Allow: /public/
Sitemap: https://www.example.com/sitemap.xml