Category Archives: Microsoft Excel

Using the “unique” filter in Excel via keyboard to remove duplicate items from a list

Suppose someone gives you long list of names, and your job is to find only the unique ones. (For example, if “John Smith” is listed twice, suppose you want to create a new list where John Smith is only listed once.)

This is a job for Excel’s advanced filters, and you don’t need a mouse. Follow these steps.

  1. In Excel, create a new worksheet. (Ctrl+N can help on that last part.)
  2. If your data doesn’t have a heading row, put a header such as “Names” in cell A1. (Type in the header and press Enter.)
  3. Right below the header, paste your data into Excel. (Remember you can use Ctrl+V to paste.)
  4. With your cursor somewhere within the list, press Alt+D to open the Data menu, then press F to select the Filter command, then press A to choose the Advanced Filter command. The Advanced Filter dialog box appears.
  5. Press Alt+O to select the second radio button option, “Copy to another location.”
  6. Press Alt+T to select the “Copy to” entry, and type in the cell location you want, such as “b1” for cell B1.
  7. Press Alt+R to select the checkbox option for “Unique records only.”
  8. That’s it! Press Enter to select OK.

[Screenshot of Excel showing advanced filter options to filter for unique items only

A new list appears in cell B1 (or whatever cell you selected in step 6), in the same order as the original list — except with any duplicate entries completely removed.

[Screenshot of Excel showing the results of the advanced filter options to filter for unique items only

Ctrl+F1: Switch off the task pane in Office

When you start up Excel or other Office programs, they often stick a “Task Pane” up on the right, usually with the “Getting Started” heading. Annoying, isn’t it?

Switch it off one time with Ctrl+F1.

Switch it off permanently by following these steps (which you have to repeat for Excel, Word, PowerPoint, etc.): Hit Alt+T to open the Tools menu, O to select the Options command, then deselect the “Startup Task Pane” checkbox using Alt plus whatever is the underlined letter (which is different in different programs — nice consistency there, Microsoft), then press Enter for OK.

Ctrl+Shift+Alt+F9: Calculate all formulas and dependent formulas

To finish the options for F9, let’s suppose you really need Excel to double-check EVERY formula to make sure the numbers are right (again, probably only useful if you’ve opened a bunch of workbooks that are huge, manually calculated, and may have been corrupted). Press Ctrl+Shift+Alt+F9 to do this:

Rechecks dependent formulas, and then calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.

I’ve done this before with a spreadsheet that had six dependent files, 60,000 rows and over 100 columns of complex formulas — it wasn’t done until after I came back from lunch.

Ctrl+Alt+F9: Calculate all formulas

To continue the theme from yesterday and Monday, there’s another recalculate option — mostly useful if you have a spreadsheet that seems to have been corrupted and you want to check all the formulas. Press Ctrl+Alt+F9 to do the following:

Calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.

Shift+F9: Calculate in active worksheet

Yesterday we learned about the F9 key and how it calculates formulas manually if you’ve switched off automatic calculation (which you’d only do if you’re working with a big spreadsheet that has lots of formulas).

The technical definition of what F9 does, per Excel, is:

Calculates formulas that have changed since the last calculation, and formulas dependent on them, in all open workbooks.

Sometimes you don’t want to recalculate in all open workbooks. Perhaps, instead, you only want to recalculate the formulas in the sheet that you’re currently working with (because you don’t want to wait for all the other worksheets to get recalculated as well). If so, then press Shift+F9.

Here’s the technical definition of Shift+F9’s functionality:

Calculates formulas that have changed since the last calculation, and formulas dependent on them, in the active worksheet.

F9: Calculate in Excel

In Excel, if you have a big spreadsheet, it can start to get slow. Really slow. At that point, you can switch off the automatic calculation of formulas.

To do so, use the Tools | Options command, switch to the Calculate tab, then select Manual. (The keyboard method to navigate there is Alt+T for the Tools menu, O for the Options command, Ctrl+Tab to switch tabs to the Calculate tab, then Alt+M to select the Manual radio button. Then press Enter to close the dialog box.)

From now on, any time you change a number or formula, the spreadsheet won’t update itself until you press F9.

Alt+4, Alt+5, Alt+6, Alt+7: Other buttons in the file dialog box

By now you may have figured out the pattern: When using Word, Excel or PowerPoint, and at the moment when you have a file dialog box open, you can use the Alt key plus a number to select the buttons along the top right.

We’ve already seen three:

  • Alt+1: Back (a folder)
  • Alt+2: Up one level (of folders)
  • Alt+3: Search the Web

But there are several more:

  • Alt+4: Delete (if you have a file or folder selected)
  • Alt+5: Create New Folder
  • Alt+6: Views
  • Alt+7: Tools (pulls down the Tools menu)

Because the buttons are in the same order, 1 through 7, it actually becomes fairly easy to remember which button to press.

Several of these, such as Alt+5, are actually useful. Whether or not you use these shortcuts depends on how often you use the buttons. Practice!

Remember, the easiest way to see this dialog box is to press F12 when using Word, Excel or PowerPoint to get the Save As dialog box, or to press Ctrl+O for the Open dialog box.

Alt+3: Search the web instead of using the file dialog (Office)

I have to admit I never use this one.

But, just suppose: You’re in Word or Excel or PowerPoint. You’ve opened up a file dialog box. It could be an Open file dialog box (by pressing Ctrl+O or using the File | Open menu, or by pressing Ctrl+F12). Or it could be a Save As file dialog box (by pressing F12, or selecting File | Save As).

And then you change your mind, and want to search the web instead. Instead of hitting Esc to cancel the file dialog box, and then pressing Ctrl+Esc to get the Start menu and then running your web browser, and then opening up a search engine, you can do all of that with one button. You could just use your mouse to hit this button, the “Search the Web” button:

[Screenshot of Save As dialog box with Search the Web button circled]

Or, you could press Alt+3. When you do so, instantly the dialog box closes (with no action taken), and your web browser comes up, with a search engine displayed.

(Note to readers: If you know how to change the search engine used here from MSN Live to something else, please let me know. The standard methods don’t seem to work.)

It’s a pity this keyboard shortcut only works in Office apps and not all apps that use a file dialog box.

Alt+1: Go back a Folder when in file dialog box (Office)

Similarly to yesterday’s tip, suppose you’ve opened the Save As dialog box and have looked at one or more different folders or drives, and you want to go back to the one you were just looking at. You could click on that little green “Back” button up in the top center, next to the “Save in” or “Look in” drop-down list. But instead of clicking a button, you can just type Alt+1.

Each time you press Alt+1 you’ll go back another folder until you return to the one you started out looking at.

Alt+2: Go up one Folder when in file dialog box (Office)

Yesterday, I mentioned you can press the Backspace key to move up a folder when you’re working with files.

For Microsoft Office apps (Word, Excel, PowerPoint, etc.), you can do the same thing with Alt+2.

Try it!

Suppose, for example, you’re viewing top-secret plans in Word, and want to save a copy of them on your Desktop. You’d do the following:

  1. Hit F12 to get the Save As dialog box.
  2. Alt+2 several times, until the Desktop is displayed. (Normally you’d instead hit Shift+Tab, then Backspace several times.)
  3. Type the file name you want and press Enter.

F12: Save As (Word, Excel, PowerPoint)

When working with Excel, Word, or PowerPoint, you can save the current document with a new name, or in a different directory, or on a floppy disk (remember those?), by tapping the F12 key. The “Save As” dialog box appears. Then you can enter your filename and press Enter.

Not the right folder? Remember, you can move up a folder with the Backspace key, once you’ve moved focus to the file list pane — so press Shift+Tab then Backspace a few times to get to the Desktop quickly. We’ll have more tips on what you can do from this dialog box for the rest of the week.

Ctrl+Space Bar: Select Column in Excel; Shift+Space Bar: Select Row in Excel

In Excel, hit Ctrl plus the Space Bar to select the entire column of your active or selected cells. (Then you can delete all the cell contents with the delete key, or copy with Ctrl+C or whatever you like.)

Similarly, Shift plus the Space Bar selects the entire row of cells.

For example, suppose you want to move a column. Follow these steps:

  1. Move your cursor to some cell in the column you want to move. (Remember you can press F5 to instantly go anywhere.)
  2. Press Ctrl+Space Bar. The entire column is selected.
  3. Press Ctrl+X to Cut the column.
  4. Move your cursor to the location right where you want the column inserted.
  5. Press Shift+F10 to display the shortcut menu.
  6. Using the arrow keys and Enter, select the Insert Cut Cells command.

Ctrl+Backspace: Display active cell in Excel

Suppose you’ve used the mouse to scroll too far in Excel. You’re lost. The cells are zooming by at a million miles an hour. You just want to get back to see the cell where your cursor is. Help! Scrolling around to find the active cell will take forever!
Ctrl+Backspace moves the visible area of the spreadsheet back to whereever your cursor is (the so-called active cell).

Alt+Page Down, Alt+Page Up: Move left and right one screen in Excel

To move to the right in Excel to see your next group of columns, you could scroll. Or you could press the Right Arrow key a bunch of times.

Or, press Alt+Page Up. You immediately find yourself one screen to the right looking at your next batch of columns.

Conversely, Alt+Page Down moves you to the left.

(Compare to the regular Page Up and Page Down, which you already know moves you up and down a screen.)

Alt+Shift+F10: Open “Smart Tag” menu in Word, Excel & PowerPoint

When you’re using Excel 2003 or later, from time to time it’ll point out mistakes in your formulas or other observations by indicating a green triangle in the upper left of the cell, along with an exclamation point in a yellow diamond. The idea is you click on the diamond to see a menu with some options from Excel.

Clicking? Moi? Nope, instead of reaching for the mouse, just press Alt+Shift+F10, and the menu then opens. (Just have your cursor somewhere in the cell with the Smart Tag.) Once the menu appears, it’s just a regular menu, and you can use the Up Arrow or Down Arrow plus Enter to select an item, or press Esc to cancel.

Similarly, in Microsoft Word, sometimes you’ll be typing and Word will make a correction and show a blue double underline. Move your mouse to the underline and a yellow lightning bolt appears. Click on the lightning bolt and you’ll have some menu items to control the behavior of whatever automatic correction Word made.

Again, clicking? No need. The same Alt+Shift+F10 will open the Smart Tag menu in Word. Just have your cursor somewhere in the word with the double blue underline.

Ctrl+D, Ctrl+R: Fill Down, Right in Excel

Everyone knows you can copy and paste cells in Excel, but a lot of times you want to do it quickly in a specific way. Turns out there are two simple keyboard shortcuts that let you copy cells quickly.

Move your cursor to a cell that you want to copy. Suppose you want to copy it down five times. Hit Shift+Down Arrow four times to select the four cells below your current one. Then press Ctrl+D. Whatever is in the first cell is copied to fill the remaining cells. (Whatever was in those cells is overwitten.)

You can do the same thing to the right with Shift+Right Arrow and Ctrl+R.

Ctrl+$: Apply Currency Formatting in Excel

In Excel, type a number into a cell (such as “54.1”) and press Enter. Then press Ctrl+Shift+4 (or another way to think of that is Ctrl+$). Instantly the currency format is applied to the selected cells, and your “54.1” becomes “$54.10”.

You have quite a few other options as well:

  • Ctrl+~ (Ctrl+Shift+`): General format
  • Ctrl+! (Ctrl+Shift+1): Number format (with two decimal places and the thousands separator)
  • Ctrl+@ (Ctrl+Shift+2): Time format
  • Ctrl+# (Ctrl+Shift+3): Date format
  • Ctrl+$ (Ctrl+Shift+4): Currency format
  • Ctrl+% (Ctrl+Shift+5): Percentage format
  • Ctrl+^ (Ctrl+Shift+6): Exponential format

Ctrl+0: Hide Columns in Excel (Ctrl+Shift+0 to Unhide Columns)

Similar to yesterday’s tip, you can press Ctrl+0 to hide the column where the cursor is located. Ctrl+A, Ctrl+Shift+0 unhides all the columns in the sheet.

Update on January 5, 2014:

This keyboard sequence is used for changing your language by Windows, preventing it from working in Excel. If you’re running an older version of Windows, see Guilherme’s comment below.

In Windows 7, try this to get this shortcut to work in Excel:

1. Run the Control Panel, and select Region and Language.
2. Select the “Advanced Key Settings” tab.
3. Select the “Between input languages” item, then press Alt+C to select the Change Key Sequence item.
4. Under “Switch Keyboard Layout” make sure that “Not Assigned” is selected, then select OK and OK.

That will fix ALL keyboard shortcut items that involve Ctrl+Shift.

Ctrl+9: Hide Rows in Excel (Ctrl+Shift+9 to Unhide)

We’ll hit some more Excel tips this week.

In Excel, move somewhere in a row you want to hide and press Ctrl+9. The entire current row is hidden (and you don’t need to select the entire row, just have your cursor somewhere in the row).

Remember you can use Shift+Up Arrow or Shift+Down Arrow to select while moving; this way, you can select cells on several rows. Once you have cells from more than one row selected, all of those rows are hidden when you press Ctrl+9.

Next, try this: Press Ctrl+A to select the worksheet, then press Ctrl+Shift+9 — also known as Ctrl+( — to unhide all of the rows that are hidden.

F7: Spell Check (Microsoft Office)

In most Microsoft Office apps (including Word, Excel, PowerPoint and Outlook), tap F7 to begin the spellcheck process.

In the spellecheck dialog box, don’t forget to use Alt plus an underlined letter to hit the button! Using Alt+A to Add a correct word (such as your last name) to your custom dictionary is smart because it saves time in two ways — now, by saving you from having to use your mouse to hit the button, and later, by preventing this correctly spelled word from ever wasting your time again.

Ctrl+Shift+P: Select Point Size of Font (in Microsoft Office apps)

Similar to yesterday’s tip, Ctrl+Shift+P will jump your cursor in the font size (also known as “point size,” thus the letter P for the keyboard shortcut) box on the Formatting toolbar in Microsoft Word, Excel, or PowerPoint.

Once there, type in a number such as 18 and press Enter. Whatever text is selected will change to the new size; if no text is selected, whatever you type next will be at the new size.

Ctrl+Shift+F: Select Font (in Microsoft Office apps)

Using Excel, Word, or PowerPoint? Have the Formatting toolbar visible? Good, don’t spend another second moving your mouse up to that font selection area. Instead, press Ctrl+Shift+F, and all of a sudden your cursor is in the Font selection button on the Formatting toolbar. From there you can type in the first few letters of a font name (such as ver to get “Verdana”) and press Enter, or you can press the Up Arrow and Down Arrow keys to select the fonts one at a time. Press Alt+Down Arrow to see the pulldown menu.

Does this work with Microsoft Outlook? Alas, no. That would imply a consistency of design that, sadly, does not exist.

F8 and Shift+F8: Extend Selection in Excel

You may know that when you’re selecitng cells in Excel with the mouse, pressing the Ctrl button as you click adds the selected cells to the selection.

But, there’s an easier way. First, let’s try “Extend Mode” with F8. If you press F8, you enter Extend Mode, and now the arrow keys will extend the selection. (Of course, we already covered here that pressing Shift when you use the arrow keys does the same thing without messing around with the F8 key.)

However, this next bit’s useful. If you press Shift+F8, then you lock in the current group of selected cells, and can use the arrow keys to move to another area, and then select new cells (either using the normal Shift select method or Extend Mode).

Once the cells are selected, you can do whatever you like to them, such as bold with Ctrl+B.

Ctrl+*: Select the current data block in Excel

Normally you press Ctrl+A to select all, but that’s not always so useful in Excel since it selects the entire (gigantic) worksheet, most of which is blank.

How many times have you started to try to select your column of numbers by using the mouse and then Excel starts scrolling off the screen like mad? Before you know it, you’re down at row 30,000 and then are trying to scroll back up (all while holding down the left mouse button) only to end up scrolling too far the other way. No fun. So — don’t try to play games of dexterity with Excel. If you want to select everything in the block where your cursor is, try Ctrl+*.

F2: Edit a cell in Excel

Forget the clumsy task of clicking in the formula bar when editing a cell in Microsoft Excel. Instead, press F2 and you’ll be editing the cell right in the spreadsheet where the cell is.

Once you’re editing a cell, you can use the standard navigation keys (Home, End, Ctrl+Left Arrow, Ctrl+Right Arrow) to get to where you want, then press Enter to accept your changes (or press Esc to cancel).

For example, suppose you need to change the number “151,000” into “15,100.” Don’t retype it! Just press F2, then press Backspace to get rid of the extra zero, then press Enter. Done!

End, Arrow Key: Move by sections in Excel

Excel has a strange navigation key that’s fairly unique and takes a bit of getting used to, but once you start using it, you’ll wonder how you ever used Excel without it.

Load up an Excel spreadsheet for practice, and then position your cursor somewhere in the middle of some cell entries. (Remember you can use the F5 key to go to a location.)

Now press End then the Right Arrow key (one after the other). You’ll go to the rightmost entry in the column of cell entries. Hit End, Down Arrow. You’ll move down until you hit the next empty cell.

If you start in an empty cell, Excel will keep moving in the direction of the arrow key you press until it hits a non-blank cell.

Ctrl+Page Up and Ctrl+Page Down: Change tabs

If a program or dialog box has tabs (different divided sections or pages with a label at the top), chances are that Ctrl+Page Up and Ctrl+Page Down will change tabs.

Try this out with Firefox and Excel to start off.

In Firefox, remember from yesterday that you can create a new tab with Ctrl+T. Now use Ctrl+Page Up to rotate through the pages (or Ctrl+Page Down to rotate the other way).

In Excel, you’ll start out with three sheets (Sheet1, Sheet2, Sheet3) when you start a new document. Ctrl+Page Down will move from Sheet1 to Sheet2 and Sheet2 to Sheet3. Ctrl+Page Up will move from Sheet3 to Sheet2 or Sheet2 to Sheet1.

For dialog boxes, if Ctrl+Page Up and Ctrl+Page Down don’t change tabs for you, try Ctrl+Left Arrow and Ctrl+Right Arrow, or Ctrl+Up Arrow and Ctrl+Down Arrow. Unfortunately, applications aren’t always as consistent as we’d like.

Ctrl+N: New Document

For most applications that support working with more than one document at once, Ctrl+N starts a new document, without having to answer questions about what kind of new document you want. Try it in Microsoft Word, Excel, etc.

Be careful when using Microsoft Outlook, because Ctrl+N has a different meaning depending on what section of Outlook you’re in. For example, in Calendar mode, Ctrl+N creates a new appointment, while in Mail mode, Ctrl+N creates a new e-mail message.

F5 (or Ctrl+G): In Word and Excel, Go To a location

To round out our navigation week, let’s use an application-specific keyboard shortcut.

If you use Microsoft Word, try this (when editing a long document): Press F5, and the “Go To” dialog box appears. (It may look a bit different depending on which version of Microsoft Word you’re using.) Type in a page number, like 15, and press Enter. Your cursor should now be at the top of page 15. You can then press Esc to cancel the dialog box.

If you use Microsoft Excel, try pressing F5 and in the Go To dialog box, type in a cell (such as B500), then press Enter.

There are more advanced ways of using the Go To dialog box in both applications, but we’ll save that for a future day

If you prefer, you can use Ctrl+G instead of F5.