excel formula for dynamic range

Question

I've created a report in excel. The intent is to paste the data into the data tab and refresh the workbook to populate all tabs that include charts, and pivot tables.

I have additional columns that contain formulas that populate based on the data entered. Each extract will have a different number of rows, so I would like these columns to only populate based on the number of rows in the data entered. Is there a way to do this and avoid having to drag down formulas or delete extra formulas? Like a form of dynamic range?

Example: Lets say my extract has 10 rows of data. I have column A as open date and column B as closed date. If I wanted to have column C automatically populate when the data is entered with turnaround time (closed date - open date), how could I do that only going 10 rows down?

Lets says the next weeks data extract has 20 rows...could I have column c populate all 20 rows this time without having to drag the formulas 10 more rows down?

I prefer to do this without VBA but if necessary or easiest, that is fine...please help.

Answer 1

You can easily make this happen by using Excel Tables.

Background on Excel Formatted Tables

When you format your data table into an Excel Formatted Table, you'll notice that when referring to the table you'll get a different type of range reference. No longer does the range say B3:B6, it will say something like: [@Name]. This is great for dynamic ranges since the number of rows is no longer relevant to the range: [@Name]... Excel knows you are referring to ALL rows in [@Name].

How to Update your Table With new Values

Let's say you setup your data as an Excel Table and there are 10 rows of data. The next day you want to update the data with a total of 20 rows. Simply Copy and Paste Special (Values) and the table will extend to 20 rows in height and will carry down any additional columns you have as formulas. Piece of cake. The problem will be removing rows when your data on day 3 is back down to 10 rows... you will need to manually delete the extra rows.

Here's a screenshot of the difference:

To quickly setup an Excel Formatted Table, just press Ctrl + T within your block of data you want to convert and click OK.