Office poll: How important is Office certification?

A couple of years ago, we had a great discussion on Microsoft certification  in the Office area. Just this morning, someone asked me if I thought certification in Office was helpful. I’m wondering how you guys feel about it–three years later. Have any of you recently gone through the process? If you’re certified, do you believe it’s helped you get a better job? If you’re not certified in Office, are you considering it?

By the ways, the links in the 2007 post on Office certification are still valid.

Note: There is a poll embedded within this post, please visit the site to participate in this post’s poll.

Turn off Word’s Mini Toolbar!

Word 2007 and 2010 have an interesting feature called the Mini Toolbar. It’s one of those features you’ll either love or hate…or love to hate. Here’s what happens: When you select text, Word displays the Mini Toolbar—right over the text you just selected.

If you’re busy formatting, it can be convenient. I don’t do a lot of formatting. I’m a straight text kind of gal. I type, I edit, and then I format only when I have to. While I’m writing and editing, the Mini Toolbar just gets in my way. Now, there are those that say “Just ignore it,” but why should we have to?

If you don’t like the Mini Toolbar, you can turn it off as follows:

Office 2007	Office 2010
1. Click the Office button. 2. Click Word Options. 3. Click Popular in the left pane (the default). 4. In the Top Options For Working With Word section, uncheck the Show Mini Toolbar on Selection option. 5. Click OK.	1. Click the File tab. 2. Click Options under Help in the left pane. 3. Click General (the default). 4. In the User Interface Options section, uncheck the Show Mini Toolbar on Selection option. 5. Click OK.

It’s so easy that you can turn it off while writing and editing, and then turn it back on when you’re ready to format. If you do that a lot, consider adding a macro button that toggles the option to the Quick Access Toolbar.

Returning the first and last item in a subset in Excel

Knowing where a list begins and ends can be helpful. For instance, the simple sheet in the figure below tracks time. You might want to use the first and last dates in the list to mark the period beginning and ending dates. Since the dates are in sequential order, it’s easy to discern the first date in the list. The last date can take a little more effort if the list is long. You have to browse to the last entry—not such a big deal right?

But let’s complicate the situation a bit. Suppose you need to know the first and last elapsed time entry for each date (there’s your subset). That changes things quite a bit—is that complicated enough?

The first thing you need is a unique list of dates. To generate this list, use an Advanced Filter as follows:

1. Select the dates and header cell in column A—that’s A1:A8 for this example.
2. From the Data menu, choose Filter | Advanced Filter.
3. In the resulting Advanced Filter dialog box, click the Copy To Another Location option. Then, enter G1 in the Copy To control. Be sure to check the Unique Records Only option.

   

4. Click OK and Excel will copy a list of unique values to column G.

Now, you need a formula that returns the first item for each date. That formula is simple and you might be familiar with it. I recommend VLOOKUP(), in the following form:

VLOOKUP(lookupvalue,table,columnoffset,exactmatch)

In cell H2, enter the formula

=VLOOKUP(G2,A2:D8,4,FALSE)

In this case, lookupvalue is G2—the value you want to match in table. When the function finds a match, it returns the corresponding value in table’s fourth column. The last argument is FALSE, which forces the function to return only an exact match. Copy the formula to cells H3:H5.

Unfortunately, this function isn’t flexible enough to also return the last item. In fact, none of the lookup and reference functions can do so (not that I know of).

To find the last item in a subset—the last Elapsed Time value for a specific date—combine HLOOKUP() and MATCH(). The truth is, over the years, I’ve seen many convoluted formulas for this task. Combining these two functions in the following form is the most efficient solution, that I’ve come up with:

HLOOKUP(hlookupvalue,htable,MATCH(lookupvalue,table)+1)

Enter the following formula in cell I2 and copy it to cells I3:I5:

=HLOOKUP("Elapsed Time",$D$1:$D$8,MATCH(G2,$A$2:$A$8)+1)

The MATCH() function returns the relative position of the value that matches the lookup value. For example, when matching the value in G2, 6/2/2010, this function returns 3—the relative position within table of the last matching value. The HLOOKUP() function uses the result of the MATCH() function to specify the row in htable, from which it grabs its value, as follows:

HLOOKUP("Elapsed Time",$D$1:$D$8,MATCH(G2,$A$2:$A$8)+1)

HLOOKUP("Elapsed Time",$D$1:$D$8,3+1)

HLOOKUP("Elapsed Time",$D$1:$D$8,4)

As a result, the HLOOKUP() function returns the value in the fourth row of htable (D1:D8)–the last entry for the lookup date of 6/2/2010.

If you have a more efficient method for finding the last item in a subset, please share it!

Office challenge: What’s the quickest way to select a date that’s not currently showing in Outlook’s Date Navigator?

While we’re discussing Outlook, how would you suggest users select a date that’s not currently showing? There’s more than one way of course, but think of the quickest route possible.

Last week we asked…

How can you select a custom Calendar view in Outlook? Jbenton answered correctly: In Calendar view, drag over the desired dates in the Date Navigator. The Calendar view will update accordingly. This is a handy feature for reviewing a specific period, whether it spans just a few days or a few months.

The Date Navigator is the small calendar, in the top-left or right corner (probably). If you can’t find it, someone has probably turned off the Navigation Pane. In Outlook 2007 and 2010, it’s a simple matter to turn it back on. In Calendar view, click the View tab. In the Layout group, click Navigation Pane | Off (if Off is checked). In Outlook 2003, choose Navigation Pane from the View menu.

As usual, there are a few more tricks to share. First, to select more than one day, simply drag the mouse over the days in question, as suggested by Jbenton. This method is the most flexible as it allows you to select up to the last few days of the previous month, the entire current month, and the first few days of the next month. In other words, you can select every date showing on the Date Navigator.

This next trick lets you select a period in another month—before you get to that month. Select the dates in the current month. Then, click the appropriate arrow (circled in the figure above) to display the next or previous month. The Date Navigator will automatically select the same dates that you selected before moving to the next month. This particular shortcut is helpful if you want to view the same period in several months because ou select the days only once.

To display non-consecutive days, click a day, hold down the [Ctrl] key and click away. As long as you hold down the [Ctrl] key while clicking, Outlook adds each day to the Calendar view.

You don’t have to use the Date Navigator. Hold down the [Alt] key and press any key between 1 and 9, and Outlook will display that number of days from the current day in Calendar view. (I’ve never discovered a way to select previous days.)

Please share your favorite shortcuts for displaying a custom period in Calendar view.

How to use Excel 2010′s new conditional formatting with references

Excel 2010’s conditional formatting feature lets you reference different sheets—something you couldn’t do before. In earlier versions you had to copy or link data to the same sheet. Now, you just include the reference to another sheet as you would any other reference!

To illustrate this new feature, we’ll use the simple products sheet shown below. (I based this example on a subset of the Products sheet in the Northwind database, but changed it considerably for this example.) There are two regions, Northwest (shown below) and Southwest. Each region has its own sheet for tracking product sales.

Now, let’s suppose you want to see where the Northwest region is outselling the Southwest. Using Excel 2010’s new referencing option, it’s easy to reference another sheet, as you’ll see:

1. Select the sales values in Northwest, that’s D2:11.
2. Click the Home tab.
3. In the Styles group, click Conditional Formatting | Highlight Cells Rules | Greater Than. The resulting dialog box will display a default format.
   
4. Click the RefEdit icon (circled in the above picture).
5. Click the Southwest tab.
6. Click cell D2 (in Southwest).
7. Alter the absolute cell reference $D$2 to $D2—that way the row number can adjust to accommodate the entire column in Northwest. In other words, every product in Northwest will evaluate the same product in Southwest.
8. Click the RefEdit icon.
9. Choose an appropriate format, such as Green Fill With Dark Green Text.
10. Click OK. According to the conditional formatting, the Northwest region is outperforming the Southwest region in four products.

This new referencing option doesn’t work with grouped sheets, which makes sense. If you want to see where the Southwest is outperforming the Northwest, just repeat the same process, but start by selecting the sales values on the Southwest tab. Then, in step 5, click the Northwest tab instead of the Southwest tab.

Both Excel 2003 and 2007 will let you go through the motions of selecting a cell or range on another sheet, but when you try to commit the reference, Excel displays an error.

This new conditional formatting option is easy to implement and one you’ll probably find many uses for. Try it out and let us know how you like it!

A Special Offer From Our Sponsor

A new use for the mouse wheel in Office 2007 and 2010

I don’t use my mouse wheel much. I zoom in on specific areas of the screen (hold down the [Ctrl] key) or move a document up and down, but that’s the extent of my mouse wheel usage. If you’re using Office 2007 or Office 2010, there’s a new mouse wheel trick—you can move through the tabs:

• Roll the wheel back to browse to the right.
• Roll the wheel forward to browse to the left.

Hover the mouse over the ribbon or tabs. Then, roll slowly to get the best effect.

At first, I thought, Big deal… but it only takes a few times to become accustom to it—this new behavior grows on you. It’s an almost no-effort way to take a quick peek at the options on each tab. I use it when I’m looking for something. It’s seems more efficient than clicking each tab.

Neither the Office button in 2007 nor the File tab in 2010 is in the cycle. You must click those to access those features and options.

Use Word’s hidden ruler to reveal more page information

You’re probably familiar with Microsoft Word’s rulers. The one along the top of the screen measures the width of the page and tags items that fall between the left and right margins.

Similarly, the one to the left, measures the length of the page. There’s another view that tells you a bit more:

• The distance between the edge of the paper and the margin.
• The distance between the selected tab and the left and right margins.

Accessing this alternate view is easy:

1. Using the left mouse button, click a margin or tab on the ruler, but don’t release the mouse button.
2. While holding down the left mouse button, press the right mouse button.

Using the above figure, the alternate view tells you the following:

• The left and right margins are both 1.25″.
• There is 0.56″ between the left margin and the selected tab.
• There is 5.44″ between the selected tab and the right margin.

Once you know this information is easily accessible, you’ll probably find many uses for this alternative ruler view.

TechRepublic’s Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!

Word’s hidden ruler

You’re probably familiar with Word’s rulers. The one along the top of the screen measures the width of the page and tags items that fall between the left and right margins.

Similarly, the one to the left, measures the length of the page. There’s another view that tells you a bit more:

• The distance between the edge of the paper and the margin.
• The distance between the selected tab and the left and right margins.

Accessing this alternate view is easy:

1. Using the left mouse button, click a margin or tab on the ruler, but don’t release the mouse button.
2. While holding down the left mouse button, press the right mouse button.

Using the above figure, the alternate view tells you the following:

• The left and right margins are both 1.25″.
• There is 0.56″ between the left margin and the selected tab.
• There is 5.44″ between the selected tab and the right margin.

Once you know this information is easily accessible, you’ll probably find many uses for this alternative ruler view.

Office challenge: How can you select a custom Calendar view in Outlook?

Outlook has several Calendar views: Day, Work Week, Week, and Month. They work well, but they’re not always adequate. What if you want a quick look at the next six weeks? How would you create a custom view?

Last week we asked…

What’s the quickest way to access Excel’s Visible Cells Only option? You can add this command to a toolbar in Excel 2003 or the Quick Access Toolbar (QAT) in Excel 2007 and 2010. In Excel 2010, you can add it to a custom tab.

To add this command to a toolbar in Excel 2003, do the following;

1. Right-click the background of any toolbar and choose Customize from the resulting context menu or choose Customize from the Tools menu.
2. Click the Commands tab.
3. Choose Edit from the Category list.
4. At the bottom of the list, you’ll find Select Visible Cells.
5. Drag and drop Select Visible Cells to a toolbar.
6. Click Close.

If you’re using Excel 2007 or 2010, add this tool to the QAT, as follows:

1. From the QAT’s dropdown, choose More Commands.
2. In the Choose Commands From dropdown, select All Commands or Commands Not In The Ribbon.
3. Choose Select Visible Cells from the resulting list and click Add.
4. Click OK.

With the Select Visible Cells tool on a toolbar, you can select a range that contains hidden data and click the tool, completely bypassing the Go To dialog box. It’s ever faster than pressing [Alt]+;–although that’s a great shortcut if you prefer the keyboard. It’s a small change, but users who need it will appreciate your efforts to make things a bit more efficient for them.

Jbenton was the first to suggest adding the Select Visible Cells tool to a toolbar. Thanks Jbenton!

I hadn’t expected the strong support for keyboard shortcuts. I love them as well, but in a support environment, flexibility is the key. Being able to provide the tools that make your users efficient is important, and those tools won’t always be the tools you prefer for yourself. I wish I had a nickel for every time I’ve heard “No, I want to do it this way” to the question, “Wouldn’t you rather…?”

Thanks for a good challenge everyone and the great discussion on keyboard versus mouse—carry on!

Next Page »