With Excel 2007, Microsoft has introduced a powerful and useful feature called as Tables. One of the advantages of Tables is that you can write legible formulas by using **structural references**. That means, you can *write easy to understand formulas like this,*

**But, there is a problem.** When you write these formula and drag the formula cell sideways to fill remaining cells, Excel changes table column references and thus makes your formulas almost useless.

**Well, there is a simple workaround for this problem**.

### Use copy & paste.

**Instead of dragging the cell to fill formulas, you can use copy & paste to fill the formulas**. In this case, Excel will preserve all table references while changing the cell references accordingly. See this demo to understand:

### Share your Table Tips & Tricks

Ever since I discovered the tables feature in Excel 2007, I have been using them to save time and simplify my work with data. Tables have several useful features that make life simple for analysts and data junkies everywhere.

**What about you? **Do you use Excel Tables? What are your top table tricks? **Please share using comments.**

### More on Excel Tables

If you are using Excel 2007 or above, I encourage you to learn Excel Tables. They will make your life simpler. Go thru below articles to learn more,

## 11 Responses to “Use Copy & Paste to Preserve References to Tables [Quick Tip]”

If you're interested to work with Tables from the VBA side of the fence, check out my article on that:

http://www.jkp-ads.com/articles/excel2007tablesvba.asp

I think Tables are the best new feature in Excel 2007, it makes formula building a lot easier once you get the hang of it.

Another workaround for the problem you describe is selecting all three cells in this case and then hit CTRL+R.

I use data tables to create distinct data lists. Copy and paste a column and the Crtl+t to make the pasted column into a table. Then choose to remove duplicates and convert back to a range.

If anyone knows how to use a data table as a validation list it would be very handy

To use a column form a data table in Data Validation list, I use INDIRECT function.

Example: =INDIRECT("Table1[Column1]").

Its very handy

@ Tim: to do that you need to create a named range which refers to the column you need from the table.

In the example above if you would want a validation list with the salesmen you would need to create a named range which refers to MonthlySales[Saleman].

Another option would be to use a formula as source for your list in the validation with INDIRECT like =INDIRECT("MonthlySales[Saleman]")

@ all

the wonderful 😉 tables also exist in Excel 2003 !

under # Data # List

Regards Stef@n

@ Fowmy; cool, I didn't know that. It's strange though that Excel won't let you use a column from a table as a validation list directly though...

@ Stef@n; the lists in 2003 don't have all the options as the 2007/2010 tables though. You can't use the structural references for example.

@ m-b

Refer this link for more info:

http://www.cpearson.com/excel/indirect.htm

some one help me to avoid repetation of lists, when we use =INDIRECT(“Table1[Column1]“) in validation. i.e. I need unique list to be displayed in the validation list.

Thanks

Jude

Tip I learned from ExcelIsfun, Mike Girvin, absolute table reference looks like this Table1[[Field1]:[Field1]]. This one you can drag across without any problem.

Downside, it sure makes your formulas longer.