Auto resizeable range with Offset function

Resizeable range is commonly known as the dynamic range in other Excel sites. With the range, we will no longer need to resize the data source for Pivot tables. Before we learn how to set up one, let's first understand the offset function, the main component in resizeable range.

The offset formula will return the address of a cell if the width and height is set to 1. And it will return a range of cell if the width is set to more than 1. Using the counta formula, we will be able to measure the height (no of rows) and width (no of columns) of the database automatically. And if the counta formula is applied to the offset formula instead of entering a fixed number, we can a re-sizable range that varies based on the size of the database. Here is how the formula of a re-sizable range.

In the above formula, the starting point of the database is A1. This is denoted by first the 3 parts of the Offset formula. In the fourth part, we used a counta formula to count the number of records in columns A, a mean to determine the height or number of rows of the database. In the same way, we use a counta formula for the fifth part to count the number of cells in row 1 to determine the number of columns in the database (Each column should have a field name).

Even though the formula will give an automatic resizeable range, the solution will not be complete without the steps that follow. This is because pivot table cannot accept formula in their range. But they can accept a range name and the range name can accept formula. Therefore, we have to create a range name and put the above formula into its reference.

Then put the range name into the pivot table range when you are asked for the source range in Step 2 of 3 of the pivot table wizard.