Use Formula to Copy Row Dynamically in Google Sheet

Sarmad Gardezi

This tip is one of the most challenging Tip I’ve shared but it’s worth the effort to learn.

The index function has a curious, hidden property that’s extremely handy. With this feature you can dynamically enter one row data to another one easily. If you put the index function into a range reference it returns a valid cell address, which means you can build dynamic ranges of data.

Let’s see an example.

Suppose we have a column of values — perhaps it’s recent transaction values or new leads each day — and we want to compute a rolling 7-day average.

Double Index Trick

We start by using this double index trick to extract the last 7 values from the list.

Whenever new data is added, the double index formula includes the new data and “rolls” down the range.

Here’s the formula to extract the last 7 values from column A:


In the image you can see that the double index formula in cell C2 has returned the last 7 values from column A. If we add additional data to column A then the formula will update to show the last 7 values.

(You can change the number 6 to something else if you want a different period.)

Can you see how it works?

The COUNTA() simply counts how many values we have in column A.

The INDEX(A2:A,COUNTA(A2:A)) returns the value at the position we specify. The first INDEX gets the value 7th from the bottom and the second index gets the bottom value.

However, per the index trick above, when we put an INDEX function next to a colon “:” in a range reference it returns the cell address instead of the value! Wow!!

So the two index functions return cell address A10 and A16 respectively, instead of the values in those cells! This gives the range reference A10:A16 which returns the 7 values for us.

This formula is equivalent to saying:


Except that the double index keeps “rolling” with the data to get the last 7 values as new data is added.

Rolling Average

The double index formula can be used to create a rolling average, by wrapping this double index with a standard AVERAGE function:


Congratulations, that was a tough lesson! It can make your daily work easy.

More great articles

How to Disable Jetpack image Sitemaps from WordPress

Sitemaps are files that list each post and page that should be indexed by search engines like Google or Bing.…

Read Story

Number the Headings in Google Docs using Google App Script

Is it possible to number the headings in a Google Docs/Drive document? Yes you can automatically place the numbers in…

Read Story

The Best Websites to Learn Coding Online in Pakistan

Pakistan is now playing an impotent role in the developing field of IT. Many students starts their work by becoming…

Read Story