Efficiency365 Test 6: Excel : Copying Formulas

Yes, you have been copying formulas for decades. I know. But you don’t know the most efficient method – most probably! Try it out. Spend just 4 minutes of your time.

Open the file.

It has values in column B and a formula in C4. In column C we are calculating tax – at the rate of 3%. Simple stuff.

Contents

The challenge

What is the most efficient way of copying this formula across all the data in column B.

You think you know the answer? No problem. Scroll down and check if your method is efficient!

This area is intentionally kept blank… scroll, scroll, scroll…

What was your method? Copy paste?

This requires selecting all the area to be pasted. Selection takes time. Therefore inefficient.

Drag?

Yes it works. But for large data it takes inordinate amount of time. Even if you are paid for dragging, even if your boss also uses the same method, do you really want to waste your precious life in dragging?

Double Click ?

Yes. It works – miraculous – but no. It is dangerous. Stops at the first blank it finds. In fact, here you may end up paying less tax and get embroiled into a compliance case!

Other methods?

Most methods fail because there are gaps in the data. Whatever your method is, it is repetitive.

What is the real problem?

We have never thought seriously about common inefficiencies like this. We have a job to do. We know some method which works. So we just do it. Without thinking.

In effect, we are helping Excel.

Excel should help us. Why is it not helping us?

Communication Gap

We wish that Excel should be smart enough to copy the formula all the way to the logical end point of the data. But our wish never gets fulfilled because we never told Excel WHERE OUR DATA ENDS.

Therefore, Excel has to GUESS the end point. The first blank is the end point Excel stops at – to be on the safer side.

Communicating properly = Efficiency

How do we inform Excel that the data begins at B3 and ends at B5003? We ourselves don’t know that to start with.

Now delete the data in column C (delete the title Tax and the formula in C4).

Select the data MANUALLY for the first time.
GO to B3 and try SHIFT CTRL END shortcut.
Manual adjustment will be required.

Now that you have selected the correct data range, finalize it.
Insert menu – Table. Click Ok. (Amount is the header).

This is how you inform Excel about the EXTENT of the data.

Adding and Copying Formula

Now click in C4 and add the formula

=B4*3%

and press ENTER

That’s it. Job done! This is called efficiency.

While in C4, if you move the cursor to B4, the syntax will look like

=[@Amount]

Don’t worry. Continue typing the formula and press Enter.

What just happened?

Table is a very powerful thing. Excel noticed that you are adding formula right next to the table. So it assumed that you will want to copy the formula all the way across the data.

This time, for a change, Excel KNOWS the extent of the data. So it just copied the formula.

Undo and try putting the formula in any cell in column C as long as it is next to the table. It will still work.

What is the benefit?

Time saving is the obvious benefit. But there is a more important thing which happened.

You got GUARANTEED ACCURACY.

Two benefits. One simple action – create table.

Now Excel is helping us

Did you also notice that all the inefficient ways we were using earlier are completely redundant now? If communication is done correctly, the results are efficient.

If you take up the responsibility for everything thinking that “Excel cannot do this, let me do it”, then you are asking for trouble.

Don’t assume Excel cannot do it. In fact, do the reverse.

Assume that Excel (or all Office tools for that matter) have already noticed your needs, problems and inefficiencies and they have already created the solution for you.

It is jut a question of finding where the solution is (Ribbon, Right Click, Options, Function) and using it.

Best Practice

Raw data should be converted to Table before adding calculated columns.

Add calculated columns at the end of the data so that future copy paste is easy.
If more raw data is pasted later, the calculated column formulas will be automatically extended by Table.

***

One Response

Subscribe to Blog

Join 1,812 other subscribers

Pivot Table Pro Course

Yes. You use Pivot Tables everyday. Now it is time to find out the real power and nuances. 5.5 hours video, exercises, samples, Q&A.

Excel to Power BI Course

Learn Power BI using the concepts you already know in Excel. Fast transition, in-depth coverage and immediately usable.