Excel Tips
Page 5

Using the F12 Function Key in Word, Excel and PowerPoint
F12 (alone) is the Save As command.
Shift + F12 is the Save command.
Ctrl + F12 is the Open command.
Ctrl + Shift + F12 is the Print command.

Changing an AutoShape
There may be times when you want to completely change an AutoShape from one to another. For instance, you may want to change one banner shape to another. While you can just delete your original AutoShape and then draw a new one, it is easy to change AutoShapes by following these steps:

1. Make sure the Drawing toolbar is displayed.
2. Select the AutoShape you want to change. Handles should appear around the outside of the shape.
3. Click on Draw on the Drawing toolbar, and then choose Change AutoShape. The familiar AutoShape categories will appear.
4. Select the AutoShape you want to use.

This changes the AutoShape used, without changing the overall size or shape of the bounding rectangle that contains the AutoShape. You can then proceed to edit the new AutoShape, as desired.

Selecting Multiple Cells with the Name Box in Excel
The Name box, found on the left side of the Formula bar, is typically used to set or select named ranges in a workbook. In addition, you can jump to a particular cell by typing its address in the Name box and pressing Enter. What you may not know is that you can also use the Name box to select multiple cells or ranges. For instance, enter: B3:D10 in the Name box and press Enter. Excel selects range B3:D10.

This also works for noncontiguous ranges. Just separate the cell addresses with commas. For example, enter: B3, D10, F1:F4 in the Name box and press Enter. Excel selects three noncontiguous ranges.

Quickly Open the Paste Function Window
Next time you need to look up an Excel function try Shift + F3. The Paste Function window will immediately open and you're ready to find the function.

Opening a Copy of a File
There may be times when you want to make a copy of a workbook or file, without affecting the original. To do this, follow these steps:

1. Click on the Open tool, or choose Open from the File menu.
2. Select the workbook or file you want to make a copy of.
3. Click on the down-arrow at the right side of the Open button.
4. Choose the Open As Copy option. A copy of the workbook or file opens.

The file that is opened uses the same file name, but the phrase "Copy of" is attached to it as a prefix. Thus, if the original workbook or file you selected in step 2 is named "Budget.xls," what Excel creates is a workbook named "Copy of Budget.xls." If you want to rename the file, you will need to either use the Save As command, or rename the workbook after closing it.

Configure Excel So Comments Are Always Visible
Comments are a great way to provide user instructions or background information about data in specific cell. To create a cell comment, click in the desired cell and choose Insert, Comment. Then, type your message and click outside of the comment box. By default, Excel indicates that a cell contains a comment by inserting a small red triangle in the upper-right corner of the cell. To view the comment, you ordinarily have to hover the mouse pointer over the triangle. However, you can configure Excel to always display comments. To do so, select Tools, Options from the menu bar and click on the View tab. Then, choose the Comment And Indicator option in the Comments panel and click OK. If you find that a comment box gets in the way of a particular bit of data, just put your pointer on the edge and drag the comment box to a new position. An arrow will continue to anchor the comment to the appropriate cell.

Enhanced Filling
Excel allows you to quickly and easily fill a series of cells with a progression of values by using the Autofill handle. This is the small black dot that appears at the lower-right corner of a cell when it is selected. Just click on the fill handle and drag to select the cells you want filled. Using this feature allows Excel to make decisions about how the cells you select should be filled.

You can gain more control over the filling process if, instead of clicking on the fill handle, you right-click on it and then drag. When you release the mouse button a Context menu appears that allows you to select the type of fill you want to perform.

Easily Transpose a Range of Data in Excel
Occasionally, you may have data stored in a worksheet column that you'd prefer to have organized in a row--or vice versa. It's easy to transpose data, even if you want to transpose multiple rows or columns. To do so, select the range that you want to transpose. Select Edit, Copy from the menu bar. Then, select the new destination for your data and choose Edit, Paste Special from the menu bar. When the Paste Special dialog box appears, simply select the Transpose check box and click OK. If the range you're transposing contains multiple columns and rows, the data from the source range's top row is placed in the left column of the new range, and the original range's left column becomes the new range's top row.

Odd Arrow Key Behavior in Excel
If you are ever using Excel and the arrow keys don't work like you think they should, it could be because of the Scroll Lock key. Normally, when you press an arrow key, Excel moves the cell highlight in the direction of the key you pressed. If the Scroll Lock key has been activated, however, Excel doesn't move the cell highlight, it instead moves the worksheet, changing what is displayed on the screen. To solve this odd behavior, press on the Scroll Lock key another time. The arrow keys should again behave as you expect them to.

Always Display Page Breaks in Excel
To see the page breaks on a worksheet, you have to go to Print Preview first. If you want these breaks to always show, select Tools, Options from the menu bar. Then, click on the View tab and select the Page Breaks check box in the Window Options panel. Then click OK. From now on, Excel always displays automatic page breaks in your worksheets, allowing you to easily format your data correctly before printing.

Working with Roman Numerals
Excel includes a worksheet function that allows you to convert a number to Roman numerals. Type the following formula: =ROMAN(123) All you need to do is replace 123 with the number you want converted. You can use any number between 1 and 3999.You should note that the ROMAN function returns a text value, and you therefore cannot use the result in any sort of calculation--as far as Excel is concerned, it is no longer a number.

Removing Gridlines in Excel
To remove the gridlines in Excel, go to Tools, Options and click on the View tab. Uncheck the Gridlines box in the Window options section. Click OK. You will find that gridlines are still on new workbooks you create so you can use this when you need it and not affect new files.

Massive Printouts in Excel
Ever had this happen to you? You run a print job and are surprised to get 22 pages of output. That would have been fine, except you were expecting one or two at the most. Problem is, most of the pages that came out of the printer are empty. The problem is most likely that you mistakenly selected a cell in a distant column and row and bumped into your Space Bar. That leaves no visible signs, but Excel thinks you want to print this space. To solve this:

1. Press Ctrl + End. Excel moves to the cell it thinks is at the lower-right corner of your data.
2. If there is nothing else in that column, delete the column.
3. If there is something in the column, but nothing else in that row, delete the row.
4. Click on the Print Preview tool on the toolbar. Hopefully you are back to your expected number of pages.
5. Print your worksheet as normal.

Create Eye-Catching Excel Comments
When you create a cell comment in Excel by choosing Insert, Comment from the menu bar, Excel automatically formats the comment as a yellow rectangle. You can change the way the cell comment appears by applying the following formatting effects to a comment after it has been created.

1. While your insertion point is in a comment, you can change typical font attributes, such as font name or size, text color, or underline formatting.

2. To change the comment box itself, right-click on the cell with a comment and choose Edit Comment. Click on the comment box, being sure to click on the edge of the box and not within it. Next, choose Format, Comment from the menu bar to reveal the formatting options that are available.

3. You can even use a different shape for your comments by substituting one of Excel's AutoShapes. Select the comment box, then choose View, Toolbars, Drawing. Finally, choose Draw, Change AutoShape from the Drawing toolbar and select the AutoShape you want to use.

Enter Duplicate Data in Multiple Excel Cells
Press the Ctrl Key and select all of the cells that the data item should be in. Next, type the data. Then press Ctrl + Enter. All of the cells will contain the same information.

Quickly Deleting Rows and Columns
You probably already know how to completely delete rows or columns by using Excel's menu commands. Excel provides an even quicker way to delete rows or columns, however. All you need to do is select the row or column that you want to delete, and then press Ctrl + - (that's Ctrl and the minus sign at the same time). Excel removes the row or column from your worksheet, as directed.

Quickly Add Several Sheets to an Excel Workbook
If you need to add worksheets to an Excel file, chances are that you use the Insert, Worksheet menu command. There's a much faster way to go about this task. Press Shift + F11 and Excel adds a new blank worksheet before whichever sheet you were working on at the time. If you need to add several worksheets to a workbook, you can use either technique to create all of the sheets at once. While holding down the Shift key, click on the same number of worksheet tabs as new sheets you want to create. Then, press Shift + F11.

Dates In Excel
Excel adds dates in a default format. If you would like the date in a different format, select the cells to be formatted, and go to Format, Cells. Click the Number tab. On the left, in the Categories list choose Date. On the right (in the "Type" section) a list of standard options is displayed. Select the format you need. Click OK.

Changing the Comment Color in Excel
Word relies on the system colors set in Windows to determine what color the comment boxes should be. Word uses the ToolTip color setting for the comment boxes. If you want to change that setting, follow these steps:
1. Minimize Word, along with all your other programs.
2. Right-click on any area of the desktop itself. Windows displays a Context menu.
3. Choose Properties. Windows opens the Display Properties dialog box.
4. Make sure the Appearance tab is selected.
5. Using the Item drop-down list, choose ToolTip.
6. Click on the Color indicator, to the right of the Item drop-down list. Windows displays a small palette you can use to select a color.
7. Click on the color you want to use.
8. Click on OK.
Now your comment boxes will be the color you selected. In addition, since you changed the ToolTip color, all your ToolTips in Word and all your other programs will be the same color, as well.

Copy an Excel Worksheet From One Workbook to Another
To move worksheets from one workbook to another, first, open both workbooks. (The one with the worksheet and the one to which the worksheet needs to be relocated.) Then arrange the workbooks side by side using the Window menu, Arrange, Vertical. Click OK.

Next you need to click and hold the sheet tab to be moved. Then, still holding down the left mouse button, drag the sheet tab into the other file. You'll see the small triangle that appears when a sheet is moved so you can tell where it will be located. When it's where you need it to be, release the mouse button.

Quickly Zoom In and Out of Your Office Documents
If you have a wheel on your mouse, you can use the wheel button to quickly zoom in and out of an Office document. Hold down the Ctrl key as you rotate the wheel button forward or back. This works in Excel, Word, PowerPoint, and Publisher as well as many other Microsoft programs.

Fine-Tune Your Fonts in Excel
When you're using a small font in a tight space, sometimes the 8-point font is too large to fit and the 7-point font is too small to read. Luckily, you can choose a font size that's halfway in between those two: Select Font from the Format menu. Then, on the Font tab, type 7.5 in the Size box and click OK. This tip works with any size true type font - not only in Excel but also in Word.

Copy and Paste up to 12 Pieces of Information at Once
If you're constantly copying text and data between different Office programs, there's a way to save time by copying multiple items at once. For example, you can copy a chart in Excel, switch to PowerPoint and copy a bulleted list, switch to Internet Explorer and copy a page of text, and then switch to Word and paste the collection of copied items into your document. Here's how:

1. In any Office 2000 program, on the View menu, point to Toolbars and click Clipboard to display the Office Clipboard.
2. Select an item you want to copy.
3. Copy the item into the Office Clipboard by clicking Copy on the Edit menu or clicking the Copy button on the Clipboard toolbar.
4. Repeat steps one through three until you have copied all the items (up to 12) you want. If the item you want to copy is in another program, switch to that program first.
5. In an Office program, click where you want to paste the items.
6. To paste all the items at once, click Paste All on the Clipboard toolbar. Or to paste the items one at a time, click the icon for the item you want to paste.

Quickly Remove Objects From an Excel Chart
If you have a chart that contains data, value, or percent labels that you want removed, you don't have to display the Chart Options or Format Data Series dialog boxes to get rid of them. Just click on one of the chart labels, which selects all of the labels for the data series, and press the Delete key. As an alternative, you can right-click on one of the labels and choose Clear from the shortcut menu.

Changing Gridline Color in Excel
The gridlines help you track information on the screen and to locate cells quickly. Normally the gridlines are shown in black, but
you may want to make them another color. To change the gridline color, follow these steps:

1. Choose Tools, Options from the Menu Bar.
2. Click on the View tab.
3. Use the Color drop-down palette to select a color for your gridlines.
4. Make sure the Gridlines check box is selected.
5. Click on OK.

You can specify different gridline colors for each worksheet in a workbook.

Change the Font or Size of Text Using Keyboard Shortcuts
You can use keyboard shortcuts to access the Font box and the Font Size box on the Formatting toolbar and then quickly change the font or size of selected text. Here's how:
1. Select the text you want to change.
2. Press Ctrl + Shift + F to access the Font box or Ctrl + Shift + P to access the Font Size box.
3. Press the Up Arrow or the Down Arrow to select the font name or size you want.
4. Press Enter to accept the font name or size.
Note: This tip works in Excel, FrontPage, PowerPoint, Publisher, and Word.

Paste Information from Excel as a Picture
If you would like to place an image of an Excel file into a Word document, image editing program, or other program, do the following:
1. On the Excel worksheet or chart sheet, select the cells or click the chart or object you want to copy.
2. Hold down Shift and click Copy Picture on the Edit menu. For best picture quality, make sure As shown on screen and Picture are selected, and then click OK.
3. Click the worksheet or other document where you want to paste the picture.
4. Click Paste on the Edit menu.
5. To make adjustments to the image after you've pasted it, use the Picture toolbar. (To open it, point to Toolbars on the View menu and click Picture.)

Add a Dropdown List to a Cell in Excel
First, type the list of valid entries in a single column. (This list must be on the same sheet as the cell that will contain the dropdown list. If you need to, place the list in an unused column and hide the column later.) Select the cell or cells that will contain your dropdown list. Choose Data/Validation, and select the Settings tab. From the Allow dropdown list, select List. In the Source box, select or enter the range address of the list items in your sheet. Make sure the In-cell dropdown box is selected. Then click OK. 

Printing a Portion of a Worksheet in Excel
You already know how to print a worksheet. What you may not know is how you can quickly print just a portion of a worksheet. You can accomplish this by following these steps:
1. Select the cell range you want to print.
2. Choose Print from the File menu. You will see the Print dialog box.
3. In the Print What box, choose the Selection option.
4. Click on the OK button.

Make a Quick Excel Chart
Sometimes going through the Chart Wizards seems to take forever. If you want to put a chart into your workbook very quickly, you can follow these two simple steps:
1. Select the table on which the chart is to be based.
2. Press F11.
At this point you can perform whatever customization you desire on the chart and work with it as normal.

The Functions Box in Excel
When you are writing a formula, you can click the down arrow next to the Functions dropdown list to choose a formula. When you do this, the resulting dialog box may be in the way of the cells you are working with. However, the palette isn't docked in place. Simply drag it with your mouse to a section of your worksheet that's out of the way of your data.

Excel's Undo List
If you need to backtrack through many steps to undo something, and you are not sure how many steps you need to go back through, use the dropdown arrows on the Undo and Redo buttons. These provide you with a running list of your recent actions so that you can easily undo or repeat exactly the steps you want. This works also in Word.

Change the Font Size by Typing the Point Number
If you know the font point size you want to use, click on the Font Size point number on the Text Formatting toolbar. This will select the number. Then type in the new point size. This saves clicking the down arrow to choose a new number. This works in most programs.

AutoFilling with Weekdays
The AutoFill feature of Excel is very handy, allowing you to automatically fill cells with all sorts of information, based on the content of cells you select. For instance, if you fill two cells with the words "Monday" and "Tuesday," and then select those cells, you can drag the AutoFill handle to fill other cells with other days of the week.

What if you want to only fill cells with the workdays, Monday through Friday? This is easy to do if you make one small change in how you use the AutoFill handle. Instead of clicking and dragging it with the left mouse button, click and drag with the right mouse button.

When you release the button, a Context menu appears. Two of the options on the menu are "Fill Days" and "Fill Weekdays." If you choose Fill Days, then the range is filled with the names of the seven days of the week, the same as if you had used the left mouse button to do the AutoFill. The other option, Fill Weekdays, fills the range with the names of only the five days of the week, Monday through Friday.

Drawing Lines in Office Programs
The Drawing toolbar allows you to create a number of shapes that were previously only available through the use of a drawing program. One such shape is a line. To draw a line, follow these steps:

1. Select a line weight and type by clicking on the Line Style tool on the Drawing toolbar.
2. Click on the line tool.
3. Position the mouse pointer where one end of the line is to be located.
4. Click and hold the mouse button.
5. Drag the mouse until the line is the desired length.
6. Release the mouse button.

Creating an AutoShape
In addition to working with numbers and text within cells of a workbook, Excel allows you to highlight your worksheets by adding simple drawings. One classification of drawing objects you can add are called AutoShapes. In many respects AutoShapes can be considered simple drawing objects because they are very easy to create, even though they appear complex. Excel defines quite a few different AutoShapes (over a hundred), all selectable by clicking on the AutoShapes tool on the Drawing toolbar.

When you click on the AutoShapes tool you actually display a series of menus from which you can pick the shape you want. For instance, let's say you wanted to draw a heart. You would click on the AutoShapes tool, then choose Basic Shapes, and finally choose the heart shape.

Once you select a shape, you draw it by positioning the mouse pointer at one corner of where you want to place the shape, clicking the mouse button, and dragging until you reach the opposite corner. When you release the mouse button the shape appears on the screen.

Inserting Rows in Excel
If you want to insert rows in a worksheet, you probably know that you can do so by choosing Rows from the Insert menu. This works great for inserting single rows. If you want to insert multiple rows, you have two choices. First, you can insert a single row by using the menu, as already mentioned. Then press F4 to repeat the command and keep inserting rows.

The second method involves selecting rows before inserting. For instance, if you want to insert five rows, select five existing rows in the worksheet, then choose Rows from the Insert menu. Excel then inserts five rows in your worksheet, just before the first row you selected.

Quickly Outline Excel Cells
If you need a quick outline to a cell or group of cells, select the cells to be outlined and try Ctrl + Shift + &. To remove the border, select the cells involved and press Ctrl + Shift + _.

Paste Special in Excel
Within Excel there is another pasting option available from the Edit menu. This option, Paste Special, is rather unique. It allows you to specify how Excel should paste the information in the Clipboard. When you choose it, you will see the Paste Special dialog box. The settings in the dialog box control which portion of the information in the Clipboard you want pasted, as well as what operations you want taken on the information being pasted.

Using Alt With Drag and Drop in Excel
You can drag a selected cell or cells to another worksheet within your workbook. To do this, hold down the Alt key as you drag a cell or range onto the tab of the desired worksheet. Excel activates that worksheet and allows you to continue dragging the selection to the spot where you want to move it. To copy the selection rather than move it to another worksheet, hold down Ctrl + Alt while you drag.

This doesn't work when you're dragging charts or drawn objects. To drag and drop a cell or selection in Excel 2000, you must put your pointer on the edge until you see the left pointing white arrow. In Excel 2002 you will use the move tool (four arrows).

Wrap Large Amount of Text in Excel
When you enter a long string of text into cell A1, the text spills over into cells that are outside the area of the table (column E) or the print area. You do not want the text to extend into column E. Be sure that the text you typed was only entered into cell A1. Select cells A1:E1. Choose Edit, Fill, Justify. Click OK and the following message will appear: Text will extend below selected range. Before you click OK, check if there is data or text in the rows below. Allowing the text to extend below that will overwrite the existing data. When you click OK, the text will wrap into the rows below.

AutoFiltering in Excel
Filtering a list means displaying only a part of it. You provide the criteria you want used, and then Excel displays only those list records that match the criteria. Filtering is especially useful if you have a large list and you want to work with only a subset of the records in the list. The easiest way to filter your list is to use the AutoFilter feature. You do this by following these steps:

1. Select any cell in your list.
2. Select Data, Filter, AutoFilter from the menu bar. Excel determines where your column (field) labels are located and adds pull-down arrows to the right side of each label's cell.

AutoFilter is now ready to use. If you click on one of these pull-down arrows, Excel displays the unique values in that column (field). You can then select one of the values and Excel displays only those records that match that value for that field. (The pull-down arrow then turns blue.) All the rest of the records in the list will be hidden.

To turn off AutoFiltering, either select Data, Filter, AutoFilter from the menu bar or select the pull-down arrow for a field that has criteria set, and then choose the (All) option.

Insert Rows and Columns in Excel With the Mouse and Shift Key
An easy way to insert rows and columns is to use the fill handle while holding shift. Select a row and put the mouse cursor over the fill handle at the left end of the row and hold down the Shift key. Notice that the cursor changes to the insert indicator. Now, while holding Shift, drag the mouse down to insert rows after the selected row. To insert columns, select a column first and while holding Shift, drag the mouse to the right.