Imagine that you have 50,000 rows of data. You want to add a new calculated column. Adding the formula is easy. But copying it to 50,000 rows is nothing but frustration.
Three methods are typically used: All of them are inefficient!
Copy and Paste, Drag and Double Click. Drag is lengthy – so is Copy Paste. Double Click is DANGEROUS because it stops if the column on the left has a blank cell. And usually we have lots of blank cells – at least we have to assume so!
Of course, there is a solution – a magical solution!
Consider this data as an example…
Contents
The problem: Why does Double Click Stop at gaps?
The issue is simple. When we double click at the corner of the formula, we expect that Excel should find the actual end point of the data. However, we fail to realize that Excel has no way of knowing whether the data is in three rows or it includes the last two rows as well. To be on the safer side, Excel stops when it encounters a gap.
In short it is a communication gap.
How do we solve the problem?
By communicating correctly!
If Excel is getting confused with the ACTUAL end point of the data, then why not explicitly inform Excel about it?
This is done by selecting the original data and inserting a table.
Do this BEFORE adding the formula.
In this example, the data is only few rows. If you have large number of rows, you will have to select all of them manually. Shift CTRL End shortcut may work. If not, you must figure out the data end point and select the data manually.
Once the data is selected – choose Insert – Table.
Recommended PivotTables option was added in Excel 2013
For a table to be created there are some simple rules
- First row must contain headers
- No merged cells
- No empty headings
- No duplicate headings
Now the table is created. Table always knows its boundary – beginning and ending.
Now add the formula in the second column as usual and press ENTER.
Now Excel will automatically copy the formula to all the rows. Gaps are no longer your concern. It is fast and accurate.
Dual benefits: Tangible and Intangible
The tangible and obvious benefit is that you are saving the time which you would otherwise require to copy the formula using any of the older and inefficient methods.
The second benefit, which is intangible but more important, is that the accuracy of the calculated data. None of the rows would be missed even if there are gaps.
Key Learning:
Convert tabular data into a table before adding formulas
Note the formula. Once you have a table, you can use the column names in formulas! The @ sign indicates that the data should be taken only from the current row. If you omit the @ sign, it will consider the entire column if you use it with a summary function.
