At some point you’d have to take a deeper look into your websites sitemap. You should definitely do this if your sitemap does not get fully indexed into Google and you want o find out why. If you open the XML sitemap with a text editor it will most likely be in this format:
<urlset xmlns=”http://www.sitemaps.org/schemas/sitemap/0.9″>
<url>
<loc>http://www.domain.tld/</loc>
<lastmod>YYYY-MM-DDThh:mm:ssZ</lastmod>
<priority>1.0</priority>
<changefreq>yearly</changefreq>
</url><url>
<loc>http://www.domain.tld/path/</loc>
<lastmod>YYYY-MM-DDThh:mm:ssZ</lastmod>
<priority>0.5</priority>
<changefreq>yearly</changefreq>
</url>
</urlset>
Step 1: Open Excel, click in the top left corner on “File” > “Options”.
Step 2: A little menu will pop up. Click on “Customize Ribbon” to edit the navigation tabs in Excel.
Step 3: In the tab “Main Tabs” activate the tick-box for “Developer”. This will unhide the Developer tab in Excel where you’ll find the option to import XML files.
Step 4: Click okay to leave the settings menu and click on Developer in the top navigation. On the right side will be a button called “Import”. Click that button.
Step 5: You can now select the XML Sitemap file you want to import. Click on “import” and follow the steps to paste all the information from the file into the first row/column (A1) of your spreadsheet.
Step 6: As filters are already activated and the values are in the correct cells, you can now review the sitemap data to see where you might have errors.
Step 7: Based on the steps before you can also prepare a text-file which can get uploaded to a scraper like “ScrapeBox” or “ScreamingFrog”. These tools will give you the values you need to make further decisions on your sitemap.
By the way, you could import Twitter search results via the RSS feed into Excel using the same method, if you re into that kind of thing.
I can’t find a way how to import the Sitemap into Excel on Mac. After activating the developer mode there is no ‘import’ box. Copy/pasting it works as a work around and then command + F to replace the <loc… by nothing. But it's quite annoying when you have to do this with a whole bunch of sitemaps. Would be good if Microsoft could keep both versions the same and not remove functions which always worked in the past.
This is actually helpful, thanks.
thank you. so useful for me
Thank you very much! Helped me out perfectly today!
Thank you dude, it works!