Hidden gems in Excel 2007, part 1: The sheets that never end

(This article first appeared as an Office Hours column)

Sometimes love at first site lasts for years. Our friend (and Group Manager for Office Developer Documentation), Jean Philippe Bagel (affectionately known as J.P.), has been known to wax lyrical on a variety of subjects, ranging from his children to his homeland, La France. But when it comes to Excel, well, that’s when he’s most excitable.

This week and for the following two Thursday mornings when I post, I’ll be offering you one of J.P.’s favorite Excel 2007 tips. Today it’s all about the endless row of worksheets.

So many worksheets, so little space

“Have you ever had an Excel workbook with multiple sheets, those sheets having names other than the proverbial Sheet1, Sheet2, and Sheet3? My wife's business has many great examples of this, where business data is being kept in various workbooks, where tabs are corresponding to months, from January to December:

Rather quickly, you get to the point where you no longer can see all the tabs in one view. Of course, you can use the built-in tabs navigation buttons, and go the next, previous, first, or last sheet, but wouldn't it be great if there was a way to see all the sheets and be able to click on the one you want to work on? Well, there is one, and it has been there since Excel 4 (I think).

Simply right-click the tabs navigation buttons and a floating list of all the worksheets in the workbook will appear, as pictured below. It is especially useful when sheet names are long. I use this time and time again, and find it the best way to navigate complex spreadsheets. Now seems just the perfect time to also remind you of two great keyboard shortcuts: CTRL+PageUp activates the previous sheet in your workbook, while CTRL+PageDown activates the next one.”

Next week: Gentle and automatic list conditioning. (I wonder what that sounds like in French…?)

— Annik