{"id":23540,"date":"2022-02-11T11:03:21","date_gmt":"2022-02-11T10:03:21","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=23540"},"modified":"2024-02-06T15:50:49","modified_gmt":"2024-02-06T14:50:49","slug":"refresh-external-data-source","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/refresh-external-data-source\/","title":{"rendered":"Refresh External Data Source in Excel Pivot Table"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-External-Data.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Refresh External Data Source in Excel Pivot Table\"  class=\"alignnone size-full wp-image-25468\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-External-Data.png\" alt=\"Refresh External Data Source in Excel Pivot Table\" width=\"800\" height=\"450\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-External-Data.png 800w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-External-Data-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-External-Data-768x432.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/><\/a><\/p>\n<p>The cool thing with <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\" title=\"Pivot Tables\" data-wpil-keyword-link=\"linked\">Pivot Tables<\/a> is you can<strong> use a data source that is in a different workbook<\/strong>. The <strong>external data source<\/strong> could be stored in a<strong> shared drive in your company server<\/strong> that your team uses, or it could be in a <strong>different location stored in your computer<\/strong>.<\/p>\n<p>Imagine this <strong>external data source will be updated frequently<\/strong> and we would not know when it got updated. So how would we ensure that our <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\" title=\"Pivot Table\" data-wpil-keyword-link=\"linked\">Pivot Table<\/a> is refreshed and shows updated data?<\/p>\n<p>I have a couple of methods for you on how to refresh external data source in the Pivot Table:<\/p>\n<ul>\n<li><a href=\"#manual-refresh\"><strong>Manual Refresh<\/strong><\/a><\/li>\n<li><a href=\"#refresh-when-opening-the-file\"><strong>Refresh when opening the file<\/strong><\/a><\/li>\n<li><a href=\"#refresh-at-regular-interval\"><strong>Refresh at regular interval<\/strong><\/a><\/li>\n<\/ul>\n<p>Let&#8217;s look at each method one by one!<\/p>\n<p>&nbsp;<\/p>\n<p><em><strong>Before you proceed, don&#8217;t forget to download this workbook and follow along:<\/strong><\/em><\/p>\n<div class=\"after-post-box\">\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Refresh-External-Data.xlsx<\/span><\/a><\/p>\n<p class=\"line-two\">\n<\/div>\n<hr \/>\n<p>Let us check out our Pivot Table if it does really have an External Data Source.<\/p>\n<p>Go to\u00a0<em><strong>PivotTable Analyze &gt; Data &gt; Change Data Source<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Refresh External Data Source in Excel Pivot Table\"  class=\"alignnone size-full wp-image-23548\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-01.jpg\" alt=\"Refresh External Data Source in Excel Pivot Table\" width=\"1012\" height=\"679\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-01.jpg 1012w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-01-300x201.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-01-768x515.jpg 768w\" sizes=\"(max-width: 1012px) 100vw, 1012px\" \/><\/p>\n<p>You can see that it is using an <strong>external data source<\/strong>. Click <strong>OK<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Refresh External Data Source in Excel Pivot Table\"  class=\"alignnone size-full wp-image-23554\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-01b.jpg\" alt=\"Refresh External Data Source in Excel Pivot Table\" width=\"388\" height=\"216\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-01b.jpg 388w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-01b-300x167.jpg 300w\" sizes=\"(max-width: 388px) 100vw, 388px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong><a id=\"manual-refresh\"><\/a>Manual Refresh<\/strong><\/span><\/p>\n<p>To update this the good old fashioned manual way, right-click on the Pivot Table and select <strong>Refresh<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Refresh External Data Source in Excel Pivot Table\"  class=\"alignnone size-full wp-image-23549\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-02.jpg\" alt=\"Refresh External Data Source in Excel Pivot Table\" width=\"704\" height=\"505\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-02.jpg 704w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-02-300x215.jpg 300w\" sizes=\"(max-width: 704px) 100vw, 704px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong><a id=\"refresh-when-opening-the-file\"><\/a>Refresh when opening the file<\/strong><\/span><\/p>\n<p>There is another way to ensure your Pivot Table is updated.<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> Go to <em><strong>PivotTable Analyze &gt; Data &gt; Change Data Source &gt; Connection Properties<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Refresh External Data Source in Excel Pivot Table\"  class=\"alignnone size-full wp-image-23550\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-03.jpg\" alt=\"Refresh External Data Source in Excel Pivot Table\" width=\"497\" height=\"222\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-03.jpg 497w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-03-300x134.jpg 300w\" sizes=\"(max-width: 497px) 100vw, 497px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> Select the checkbox &#8211; <strong>Refresh data when opening the file.<\/strong><\/p>\n<p>This triggers a pivot table <strong>update whenever you open the Excel file<\/strong>. This is a good option if you prefer this to happen only once when you open the file.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Refresh External Data Source in Excel Pivot Table\"  class=\"alignnone size-full wp-image-25471\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-1.png\" alt=\"Refresh External Data Source in Excel Pivot Table\" width=\"470\" height=\"499\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-1.png 470w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-1-283x300.png 283w\" sizes=\"(max-width: 470px) 100vw, 470px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong><a id=\"refresh-at-regular-interval\"><\/a>Refresh at regular interval<\/strong><\/span><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> Go to <em><strong>PivotTable Analyze &gt; Data &gt; Change Data Source &gt; Connection Properties<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Refresh External Data Source in Excel Pivot Table\"  class=\"alignnone size-full wp-image-23550\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-03.jpg\" alt=\"Refresh External Data Source in Excel Pivot Table\" width=\"497\" height=\"222\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-03.jpg 497w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Refresh-External-Data-03-300x134.jpg 300w\" sizes=\"(max-width: 497px) 100vw, 497px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> Select the checkbox &#8211; <strong>Refresh every X minutes.<\/strong><\/p>\n<p>This will refresh your pivot table automatically after the number of minutes has passed.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-2.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Refresh External Data Source in Excel Pivot Table\"  class=\"alignnone wp-image-25472 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-2.png\" alt=\"Refresh External Data Source in Excel Pivot Table\" width=\"470\" height=\"497\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-2.png 470w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Refresh-2-284x300.png 284w\" sizes=\"(max-width: 470px) 100vw, 470px\" \/><\/a><\/p>\n<p>Try out these options and have fun keeping your Pivot Table up-to-date!<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The cool thing with Pivot Tables is you can use a data source that is in a different workbook. The external data source could be stored in a shared drive in your company server that your team uses, or it could be in a different location stored in your computer. Imagine this external data source [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":25468,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[4],"tags":[912,911,913],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/23540"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=23540"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/23540\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/25468"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=23540"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=23540"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=23540"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}