Today’s Classic Tip comes from TechRepublic’s Excel Tip Techmail dated June 2, 2008, and concerns how to use the Fill Series feature in Excel:
Today’s Excel Tip
USING THE FILL SERIES FEATURE
A common task in building a spreadsheet is entering a series of dates
or values. Fortunately, you don’t have to key in every item of a series
manually. The Fill Series and the Fill Handle features make these jobs
For example, suppose you’re going to capture quarterly sales figures
for a two-year period starting with the quarter that ends 3/31/00. You
could do this by just typing the values by hand. Or you could highlight
the cell containing the date 3/31/00 and the seven cells to its right
and then select Edit | Fill | Series. In the Series dialog box, click
Month in the Date Unit section, enter a 3 in the Step Value text box,
and click OK. Excel will place dates in the seven cells you highlighted
at three-month increments beginning with 3/31/00.
Here’s another option: Place 6/30/00 in the cell to the right of your
cell and highlight them both. Then click the bottom-right corner of the
selected cells and drag. When you do, you’ll activate the Fill Handle
(that small black square at the lower right of the selection). Drag the
selection over six cells to the right and let go. Excel notes the
difference between the two cells and matches that difference for the
selected cells using the Fill Handle.
The Fill Series dialog box offers many options for doing more complex
series fills. Experiment with the different choices, or check out
Excel’s online help for more information.
Pretty simple tip, huh? If only it were that easy in Excel 2007. With the introduction of the infamous Ribbon in Office 2007, doing simple tasks like this increases in complexity for longtime users while paradoxically making things easier for people who aren’t as familiar with functions in Office 2007.
The “old style” pull-down menus that the Ribbon replaced have been in use since the Windows 3.1 versions of Office. With only minor changes from version to version, the locations of the commands have also not changed very much. That meant when you went from version to version of Office, the learning curve wasn’t so bad. Figure out the minor changes and off you went.
Microsoft also made it easy by creating keyboard shortcuts for just about everything. Once you became familiar with a routine, your hands never had to leave the keyboard. For example, the tip above could be accomplished easily by pressing [Alt][E], then [I] (oddly enough for Fill), then [S]. After a while, the keyboard shortcuts are much more efficient than taking your hand off the keyboard and choosing the selection from a toolbar — especially where multiple clicks are concerned.
Arguably the Ribbon makes it easier for someone less familiar with Excel or more comfortable with the mouse, but I’m not sure how. Let’s look at the above tip again. Where in the Excel Ribbon below would you go to find Fill?
Oh yeah… it’s that teeny tiny arrow on the far right. You’ll find it by hovering the mouse over each and every icon until the help appears. It’s the one shown here:
Click it and FINALLY you’ll see your Fill choices:
Click again and you’re done.
Fortunately, Fill is on the Home tab of the Ribbon and not buried on another tab. Sorting out which icon on which of the several Ribbon tabs represents the function you want is half the battle. Having to take your hands off the keyboard to click repeatedly just adds insult to injury.
Using Office 2003 commands
“Ah, but wait!” you say. “You can use Office 2003 keyboard commands in Office 2007, and they’ll still work!”
Yes, but only if you remember the exact command and its location. Much like the tip above, you might remember that Fill was under the Edit menu, but think that the next key was [F], and not [I]. With the old pull-down menus, you could at least press [Alt][E] to see the next key. Office 2007’s keyboard emulation doesn’t offer such help.
One step forward, two steps back
Microsoft spent a lot of time and effort making Word, Excel, and the rest of the Office programs easier to use by introducing the Ribbon. Be prepared to relearn all your old tips and tricks to overcome many of Microsoft’s “improvements,” especially where the Ribbon is concerned.