{"id":4007,"date":"2021-04-01T22:05:27","date_gmt":"2021-04-01T20:05:27","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=4007"},"modified":"2024-02-06T16:05:39","modified_gmt":"2024-02-06T15:05:39","slug":"show-percent-difference-previous-years-excel-pivot-tables","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/show-percent-difference-previous-years-excel-pivot-tables\/","title":{"rendered":"Show The Percent of Difference From Previous Years With Excel Pivot Tables"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p>I am sure that your boss has asked you to come up with a Year on Year variance report at some stage. \u00a0There are a couple of ways to get him\/her an answer.<\/p>\n<p>One is using <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"Formulas\" data-wpil-keyword-link=\"linked\">Formulas<\/a>, but that will take time to set up and you are exposed to errors!<\/p>\n<p>The other method\u00a0is the <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> way, which is quick and reduces the risks of making any errors&#8230;.ah yeah I almost forgot, it is also easy to add new data to your variance analysis!<\/p>\n\n<p>In the example below I show you how to get the\u00a0<em>Percentage Difference from Previous Years Values<\/em>:<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/-6J-_femDAY?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\/07\/Percent-of-Difference-From-Previous-Years.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years.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\">Percent-of-Difference-From-Previous-Years.xlsx<\/span><\/a><\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong>\u00a0<\/span>Insert a new Pivot table by clicking\u00a0on your data and going\u00a0to<strong><em>\u00a0Insert &gt; Pivot Table &gt; New Worksheet or Existing Worksheet<\/em><\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\"  class=\"alignnone size-full wp-image-4011\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-01.png\" alt=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\" width=\"386\" height=\"344\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-01.png 386w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-01-300x267.png 300w\" sizes=\"(max-width: 386px) 100vw, 386px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span>\u00a0In the\u00a0<strong>ROWS<\/strong>\u00a0section put in the\u00a0<em>Sales Month\u00a0<\/em>field, in the\u00a0<strong>COLUMNS<\/strong>\u00a0put in the <em>Financial\u00a0Year<\/em>\u00a0field and in the\u00a0<strong>VALUES\u00a0<\/strong>area you need to put in the\u00a0<em>Sales<\/em>\u00a0field twice, I explain why below:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\"  class=\"alignnone size-full wp-image-4012\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-02.png\" alt=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\" width=\"354\" height=\"637\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-02.png 354w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-02-167x300.png 167w\" sizes=\"(max-width: 354px) 100vw, 354px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong>\u00a0<\/span>Click the second Sales field\u2019s\u00a0<em>(Sum of SALES2<\/em>) drop down and choose<strong>\u00a0Value Field Settings<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\"  class=\"alignnone size-full wp-image-4013\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-03.png\" alt=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\" width=\"354\" height=\"637\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-03.png 354w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-03-167x300.png 167w\" sizes=\"(max-width: 354px) 100vw, 354px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong><\/span><strong>\u00a0<\/strong>Select the\u00a0<strong>Show Values As<\/strong>\u00a0tab and from the drop down choose\u00a0<em><strong>% Difference From. \u00a0<\/strong><\/em>Select <strong>Financial Year<\/strong> as the Base Field, and <strong>(previous)<\/strong> as the Base Item. This means that we will compute the difference with the previous years in percentage terms.<\/p>\n<p>Also change the\u00a0<strong>Custom Name<\/strong> into <strong>% Difference from Previous Year<\/strong>\u00a0to make it more presentable.\u00a0Click\u00a0<strong>OK.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\"  class=\"alignnone size-full wp-image-4014\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-04.png\" alt=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\" width=\"373\" height=\"319\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-04.png 373w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-04-300x257.png 300w\" sizes=\"(max-width: 373px) 100vw, 373px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5:<\/strong><\/span>\u00a0Notice that the<strong><em>\u00a0% Difference from Previous Year<\/em>\u00a0<\/strong>data is in a decimal\u00a0format that is hard to read:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-05A.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\"  class=\"alignnone size-full wp-image-4015\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-05A.png\" alt=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\" width=\"274\" height=\"333\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-05A.png 274w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-05A-247x300.png 247w\" sizes=\"(max-width: 274px) 100vw, 274px\" \/><\/a><\/p>\n<p>To format the <strong><em>% Difference from Previous Year<\/em><\/strong><em><strong>\u00a0<\/strong><\/em>column, click the second\u00a0Sales field\u2019s\u00a0<em>(% Difference from Previous Year<\/em>) drop down and choose<strong>\u00a0Value Field Settings.\u00a0<\/strong><\/p>\n<p>The goal here is for us to transform\u00a0numbers from\u00a0a decimal\u00a0format (i.e. 0.23), into a percentage\u00a0format that is more readable (i.e. 23%).<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-05B.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\"  class=\"alignnone size-full wp-image-4016\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-05B.png\" alt=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\" width=\"354\" height=\"637\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-05B.png 354w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-05B-167x300.png 167w\" sizes=\"(max-width: 354px) 100vw, 354px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 6: <\/strong><\/span>Click the <strong>Number Format<\/strong> button.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-06.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\"  class=\"alignnone size-full wp-image-4017\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-06.png\" alt=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\" width=\"373\" height=\"319\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-06.png 373w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-06-300x257.png 300w\" sizes=\"(max-width: 373px) 100vw, 373px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 7:\u00a0<\/strong><\/span>Inside the\u00a0<em>Format Cell<\/em>s dialog box, make your formatting changes within here and press\u00a0<strong>OK<\/strong>\u00a0twice.<strong> \u00a0<\/strong><\/p>\n<p>In this example, we used the\u00a0<strong>Percentage\u00a0<\/strong>category to make our <em>% Difference from Previous Year<\/em>\u00a0numbers become more readable.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-07.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\"  class=\"alignnone size-full wp-image-4018\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-07.png\" alt=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\" width=\"529\" height=\"465\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-07.png 529w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-07-300x264.png 300w\" sizes=\"(max-width: 529px) 100vw, 529px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 8:\u00a0<\/strong><\/span>Right click on the columns with the empty columns and click <strong>Hide<\/strong>.\u00a0These columns are empty because there are no previous values it can compare values on. For example the Year 2012 is the first year and has no previous year to compare to.<strong>\u00a0<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-08.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\"  class=\"alignnone size-full wp-image-4019\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-08.png\" alt=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\" width=\"721\" height=\"539\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-08.png 721w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-08-300x224.png 300w\" sizes=\"(max-width: 721px) 100vw, 721px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>You now have your Pivot Table, showing the <strong>% Difference from Previous Year<\/strong><strong>\u00a0<\/strong>for the sales data of years 2012, 2013, and 2014.<\/p>\n<p>You can see that each red box is the percentage of difference computed against the previous year (i.e. Year 2013 vs Year 2012, and Year 2014 vs Year 2013).<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-09.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\"  class=\"alignnone size-full wp-image-4020\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-09.png\" alt=\"Show The Percent of Difference From Previous Years With Excel Pivot Tables\" width=\"806\" height=\"376\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-09.png 806w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-09-300x140.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Percent-of-Difference-From-Previous-Years-09-768x358.png 768w\" sizes=\"(max-width: 806px) 100vw, 806px\" \/><\/a><\/p>\n<p><span style=\"color: #000000;\"><strong>Further Learning:<\/strong><\/span><\/p>\n<ul>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/clean-data-set\/\" target=\"_blank\" rel=\"noopener\">Clean Data Set for Pivot Table<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/prepare-data-for-pivot-tables\/\" target=\"_blank\" rel=\"noopener\">Prepare Data for Excel Pivot Tables<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/move-and-remove-fields-and-items\/\" target=\"_blank\" rel=\"noopener\">Move and Remove Fields and Items in Excel Pivot Tables<\/a><\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100.html\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2964 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/728x90.gif\" alt=\"PIVOT BANNER\" width=\"728\" height=\"90\" title=\"\"><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; I am sure that your boss has asked you to come up with a Year on Year variance report at some stage. \u00a0There are a couple of ways to get him\/her an answer. One is using Formulas, but that will take time to set up and you are exposed to errors! The other method\u00a0is [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17170,"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,18],"tags":[88,139,124],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/4007"}],"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=4007"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/4007\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17170"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=4007"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=4007"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=4007"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}