Excel Tips
Page 3

Excel Gridlines In Color
You can view Excel gridlines in a color other than the default black. To do this, choose Tools, Options. When the Options dialog box opens, click the View tab. Click the arrow at the right side of the Color list box (lower left-hand corner) to open the color palette and select a color. Click OK to close the dialog box and save your color selection. If you print this on a color printer with gridline printing activated, the worksheet border will print in your selected color. However, the gridlines will still print in black.

Open Multiple Documents or Workbooks at the Same Time
Word and Excel will let you quickly load multiple documents or workbooks in one easy step. With either program open, follow these steps:

1. Click on the Open tool on the toolbar, or choose Open from the File menu. This displays the Open dialog box.
2. Instead of double-clicking on a file name, create a "selection set." Click once on the first file you wish to open, then hold the Ctrl key down as you click additional files.
3. To remove files from the selection set, hold down the Ctrl key down while clicking on a selected file.
4. When you are satisfied with the list, click on Open.

Quickly Inserting Rows and Columns
You can insert a row or column by using the menu bar, but there is an even quicker way to insert rows or columns. All you need to do is select the row or column that you want the new row or column to appear before. Then, press Ctrl++ (that's Ctrl and the plus sign at the same time or Ctrl Shift+). Excel adjusts your worksheet so the new column or row appears as directed.

Quickly Deleting Rows and Columns
You can delete rows or columns by using the menu bar, but there is a quicker way to do it. 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.

Re-Order Worksheet Columns in Excel
You've probably run into situations in which you need to re-arrange the order of the columns in a worksheet. First, select the columns you want to move. Move your mouse pointer to the edge of the selection until it changes from a cross to a regular pointer arrow. Now, press [Shift] and then click and drag the column to the new position. You'll see a faint "I" bar that runs the entire length of the column, along with a box indicating what the new address will be. Simply release the mouse button before releasing the [Shift] key, and the column is moved without overwriting any data. Note that the same basic technique also works with rows.

Special Custom Lists In Excel
You can create a custom list in Excel for almost anything. Suppose you'd like to automatically list all the last names of your students. Open Excel and on a blank worksheet, enter the names in a column beginning in cell A1. After entering the names, use the mouse to select them. Now choose Tools/Options. When the Options dialog box opens, click the Custom Lists tab. Click Import and Excel will create a custom list from your column of names. Click OK to close the dialog box. You can now delete the names in column A. To check your custom list, click any cell (B1, e.g.), type the first name of your list and press Enter. Now, click in cell B1 again to select it. Click the bottom right corner of the selected cell and AutoFill through the number of students in your list. You should see your list of names.

Doing Simple Arithmetic In Excel
When you want to simply add, subtract, multiply or divide a few numbers in Excel, you can just type in an equal sign followed by the numbers. For example, if you want to add 96 and 23, you'd type =96+23 and press Enter.

Creating a Chart in Excel
Here is a shortcut for creating an Excel chart. When you need to create a chart, select your data and then press the F11 key. Excel will quickly create your chart. You can then make changes to the format if needed.

Display the Toggle Grid Button
Right-click the Toolbar area at the top of your screen. Choose Customize at the bottom of the list. On the Commands Tab, scroll down and choose Forms on the left under Categories. On the right under Commands, scroll until you see Toggle Grid. Click and drag the Toggle Grid button anywhere onto the Standard Toolbar. Now, all you have to do is click the button and it will remove the gridlines from the viewing area.

Changing The AutoEntry Direction In Excel
Suppose you'd like to type a number into an Excel cell and then press Enter to move to the next cell on the right, not the next cell down. What you have to do is change the AutoEntry direction. To make this change, choose Tools/Options. When the Options dialog box opens, click the Edit tab. Select (or leave selected) the check box labeled 'Move selection after Enter.' Now, click the arrow at the right side of the 'Direction' list box and select Right. Click OK to close the dialog box and record your selection. To test the change, type a number into cell A1 and press Enter. Excel should move to cell B1.

Excel Borders
One way to produce a more attractive worksheet in Excel is to put a border around your data, or segments of your data. As an example, open a blank worksheet and type some data into several contiguous cells. Now, select the cells that contain data and choose Format/Cells. When the Format Cells dialog box opens, click the Border tab. Let's click now on the 'Outline' preset and then click OK to close the dialog box and apply your new border. As you can see in the Border page of the Format Cells dialog box, you can select the type of border you want and also the color of the border.

Using a Graphic for a Background on an Excel Worksheet
In Excel you are not limited to a plain white background for your worksheets. Instead, you can use any graphic image for a background. The effect is much like the backgrounds you see on Web pages. The image used as the background is tiled (repeated) so that it fills the entire background of the worksheet. To use a graphic
as a worksheet background, follow these steps:

1. Go to Format on the Menu Bar, choose Sheet, then Background.
2. Locate and select the graphic image you want to use as a background.
3. Click on OK.

The image is imported and fills the background of your worksheet. To later remove a background, choose Sheet from the Format menu, then choose Delete Background from the resulting submenu.

Return to the Active Cell in Excel
If you have a cell selected and decide to scroll down the page looking for other information, you can quickly return to the active cell by pressing Ctrl + Backspace.

Controlling the Behavior of the Mouse Wheel in Excel
If you have one of the newer mice that has a wheel between the two mouse buttons, you may know that Excel recognizes the wheel and allows you to use it for different purposes. The default behavior of the
wheel is to scroll your worksheet up and down. You can modify how Excel reacts to moving the wheel by following these steps:

1. Choose Options from the Tools menu.
2. Make sure the General tab is selected.
3. Select the Zoom On Roll With IntelliMouse check box.
4. Click on OK.

Now when you use the mouse wheel, Excel zooms your worksheet in and out. If you decide you again want to use the wheel to scroll, simply repeat the steps but clear the check box.

Maintain Consistency Between AutoShapes
When you're adding several AutoShapes to a worksheet, you'll often want them to have consistent formatting, such as fill color and 3-D effects. Although you can apply the formatting after the fact, you'll save time if you set the AutoShape default that applies to all newly created AutoShapes. To do so, add an AutoShape and format it as you want. Then, while the object is selected, click the Draw button on the Drawing toolbar and select Set AutoShape Defaults. Note that the default you set applies only to the current worksheet. This also works in Word and PowerPoint.

Entering the Current Time in Excel
If you want a quick way to enter the current time, select a cell and press Ctrl + : (colon). Obviously, to access the colon you need to hold down the Shift key, so you might express this shortcut as Shift + Ctrl + :. The result is that Excel places the current system time in the selected cell. All you need to do is press Enter to accept the time.

Merging Cells In Excel
You want an attractive header in an Excel worksheet. The only problem you have is that the header spans several columns and it's difficult to center. What you need to do is use Merge and Center in Excel's toolbar. Type in your header, then select all of the cells that your header spans. Click the Merge and Center button (to the right of the Align Right button).

Orientation Selection In Excel
There are basically two types of worksheets - long and narrow, and short and wide. When you have a wide worksheet, you can probably view it better if you switch the worksheet's orientation to landscape. To do this, open a worksheet that contains some data and then choose File, Page Setup. When the Page Setup dialog box opens, select the radio button labeled 'Landscape' and then click OK. See if your worksheet fits the landscape orientation best. If not, choose File, Page Setup again. Select the 'Portrait' radio button and click OK.

Navigating To A Cell In Excel
When you need to move to a specific cell in an Excel worksheet, you can scroll to the area that contains the cell and then click it. However, if the cell you want to select is buried somewhere in a rather large worksheet, you might find it easier and quicker to use Excel's Go To command. Let's say you want to move to cell K324 in your worksheet. That's a lot of worksheet scrolling no matter how you do it. But you can press Ctrl + G, then type in K324 and press Enter and you're there.

Quickly add sheets to an Excel workbook
If you need to add worksheets to an Excel file, you probably use the Insert, Worksheet menu command. There's a much faster way to go about this task. Simply press Shift + F11 and Excel adds a new blank worksheet before whichever sheet you were working on at the time.

Moving A Worksheet In Excel
Let's suppose you've created a workbook of three worksheets. Now that everything is in place, you see that Sheet 2 really should be the first sheet and Sheet 1 the second sheet. To move Sheet 2 so that it comes before Sheet 1, locate the sheet name tabs at the bottom of the workbook. Grab the Sheet 2 tab with the mouse and drag it to the left. When a small down arrow appears at the left side of the Sheet 1 tab, release the mouse button. Sheet 2 is now first and Sheet 1 is second.

AutoFill In Excel
If you want to enter a series beginning with 1 and you drag the AutoFill handle on the cell with the number 1, you'll end up with nothing but ones, unless you know the trick. Hold down the Ctrl key while you click and drag the AutoFill handle. You'll get 1, 2, 3, 4, etc. If you drag down without using the Ctrl key, you'll get 1, 1, 1, 1, etc.

Using Personalized Menus and Toolbars
Office 2000 programs display only the commands that you use most often on the new personalized menus and toolbars and then expand to show the rest. Also, toolbars share space in a single row so that more screen space is available for your work. If you would prefer to see all of your toolbars on separate rows and to see your menus expand the minute you click on them, do the following:
1. Right-click anywhere on your toolbars; choose Customize at the bottom.
2. De-select the top two checks (Standard and Formatting toolbars share one row and Menus show recently used commands first).
3. Then click Close at the bottom of the window.

Selectively Format Text Within Excel Worksheet Cells
You've no doubt formatted Excel data by applying bold, underline or color formatting to worksheet cells. However, you may not be aware that you can also apply formatting to just certain text within a cell. For example, you may want to emphasize a particular word or phrase within a long string of text. To do so, select the cell and then select the text you want to format within the Formula bar. Then, simply apply the types of formatting changes you'd typically make and press Enter when you've finished.

Align Objects in Excel
There is an easy way to align objects on a worksheet. Hold down the [Alt] key when dragging a chart or graphic, and the object's frame will snap to the cell.

Custom Number Formats For Excel
Let's imagine that you must enter several hundred telephone numbers into an Excel worksheet. You don't have to worry about getting the format right because Excel has a special format for phone numbers. To select the phone number format, select the cells you want to use (click on the header of a column if they are all going to be in the same column) and then press Ctrl + 1 to open the Format Cells dialog box. When the dialog box opens, click the Number tab. Now, under 'Category' click 'Special.' Under 'Type' select 'Phone Number' and click OK.

Entering Compound Fractions In Excel
To enter fractions in Excel: enter a zero and a space before the fraction. If you fail to enter the zero, and enter something such as 7/8, Excel will think you're entering a date. To enter a compound fraction, enter the whole number first, then a space, and then your fraction. For example, you would enter 12 2/3 as 12 space 2/3.

Enter Multiple Lines In An Excel Cell
When you're adding titles to an Excel worksheet, you may find that using more than one line in a cell improves your worksheet's appearance. For example, if you would like to enter names in Column A, you could enter the first name on one line of the cell, then enter the last name on the next line. To try this, click cell A1 and type your first name. Now press Alt + Enter and type your last name. You now have two lines in the same cell. Excel will adjust the cell height to accommodate the text.

Center Excel Data on a Printed Page
When you print a worksheet, you may feel that the printout would look better if the data were centered on the page. If so, choose File, Page Setup from the menu bar. Then, click on the Margins tab. You can now use the check boxes in the Center On Page section to control how the data is presented. Note that the centering options apply to the space defined by the Top, Bottom, Left and Right margins, not the physical dimensions of the page.

Using AutoFormat In Excel
You can create attractive worksheets in Excel by using the AutoFormat feature. Select the range of cells that contain the titles and data on your worksheet. Choose Format,AutoFormat from the Menu Bar. This will open the AutoFormat dialog box from which you can select the format you want to use. After you make a selection, click OK to apply your selection and close the dialog box.

Some Excel Keyboard Shortcuts
ALT + = will AutoSum
CTRL + SHIFT + $ will change the cell format to Currency
CTRL + SHIFT + % will change the cell format to percentage
CTRL + SHIFT + & will add a border around the current selection
CTRL + SHIFT + _ will remove a border from a selection
CTRL + SHIFT + * will select the current region

Show or Hide the Formulas in an Excel Spreadsheet
When you're working in an Excel worksheet, you can alternate between viewing the values in the cells and displaying the formulas. To toggle between the different views, press CTRL+` (single left quotation mark). On most keyboards, it's the key directly to the left of the "1" key.

Using the Underline Button in Excel
Clicking the Underline button on the toolbar will single underline the contents of a cell. If you want to double underline the contents, hold down the Shift key while clicking on the Underline button.

Viewing Formulas versus Results
Normally, Excel displays the results of your formulas on a worksheet. If a cell contains a formula, you can view the formula by selecting the cell and looking at the Formula Bar. You can also instruct Excel to display the formulas for all cells instead of the results of those formulas. Pressing Ctrl+` will toggle between the formulas and their results. (The ` key is the one just above the Tab key and to the left of the 1 key.)

Control Number of Sheets in a New Excel Workbook
By default, Excel creates three worksheets in every new workbook. If you find that you routinely don't use all three or that you require more, change the default number that Excel creates. To do so, select Tools, Options from the menu bar. Then, click on the General tab and change the number in the Sheets In New Workbook spinner box to the number of desired sheets.

Naming a Worksheet In Excel
When you want to name or rename a worksheet in Excel, you can right-click on the worksheet's tab and choose Rename. Another way to name or rename a worksheet is to double-click the tab you want to name. This selects the text in the tab and allows you to type in a new name. Press Enter when done.

Coloring Excel Cells
It's very easy to color cells in Excel when the Drawing toolbar is active. All you have to do is select the cell or cells you want to color and then click the arrow at the right side of the Fill Color button (the paint can) in the Drawing toolbar. To activate the Drawing toolbar, choose View, Toolbars, Drawing.

Canceling an Edit in Excel
As you are entering information in a cell, you may want to cancel what you are entering. For instance, you may have selected the wrong cell before you started typing, and you want to back out of the edit you are making. There are two ways you can do this. The first is to press the Esc key, and the other is to click on the red X just to the left of the Formula bar. (The red X only appears after you start typing.) Regardless of the method you choose, Excel undoes your changes and returns the cell to its state just before you started typing. If you make a mistake and press Enter, Excel replaces the contents of the cell with whatever you were typing. To undo this, you should use the Undo feature of Excel: press Ctrl+Z or click on the Undo tool on the toolbar.

Zooming In Excel
When you need to get a closer look at a portion of your Excel worksheet, you can simply zoom in on it. Let's suppose that you need to have a close look at a range of cells from A1 through D8. Select the range and choose View, Zoom. When the Zoom dialog box opens, select Fit selection, and then click OK. The selection will now occupy the entire Excel window. To get back to the normal view, choose View, Zoom and, in the Zoom dialog box, select 100% and click OK. You can also do this with the Zoom button on the Standard Toolbar. After selecting the cells you wish to zoom in on, click the down arrow to the right of the Zoom button and choose Selection.

Format Cells to Ensure Proper Sorting
Although it's likely that you format column headers in a way that visually separates them from your data, you should be aware that there's value to doing so beyond just making your data easier to read. Excel looks at formatting to determine whether the first row in a table range contains column headings or data when you execute the Sort command. If the formatting is different, Excel assumes that the first row is a header row, otherwise the data in that row is sorted along with the rest of the data in the table columns. Knowing this can help avoid unexpected results, particularly when using the toolbar buttons to sort data.

Forcing a Page Break in Excel
As your worksheet gets larger, there may be times when you want to force Excel to starting printing on a new page. For instance, you might want only the first fifteen rows of information on the first page, then the balance of the worksheet on the second printout page. To make this happen, follow these steps:

1. Make sure cell A16 is selected.
2. Choose Page Break from the Insert menu. A dashed line appears between rows 15 and 16.
3. Print your worksheet as normal.

If you want to later remove the page break, make sure cell A16 is selected and choose Remove Page Break from the Insert menu. The dashed line disappears.

Working With Fonts In Office 2000
In Word, Excel, and PowerPoint, you can easily change the font and font size without ever lifting your hands from the keyboard. To change the font, press Ctrl + Shift + F. This will select the Font box. Next you can press the Up and Down arrows to select the font you want to use. After you make a selection, press Enter. To change the font size, press Ctrl + Shift + P and then use the Up and Down arrows to select a font size. Press Enter after you make a selection.

Taking A Picture Of Excel
Suppose you'd like to have a photo of an Excel worksheet that you could then paste into a Word document or a PowerPoint slide. To create such a picture, select the range of cells you want to capture and then hold down Shift and choose Edit, Copy Picture. Then choose Bitmap in the dialog box. Now you can move to Word, PowerPoint, or some other application and choose Edit, Paste to paste in the Excel picture. It can even be pasted into an e-mail message.

Some Excel Shortcut Keys
Ctrl + Shift + $ applies the Currency format using Dollars
Ctrl + Shift + % applies the Percentage format
Ctrl + Shift + plus sign (+) Inserts a blank cell
Ctrl + 9 hides selected rows
Ctrl + Shift + ( shows selected rows
Ctrl + 0 [zero] hides selected columns
Ctrl +Shift + ) shows selected columns

Filling Excel Cells
You can fill a cell with any character you want. As an example, let's fill cell A1 with plus signs. To do this, click cell A1 and then choose Format, Cells. When the Format Cells dialog box opens, click the Alignment tab. Next, click the arrow at the right side of the "Horizontal" list box and select Fill from the list. Click OK to close the dialog box. Now, type a plus sign into the selected cell and press Enter. Excel will fill the entire cell, regardless of its width, with plus signs. This process will work for any character. You could enter asterisks, minus signs -- anything!

Forcing New Lines Within Cells in Excel
If you'd prefer that information within a cell be broken onto multiple lines, you can manually insert line breaks. To do so, just press Alt+Enter and your insertion point moves down to a new line. This works both when you're entering in the formula bar or directly in a cell.

Easily Create Multiple Sums in Excel All At Once
If you need to create a series of sums, such as beneath multiple columns or at the ends of multiple rows, you probably go through a lot more work than is necessary. Rather than creating individual sum formulas or copying a sum formula from one cell to the rest of the range, you can create all the sums at once with a single mouse click. Select the appropriate range of cells and then click the AutoSum button on the Standard toolbar.

Quick Excel Column Or Row Deletion
If you want to delete an entire column or row in an Excel worksheet, click in that column or row and then press Ctrl + - (minus sign) to open the Delete dialog box. You can now select the appropriate radio button to delete an entire column or row. After you make your selection, click OK to continue and perform the deletion.