Tuesday, June 7, 2011

Use copy paste & to maintain references to tables [Quick Tip]

Posted on 23 May 2011 to learn Excel-9 comments

Using Excel 2007, Microsoft introduced powerful and useful feature called as tables. One advantage of tables that you can write formulas readable using structural references. This means, you can write easy to understand such formulas,

Structural references in Excel Tables - Example

[Help SUMIF formula]

But there is a problem. When you type this formula and dragging formula cell remaining side fill cells, Excel changes the reference columns of the table and thus makes your formulas almost useless.

In addition, there is a simple solution for this problem.

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

Preserve Table References while Copying Formulas

Since the found feature tables in Excel 2007, I have been using them to save time and simplify working with data. Schedules of many useful features that make life simple for analysts and data junkies everywhere.

What about you? Are you using Excel tables? What are the tricks above your table? Participation please use the comments.

If you use Excel 2007 or above, I encourage you to learn Excel tables. They make your life simpler. Go to the bottom of articles for more information,

Spread some love
It makes you fabulous!

& Navigation functions

Tags: data Excel Excel Excel tables, see "Paste" and "quick tip", "References" and "screencasts" and "spreadsheets" and "structured references", "sumif)

RSS feed for comments on this post. TrackBack URI

View the original article here

No comments: