Here’s one of those things that it is so easy to miss that you can hardly believe that it’s been there for years! I’m talking about the Fill Tool in Excel: the one stop shop for filling down and across a spreadsheet with ease.
Let’s suppose you have the number 1 in cell A1 of an otherwise empty spreadsheet. The next thing you want is to have 2 in cell A2, 3 in cell A3 … 200 in cell A200. There are several ways of achieving your objective such as entering 1 in cell A1 and then this formula in cell A2 =A1+1 and then copying that formula down to fill the range A3:A200 … it works!
Take a look at the top right hand corner of the home tab on the ribbon where you will see the Fill Tool icon:
Let’s use the tool to fill A1:A200 with the numbers 1 to 200
Type 1 in cell A1 and then select cell A1
Click on the Fill Tool and select Series…
You are now faced with several choices: what we want is
• Step Value of 1
• Stop value of 200
• Click OK
The range A2:A200 is now filled with 2, 3, 4, … 200
Try the other choices yourself and see what happens: for example,
choose Rows instead of columns and then repeat the above example
use the linear type again but change the step value from 1 to 1.25 with a starting value of 9 and a stop value of 200 … in this case you will see that the series never gets to 200 … can you say why?
change the Type to growth with a step value of 1 and a starting value of 5 and a stop value of 200 …
in this case you will also see that the series never gets to 200 … can you say why?
Enter a date in cell A1 the change the Type to Date, choose columns, step one, weekdays, stop value of another date, say three months ahead of the date in cell A1. Now click OK … can you see what has happened?
Can you see the trend option in the Fill Tool? Try it. The Trend option works a little differently to what we have done so far.
• Type 1 in cell A1 and make sure the range A2:A9 is blank
• Select the range A1:A10
• Fill Tool
• Select Trend
• Click OK … what happens? Did the range A2:A10 fill with 2, 3, 4, … 10? I hope so
Repeat this trend exercise with Growth selected, otherwise exactly the same
Now repeat the Trend exercise for the Linear Type but enter 1 in cell A1, 3 in cell A2 and 5 in cell A3. Now select the range A1:A10 and run the Fill Tool with columns, linear, trend all selected … what happened?
Repeat the exercise we have just done but change the values in cells A1:A3 to 1, 3 and 5. Ensure that the step value is set to 1 then select columns, growth and trend … what happened?
In other words, play with the Fill Tool and see just how useful it is!
Try the other options for yourself now:
Up, down, left, right …
Click the link below to download this page in PDF format.
Click this link to download this page as a PDF file fill_tool_excel