{"id":2896,"date":"2016-05-04T23:04:39","date_gmt":"2016-05-04T21:04:39","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=2896"},"modified":"2024-05-15T19:46:50","modified_gmt":"2024-05-15T17:46:50","slug":"create-dynamic-data-range-offset-function-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/create-dynamic-data-range-offset-function-excel\/","title":{"rendered":"Create a Dynamic Data Range with the OFFSET function"},"content":{"rendered":"

\"Excel<\/a><\/p>\n

What does it do?<\/strong><\/em><\/p>\n

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

Formula breakdown:<\/strong><\/em><\/p>\n

=OFFSET(reference<\/span>, rows<\/span>, columns<\/span>, [height]<\/span>, [width]<\/span>)<\/p>\n

What it means:<\/strong><\/em><\/p>\n

=OFFSET(start in this cell<\/span>, go up\/down a number of rows<\/span>, go left\/right a number of columns<\/span>, height of range<\/span>, width of range<\/span>)<\/p>\n

\n

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

Click here to see how the formula works visually & interactively\u00a0<\/a><\/span><\/p>\n

When we have a large data set and add new rows\/columns of data on a daily basis, we sometimes want to capture that new data in\u00a0our analysis automatically without having to amend the\u00a0data range.<\/p>\n

One example is when we are using a Pivot Table<\/a> and our data source is not in an Excel Table. \u00a0We want the new data to be added in the Pivot Table’s Data Source automatically without having to Change the Data Source each time manually.<\/p>\n

This is how it is done below (go to the bottom of the page if you want to see the animated gif tutorial):
\n