Excel Scroll Wheel Shortcuts

I started on a Mac, where the mouse had one big button. Later, I moved to a PC, with a two button mouse. For the past few years I’ve used a mouse with two buttons and a scroll wheel.
At first, I completely ignored the scroll wheel, and found it faster to drag the scroll bar, than to move the scroll wheel. Now I use the wheel occasionally, but more often in Firefox or Word, than in Excel.

Use the Scroll Wheel With Outlines

AlexJ, who shared his technique to hide Excel rows with outlining a few days ago, also uses the scroll wheel to show and hide outline levels.

  1. Point to a cell within the outlined range
  2. Press the Shift key, then roll the scroll wheel down, to hide the outline levels for that cell.
  3. Or, press the Shift key, then roll the scroll wheel up, to show the outline levels for that cell.

Use the Scroll Wheel to Zoom

He also reminded me that you can use the scroll wheel to zoom in or out on the worksheet.

  1. Point to a cell within the outlined range
  2. Press the Ctrl key, then roll the scroll wheel down, to hide the outline levels for that cell.
  3. Or, press the Shift key, then roll the scroll wheel up, to show the outline levels for that cell.

Use the Scroll Wheel to Scroll

And yes, you can even use the scroll wheel to scroll. Really fast!

  1. Click the scroll wheel, and a four-headed arrow appears
  2. Move the mouse up, down, left or right, and a single arrow appears, pointing in the direction that the mouse is moving.
  3. The Excel sheet scrolls very quickly, increasing in speed as you move the single arrow toward the edge of the worksheet.
  4. To stop the scrolling, tap the scroll wheel, or click a mouse button.

Other Shortcuts?

Do you use the scroll wheel? Do you have any other shortcuts for it?
scrollwheel
_________________________

Paste Values Shortcut For Excel

After I create formulas in Excel, sometimes I want to lock in the results of those formulas, so I use the Paste Values command. For example, I might use the RAND function to create a set of random numbers. After creating them, I don’t want them to change every time the sheet calculates.
Here’s how I’d use the Ribbon commands to replace formulas with values.

  1. Select the cells that contain the formulas
  2. On the Ribbon’s Home tab, click the Copy command
  3. On the Ribbon’s Home tab, click the lower half of the Paste command, to open the list of options
  4. Click Paste Values

PasteValuesRibbon
The steps are similar in earlier versions of Excel, but the commands are on the Standard toolbar, and the arrow is to the right of the Paste button.
PasteValues2003

Use a Shortcut Menu

An even quicker way to paste values is by using a mouse shortcut:

  1. Point to the border of the selected range, and the pointer should change to a four-headed arrow.
  2. Press the right mouse button, and drag the cells slightly to the right.
    PasteValuesMouse01
  3. Keep pressing the right mouse button, and drag the cells back to their original location.
  4. Release the right mouse button and a shortcut menu will appear.
  5. Click on Copy Here as Values Only.

PasteValuesMouse02
The formulas are replaced by the values of their results.

See the Steps In a Video

This technique is a bit tough to explain, so if the written explanation didn’t make sense, here’s a very short video.

____________________

Create a Word Keyboard Shortcuts List

If you like to use keyboard shortcuts, you might have some of them memorized, in Word and Excel, or other programs. If your memory isn’t too good, you can print a list of shortcuts and keep it near your computer as a reference.

A few months ago, I mentioned some sites where you can download lists of Excel keyboard shortcuts. It’s even easier to get a list of Word keyboard shortcuts. There’s a built in macro that you can run, to create the list.

Create a List of Word Keyboard Shortcuts

  1. In Word 2003, click the Tools menu, click Macro, then click Macros.
  2. In the Macros dialog box, click the dropdown arrow for ‘Macros in’ and select Word commands
  3. In the list of macros, click on ListCommands, then click Run.
    WordListCmds
  4. In the List Commands dialog box, click on Current menu and keyboard settings, then click OK

A new document will be created, with a table that lists all the keyboard shortcuts and menu commands.

WordCmdList

______________________

Go To Special Sections of an Excel Worksheet

Excel reports often have blank rows that separate the sections. In this example, I needed a new formula to calculate the average price per order. After entering the formula in cell E2, I copied it down to the last row in the report. That was quicker than pasting the formula into each little section in a long report.
Now all the blank rows have an error in the new column, because Excel doesn’t like to divide by zero.
ErrorsBlankRows
You don’t want the formula in those rows, so you can use Excel’s Go To feature to quickly clear the cells that contain errors.

Use Go To Special

  1. Select column E, where the average order formula was added.
  2. On the Ribbon, click the Home tab.
  3. Click Find & Select, then click Go To Special.
  4. (In Excel 2003 and earlier versions, click Edit►Go To, and click the Special button. The keyboard shortcut for Go To is F5.)
    GoToSpecial
  5. In the Go To Special dialog box, select Formulas. You want to go to the cells that contain a formula, where the formula results in an error.
  6. Under Formulas, remove all the check marks except for Errors, then click OK.GoToErrors
  7. On the worksheet, only the error cells are now selected.
    GoToErrorsSel
  8. To clear them, press the Delete key on the keyboard.

Other Uses for Go To Special

As you can see in the Go To Special dialog box, there are many types of special cells you can select on a worksheet. I often use it to find blank cells in a column, then I delete those entire rows, or fill in the blank cells.

________________________________

Hidden Word Shortcuts

In Microsoft Word, recording a macro is just a double-click away.
Normally, to record a macro in Word, you’d click on the Tools menu, then click Macro, then click Record New Macro.
A much quicker way is to double-click on the REC box in the status bar, at the bottom of the Word window.
WordREC
The Record Macro dialog box immediately opens, and you can begin recording.
Double-click the REC box again, to turn off the recorder.

Other Shortcuts

There are other double-click shortcuts in the status bar:
TRK: Toggle the Track Changes feature
EXT: Toggle the Extend Selection feature
OVR: Toggle the Overtype feature (does anyone use that?)
Page Number (or any area in the left end of the Status Bar): Open the Go To dialog box

More Google In One Fell Swoop

When you’re searching in Google, by default it shows you 10 results per page. Sometimes you’re lucky and the item you’re looking for is right there on the first page. Other times you’re not so lucky, and have to look through a few pages before you find your answer.
To cut down on the page views, you can change your Google preferences and increase the number of results per page. I find that 20-30 per page works well for me. Then, use the space bar on the keyboard to quickly scan down the page.

Change the Number of Results

1. On the Google search page, click the Preferences link.

2. On the Preferences page, scroll down to the Number of Results section

3. Select a higher number from the drop down list.

4. At the bottom right or top right of the Preferences page, click Save Preferences. That will automatically return you to the previous Google Search page.

Insert or Delete Cells with AutoFill

Here are a couple of quick tips for you — try them a few times today, and maybe you’ll still remember them by Monday.
I often have to insert a few cells in a list, so I select a range of cells, then choose Insert►Cells, click Shift cells down, then click OK. That method works very nicely, but it’s four clicks. I don’t have time for four clicks!
Recently I learned that I can press the Shift key and drag the AutoFill handle to insert cells. You can drag up, down, left or right, to insert cells in any direction. It’s much quicker than all those clicks!

You can use the same technique to delete cells too. Hold the Shift key and drag the Fill Handle over the selected cells, to grey out the cells.

Quick Navigation in a Word Document

Occasionally I work with long Word documents, and want to get from one section to another as quickly as possible. To do this, I use one of Word’s best hidden features — the Document Map. It lists all the headings in your document, and to go to a section you just click on its name in the Document Map.

Just like an Excel outline, you can collapse and expand sections by clicking the + and – buttons at the left of the section names.
To view the Document Map in Word 2007, click on the View tab, and add a check mark to Document Map.

In Word 2003, and earlier versions, click the Document Map button on the Standard Toolbar.

If you like to use the keyboard, you can press the F6 key to move into the Document Map, and arrow up or down. Press Enter to select the highlighted section name.
Note: If you have the Document Map open, you can’t use Word’s Split Window feature.

Excel Keyboard Shortcuts

“Microsoft Excel has no keyboard shortcut for “save as”. Probably because they are *only* on version 11.”
That was one of the Twitters that I posted on Saturday. However, as Jason Morin pointed out in the comments, you can use the F12 key to open the Save As dialog box.
Why didn’t the twitterer know that? Well, when you go to the Keyboard Shortcuts topic in Excel’s Help, there’s an incomplete list. It doesn’t even show Ctl + S as a shortcut to Save, let alone tell us how to do a Save As.
To find a full list of Excel Keyboard shortcuts, you can download one of the following files or view the list on the web page.

Chip Pearson’s site

View: Excel Keyboard Shortcuts list
Download: Excel Keyboard Shortcuts Excel file

Contextures site

View: Excel Keyboard Shortcuts list
Download: Excel Keyboard Shortcuts Excel file

Microsoft Excel Team Blog

Download: Excel Keyboard Shortcuts Excel file
In a blog post from Sept 2007, there’s also a discussion of what keyboard shortcuts you’d like to see.

Controlly Tricks in Excel

Last week I listed a few tricks with the Shift key in Excel, and in the comments, Jon Peltier mentioned a trick he’d discovered with the Ctrl key. Here are a few more things you can do with the Ctrl key in Excel.
There are many other Ctrl tricks, so if you have favourites, please share them in the comments.

Select Nonadjacent Ranges

This is my favourite use of the Ctrl key — to select nonadjacent ranges. For example:

  1. Click on cell A1 to select it
  2. Press the Ctrl key, and click on cell A5, then click on cell B3
  3. Release the Ctrl key, and all three cells are selected, with B3 as the active cell.

Enter Data in Several Cells

After you’ve selected several cells, adjacent or nonadjacent, you can enter the same data or formula in all of them

  1. Select the cells, and type the data or a formula.
  2. Press the Ctrl key, then tap the Enter key, to enter the data or formula in all the selected cells

Show the Active Cell

Sometimes I scroll to the bottom or far side of a worksheet, to view something, then want to get back to the active cell quickly.

  • To show the active cell, press Ctrl + Backspace

Select Precendent Cells

If you’re analyzing a formula, you might want to go to the precedent cells — those cells that are used in the current cell’s formula.

  • Select a cell with a formula that refers to other cells
  • Press Ctrl + [

The first cell referenced in the formula will be the active cell, and other referenced cells on the same sheet will be selected.