Software

This old Excel tip shows the backwardness of the Ribbon

Here's a tip from our Excel Techmail which highlights the problems caused by the Ribbon interface in Office 2007. This tip shows how to quickly create a series in Excel 2000. See how complicated such things are now.

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

easy.

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.

11 comments
ssarfraz
ssarfraz

I am not sure if you tried to track the [ALT] combination for Series command, but here it is: [ALT] + [H] + [FI] ** last hot key is (fi) I am not a big fan of MS at all, but guys, they have what you asked for... so please... :)

psychosmurf
psychosmurf

I've been an office user since Office 95. I knew how to use it, where to go to find everything and how to do anything in Office. With Office 2007, BOOM! I'm back to being a highly evolved monkey doing a math problem. I've lost more productivity with this suite of products than anything I've ever used. I spend more time re-learning how to use stuff I already knew how to use than I do working to produce. It would seem that Microsoft is very much trying to damn its own products into oblivion: I've actually switched to OpenOffice because of "The Ribbon". It's just easier to use and with the Office exporters no one at work knows the difference. It would seem that Redmond is focusing WAY too much on flashy form and not enough on function these days.

dogknees
dogknees

The method using the fill handle still works in exactly the same way as previously. Where's the problem? There is one item that I've tried in vain to locate in the Ribbon though. Where is "Move or Copy Sheets"? It doesn't even appear in the Help as far as I've been able to find.

John Sheesley - TechRepublic Pro
John Sheesley - TechRepublic Pro

While working on a post for Classics Rock, I stumbled across an old Excel tip showing how to do a Fill Series. It was a pretty simple tip, but doing the same thing in Excel 2007 wasn't easy because the Ribbon gets in the way. http://blogs.techrepublic.com.com/classic-tech/?p=139 I think the Ribbon was a good idea with a bad implementation. Microsoft should have kept an old-style menu option and not made it nearly mandatory for you to take your hands off the keyboard to do common tasks. What do you think about the Ribbon and how it's used in Office 2007?

boxfiddler
boxfiddler

to the Quick Launch Bar. At the end of the bar, click the little arrow, then choose More Commands. When the dialogue comes up, in the left-hand drop down box, choose All Commands. Scroll down until you find Move or Copy Sheet. Double click it to put it in the Quick Launch Bar.

s31064
s31064

Right-click the sheet's tab at the bottom and the option is still in that list.

boxfiddler
boxfiddler

it takes up too much screen space on small monitors. Yes, I know I can hide it, but then I have to unhide it when I want to use one of its functions. That mini-formatting bar that could come in handy for some, I have shut off because it startles me when it pops up. I don't find anything 'intuitive' about the ribbon, likely because I am very comfy with menus. Were it not for the quick launch bar at the top of the window - which now stretches nearly completely across the top of the window in my Office 07 applications, I would be doing more hunting and clicking around to complete a task than I did in previous versions. Bleh. I think we should have been given the option to use either the ribbon or menus.

craftamics
craftamics

Just like XP allowed us to use the classic start menu of Win2K, MS should have allowed 2007 Office users the option of using the old style menus. I have wasted hours trying to do things that took me minutes in Office 2000.

dogknees
dogknees

I realise that you can do this, and I've now tracked down where it's hiding in the Ribbon. Look under Cells/Format/Move or Copy Sheet. Now can anyone explain why it would be there? In what way is moving and copying sheets a part of formatting a cell?

s31064
s31064

The Ribbon is the most obnoxious thing Microsoft has done to Office users since 4.0. My Quick Access Toolbar also stretches across the whole screen (at 1680 x 1050, just to give you an idea of the length). If they wanted to go with the Ribbon, it should have been editable like the old toolbars, so I could put what I want where I want. I can't believe they didn't get this feedback from any of the beta testers. (I'm on the beta tester lists and I don't remember being given the option to test Office 2007. Did it even go to a public beta group?)

boxfiddler
boxfiddler

Uncle Billy works in mysterious ways. :D

Editor's Picks