You can import a lot of different data into your Excel spreadsheet. In this post, I’ll show you how you can get tables from Wikipedia both manually and with the help of Power Query. For this example, I’m going to use the page for the largest fast food restaurant chains. The data is sorted in a table, making it ideal to import into Excel.
How to import a Wikipedia table into Excel by copying and pasting
1. Copy and paste the values. Since the data is easily organized in Wikipedia, you can just use your mouse to drag, and copy and paste everything into Excel. The hardest part is just to make sure you’ve selected everything you want to copy.
Here is how the data looks after you’ve removed formatting:
Pro tip: if you have the latest version of Excel, you can use CTRL+SHIFT+V to copy the values without any additional formatting.
2. Remove the comments and values in brackets. In Wikipedia tables, you’ll often see references and notes after values. To remove this, you can use find and replace in Excel to get rid of the values. Set it up so that you replace [*] with nothing. The asterisk will remove everything which comes between the brackets, as well as the brackets themselves.
You can repeat these steps if there are any values in parentheses ( ) as well and any other special characters. After these steps, your data should look much cleaner. The downside is that it may take multiple adjustments to get the data cleaned up correctly and for all the issues to be accounted for.
Import Wikipedia tables using Power Query
Another way you can import tables is with the help of Power Query. Here’s how you can do that:
1. In the Data tab, select From Web and copy the link and press OK.
2. Select the table which resembles the data you want to import. Oftentimes there can be multiple tables, so it can be a good idea to cycle through them to see which one is the best match for your data. Then hit the button to Transform Data.
3. Remove any unneeded columns. To remove columns in Power Query, right-click on the headers and select Remove.
4. Clean up the data. You can use the Extract option in Power Query to grab values that come before a specific character. This is similar to the find and replace function but it can remove everything before a value:
Here you can specify the opening parenthesis as the character. You can repeat this step for brackets and other characters as well. This type of find and replace can also be done within Excel by just using *( to grab everything before the opening of a parentheses or *[ before an opening bracket.
Then, convert the values to Whole Number to ensure they are formatted correctly. If they aren’t, you’ll see some error values, in which case you’ll have to go back to the previous step to correct them. Otherwise, you are done and ready to move on to the last step.
5. Load the data back into your Excel spreadsheet. For this step, all you need to do is click on Close & Load on the Home tab.
Your formatted table is now loaded into Excel:
The benefit of using Power Query is that it saves your steps just like a macro would. If you want to refresh the data and download it again, to check for updated information, you can just right-click on the table and select Refresh.
If you like this post on How to Import Tables From Wikipedia Into Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.