fbpx
Search
Close this search box.

Use Copy & Paste to Preserve References to Tables [Quick Tip]

Share

Facebook
Twitter
LinkedIn

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,

Structural references in Excel Tables - Example

[Help on SUMIF formula]

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:

Preserve Table References while Copying Formulas

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,

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

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

  1. 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

  2. m-b says:

    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.

  3. Tim says:

    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

  4. Fowmy says:

    To use a column form a data table in Data Validation list, I use INDIRECT function.
    Example: =INDIRECT("Table1[Column1]").

    Its very handy

  5. m-b says:

    @ 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].

  6. Fowmy says:

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

  7. Stef@n says:

    @ all
    the wonderful 😉 tables also exist in Excel 2003 !
    under # Data # List
    Regards Stef@n

  8. m-b says:

    @ 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.

  9. Jude says:

    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

  10. GraH says:

    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.

Leave a Reply