Fill handle tricks.
[FIGURE 1 OMITTED]
Type January in cell A1. Before you press Enter, grab the Fill Handle with the mouse and drag right or drag down. Excel will type February, March, and so on for as far as you drag. If you drag past 12 cells, Excel will start over again with January.
The Fill Handle can extend month and weekday names as well as their three-letter abbreviations (columns A, C, E, and G). You don't need to start with January or Monday. Start with any month or day, and drag the Fill Handle. Excel will extend from that starting point (column C).
Since Excel was originally targeted toward accountants, there are three different ways to fill a series of quarters. Excel recognizes Quarter 1, Qtr 1, and Q1, and it will correctly restart over at Quarter 1 if you drag past Quarter 4.
If you type a number followed by any word, or any word followed by a number, Excel will copy the word and fill the number series. For example, Room 1 in cell O1 automatically fills to Room 2, Room 3, and so on. This trick also works with ordinal numbers. "1st Period" extends to "2nd Period," "3rd Period," and so on.
Use Ctrl+; to enter today's date in a cell. Click and drag the Fill Handle to fill in tomorrow's date, the next day's, and so on.
Filling a Series of Numbers
As we've seen, Excel can extend months, days, quarters, dates, and rooms. It's initially disappointing when you drag the Fill Handle on a cell containing the number 1. Excel decides to copy the cell instead of filling it. As you can see in column S, you get 1, 1, 1, 1, 1.
One option is to type the first two numbers in the series. I entered 1 in cell U1 and 3 in cell U2. I selected the range U1:U2 and dragged the Fill Handle. Excel then filled in the odd numbers 1, 3, 5, 7, and so on.
A faster option is to simply type the 1 and hold down the Ctrl key while dragging the Fill Handle. Excel automatically extends it to 1, 2, 3, and so on (column W).
The Ctrl key tells Excel to do the opposite of the normal behavior. If you press Ctrl and drag with a date, Excel would copy the date.
Immediately after dragging the Fill Handle, an Auto Fill Options icon appears to the right of the last entry in the series. If you forget to hold down the Ctrl key, use the Auto Fill Options dropdown, shown near cell AF7, to choose either Copy Cells or Fill Series.
Right-Click and Drag
Enter a date in a cell. Drag the Fill Handle while holding down the right mouse button. The tooltips will appear as if Excel is filling days, but when you release the mouse button, you are given an option to fill months, years, or weekdays. In Column Y, I used Fill Weekdays to fill in just Monday through Friday dates.
If you need to process a form on the 17th of every month, enter 4/17/2008 in a cell, right-click and drag the Fill Handle, then choose Fill Months to quickly enter the 17th of every month.
If you forget to hold down the right mouse button, you can open the Auto Fill Options dialog to choose to fill weekdays, months, or years.
Filling a Custom List
By default, Excel can't fill future IMA Annual Conference sites (column AA) or even the alphabet (column AC). But you can train Excel to learn new lists. Type the list in a range. Select the range. In Excel 2007, use Office Icon, Excel Options, Popular, Edit Custom Lists, Import, OK. In Excel 2003, use Tools, Options, Custom Lists, Import, OK. Excel will now be able to extend your own custom series.
Skip Formatting Using Auto Fill Options
If your original cell has borders, you might be disappointed to find out that the borders are copied down throughout the series. The border in AE1 wasn't intended to be copied throughout AE2:AE6. Open the Auto Fill Series icon, and you can choose to Fill Without Formatting. Alternatively, if you only want to copy the formatting, the dropdown offers a choice to Fill Formatting Only.
Note that the Auto Fill Series icon often gets in the way of values in the next row. This is a difficult icon to dismiss. The usual trick of pressing ESC will not cause it to go away. One bizarre workaround: Click the line between any two column headings (as if you were about to resize the column), and the icon will be dismissed.
Shift and Drag to Insert or Delete Cells
Holding down the Shift key while dragging the Fill Handle tells Excel to insert cells. For example, select cell I4. Press Shift, and drag the Fill Handle down to cell I6. Excel will insert two blank cells, moving values from I5:I6 down to I7:I8. Note that this will only affect the current column. It doesn't insert cells in adjacent columns.
Be careful of using Shift when dragging the Fill Handle left or up. Excel will delete the filled range and shift the remaining cells over or up.
Double-Click to Ride the Range
If you have 12,000 rows of data, it can be tedious to drag the Fill Handle down to the bottom of your data set. If the cell immediately to the left or right of the current cell has data, double-click the Fill Handle to make Excel extend the series (or copy a formula) down until it encounters a blank cell in the adjacent column. Provided you have no blank cells within your data set, this is a fantastic way to copy a formula to the bottom of a data set.
Bill Jelen is the host of MrExcel.com. To see a short preview of his IMA Annual Conference session, visit www.MrExcel.com/ima.html. Send questions for future articles to IMA@MrExcel.com.
|Printer friendly Cite/link Email Feedback|
|Title Annotation:||Excel; computer tricks|
|Date:||Apr 1, 2008|
|Previous Article:||Ethics the Framework for success: while some ethical decisions are simply a matter of right vs. wrong, the tough ethical decisions are right vs....|
|Next Article:||Tools of the trade.|
|Defeated Blaydon display their spirit.|
|Program Excel to alert you to a deadline.|
|Excel 2003 Visual Encyclopedia.|
|Excel 2003 Visual Encyclopedia.|
|Special edition using Microsoft Office; home and student edition 2007.|