Excel tips - Contextures Blog

Clean Up Your Excel Dictionaries

While rushing through a spell check in Excel, you accidentally added an incorrect word to your custom dictionary. Now that it’s in there, how do you get it out?

If you’re adventurous, you can use a text editor, such as NotePad, to modify the dictionaries, which are found in:

C:\Documents and Settings\YOUR_NAME\Application Data\Microsoft\Proof

Or, you can modify the list in Microsoft Word, which I find easier:

In Word, choose Tools ► Options
On the Spelling & Grammar tab, click Custom Dictionaries
Select a dictionary in the list, and click Modify
Scroll through the list, and click on the word you want to remove
(to select multiple words, press Ctrl and click)

Click the Delete button
When finished, click OK
Close the dialog boxes

Move Quickly With Excel Shortcuts

In an Excel worksheet, you can use the keyboard navigation keys (Home, End, arrow keys) to move up down or sideways. There are mouse shortcuts too!

Keyboard Navigation Shortcuts

Here are a few keyboard shortcuts, to help you move through the worksheet cells, quickly:

Right ………. Tab
Start of worksheet …… Ctrl + Home
End of worksheet …… Ctrl + End
Start of row ………. Home
Up/ down screen … Page Up/ Down

Work in Excel List

The following keyboard shortcuts help you make smaller movements through a range of cells on an Excel sheet:

Select current range …… Ctrl + *
Move to range border ……. Ctrl + arrow
Move down ………. Enter
Move up ………. Shift + Enter
Move right ………. Tab
Move left ………. Shift + Tab

Mouse Navigation Shortcuts

If you’re a mouse user, like I am, a quick way to navigate is to:

point to the border of the active cell
when the pointer changes to a 4-headed arrow, double-click.

That will take you to the end of the list in that direction, stopping just before the first blank cell.

More Excel Shortcuts

For more time-saving Excel shortcuts, go to the Microsoft Excel Shortcuts List page on my Contextures site.

Excel Keyboard Shortcuts - Number Format — Excel Keyboard Shortcuts – Number Format

Close All Excel Files

While working in Excel 2003 (or earlier versions), you might open several files, to compare them, or copy and paste from one to another.

When you’re finished with those files, you can quickly close all of them, and leave Excel open. (Note: This tip also works in Word)

On the keyboard, press the Shift key.
In Excel, click on the File menu
Click on the Close All command

Close All in Excel 2007 or Later

In Excel 2007, and later versions, you can add the Close All command to the Quick Access Toolbar (QAT).

For instructions for adding or removing a command on the QAT, go to the Excel Quick Access Toolbar Tips page on my Contextures site.

Add or remove commands from Quick Access Toolbar

Video: Add Macro to Quick Access Toolbar

If you use an Excel command frequently, or if an Excel command isn’t available on the Excel Ribbon, you can add its icon to the Quick Access Toolbar (QAT).

This short video shows the steps, and the written instructions are on the Excel Quick Access Toolbar Tips page on my Contextures site.

Quickly Repeat Previous Action in Excel

After you do something in Excel, you might be able to repeat that action, by pressing the F4 key.

Format Cell Bold Font

For example, after you format a cell with bold font, select another cell and press F4.

That second cell is also formatted with bold font, and it’s much quicker than moving the mouse to click on the Bold button in the toolbar.

Repeat Action with Ctrl + Y

You can also use the Ctrl+Y keyboard shortcut to repeat an action.

That shortcut is handy if you’re using a laptop with a minimal keyboard, that doesn’t have the row of function keys across the top.

Use Excel Commands

Another way to repeat a command is to use the Excel 2007 Ribbon commands, or Quick Access Toolbar commands.

Or, in Excel 2003, on the Excel toolbar, click on the Edit menu

Then, in the drop down list, click the Repeat command, as shown below.

Note: The screen shot below says Repeat Font, but that will look different, depending on what your previous action was.

Grab the Excel Color Palettes

While working on an Excel 2003 worksheet, you might want to colour a few of the cells, change the font colour for the text, or add borders to a range of cells.

Select a Fill Colour

To add colour, you select a cell, click on the drop-down arrow at the right side of the colour button on the toolbar, and click on a colour.

This process is simple enough, but all those trips up and down from the toolbar can get a bit tiring, and take extra time.

To Save Time, Move the Palettes

If you have lots of formatting to do, it will be easier if the palettes are closer to the area where you’re working.

You can detach the palettes from the toolbar, and move them onto the worksheet.

Note: This feature is not available in Excel 2007, or later versions.

Move Excel Palette

To move one of the “tear away” palettes (Font Color, Fill Color, or Borders), follow these steps:

On the toolbar, click the drop-down arrow, to open the palette.
At the top of the palette, point to the thin grey bar.
When your pointer changes to a four-headed arrow, drag the palette down onto the worksheet.

Close a Floating Palette

When you’ve finished working with the palette, click the X at its top right, to close it.

Video: Move Excel Toolbar Colour Palettes

And here’s a short video, if you’d like to see the steps for moving one of the Excel 2003 palettes.

3 Data Entry Tips for Excel

Here are three quick data entry tips for Excel — use these shortcuts to enter data in a cell:

To enter today’s date in a cell: Ctrl+;
To copy the data from the cell above: Ctrl+’
To create a line break within a cell: Alt+Enter

Add line break in cell with Alt + Enter keyboard shortcut

Video: Date and Time Shortcuts

Watch this 1-minute video to see the keyboard shortcuts for entering dates and times in a worksheet.

Video: Make a Quick List of Dates

Another quick way to enter dates is by dragging down with the active cell’s fill handle.

Watch this short video and see how to create a list of dates that are a week apart.

More Excel Data Entry Tips

For lots more Excel data entry tips, visit the following pages on my Contextures site:

Data Entry Tips

Line Breaks, Add, Remove

Fill Blank Cells

AutoFill Examples

Excel Shortcuts List

Double-Click Shortcuts

___________