Comments on: Using OFFSET function in Excel - formula examples

The tutorial explains the arcane twists of the OFFSET function and covers the most efficient ways to use OFFSET in Excel. Continue reading →

Table of contents

Comments page 2. Total comments: 93

  1. Is there a way to create an Excel table with a dynamic (formula-based) # of rows? For example, I'd like to build an amortization table, and I'd like to be able enter the # of months in a cell (e.g. 360 for a 30-year mortgage, 180 for 15-year mortgage, 60 for a 12-month car loan, etc) and then have a table with my amortization calculations populate based on the # of rows that I'm interested in.

  2. Hello i have one query : i want to insert data and make one table according to this below equation for T COLUMN NUMBERS with the same interval. how can i do this kind of functionality in xlx

    0 T3 T(3+8)=T11 T(11+8)=T19 T(19+8)=T27

    10 T(3+128)=T131 T(11+128)=T139 T(19+128)=T147 T(27+128)=T155
    20 T(131+128)=T259 T(139+128)=T267 T(147+128)=T275 T(155+128)=T283
    30
    40
    50
    60
    70
    80
    90
    A0
    B0
    C0
    D0
    E0
    F0
    100

  3. Thank you so much for the information. It helped me a lot in several cases

  4. =sheet1! B1
    By dragging to down
    =sheet1! B2
    I want
    =sheet2! B1
    So please help me

    1. =INDIRECT("'"&"Sheet"&ROW()&"'!B1").
      When you copy and paste this formula down or drag it down, it should work.

  5. Very Helpful Offset

  6. Hi, how do I copy even cells in one column and odd cell in another column. Example value in cell B1 =A1, B2=A3 and C1=A2, C2=A4.
    Thanks,

  7. Hi,

    I have around 10000 in one column , i would like do do shift every 100 rows then write the value and do on .

    Thank you in advance

    1. Hi,
      I have around 10000 values in one column , i would like to do shift every 100 rows then write the value and do on .
      Thank you in advance

  8. Great info!!
    I use OFFSET and Define Name to dynamically track the most recent 5 test results on a spreadsheet that expands over time.
    =OFFSET('Test Results'!J6,1,COUNTA(‘Test Results'!$I$5:$DD$5)-5,1,-1)

    The problem I run into is when entering the first few results. Any time there are less columns (or rows) with data than you have indicated in your OFFSET formula, the formula will reference irrelevant cells or produce errors if it runs out of columns (or rows).
    For example, if my OFFSET is set to -5 (columns), but I only have 2 or 3 columns of data, my Offset formula will still try to reference 5 columns… so it will end up referencing column titles or whatever is to the left of my starting reference, or it will produce an error because there are no more columns remaining on the spreadsheet.

    Is there a way to avoid this? Maybe some kind of parameter that tells it to stop at a specified column, or mixing in an IF, THEN formula?

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  9. Lovely explanation
    Thanks for that

  10. Hi,

    Need formula on below problem-

    Column-"A"- Select-"YES"

    Column-"B"- Select-"YES"

    Column-"C" - SUM THE VALUE

  11. It isn't true that the HEIGHT and WIDTH arguments of the OFFSET function must be positive (!)

    I have played with your simple example of the SUM & OFFSET (where the result is displayed in cell G2), setting the above mentioned arguments to negative values.
    Here are the results:
    =SUM(OFFSET($A$1,3,3,-1,-1)) Result: 20
    =SUM(OFFSET($A$1,3,2,-1,-1)) Result: 45
    =SUM(OFFSET($A$1,3,2,-2,-2)) Result: 115

    Excel accepted the negative values of the HEIGHT and WIDTH arguments, without any problem….
    So, Microsoft's user's manual is probably wrong….

  12. Anyone up for a super big challenge? Here is my formula: I need it OFFSET!

    IF(SIN($D2)*SIN($G2)+COS($D2)*COS($G2)*COS($H2-$E2)> 1,3963.1*ACOS(1),3963.1*ACOS(SIN($D2)*SIN($G2)+COS($D2)*COS($G2)* COS($H2-$E2)))

    HELP! Imagine a big data base that looks like this:
    New York Chicago DC
    New York to LA 1300 miles !!! !!! !!
    Chicago to Boston 700 Miles
    DC to Charlotte 300 Miles

    OK So I want to create this big routing calculator in Excel. I have all of the GPS coordinates...I have the distance between routes, but I need to find out which one to do next...

    So go to the New York to LA...You see it's 1300 Miles? Note going horizontally is New York, Chicago, and DC. I need to fill those in. So I need to copy that massive formula ACROSS, but I need the rows to go DOWN. Columns stay the same, rows go down using OFfset.

    Any takers?

  13. Anyone up for a super big challenge? Here is my formula: I need it OFFSET!

    IF(SIN($D2)*SIN($G2)+COS($D2)*COS($G2)*COS($H2-$E2)> 1,3963.1*ACOS(1),3963.1*ACOS(SIN($D2)*SIN($G2)+COS($D2)*COS($G2)* COS($H2-$E2)))

    Imagine a big data base that looks like this:
    New York Chicago DC
    New York to LA 1300 miles !!! !!! !!
    Chicago to Boston 700 Miles
    DC to Charlotte 300 Miles

    OK So I want to create this big routing calculator in Excel. I have all of the GPS coordinates...I have the distance between routes, but I need to find out which one to do next...

    So go to the New York to LA...You see it's 1300 Miles? Note going horizontally is New York, Chicago, and DC. I need to fill those in. So I need to copy that massive formula ACROSS, but I need the rows to go DOWN. Columns stay the same, rows go down using OFfset.

    Any takers?

  14. Excellent article. I make good use of the OFFSET() function including within the SUM() function, i.e. SUM(OFFSET()). I have today come across something that is baffling me. I am trying to use the OFFSET() function to define the range inside a SUMIF() function. When I use the construct on some small test data it works perfectly but when I put it to use on the full data set it only sums the first column. Here is the formula:

    =SUMIF(A25:A160,A200,OFFSET(A25,0,958,ROWS(A25:A160),28))

    Can anybody see the glaringly obvious that I am missing? It works fine if I use a similar construct as a SUM(). Or is there an unpublished limitation in the function?

    (Using the ROWS() function ensures that everything will stay lined up if somebody inserts a row. 958 and 28 will be variables calculated in
    other cells.)

    Thank you.

  15. Very helpful.

  16. Just to say thanks for your Excel tips they are the best currently on the internet. I am currently using OFFSET and it is working very well.

  17. Hi,
    I need a excel formula that will arrange values in a row from differents columns but avoid 0(zero)value if in any cell.

    Data in this format
    A B C
    1 24 15
    13 0 6

    To be arrange
    A B C
    1
    24
    15
    13
    6

    Kindly awaiting your post

  18. Hi,
    I need a excel formula that will arrange values in a row from differents columns but avoid 0(zero)value if in any cell.

    Data in this format
    1 24 15
    13 0 6

    To be arrange
    1
    24
    15
    13
    6

    Kindly awaiting your post

  19. Hello,

    I have a spreadsheet with headings in column A. I would like to use a formula to to create a contiguous information - that is no unnecessary blank rows but I do need to keep all the headings in the rows.

    Thank you.

  20. Hi! This is an awesome forum and thank you for teaching me the offset function. It is working almost perfectly for me but I need to understand how to return the last 5 values in a single column of data and ignore the zeros... so my data looks like this - my offset calc is bringing back the last 5 values - but is bringing back 18, 16, 0, 17, 24 and I need to bring back 18, 16, 17, 24, 22... recommendations? Thank you so much!

    Score
    15
    12
    0
    0
    22
    14
    6
    5
    0
    0
    0
    0
    0
    15
    18
    22
    24
    17
    0
    16
    18

  21. I am trying to create a sheet where the data is referenced from another sheet "Data_Sheet_name" and then creating multiple such coupled sheets using VBA. But every time I create a copy of the data sheet the referencing go haywire. So I want a formula which can dynamically be linked to the sheet based on the active sheet name.

  22. Hi,
    I have a spreadsheet with months 1,2,3 etc across the rows and a specific number of months to count backwards in a cell. For instance, I want to use the offset function to count back 6 columns from the reference cell. However, the 6 month count results in being either off the spreadsheet or into my column with all my field labels. This is causing the error either #value or #ref.. Unless I put the offset formula starting after column F (6 columns forward). How can I incorporate the offset function while not going off the sheet or into a cell that has text?
    Thanks
    Greg

    1. Hey Greg, I'm having the same problem. Did you ever figure it out?

  23. Hello, I have a problem which is not related to this tutorial. I am very new to excel, and I have learnt a lot from you. Thanks a lot. Suppose you have data in columns A1 to D20 and you want one formula to find minimum in every ROW and SUM the minimums. I have come across the following formulas in the internet, which I would kindly ask you to unravel me. I thank you in advance:

    =SUM(MOD(LARGE(MAX(A1:D20+1)*ROW(A1:D20)+A1:D20,ROW(A1:D20)*COLUMNS(A1:D20)),MAX(A1:D20+1)))

    =SUM(MOD(LARGE(100*ROW(A1:D20)+A1:D20,ROW(A1:D20)*COLUMNS(A1:D20)),100))

  24. Excellent tutorials on Use of OFFSET Function.

  25. Could you explain please how to use SUMIFS and Offset together in one formula. Thank you in advance.

  26. hello there, I am trying to find the average of completion of TASKS CATEGORY based on the completion of individual TASKS.
    I have 22 tasks in each table and a total of 3 main tasks category, and I am not able to return to average based on the product name.
    The tab I am using has the same table repeating multiple times, one for each product I am working on. Each table has the same list of tasks to be performed with the following layout:
    B1 is the name of the product (unique)
    B4 to B25 is the list of tasks named p1....p22
    c4 to c25 is the percentage value of progress for each task.

    The table repeats itself underneath several times, what changes is the product name on b1, b27, b53, b79.. The tasks name (p1 to p22) are identical for each table in column b. I think the problem I am having is to return the average of the categories: tasks named p1 to p7 (cells b4 to b10) belong to the first category, tasks named p8 to p15 (cells b11 to b18) belong to the same category, and tasks named p16 to p22 (cells b19 to b25) belong to the third category. How do I get the average of the categories based on individual tasks completion % in column c based on the unique product name which is in b1, b27, b53, b79.. Thank you so much!!

  27. Hi This one is a bit of a challenging one

    I am not 100% sure if this uses the offset ruling or just the countif but i would really (really really really !!!) appreciate the advice.

    My data set involves dates and times and I want to make a count of new events occurring in a separate column. A new event is classed as anytime the previous column has increased by 5 minutes.

    Backgroud Info:
    so the data is times and dates of pictures taken of animals and i have to make a count of every time an event happens.

    An event is classified as any new animal that comes into view or if the same animal goes off camera and returns with over a 5 minute increase from the last time it was shown on camera.

    So if it goes off camera and comes back under 5 minutes time(for example 4mins 31) i dont want to count it.

    Further more, if another animal comes into view so 2 are in the shot i want to classify that as a new event also.

    So for example :
    date of shot time No.of animals in shot Event Counter
    01.02.2016 10.50 1 1
    01.02.2016 10.51 1
    01.02.2016 10.51 1
    01.02.2016 10.51 2 2
    01.02.2016 10.51 1
    01.02.2016 10.56 1 3
    02.02.2016 16.51 2 4,5
    02.02.2016 16.52 2
    02.02.2016 16.58 1 6
    02.02.2016 16.59 1
    02.02.2016 17.05 1 ...formula???

    so as you can see it is the end column i need the formula for to take into account date and time increase by 5 minute increases and an added count for if there is an extra animal in shot.
    I have 15,000+ data points so if you can help me it would just be amazing.
    The times only show shots of animals so theres no need to worry about an animal column.

    fingers crossed you can help!Thanks

  28. Respected Madam/Sir,

    I found Tutorials on Using OFFSET function in Excel-formula examples, very useful and helped me to understand the offset function better.

    Excellent detailed examples on offset.

    Thanks a lot.

    Regards,
    P. G. Kerkar

  29. hi,

    i am facing an issue in excel how can i merge these two colums in one cell in top to down maner.
    fr eg:- ram shayam amit
    i want this in one cell like ram
    shayam
    please help its urgent!!!

  30. Hi All,

    Excellent examples and discussions on OFFSET.

    How can you ensure that the cell format is the same when using OFFSET. For e.g., the OFFSET works, it returns the value of the cell to the appropriate new location, but some of my original cells are text hyperlinks (URLs). Thus, OFFSET only returns the text and it is not hyperlinked. Is this achievable?

    Thank you.

  31. Hi All,

    Hopefully I am in the right spot for this question..

    Basically I want to count the number of times a certain number appears in the last 20 results in a list - the list is updated often, so last 20 results changes.

    RESULT NUMBER # APPEARANCES
    3 1 ?
    10
    7
    7
    4
    10
    2
    2
    1
    11
    5
    11
    4
    2
    8
    4
    7

    Appreciate any assistance you can give.

  32. Hi,
    Great summary on Offset, just one question that can I use offset in two different excel workbook?

    1. Hi Xian,

      Yes, it can. For example, this formula will return a value from cell B4 in Sheet1 of Book2:

      =OFFSET([Book2]Sheet1!A1,3,1)

  33. Hi,
    A BIG thanks for this clarification. However, I need your help in the following issue: OFFSET function returns the value of a specific cell. How can I get the value next to that cell, and the one next to it, and the one next to it, etc. In other words, I need your help in the following: As I go to the right, I want to get the values of certain cells that go downwards.

    Hope that is clear. I really appreciate your help.

  34. I have a variable value in C6 which should be matched with the value in E6. C6 depends on the value in A2 not directly but indirectly.
    I want the excel to auto calculate A2, for any given value in E6, such that C6=E6.

  35. good article!

  36. Thank you for your help. I can now view well the function of Offset as compared to Index and Vlookup.
    I'm a self trainee with good progress

  37. Hi,

    My challenge is to retain the same format ( including color of the cell value) when I do a offset. Could anyone help me with this. Thanks

  38. I ran into an interesting problem with the offset function:
    I have a table with string data containing newlines. For example:

    this
    is
    my
    data

    the above would all be in 1 cell, but with 1 word per line and a new line character after each word (by pressing alt+enter). This works great, but if I use the offset function and it selects that cell, I get "thisismydata" with no newline characters in it.

    Any tips?

  39. This post is very insightful indeed. I have learned a lot. Many thanks!
    Also, kindly help me with a simple formula on this:
    I have an excel data that I use to monitor attendance at meetings. The columns labelled YES/NO tell whether the meeting was held or not. I want to monitor attendance in the last N meetings held (eg.last 2 or 3 columns with a "YES"). It should not count the cell when the column heading is "NO".

    That is: if I enter 2 in cell H2 it should return (2 in H3 for Mr. A and 1 in H4 for Mr. B etc). if I enter 3 in H2 it should return (3 in H3 for Mr. A and 2 in H4 for Mr. B etc)

    A B C D E F G H
    1 Jan15 Feb-15 Mar-15 May-15 Jun-15 Jul-15 Last N meetings
    2 Name YES YES NO YES NO YES
    3 Mr.A 1 1 1 1
    4 Mr.B 1 1 0 1
    5 Mr.C 0 1 1 1
    6 Mr.D 0 1 1 1
    7 Mr.E 1 1 0 1

    Thank you

  40. Thanks for this email. I need your help.

    I want a formula to search for certain strings located in a column in one sheet from a column in another sheet and replace the content of the column in the second sheet with that of the string from the first sheet if found.

    i.e. if 'xyz' in cell A1 of sheet test if found in 'sjkhgjjhkxyzrewqed' of cell b3 in sheet test2 replace 'sjkhgjjhkxyzrewqed' with 'xyz'.

    Thanks to your expected help.

    Regards

    1. hi, i understand that you want to replace sheet2 b3 cell should be enter as sheet1 a1 value

      in that case you can use these formulas in sheet2 b3 cell
      those are

      1) =xyz!A1

      2)=indirect("xyz!A1")

      here "xyz" means sheet1 name

      Hope you find the solution
      or else please let me know

  41. Hi
    I have to 2 excel sheet.
    Sheet A :The value for average of differnt columns in different cells but in same row G41.(average of column c in c41;average of column G in G41;average of column J in J41 and so on )set repeats after every 6 columns.

    Now in sheet "B" I want all the results of sheet A in column H one after other .(I cant drag the formula as the range is not continues). Please help

  42. Hi!, I want to thank Oyu in advance.
    I use this function to count the number of zeros after the last non zero data. It works perfect, except when the first cell in the row is zero.
    COLUMNS(D24:W24)-MATCH(2;1/(D24:W240))-COUNTBLANK (D24:W24)

    I want the formula to start counting zeros from the first cell if it is zero.

    Thanks

    1. =SUM(OFFSET(H4,0,0,1,-$L$3))

  43. Hey, I just wanted to say that I really appreciate you putting this together. I'm sure it involved quite a bit of work!

  44. Thanks so muchh!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)