Most probably, you have been missing this Auto-copy feature for the last 8 years. Take 3 minutes to find out how it can simplify your life and increase accuracy.
Contents
How do we copy formulas?
Most people use one of the following methods:
- Drag – which is a total waste of time!
- Copy Paste – also requires drag – to select the paste area.
- Double Click – looks very efficient – but is most dangerous. NEVER use it.
Why not use Double Click? When you double click – you are hoping that the formula will be copied all the way till the data ends. Unfortunately, this is wishful thinking. You never found out where the data ends and you never informed Excel about it. So what is the result?
Excel is now GUESSING where the data ends! It is helpless. So when there is a gap in the data (on the left or right side), it just stops. This can be very dangerous if you don’t notice it. And trust me – many people don’t notice!
This can lead to wrong calculation results and can have direct adverse impact on business.
NEVER USE DOUBLE CLICK – unless you are sure that there are no blank rows. But to check if there are no blank cells, you have to waste additional time.
Why not let TABLE handle this mundane but important activity?
Select the data manually and convert to tables
The first selection of the range has to be done manually. This is how YOU find where the data ends. This selection may be simplified with SHIFT CTRL END shortcut. But it is not guaranteed to work at all times. This shortcut basically selects data from the current position to the end of the worksheet – called the LAST CELL. Of course last cell is not guaranteed to be the last row of your data. Hence it is of limited use.
In any case, select the raw data – somehow. And then freeze that selection by converting it to a TABLE. That’s it. Now you will get lot of benefits.
Now formula copying is no longer your job
Congratulations. You just outsourced the mundane but important activity of copying formulas to Excel.
Type a formula just outside the table, in the next column – as usual. Now when you press Enter to finalize the formula, Excel takes over and copies it all the way to the end of the table. Job done. You neither have to use Drag or Double Click or Copy Paste.
Notice that the formula column was incorporated as a part of the table and auto-formatted as well. Of course, Excel does not know what that new column should be called. Just type a name instead of Column1.
Those green marks are showing an error “Formula refers to empty cells”. As we know that this is alright, we can globally remove these types of errors. To understand how this is done, read the article: Green Marks Part 5/5 (Background Error Checking in Excel)
That is how you OUTSOURCE mundane work to an expert – Excel in this case.
Benefits? Time saving + Guaranteed Accuracy
Earlier we were spending a lot of time for copying formulas (and cross checking if the copying was done properly). Now we are saving all that time. It can add up to a significant amount of time per day.
More importantly, we can now guarantee that the formulas will be copied till the logical end of the data.
Paradoxical efficiency
Why paradoxical? Because this is unheard of. If you want MORE accuracy (or more quality) you need to put MORE effort.
In general, if you want MORE of any outcome, you have to put MORE effort or input.
Here is a situation where you are putting much LESS effort and getting BETTER outcome. This is called efficiency!
There is more
Now we have data along with the formula. What happens when more data arrives next month?
Usually we append it to the bottom of existing data. If that is done, the calculated columns do not get copied. That work we have to do manually.
Now, of course, it is Excel’s job. Try it out and you will be amazed.
You will also be depressed – thinking as to why I did not know this 8 years earlier 
Best Practice
Usually, when we add calculated columns to data, we add them next to the base columns. But now that we know Table can auto-update calculated columns, it makes more sense to put all calculated columns at the end of the raw data columns.
This way, when the next data block arrives, you can just append it to the bottom of existing data and be sure that the calculated columns will be copied automatically.
If you had added calculated columns in the middle of existing data columns, appending next data block will not work at all – leading to lot of confusion.
In short, put all calculated columns after all the base columns in the raw data.
What Next
Adding all calculated columns at the end of existing data requires formulas to be put with lot of manual effort. In the next article we will see how to create more intelligent formulas in calculated columns using our best friend – TABLE.
Related articles
This article is a part of the series: Knowledge Pack – Excel Tables
***
One Response
Reblogged this on SutoCom Solutions.