 |
|
|
|
|
As you may be aware, if you type the number 1 in a cell (say A1) then type the number 2 in cell A2 you can then select cells A1 and A2 and drag them down with the Fill Handle. Excel will automatically increment each cell by 1. If we put 1 in cell A1 and 10 in cell A2 and then dragged the Fill Handle, Excel would increment each cell by ten. In other words so long as we start the pattern we want, Excel will do the rest for us. We can take this to another level though by using Excels AutoFill shortcut menu.
Type 1 in cell A1
Type 5 in cell A2
Select cells A1 and A2
Right click the Fill Handle and drag down to A10.
Release the mouse and click Growth Trend on the shortcut menu
You should now have the numbers:
1
5
25
125
625
3125
15625
78125
390625
1953125
To see what Excel has done here put =A1*5 in cell B2 and copy or fill this down to cell B10. If you had selected Linear Trend rather than Growth Trend, Excel would have simply incremented each cell by 4 (ie 5-1)
We can now take this another step by using the Series dialog box. To see this box we again right click on the Fill Handle of a cell or cells that contain numeric values and drag down and release, then select Series. We can now also set our Stop Value and Step Value. The best way to find out the different options here is to simply select an option and then study the results.
Perhaps the AutoFill is most useful when working with dates. We can use the AutoFill shortcut menu to increment any date by Days, Weekdays, Months or Years. If we select Series on the AutoFill shortcut menu we can use the Step Value to increment our date by any pattern we want!
Once you start to use this you will no longer use a formula to increment dates as this is far more flexible and much easier to use. You must have a valid date in a cell before dragging down via the Fill Handle
You can also type the text Monday or Mon in a cell and drag down or across and Excel will fill the cells with the days of the week. The same principle also applies to month names and any text followed by a number eg Quarter1, Week1 etc. But lets say we have a list of names (or whatever) that we need to use in many Workbooks. The list of names may be as many as 100 or so and the last thing we want to do is have to open a Workbook and copy the list of names each time. Fortunately Excel supplies us with Custom Lists for exactly this type of problem.
Highlight the list of names. If needed Sort the list.
Select Tools>Options-Custom Lists
In the Import list from cells box should be your selected range. If not click the collapse dialog box and select it.
Click Add, then click OK
Now in any cell type the first name of the list and then drag down with the Fill Handle. Excel will place in all the other names within the list.
So in summary, you will probably find that most Excel users have no idea of some of the features and functions shown above. Custom formats have, to a degree, been phased out due to the introduction of Conditional Formatting from Excel 97 onwards. However, Conditional Formatting is limited to changing the font attributes, ie; colour, bold etc. and background colour, while the use of Custom Formats is limited only to the users own imagination.
The Text to Columns feature can be an extremely time saving tool when used by a user who can think outside of the box. The example we used in this lesson was used to try and demonstrate that with a bit of lateral thinking you are by no means limited to what you first see in an Excel feature or function.
Basically, becoming proficient and confident in the use of these and many other Excel features will lead to you exploring other avenues in Excel that most users would not consider a viable solution to a problem. Possibly one of Excel's best attributes is its versatility and how we can apply this to the task at hand.