{"id":23504,"date":"2022-02-02T11:03:11","date_gmt":"2022-02-02T10:03:11","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=23504"},"modified":"2024-02-06T15:50:44","modified_gmt":"2024-02-06T14:50:44","slug":"clean-data-set","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/clean-data-set\/","title":{"rendered":"Clean Data Set for Pivot Table"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Clean-Data.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Clean Data Set for Pivot Table\"  class=\"alignnone size-full wp-image-25522\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Clean-Data.png\" alt=\"Clean Data Set for Pivot Table\" width=\"800\" height=\"450\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Clean-Data.png 800w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Clean-Data-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/12\/Clean-Data-768x432.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/><\/a><\/p>\n<p>When working with Excel <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>, you will need to <strong>ensure that you have a clean data set<\/strong>. So there are 2 things that I want to show you:<\/p>\n<ul>\n<li>When your data is not clean<\/li>\n<li>How to clean your data.<\/li>\n<\/ul>\n<p><em><strong>Exercise Workbook:<\/strong><\/em><\/p>\n<div class=\"after-post-box\">\n<p class=\"line-two\"><strong><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set.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\">Clean-Your-Data-Set.xlsx<\/span><\/a><\/strong><\/p>\n<p class=\"line-two\">\n<\/div>\n<hr \/>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> Here is our data set, select the data and go to\u00a0<em><strong>Insert &gt; Tables &gt; PivotTable<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Clean Data Set for Pivot Table\"  class=\"alignnone size-full wp-image-23510\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-01.jpg\" alt=\"Clean Data Set for Pivot Table\" width=\"282\" height=\"688\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-01.jpg 282w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-01-123x300.jpg 123w\" sizes=\"(max-width: 282px) 100vw, 282px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> Select\u00a0<strong>Existing Worksheet<\/strong> and pick a cell inside the same worksheet to insert 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>. Click <strong>OK<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Clean Data Set for Pivot Table\"  class=\"alignnone size-full wp-image-23511\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-02.jpg\" alt=\"Clean Data Set for Pivot Table\" width=\"883\" height=\"508\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-02.jpg 883w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-02-300x173.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-02-768x442.jpg 768w\" sizes=\"(max-width: 883px) 100vw, 883px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong><\/span> Drag the\u00a0<strong>DATA<\/strong> column into <strong>Rows<\/strong> and <strong>Values<\/strong><\/p>\n<p>You will notice that how come <strong>IN123C104Z<\/strong> is <strong>appearing as 3 rows<\/strong>? This is caused by our dirty data!<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Clean Data Set for Pivot Table\"  class=\"alignnone size-full wp-image-23512\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-03.jpg\" alt=\"Clean Data Set for Pivot Table\" width=\"810\" height=\"539\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-03.jpg 810w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-03-300x200.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-03-768x511.jpg 768w\" sizes=\"(max-width: 810px) 100vw, 810px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong><\/span> If you inspect the data table closely, you will see that they have <strong>extra spaces<\/strong>. This is what&#8217;s causing our Pivot Table to have weird outputs!<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Clean Data Set for Pivot Table\"  class=\"alignnone wp-image-23513 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-04.jpg\" alt=\"Clean Data Set for Pivot Table\" width=\"385\" height=\"370\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-04.jpg 385w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-04-300x288.jpg 300w\" sizes=\"(max-width: 385px) 100vw, 385px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5:<\/strong><\/span> Let us clean those extra spaces! Select the entire column of your data and go to\u00a0<em><strong>Data &gt; Data Tools &gt; Text to Columns<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Clean Data Set for Pivot Table\"  class=\"alignnone size-full wp-image-23514\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-05.jpg\" alt=\"Clean Data Set for Pivot Table\" width=\"1051\" height=\"698\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-05.jpg 1051w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-05-300x199.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-05-1024x680.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-05-768x510.jpg 768w\" sizes=\"(max-width: 1051px) 100vw, 1051px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 6:<\/strong><\/span> Click\u00a0<strong>Next<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Clean Data Set for Pivot Table\"  class=\"alignnone size-full wp-image-23515\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-06.jpg\" alt=\"Clean Data Set for Pivot Table\" width=\"507\" height=\"413\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-06.jpg 507w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-06-300x244.jpg 300w\" sizes=\"(max-width: 507px) 100vw, 507px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 7:<\/strong><\/span> Make sure\u00a0<strong>Space<\/strong> is ticked and select\u00a0<strong>Finish<\/strong>. This will remove the extra spaces as a result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Clean Data Set for Pivot Table\"  class=\"alignnone size-full wp-image-23516\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-07.jpg\" alt=\"Clean Data Set for Pivot Table\" width=\"507\" height=\"413\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-07.jpg 507w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-07-300x244.jpg 300w\" sizes=\"(max-width: 507px) 100vw, 507px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 8:<\/strong><\/span> <strong>Right click<\/strong> anywhere on your Pivot Table and select\u00a0<strong>Refresh<\/strong> to reflect our data cleanup.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Clean Data Set for Pivot Table\"  class=\"alignnone size-full wp-image-23517\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-08.jpg\" alt=\"Clean Data Set for Pivot Table\" width=\"469\" height=\"490\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-08.jpg 469w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-08-287x300.jpg 287w\" sizes=\"(max-width: 469px) 100vw, 469px\" \/><\/p>\n<p>Now that&#8217;s looking much better!<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Clean Data Set for Pivot Table\"  class=\"alignnone size-full wp-image-23518\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-09.jpg\" alt=\"Clean Data Set for Pivot Table\" width=\"274\" height=\"331\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-09.jpg 274w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/05\/Clean-Your-Data-Set-09-248x300.jpg 248w\" sizes=\"(max-width: 274px) 100vw, 274px\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>When working with Excel Pivot Tables, you will need to ensure that you have a clean data set. So there are 2 things that I want to show you: When your data is not clean How to clean your data. Exercise Workbook: Download excel workbookClean-Your-Data-Set.xlsx &nbsp; STEP 1: Here is our data set, select the [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":25522,"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":[923,924,922],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/23504"}],"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=23504"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/23504\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/25522"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=23504"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=23504"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=23504"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}