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.

2 Responses to “Top 10 Power BI Interview Questions & Answers”

  1. Keith says:

    Hello...
    In Power BI I have data that includes months by name only (e.g. May, April, December...)
    I need to build charts etc. but i need the months to go chronologically... not alphabetically... I cannot seem to find the fix to this.... once again, my data does NOT have an actual date attached to it (like 02/01/2023)....only month names... can i use a helper table wher i id the month names as numbers 1 thru 12? and if so, how do i manage this to work for me ?
    Thank you.
    ~Keith

    • Chandoo says:

      You need to setup an extra table to map each month name to a running number. A simple 12 row table like
      Jan 1
      Feb 2
      Mar 3
      ..
      Dec 12

      Then create a relationship between this month table and your month column
      Now, go to "table view" in Power BI and set the sort by column to month number for the month name column on this new table.
      Finally, use the new table's month name whenever you need to refer to the month name in the visuals.
      They will be chronologically arranged.

Leave a Reply