{"id":3195,"date":"2016-06-09T15:15:20","date_gmt":"2016-06-09T13:15:20","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=3195"},"modified":"2024-02-06T16:17:42","modified_gmt":"2024-02-06T15:17:42","slug":"replace-values-using-power-query-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/replace-values-using-power-query-in-excel\/","title":{"rendered":"Replace Values Using Power Query"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/installing-power-query-excel-2010\/\" target=\"_blank\">Power Query<\/a> lets you perform a series of steps to transform your Excel data. \u00a0One of the steps it allows you to take is to <strong>replace values easily<\/strong>.<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/-DsX23K9M7o?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><br \/>\n<a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Replace-Values.xlsx<\/span><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Let\u2019s go through the steps in detail:<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1<\/strong>:<\/span> Select\u00a0your data and turn it into an Excel Table by pressing the <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/333-excel-shortcuts-for-windows-and-mac\/\" target=\"_blank\" rel=\"noopener\" title=\"shortcut\" data-wpil-keyword-link=\"linked\">shortcut<\/a> <strong>Ctrl + T <\/strong>or by going to<strong> Insert &gt; Table<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replace Values Using Power Query\"  class=\"alignnone size-full wp-image-3197\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-01.png\" alt=\"Replace Values Using Power Query\" width=\"630\" height=\"386\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-01.png 630w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-01-300x184.png 300w\" sizes=\"(max-width: 630px) 100vw, 630px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2<\/strong>:<\/span> Go to <strong><em>Data &gt; Get &amp; Transform &gt; From Table (Excel 2016) <\/em><\/strong><em>or<\/em><strong><em> Power Query &gt; Excel Data &gt; From Table (Excel 2013 &amp; 2010)<\/em><\/strong><\/p>\n<p><strong>Excel 2016:<\/strong><\/p>\n<p><strong><em>\u00a0<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replace Values Using Power Query\"  class=\"alignnone size-full wp-image-3198\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-02.png\" alt=\"Replace Values Using Power Query\" width=\"618\" height=\"132\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-02.png 618w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-02-300x64.png 300w\" sizes=\"(max-width: 618px) 100vw, 618px\" \/><\/a><\/em><\/strong><\/p>\n<p><strong>Excel 2013 &amp; 2010:<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replace Values Using Power Query\"  class=\"alignnone size-full wp-image-3239\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table.png\" alt=\"Replace Values Using Power Query\" width=\"777\" height=\"129\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table.png 777w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table-300x50.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table-768x128.png 768w\" sizes=\"(max-width: 777px) 100vw, 777px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3:<\/span> <\/strong>This will open up the Power Query Editor.<\/p>\n<p>We want to change the Country <strong>Australia <\/strong>into <strong>New Zealand. <\/strong>Make sure you have the <strong>Country column selected\u00a0<\/strong>by clicking on the\u00a0<strong>Country\u00a0<\/strong>header.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replace Values Using Power Query\"  class=\"alignnone size-full wp-image-3199\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-03.png\" alt=\"Replace Values Using Power Query\" width=\"271\" height=\"250\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 4:<\/span> <\/strong>Within here you need to select <strong><em>Transform &gt; Replace Values<\/em><\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replace Values Using Power Query\"  class=\"alignnone size-full wp-image-3200\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-04.png\" alt=\"Replace Values Using Power Query\" width=\"583\" height=\"115\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-04.png 583w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-04-300x59.png 300w\" sizes=\"(max-width: 583px) 100vw, 583px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 5:<\/span> <\/strong>This will open up the <strong>Replace Values <\/strong>dialogue box.<\/p>\n<p>Place <em>Australia<\/em> in <strong>Value to Find, <\/strong>and <em>New Zealand <\/em>in <strong>Replace With<\/strong>. This will replace all of the <em>Australia <\/em>values with <em>New Zealand<\/em>.<\/p>\n<p>Click <strong>OK.<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Replace Values Using Power Query\"  class=\"alignnone size-full wp-image-3201\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-05.png\" alt=\"Replace Values Using Power Query\" width=\"681\" height=\"254\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-05.png 681w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-05-300x112.png 300w\" sizes=\"(max-width: 681px) 100vw, 681px\" \/><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 6:<\/span> <\/strong>Now you will see your changes take place.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-06.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replace Values Using Power Query\"  class=\"alignnone size-full wp-image-3202\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-06.png\" alt=\"Replace Values Using Power Query\" width=\"243\" height=\"237\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 7: <\/strong><\/span>Click <strong>Close &amp; Load<\/strong> from the <strong>Home<\/strong> tab and this will <strong>open up a brand new worksheet<\/strong> in your Excel workbook with the updated values.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-07.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replace Values Using Power Query\"  class=\"alignnone size-full wp-image-3203\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-07.png\" alt=\"Replace Values Using Power Query\" width=\"135\" height=\"129\" \/><\/a><\/p>\n<p>You now have your new table with the updated values.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Replace Values Using Power Query\"  class=\"alignnone size-full wp-image-3204\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-08.png\" alt=\"Replace Values Using Power Query\" width=\"325\" height=\"332\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-08.png 325w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Replace-Values-08-294x300.png 294w\" sizes=\"(max-width: 325px) 100vw, 325px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/podcast\/007-data-cleansing-analysis-with-oz-du-soleil-excel-podcast\/\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" title=\"3 Quick Ways on How To Create A List In Excel\"  class=\"alignnone wp-image-3233 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Oz_Podcast_Banner.jpg\" alt=\"3 Quick Ways on How To Create A List In Excel\" width=\"728\" height=\"90\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Oz_Podcast_Banner.jpg 728w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Oz_Podcast_Banner-300x37.jpg 300w\" sizes=\"(max-width: 728px) 100vw, 728px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; Power Query lets you perform a series of steps to transform your Excel data. \u00a0One of the steps it allows you to take is to replace values easily. \ufeff Download excel workbookReplace-Values.xlsx &nbsp; Let\u2019s go through the steps in detail: STEP 1: Select\u00a0your data and turn it into an Excel Table by pressing the [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17430,"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":[120,121],"tags":[117,128],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3195"}],"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=3195"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3195\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17430"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=3195"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=3195"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=3195"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}