{"id":2615,"date":"2015-04-21T14:55:17","date_gmt":"2015-04-21T14:55:17","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/?p=2615"},"modified":"2024-05-12T01:07:24","modified_gmt":"2024-05-12T01:07:24","slug":"linking-web-data-to-excel","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/linking-web-data-to-excel\/","title":{"rendered":"Linking web data to Excel"},"content":{"rendered":"<h2>Say goodbye to copy and pasting. Connecting Excel to website data<\/h2>\n<p>There are times when having live data in Excel can be really useful. You may need the latest currency exchange rates, monitor your stocks, track house prices, the possibilities are endless.<\/p>\n<p>Once you have established the data links you can refresh the data, also Excel will check to see if the data on the website has been updated, if so it will refresh to ensure you have the latest data.<\/p>\n<p>Here we will look at how we can set up Excel to connect to a live website and it&#8217;s data. As the UK general election is just around the corner we have decided this will make a good example.<\/p>\n<p>The worksheet we will create will be linked to the following website which is tracking the &#8220;most seats betting odds&#8221; for the 2015 UK general election.<\/p>\n<p><a href=\"https:\/\/www.oddschecker.com\/politics\/british-politics\/next-uk-general-election\/most-seats\">http:\/\/www.oddschecker.com\/politics\/british-politics\/next-uk-general-election\/most-seats<\/a><\/p>\n<p>To create a link to a website from Excel click the\u00a0<strong>Data<\/strong> tab and in the\u00a0<strong>Get External Data\u00a0<\/strong>group click\u00a0<strong>From Web.<\/strong><\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/04\/bets-datatab.png\"><img decoding=\"async\" class=\"alignnone size-large wp-image-2618\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/04\/bets-datatab-1024x250.png\" alt=\"bets datatab\" width=\"625\" height=\"153\" \/><\/a><\/p>\n<p>The Excel web browser will open. In <strong>Address\u00a0<\/strong>enter the website address and press\u00a0<strong>GO.<\/strong><\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/04\/webbrowser.png\"><img decoding=\"async\" class=\"alignnone size-large wp-image-2620\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/04\/webbrowser-1024x547.png\" alt=\"webbrowser\" width=\"625\" height=\"334\" \/><\/a><\/p>\n<p>In the Excel web browser you have to find the table you want to link to Excel. Each table is represented by a yellow box with a black arrow inside. If you hover over the arrow Excel will put a frame around the table to show which data the arrow represents. Click on the arrow and the arrow will change to a tick. Click\u00a0<strong>Import<\/strong> in the lower\u00a0right corner.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/04\/click-tick.png\"><img decoding=\"async\" class=\"alignnone size-large wp-image-2622\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/04\/click-tick-1024x547.png\" alt=\"click tick\" width=\"625\" height=\"334\" \/><\/a><\/p>\n<p>The below dialogue box will open. Tell Excel where you want the data and click <strong>Properties<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/04\/properties.png\"><img decoding=\"async\" class=\"aligncenter wp-image-2625 size-full\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/04\/properties.png\" alt=\"Linking web data to Excel\" width=\"274\" height=\"187\" \/><\/a><\/p>\n<p>You can now tell Excel how often you want to refresh the data under\u00a0<strong>Refresh Control<\/strong>. You can change more properties if you so wish. When you have finished setting properties click\u00a0<strong>OK\u00a0<\/strong>and Excel will import the data.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/04\/properties-2.png\"><img decoding=\"async\" class=\"aligncenter wp-image-2624 size-full\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/04\/properties-2.png\" alt=\"properties 2\" width=\"367\" height=\"473\" \/><\/a><\/p>\n<p>The example below is the final result. We\u00a0have worked with some formatting and created a chart. Now every time the odds change our\u00a0chart and worksheet will update on refresh.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/04\/bets.png\"><img decoding=\"async\" class=\"alignnone size-large wp-image-2616\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/04\/bets-1024x232.png\" alt=\"bets\" width=\"625\" height=\"142\" \/><\/a><\/p>\n<p>As we mentioned earlier,\u00a0this linking web data to Excel\u00a0has a wide range of applications and should hopefully make copying and pasting website data a thing of the past!<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Say goodbye to copy and pasting. Connecting Excel to website data There are times when having live data in Excel can be really useful. You may need the latest currency exchange rates, monitor your stocks, track house prices, the possibilities are endless. Once you have established the data links you can refresh the data, also [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4,6],"tags":[272,334,335],"class_list":["post-2615","post","type-post","status-publish","format-standard","hentry","category-excel-training","category-hints-tips","tag-get-external-data-from-a-web-page","tag-linking-data-to-excel","tag-linking-web-data-to-excel"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2615","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=2615"}],"version-history":[{"count":2,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2615\/revisions"}],"predecessor-version":[{"id":7523,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2615\/revisions\/7523"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=2615"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=2615"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=2615"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}