Sep 22, 2014

You all agree that Microsoft Excel’s Autofill feature is one of the most popular and versatile features in Excel, however not a lot of people know about its full potential, so let’s explore this feature today. If the content of a cell is a formula, Autofill, by default, copies the formula to other cells taking care of cell increments automatically. If the content is a value, it simply copies it to other cells. This is the default behaviour, but it doesn’t stop there. Every time you use Autofill, a little icon appears where you release the mouse, and if you click on this icon you will get further options, which we will shortly discuss. Please follow these steps to discover some of the features. In your Excel worksheet, type the number “10” in cell B2 and then format the cell with red font. In cell C2, type “EN89” and in cell D2, type “Sales.”

Autofill cell B2 across to cell D2 and after you released the mouse, click on the icon that appears. Select Fill Formatting Only.

See how only the formatting, in this case, the red font, is copied instead of the value.

Autofill cell B2 down 10 rows and click on the appearing icon. Select Fill Series. You can see that the numbers will be filled sequentially and the formatting is also copied.

What if you wanted the numbers to fill in multiples of 5 rather than 1? In this situation, you could autofill while holding the right click of your mouse rather than left click. A menu will pop up and you will need to select “Series…”. Type 5 in the Step Value box and click OK.

You should see correct result i.e. a sequence of linear series with increments of 5. You could repeat this process again but this time select [Growth] in the pop up box. This produces a geometric series of ratio 5. For the next exercise, in cell A2, type the number “10” and format it with red font again. Autofill it down 10 rows and click on the appearing icon. Select Fill Without Formatting. You can see that in this case, the number will be copied without formatting.

In cell B3, type in number “20.” Now select B2 and B3 together and autofill down to A11. What you will see is the linear series of an increment of 10. Excel has calculated the difference between the first 2 cells you selected and has applied this increment to the rest of the series. It’s also possible to autofill months of the year and days of the week. For example, in cell F2, type in “Jan” and autofill it down to F11. Now you’ll see a sequence of the months in the 3-letter format – Jan, Feb, Mar etc. In cell G2, if you type “January” and autofill it down to G11, you’ll see the months sequence in full – January, February, March etc. You can do the exact same thing with days of the week. If you were to type “Jan” in cell F2 and “Apr” in cell F3 then select both F2 and F3 and autofill down to F11, Excel will calculate the difference between the two months which is 3 and has continued this pattern for the rest of the series. There we have it. Although there are some other options available in the popup menu and icon, they are self descriptive and I’m sure you can explore them easily now. Happy autofilling!