There is always a point where a website owner will need to take a closer look at URLs within their XML sitemap. Viewing XML sitemaps directly in the browser is clunky and with all the extra elements such as last mod dates and image links, what you’re presented with on screen can be overwhelming.
At Crimson Agility I show my fellow team members how to use Microsoft Excel and the process that I am about to show you is what we use in-house to work with XML sitemaps in a variety of ways. I should note that this method only works with the desktop version of Microsoft Excel.
Importing your XML Sitemap Into Microsoft Excel
Here is a quick rundown of the process.
Open Excel and navigate to “Data”, select the “From Web” option, and enter your XML sitemap address. Select the “URL” and then press the “Load” button. You now have your XML sitemap in Excel format!
Let’s take a closer look at the steps involved as I have provided screenshots and a video to explain this process. I even included a few power tips at the end for those of your that are excited about using Excel to work with your XML sitemap data. I break things down so that anyone can follow along and do this on their own computer.
Step 1 – Define your XML sitemap address
The first step is to define your XML sitemap. If you don’t know your XML sitemap address, it’s typically can be found at the bottom of your website’s robots.txt file which can be found at “https://domain.com/robots.txt“. If formatted correctly, the final entry should be the URL to your XML sitemap. Copy your XML sitemap address and proceed to step two.
Step 2 – Open Microsoft Excel and Navigate to Select Data and then Choose “From Web” and enter your Sitemap URL
The next step is to open Microsoft Excel and select the blank workbook option. Once inside your new workbook navigate to Data using the top navigation and select the “From Web” option at the top left. You will be presented with a box that allows you to enter your XML sitemap URL. Paste your copied XML sitemap URL into the box and click OK.
Step 3 – Select the URL Option and Load
After you enter your XML sitemap address you will be presented with a screen that has the option to select “URL” and once selected, there will be an option that will open up at the bottom left that says “Load”. With the URL option selected click on the Load button.
Step 4 – Begin Working with your XML Sitemap Directly Within Excel
If you have followed the steps above you should be presented with an XML sitemap that contains all the URLs in Excel format. The data can then be sorted, sliced, rearranged, highlighted, and commented on at your discretion.
It’s incredibly simple and very powerful when you are faced with the task of reviewing your XML sitemap. There are so many uses for this with technical SEO, it’s one of the first items I team my junior team members and something I had to share with the community. Here are a few pro tips that I have to offer once you have your XML sitemap in excel.
Technical SEO Tips for Microsoft Excel and XML Sitemaps
Sorting by Alphabetical Order – I have found that sorting the XML sitemap by alphabetical order can help easily identify rogue URLs, strange formatting, incorrect entries, and so much more.
Search for Specific URLs – Simply use control+f to find specific items within your XML sitemap. This is a handy feature when looking for something specific allowing you to easily save them in a new sheet for future reference.
Counting the Number of Indexable URLs – You can use Microsoft Excel to allow you to count the number of URLs within your XML sitemap which can be helpful for technical SEO as you will need to know how many true indexable pages should be within your website or online store.
More SEO Tips & Advice
I have more tips like this on my YouTube channel and I invite you to subscribe and get notified when I add new videos. Hope you find this helpful and maybe you be successful in your journey through SEO.