{"id":4295,"date":"2016-09-16T01:48:30","date_gmt":"2016-09-15T23:48:30","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=4295"},"modified":"2024-02-06T16:17:53","modified_gmt":"2024-02-06T15:17:53","slug":"fix-incorrect-formatting-using-flash-fill-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/fix-incorrect-formatting-using-flash-fill-in-excel\/","title":{"rendered":"Fix Incorrect Formatting Using Flash Fill In Excel"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p><strong>Flash Fill<\/strong>\u00a0in Excel is a new feature that was introduced in Excel 2013. \u00a0One of the cool uses of\u00a0Flash Fill<strong>\u00a0<\/strong>is to fix incorrect formatting in your text automatically.<\/p>\n<p>Ever had the scenario where your data is formatted differently?<\/p>\n<p><span style=\"color: #ff0000;\"><strong>Example:<\/strong><\/span> <em>First names starting with lower case, last names all in upper case, middle initials in either cases&#8230;arghhhhhh!<\/em><\/p>\n<p>Luckily we have\u00a0<strong>Flash Fill<\/strong>\u00a0which can automatically convert the entire data\u00a0set into one consistent format. \u00a0See how below:<\/p>\n<p>&nbsp;<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/Ve9GqyP5wSQ?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><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><\/p>\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\"> download excel workbook <i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Flash-Fill-Fix-Incorrect-Formatting.xlsx<\/span><\/a><\/p>\n<\/div>\n<p>To demonstrate the power of Excel&#8217;s Flash Fill, we will start off with this table of data where we need to fix the inconsistent formatting:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Fix Incorrect Formatting Using Flash Fill In Excel\"  class=\"alignnone size-full wp-image-4297\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-01.png\" alt=\"Fix Incorrect Formatting Using Flash Fill In Excel\" width=\"481\" height=\"109\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-01.png 481w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-01-300x68.png 300w\" sizes=\"(max-width: 481px) 100vw, 481px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1<\/strong>:<\/span> Type<em>\u00a0<strong>Homer A Simpson<\/strong>\u00a0<\/em>as the first entry in the\u00a0<strong>Full Name column. \u00a0<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Fix Incorrect Formatting Using Flash Fill In Excel\"  class=\"alignnone size-full wp-image-4298\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-02.png\" alt=\"Fix Incorrect Formatting Using Flash Fill In Excel\" width=\"480\" height=\"114\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-02.png 480w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-02-300x71.png 300w\" sizes=\"(max-width: 480px) 100vw, 480px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2<\/strong>:<\/span>\u00a0We want the rest of the Text\u00a0to be formatted this way, so in the second entry, type\u00a0<em><strong>Ian B Wright<\/strong>. <\/em><\/p>\n<p>Notice that Excel did not auto-suggest to Flash Fill. \u00a0There are times that this happens.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Fix Incorrect Formatting Using Flash Fill In Excel\"  class=\"alignnone size-full wp-image-4299\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-03.png\" alt=\"Fix Incorrect Formatting Using Flash Fill In Excel\" width=\"478\" height=\"112\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-03.png 478w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-03-300x70.png 300w\" sizes=\"(max-width: 478px) 100vw, 478px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Since\u00a0Flash Fill did not\u00a0start automatically when you are expecting for it to match your pattern, you can start it manually by highlighting the entire column you want it to fill.<\/p>\n<p>Then click\u00a0<strong>Data &gt; Flash Fill <\/strong>(Another alternative is to press the\u00a0<strong>Ctrl+E<\/strong> keyboard <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>).<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Fix Incorrect Formatting Using Flash Fill In Excel\"  class=\"alignnone size-full wp-image-4300\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-04.png\" alt=\"Fix Incorrect Formatting Using Flash Fill In Excel\" width=\"478\" height=\"116\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-04.png 478w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-04-300x73.png 300w\" sizes=\"(max-width: 478px) 100vw, 478px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-troubleshoot.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use Flash Fill In Excel\"  class=\"alignnone size-full wp-image-4219\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-troubleshoot.png\" alt=\"How to Use Flash Fill In Excel\" width=\"659\" height=\"157\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-troubleshoot.png 659w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-troubleshoot-300x71.png 300w\" sizes=\"(max-width: 659px) 100vw, 659px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3: <\/strong><\/span>You now have your data auto populated using <strong>Flash Fill. \u00a0<\/strong><\/p>\n<p>What is very impressive is Excel was able to apply the same format\u00a0pattern\u00a0to the rest of the table\u00a0<strong>without the use of a single formula!\u00a0<\/strong><\/p>\n<p>Adios&#8230;I mean, goodbye inconsistent formatting \ud83d\ude42<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-05.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Fix Incorrect Formatting Using Flash Fill In Excel\"  class=\"alignnone size-full wp-image-4301\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-05.png\" alt=\"Fix Incorrect Formatting Using Flash Fill In Excel\" width=\"502\" height=\"117\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-05.png 502w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Flash-Fill-Fix-Incorrect-Formatting-05-300x70.png 300w\" sizes=\"(max-width: 502px) 100vw, 502px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/myexcelonline.leadpages.co\/power-query-data-cleansing-int916\/\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" title=\"Add Comma in Excel between Names with SUBSTITUTE Formula\"  class=\"alignnone wp-image-4486 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/728x90.jpg\" alt=\"Add Comma in Excel between Names with SUBSTITUTE Formula\" width=\"728\" height=\"90\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/728x90.jpg 728w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/728x90-300x37.jpg 300w\" sizes=\"(max-width: 728px) 100vw, 728px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; Flash Fill\u00a0in Excel is a new feature that was introduced in Excel 2013. \u00a0One of the cool uses of\u00a0Flash Fill\u00a0is to fix incorrect formatting in your text automatically. Ever had the scenario where your data is formatted differently? Example: First names starting with lower case, last names all in upper case, middle initials in [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17500,"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":[6,41],"tags":[88,162,74],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/4295"}],"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=4295"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/4295\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17500"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=4295"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=4295"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=4295"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}