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.
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.
Select the cells that contain the formulas
On the Ribbon’s Home tab, click the Copy command
On the Ribbon’s Home tab, click the lower half of the Paste command, to open the list of options
Click Paste Values
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.
Use a Shortcut Menu
An even quicker way to paste values is by using a mouse shortcut:
Point to the border of the selected range, and the pointer should change to a four-headed arrow.
Press the right mouse button, and drag the cells slightly to the right.
Keep pressing the right mouse button, and drag the cells back to their original location.
Release the right mouse button and a shortcut menu will appear.
Click on Copy Here as Values Only.
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.
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
In Word 2003, click the Tools menu, click Macro, then click Macros.
In the Macros dialog box, click the dropdown arrow for ‘Macros in’ and select Word commands
In the list of macros, click on ListCommands, then click Run.
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.
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.
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
Select column E, where the average order formula was added.
On the Ribbon, click the Home tab.
Click Find & Select, then click Go To Special.
(In Excel 2003 and earlier versions, click Edit►Go To, and click the Special button. The keyboard shortcut for Go To is F5.)
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.
Under Formulas, remove all the check marks except for Errors, then click OK.
On the worksheet, only the error cells are now selected.
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.
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.
The Record Macro dialog box immediately opens, and you can begin recording.
Double-click the REC box again, to turn off the recorder.
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
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.
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.
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.
“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.
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:
Click on cell A1 to select it
Press the Ctrl key, and click on cell A5, then click on cell B3
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
Select the cells, and type the data or a formula.
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.