Excel tools to demonstrate

=OFFSET(E4,0,-$B$1) in cell E5 for January and then copy it across row 5 for the other months. This formula says to start in the January sales cell and go a certain number of cells to the left (because of the minus sign), where this number is specified in cell B1. To see how it works, try changing the value in cell B1 to 3 or 1. (What would go wrong if you changed this delay to 4 months? How would you fix it?)

Try it! Suppose that a manufacturing company buys raw materials from a supplier. If the cost in any month is $x, the company pays 40% of this cost 1 month from now and the other 60% 2 months from now. Use the OFFSET function to calculate the payments made in January through August, based on the costs through July. These calculated payments should change automatically if you change the inputs in cells B1:B2 and D1:D2. For example, make sure your formulas react correctly if you enter 2 and 3 in cells D1 and D2. (Scroll down to see the answer.)

Another great use of the OFFSET function is to create a dynamic range name, one that expands or contracts depending on the number of data values in a range. Consider the following spreadsheet, where monthly sales values are entered in column B, and the total of all sales values is calculated in cell E1. Every month, an extra sales value and its month label are appended to the list in columns A and B. We can make the formula in cell E1 automatically adjust to the appended values by using a dynamic range name.

To do this, I selected the Define Name dropdown from the Defined Names group in the Formulas ribbon. In the Name box at the top of the resulting dialog box, I entered Sales as the range name. In the Refers To box at the bottom, I entered the formula