{"id":2853,"date":"2021-03-16T16:11:11","date_gmt":"2021-03-16T15:11:11","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=2853"},"modified":"2024-05-15T19:46:46","modified_gmt":"2024-05-15T17:46:46","slug":"return-the-last-value-column-offset-function-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/return-the-last-value-column-offset-function-excel\/","title":{"rendered":"Return the Last Value in a Column with the Offset Function"},"content":{"rendered":"<p>You can easily Excel find last value in column using the OFFSET function.<\/p>\n<p>Just follow the tutorial below and you can fetch that last value. But before you move, let&#8217;s first understand how the OFFSET function works!<\/p>\n<p><em><strong>What does it do?<\/strong><\/em><\/p>\n<p>It returns a reference to a range, from a starting point to a specified number of rows, columns, height, and width of cells<\/p>\n<p><em><strong>Formula breakdown:<\/strong><\/em><\/p>\n<p>=OFFSET(<span style=\"color: #008000\">reference<\/span>, <span style=\"color: #ff0000\">rows<\/span>, <span style=\"color: #ff6600\">columns<\/span>, <span style=\"color: #0000ff\">[height]<\/span>,<span style=\"color: #800080\"> [width]<\/span>)<\/p>\n<p><em><strong>What it means:<\/strong><\/em><\/p>\n<ul>\n<li><span style=\"color: #008000\">reference &#8211; It is the starting point from which you want to base the offset function. <strong>Required<\/strong><\/span><\/li>\n<li><span style=\"color: #ff0000\">rows &#8211; It is the number of cells you want to go up\/down. <strong>Required<\/strong><\/span><\/li>\n<li><span style=\"color: #ff6600\">columns &#8211; It is the number of cells you want to go left\/right. <strong>Required<\/strong><\/span><\/li>\n<li><span style=\"color: #0000ff\">height &#8211; It is the number of rows you want the return reference to be. <em>Optional\u00a0<\/em><\/span><\/li>\n<li><span style=\"color: #800080\">width &#8211; It is the number of columns you want the return reference to be. <em>Optional<\/em><\/span><\/li>\n<\/ul>\n<p><em>If you omit the height or width argument, the output will have the same height or width as a reference!<\/em><\/p>\n<hr \/>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/offset-function\/\" target=\"_blank\" rel=\"noopener\">The OFFSET function in Excel<\/a> is one of the Lookup functions and is great if you want to reference a range of cells and use that reference to do a calculation.<\/p>\n<p><span style=\"text-decoration: underline;color: #0000ff\"><a style=\"color: #0000ff;text-decoration: underline\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-offset-function-introduced\/\" target=\"_blank\" rel=\"noopener noreferrer\">Click here <\/a><\/span>to see how the formula works visually &amp; interactively!<\/p>\n<p>Now that you are familiar with offset function, let&#8217;s see how excel return last value in column!<\/p>\n<p>&nbsp;<\/p>\n<p>In this example, we have a list that we enter our daily sales into and want to show the last transactional value entered.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-2-1.jpg\" rel=\"attachment wp-att-2864\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Return the Last Value in a Column with the Offset Function\" class=\"alignnone wp-image-2864 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-2-1.jpg\" alt=\"Return the Last Value in a Column with the Offset Function\" width=\"459\" height=\"388\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-2-1.jpg 459w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-2-1-300x254.jpg 300w\" sizes=\"(max-width: 459px) 100vw, 459px\" \/><\/a><\/p>\n<p>This can be used to highlight the latest sales value in a Dashboard or daily metrics report.<\/p>\n\n<p>Watch it on <a href=\"https:\/\/youtu.be\/cgFeIXD_UfM\" target=\"_blank\" rel=\"nofollow noopener\"><strong>YouTube<\/strong><\/a> and give it a thumbs-up!<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/cgFeIXD_UfM?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"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><\/p>\n<p>Follow the<strong>\u00a0step-by-step tutorial<\/strong> on Excel find last value in column and <strong>download this Excel workbook<\/strong>\u00a0to practice along:<\/p>\n<div class=\"after-post-box\">\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/Offset-Last-Value-in-a-Column.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/Offset-Last-Value-in-a-Column.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Offset-Last-Value-in-a-Column.xlsx<\/span><\/a><\/p>\n<\/div>\n<p><span style=\"color: #ff0000\"><strong>STEP 1:<\/strong> <\/span>We need to <strong>enter the <em>Offset\u00a0<\/em>function<\/strong><\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/return-the-last-value-column-offset-function-excel\/#OFFSET\" title=\"=OFFSET\">=OFFSET<\/a><\/li><\/ul><\/nav><\/div>\n<h2 style=\"text-align: center\"><span class=\"ez-toc-section\" id=\"OFFSET\"><\/span><span style=\"color: #0000ff\">=OFFSET<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 2:<\/strong><\/span> 1st Offset argument &#8211; <strong>Where do we want to start our reference?<\/strong><\/p>\n<p>This is at the start of the Daily Sales list:<\/p>\n<h2 style=\"text-align: center\">=OFFSET(<span style=\"color: #0000ff\">D11<\/span><\/h2>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/1st-argumenmt.jpg\" rel=\"attachment wp-att-2856\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Return the Last Value in a Column with the Offset Function\" class=\"size-full wp-image-2856 aligncenter\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/1st-argumenmt.jpg\" alt=\"Return the Last Value in a Column with the Offset Function\" width=\"540\" height=\"356\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/1st-argumenmt.jpg 540w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/1st-argumenmt-300x198.jpg 300w\" sizes=\"(max-width: 540px) 100vw, 540px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 3:<\/strong> <\/span>2nd Offset argument &#8211; <strong>How many Rows down do we want to go?<\/strong><\/p>\n<p>This is where the COUNT function is used to count how many transactions we have in our list\u00a0and go down by that amount.<\/p>\n<p>So as we are starting at cell D11, we are going to go down 11 cells ( <em>COUNTA(D11:D1000)<\/em> ) and we end up after\u00a0the last cell with a value.<\/p>\n<p>NB: It is always a good idea to enter an ending range that is more than your last data cell. \u00a0That way as your new data get entered, we will be sure to capture it!<\/p>\n<h2 style=\"text-align: center\">=OFFSET(<span style=\"color: #000000\">D11,<\/span><span style=\"color: #0000ff\"> COUNT(D11:D1000)<\/span><\/h2>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/2nd-argument-1.jpg\" rel=\"attachment wp-att-2858\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Return the Last Value in a Column with the Offset Function\" class=\"alignnone size-full wp-image-2858\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/2nd-argument-1.jpg\" alt=\"Return the Last Value in a Column with the Offset Function\" width=\"532\" height=\"373\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/2nd-argument-1.jpg 532w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/2nd-argument-1-300x210.jpg 300w\" sizes=\"(max-width: 532px) 100vw, 532px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 4:\u00a0<\/strong><\/span><strong>How many of the last sales transactions do we want to Sum?<\/strong>\u00a0\u00a01<\/p>\n<p>So we need to take away 1\u00a0from the COUNT formula which means that our Offset function will go up\u00a0by 1\u00a0cell:<\/p>\n<h2 style=\"text-align: center\">=OFFSET(<span style=\"color: #000000\">D11,<\/span><span style=\"color: #0000ff\"><span style=\"color: #000000\"> COUNT(D11:D1000)<\/span>-1,<\/span><\/h2>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/2nd-argument-less-1-1.jpg\" rel=\"attachment wp-att-2860\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Return the Last Value in a Column with the Offset Function\" class=\"alignnone size-full wp-image-2860\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/2nd-argument-less-1-1.jpg\" alt=\"Return the Last Value in a Column with the Offset Function\" width=\"593\" height=\"375\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/2nd-argument-less-1-1.jpg 593w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/2nd-argument-less-1-1-300x190.jpg 300w\" sizes=\"(max-width: 593px) 100vw, 593px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 5:<\/strong> <\/span>3rd Offset argument &#8211; <strong>How many Columns to the right\/left do we want to move?<\/strong><\/p>\n<p>We do not want to move to any Columns, so we simply enter 0<\/p>\n<h2 style=\"text-align: center\">=OFFSET(<span style=\"color: #000000\">D11,<\/span><span style=\"color: #0000ff\"><span style=\"color: #000000\"> COUNT(D11:D1000)-1,<\/span>0,<\/span><\/h2>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 6:<\/strong><\/span> 4th Offset argument &#8211; <strong>How High do we want our referenced data to be?<\/strong><\/p>\n<p>Since we want to show the last transaction, then we need the [height] to be 1 cell high:<\/p>\n<h2 style=\"text-align: center\">=OFFSET(<span style=\"color: #000000\">D11,<\/span><span style=\"color: #0000ff\"><span style=\"color: #000000\"> COUNT(D11:D1000)-1,0,<span style=\"color: #0000ff\">1<\/span><\/span>,<\/span><\/h2>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/4th-argument.jpg\" rel=\"attachment wp-att-2861\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Return the Last Value in a Column with the Offset Function\" class=\"alignnone size-full wp-image-2861\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/4th-argument.jpg\" alt=\"Return the Last Value in a Column with the Offset Function\" width=\"609\" height=\"337\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/4th-argument.jpg 609w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/4th-argument-300x166.jpg 300w\" sizes=\"(max-width: 609px) 100vw, 609px\" \/><\/a><\/p>\n<p style=\"text-align: left\"><span style=\"color: #ff0000\"><strong>STEP 7:<\/strong><\/span> 5th Offset argument &#8211; <strong>How Wide\u00a0do we want our referenced data to be?<\/strong><\/p>\n<p style=\"text-align: left\">We want to reference 1 Column only, so we need to enter 1 for the [width]<\/p>\n<h2 style=\"text-align: center\">=OFFSET(<span style=\"color: #000000\">D11,<\/span><span style=\"color: #0000ff\"><span style=\"color: #000000\"> COUNT(D11:D1000)-1,0,1,<\/span>1))<\/span><\/h2>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/5th-argument.jpg\" rel=\"attachment wp-att-2862\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Return the Last Value in a Column with the Offset Function\" class=\"alignnone size-full wp-image-2862\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/5th-argument.jpg\" alt=\"Return the Last Value in a Column with the Offset Function\" width=\"608\" height=\"336\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/5th-argument.jpg 608w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/5th-argument-300x166.jpg 300w\" sizes=\"(max-width: 608px) 100vw, 608px\" \/><\/a><\/p>\n<p style=\"text-align: left\">As we can see the Sum of the last transaction from our example is $6,810.<\/p>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-2.jpg\" rel=\"attachment wp-att-2863\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Return the Last Value in a Column with the Offset Function\" class=\"alignnone size-full wp-image-2863\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-2.jpg\" alt=\"Return the Last Value in a Column with the Offset Function\" width=\"468\" height=\"326\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-2.jpg 468w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-2-300x209.jpg 300w\" sizes=\"(max-width: 468px) 100vw, 468px\" \/><\/a><\/p>\n<p>This is how you can use Excel formula last cell in column with data!<\/p>\n<p>This formula is dynamic as well. If we <strong>add more transactions at the bottom of our Sales list<\/strong> then it automatically Excel find last value in column, without the need to update the formula \ud83d\ude42<\/p>\n<p>Let&#8217;s try that!<\/p>\n<p>In this example, you have added two additional daily sales data and you will see the<strong> value returned by the formula will be updated<\/strong>.<\/p>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-2-1.jpg\" rel=\"attachment wp-att-2864\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Return the Last Value in a Column with the Offset Function\" class=\"alignnone wp-image-2864 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-2-1.jpg\" alt=\"Return the Last Value in a Column with the Offset Function\" width=\"459\" height=\"388\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-2-1.jpg 459w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-2-1-300x254.jpg 300w\" sizes=\"(max-width: 459px) 100vw, 459px\" \/><\/a><\/p>\n<p>This completes our Excel last value <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/formulas\/lookup\/offset\/\" target=\"_blank\" rel=\"noopener\">in column using the OFFSET<\/a> function!<\/p>\n<p>&nbsp;<\/p>\n<p>Make sure to download our FREE PDF on the<strong><a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"noopener noreferrer nofollow\">\u00a0333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>You can easily Excel find last value in column using the OFFSET function. Just follow the tutorial below and you can fetch that last value. But before you move, let&#8217;s first understand how the OFFSET function works! What does it do? It returns a reference to a range, from a starting point to a specified [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17182,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Return the Last Value in a Column with the Offset Function","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[3,11,281],"tags":[43,720,79,123],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2853"}],"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\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=2853"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2853\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17182"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=2853"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=2853"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=2853"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}