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,

[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:

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

Microsoft® Excel® 2007 Quick Reference Guide - Excel 203: Sorting, Filters & Pivot Tables

8-page bi-fold.

This quick reference guide is a valuable resource for any user looking to master the ultimate data analysis tool in Excel: PivotTables. Also discussed in this guide are sorting features as well as data analysis using filters. Topics include: sorting data using various techniques, using chronological sorting, adding data filters to extract data, applying filters to drill-down and locate data sets, using advanced filters to extract and analyze data and combining filters and sorting to create unique data sets. In addition, this guides offers extensive details on creating PivotTable reports to analyze and understand complex data. You will learn how to establish PivotTable guidelines, setup a PivotTable report, using drop-zones to compose the report, grouping and ungrouping data, using filters within a PivotTable, adding calculated items and fields, exploring layout options and creating effective PivotCharts.

The guide is also structured to be a companion for hands-on training or self-paced training. It includes step by step instructions and downloadable exercise files

Price:

Click here to buy from Amazon