{"id":4314,"date":"2016-09-14T12:00:21","date_gmt":"2016-09-14T10:00:21","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=4314"},"modified":"2024-02-06T15:46:39","modified_gmt":"2024-02-06T14:46:39","slug":"rank-smallest-largest-excel-pivot-tables","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/rank-smallest-largest-excel-pivot-tables\/","title":{"rendered":"Rank Smallest to Largest With Excel Pivot Tables"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p>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> have a lot of useful\u00a0calculations under the <strong>SHOW VALUES AS<\/strong> option and one that can help you a lot is the <strong>RANK SMALLEST TO LARGEST<\/strong>\u00a0calculation.<\/p>\n<p>This option will immediately calculate the\u00a0rankings (1 being the smallest value) for your values, allowing you to pinpoint the risks or opportunities quickly!<\/p>\n<p>In the example below I show you how to get the <i>Rank Smallest to Largest<\/i>:<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/FgXe4VZlVIA?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\/Rank-Smallest-to-Largest.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Rank-Smallest-to-Largest.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\">Rank-Smallest-to-Largest.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 <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> 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<em><strong>\u00a0Rank Smallest to Largest<\/strong><strong>. \u00a0<\/strong><\/em><\/p>\n<p>Select <strong>Sales Month<\/strong>\u00a0as the Base Field.<\/p>\n<p>This means that we will rank the Sales Values by the Sales Month (where Rank 1 is the Smallest).<\/p>\n<p>Also change the\u00a0<strong>Custom Name<\/strong> into <em>Rank Smallest to Largest<\/em>\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\/08\/Rank-Smallest-to-Largest-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Rank Smallest to Largest With Excel Pivot Tables\"  class=\"alignnone size-full wp-image-4317\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Rank-Smallest-to-Largest-04.png\" alt=\"Rank Smallest to Largest With Excel Pivot Tables\" width=\"373\" height=\"319\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Rank-Smallest-to-Largest-04.png 373w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Rank-Smallest-to-Largest-04-300x257.png 300w\" sizes=\"(max-width: 373px) 100vw, 373px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>You now have your Pivot Table, showing the <strong>Smallest to Largest<\/strong><strong>\u00a0Rankings for each Month.<\/strong><\/p>\n<p>You can see that each red box is the ranking for each individual year (for Years 2012, 2013, 2014 and the Total Rankings).<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Rank-Smallest-to-Largest-05.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Rank Smallest to Largest With Excel Pivot Tables\"  class=\"alignnone size-full wp-image-4318\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Rank-Smallest-to-Largest-05.png\" alt=\"Rank Smallest to Largest With Excel Pivot Tables\" width=\"947\" height=\"405\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Rank-Smallest-to-Largest-05.png 947w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Rank-Smallest-to-Largest-05-300x128.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/08\/Rank-Smallest-to-Largest-05-768x328.png 768w\" sizes=\"(max-width: 947px) 100vw, 947px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\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; Excel Pivot Tables have a lot of useful\u00a0calculations under the SHOW VALUES AS option and one that can help you a lot is the RANK SMALLEST TO LARGEST\u00a0calculation. This option will immediately calculate the\u00a0rankings (1 being the smallest value) for your values, allowing you to pinpoint the risks or opportunities quickly! In the example [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17561,"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,172,124],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/4314"}],"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=4314"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/4314\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17561"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=4314"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=4314"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=4314"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}