Statistics for everyone: Excel and Statistics(1), Autofill and absolute references

Excel and Statistics, everybody should know. : Contents

Autofill and absolute references

In Excel, enter 1 and 2 as shown, and then select two cells. You’ll get a small square in the lower right Œcorner(autofill handle), grab it and drag it down. You will automatically get a number, as shown in the picture on the right. Enter 1 or 3 and do the same experiment.

To the right of the automatically generated numbers is a small square. Try clicking this.

Naturally, ‘Fill Series’ is selected, let’s change it to ‘Copy cells’. Then it will appear repeatedly, 1,2,1,2,1,2. You can choose according to your needs. This is called ‘autofill’, and the rectangle is called ‘autofill handle’.

This ‘autofill handle’ automatically provides various options depending on the type of data.

Each cell has a name, with columns A and B and rows 1 and 2. ‘A1’ means ‘row 1 of column A’. If you put ‘=A1*2’ in B1 and press Enter, the apparent is 2, and the actual content is represented at the top. This is called the ‘formula bar’ (circled in red), but you don’t have to know the name. Anyway, if something looks like a number or a letter, but the actual content is completed by some function, you can check ‘what it is’ in the ‘formula bar (red circle)’. Now let’s double-click or grab the squareŽ, or ‘Autofill Handle’, and drag it.

When you double-click it, it will be automatically populated to the appropriate range. It is populated so that it is twice the value in column A. There are many situations where you can use this easily and conveniently.

C1 is put 2, and B1 is the product of A1 and C1(check formular bar). However, when I implemented ‘autofill’ as you can see in the picture on the right, everything became zero.

If you double-click B2, it will show which cell value you are using as a colorful rectangle, but you can see that it is using A2 and C2. What I intended is that when I try to ‘autofill’ multiply by C1, it doesn’t turn into C1, C2, C3.

At this time, even if you ‘autofill’ by adding a $ mark before 1, you can make 1 fixed, and this is called ‘absolute reference’. Anything that is not an “absolute reference” is a “relative reference” and “relative reference” is usually used. This mix of “absolute references” and “relative references” can solve many problems.

Your work will be happy, if you are used to C1 and $C1, C$1, $C$1.

Excel and Statistics, everybody should know. : Contents

=================================================