 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
Excel has yet another way to Copy (not Cut) data in cells and this is via the Fill Handle . You may have noticed that when you select a cell or range of cells the bottom right of the outlined cell or range has a small black square. This is called the Fill Handle. The Fill Handle can be used for filling a range with increments of any choice we choose, in fact it can do much more than just this, but we will only look at using it to Copy and to increment. To see what we mean try this:
- Type the number 1 in cell A1
- Select cell A1 and place your mouse pointer over the bottom right corner until the mouse pointer changes to a small black cross.
- Hold your left mouse button down and drag down to cell A10
- Your range should now contain the number 1 in all cells.
This is using theFill Handle to Copy. Now try this:
- Type the number 1 in cell B1
- Select cell B1 and place your mouse pointer over the bottom right corner until the mouse pointer changes to a small black cross
- Now double click the Fill Handle with your left mouse button
This should fill the range B2:B10 with the number 1. What Excel does is look in the range immediately to the left and if there is data in that range it copies down to the last cell containing data. If the range to the left is blank, it looks to the right, if there is data in that range it copies down to the last cell of that range. If both ranges on the left and right are blank, nothing happens when you double click the Fill Handle.
Let's say we have the number 5 in cell A1 and we wish to fill down to cell A100 in increments of 5.
To do this, follow these simple steps.
- Type the number 5 in cell A1 and the number 10 in cell A2.
- Select both cells A1 and A2.
- Place your mouse pointer over the black square, bottom right corner of A2 until the mouse pointer changes to a small black cross.
- Holding down the left mouse button drag down to cell A100.
Excel will fill the range A3:A100 with increments of 5 up to 500. What Excel does in this instance is look at the value of cell A1 and A2 (the selected cells) sees that there is a difference of 5 (10-5) and increments by that number.
As you can imagine this can be very handy even for a simple increment like this.
There is one more feature of the Fill Handle we will look at and that is what's known as CustomLists. By default Excel has two types of Custom Lists, these are Days of the Week and Months of the Year. Try this simple exercise.
This time Excel will have filled the 12 Months of the year for us. If we had typed a day of the week Excel would fill the range with the 7 days of the week, looping and Starting again after it gets to the seventh. While these are the only two defaults, Excel would still recognize Jan or Mon or any other day or month abbreviation. Not only this but we could type any text followed by a number such as Quart1 and use the Fill Handle to increment by 1 so you would see Quart2, Quart3 or type Quart1 in A1, Quart3 in A2 and use the Fill Handle and Excel will increment by 2
Go To Free Excel Training Lesson 8 . Back to Previous Lesson
Go to Excel Basic/Level 1 Training Index
Instant Download and Money Back Guarantee on Most Software
Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates