MS Office 101

Changing The View - Not The Selection

I'm sure just about everyone has used the arrow keys in MS Excel to move through their worksheet.

But... sometimes you want to quickly zip out to another part of the worksheet to see something without moving the cell selection and losing your current place. What do you do?

I'm betting that many of you take your hands off the keyboard, use the mouse to scroll out, take a look and then go back.

You'll be happy to know that there's a way to accomplish the "take a look" thing on the keyboard without losing your place.

The trick is all in the Scroll Lock key.

If you turn the Scroll Lock on then the arrows begin to behave differently in MS Excel. (My Scroll Lock key was located next to my Print Screen key, above the Insert, Home Page Up keys.)

You might try this and notice that, with Scroll Lock on, the arrows change what you're looking at but don't move the cell selection. I also found that the Page Up and Page Down buttons also let me move away from the selected cell.

In other words - Scroll Lock allowed me to "take a peek" without using the mouse or losing my place in the worksheet.

Yeah :-)

Office Tip of the Day

MS Excel AutoFilter Part 2—Top 10 and Custom Filters

As promised on Tuesday, we will finish our discussion of Excel's AutoFilter function. Specifically, we need to cover two items in the AutoFilter pull down lists: "Top 10" and "Custom".

So, let's get right to it.

To begin I think we should discuss Top 10.

This is pretty much what the name implies—it's a listing of the top values or percentage of the list.

Let's investigate further.

After you've opened a file and turned on the AutoFilter (Data menu, Filters submenu, AutoFilter choice) pull down one of the AutoFilter lists for a column.

Now select (Top 10...).

This window should open.

You see, this choice doesn't have to give just the top 10 items. You do have some choices here—ways to customize what and how many it's filtering for.

The first field allows you to choose to filter for either the "Top" or the "Bottom" of the list.

In the middle you can select how many items you want listed or the percentage of the list you're looking for Excel to display.

The third choice is what you want displayed. If you choose "Items" then you're flat out asking for a list of however many items from either the top or bottom. It doesn't matter how big or small the original list is, if you ask for the top 2, you get only 2 items in the filtered list.

The other option here is "Percent". If you choose percent then you're asking for the filter to display a percentage of the list (top or bottom values). The bigger the list or percentage number, the bigger the displayed filtered list will be. (For example, 10% of 100 items will be a list of 10 items - but 10% of 1000 items will be 100 items long. You get the idea.)

When you've made all your choices click OK and you'll be returned to see your filtered data.

If you change your mind you can go back to the Top 10 window and change the options—AutoFilter will update the displayed list to reflect your new choices.

That one is straightforward so I'm sure we're ready to move on to the (Custom...) option in the list.

When Custom is chosen from an AutoFilter list the following window will open.

This window allows for you to enter logical arguments to filter for ranges of values or two exact values.

The pull down lists on the left list the options for the arguments—such as equals, does not equal, is greater than, is less than, contains, does not contain, etc...

Then on the right you can either select an item from the pull down list or type in a value of your own.

Between both of these lines you have a choice of "And" and "Or".

Basically, you need to decide if the data you need must meet both requirements (the "And" choice) or just one of them (the "Or" choice).

For example, if I wanted to filter my lists for sales totals greater than $85,000 or less than $65,000 the window would look like this.

It's just a matter of knowing what you're looking for and then setting the correct ranges to get the job done.

Some of you may have noticed the information at the bottom of the Custom AutoFilter window regarding the symbols ? and *.

These are used as wildcards to get Excel to make approximate matches. For example, if I was looking for all customers with the last name beginning with a C, I would choose "Begins with" on the left and enter C* on the right. (The * simply lets the program know that any letters there will work—it's the C that it must match.)

No matter what you're trying to get with the Custom AutoFilter, when you're done setting the criteria click OK.

Again, you're returned to the worksheet with only the data meeting your requirements displayed.

No more long searches or complicated sorting—AutoFilter to the rescue!

~ April