Excel Tips
Page 4

Calculating Sums in Excel
There aren't quite 50 ways to calculate a sum in Excel, but there are more ways than one. Suppose you enter numbers in cells A1 through A5. Now, you want to calculate the sum in A7. You could type in the formula =sum(a1:a5) and press Enter. You could also click cell A7, hold down the Alt key, type in = and then press Enter.

Display What You Need in Excel
When you're working with a small worksheet in Excel, there's no need to squint at the screen to see those few dozen cells. Instead, you can instruct Excel to display only the area you need to use. To do this, select the area and then choose View, Zoom. When the Zoom dialog box opens, select the "Fit selection" button and click OK.

Print Titles on All Worksheet Pages
To make all of the column titles print on each page in Excel, choose File, Page Setup. When the dialog opens, click the Sheet. Now, click the icon at the right side of the "Rows to repeat at top" entry box. When the Rows to Repeat at Top dialog box opens, click the row where your titles appear (usually the top row) and press Enter. Back in Page Setup, click OK. Your titles will now print on all worksheet pages.

Deleting Cells in Excel
When you need to delete a cell in an Excel worksheet, you can press Delete or Backspace. However, this doesn't delete the cell--it only clears the contents. If you really want to delete cells, select the cells and then press Ctrl + - (minus sign). Then click OK in the resulting dialog box. The selected cells will disappear and the adjacent cells will shift to fill the empty space.

Double Lines in Excel Cells
If you want to spread the contents of a cell over more than one line, type as normal, but when you reach the end of a line, hold down Alt and press Enter. A new line will start within the same cell.

Window View in Open Box
First, go to File then Open in a Microsoft Office application. Now press ALT + 6 and you will notice that the window view changes from list, to details, to properties, to preview and then back.  

Working With Multiple Workbooks
When you are using Excel, you are not limited to working with a single workbook. You can open as many workbooks as you like, and Excel keeps track of them individually. You can then switch between workbooks by using the Windows menu. If you have a single workbook open and you choose the Windows menu, you will notice the workbook name at the bottom of the menu. If you have multiple workbooks open, each workbook name is listed at the bottom of the menu and the currently active workbook is indicated by a checkmark to the left of the workbook name. To work with a different workbook, select the desired workbook from the menu. In Excel 2000 there will also be a button on the taskbar for each open workbook.

More Printing Power in MS Excel
To print the column and/or row titles on all the pages automatically, set Excel to print the titles on all printed pages. Decide what information needs to be printed at the top of each column or at the left of each row. Once the decision is made, go to the File menu, Page Setup. In the Page Setup window go to the Sheet tab. "Print titles" is the second section. This is where you tell the program what titles to print on every page.

Resizing Excel Columns
Instead of manually resizing a large number of Excel columns, you can click in a column header and then press and hold Ctrl while you click other column headers. After you've selected all the columns you want to size, you can choose Format, Column, Width and set your new width. You could also choose Format, Column, AutoFit Selection to make sure everything fits in the selected columns.

Quickly Copying Worksheets in Excel
If you need to make a copy of a worksheet, you probably already know that you can use the Move or Copy Sheet option from the Edit menu. There is a quicker way to make a copy:

1. Select the tab of the worksheet that you want to copy.
2. Hold down the Ctrl key and use the mouse to drag the worksheet tab left or right. A small plus sign should appear next to the mouse pointer.
3. Release the mouse button when the pointer is between the tabs where you want the worksheet copy to appear.

Selecting a Column in Excel
If you want to quickly select a column of your spreadsheet, there are two ways you can do it. The easiest way (if you already have your hand on the mouse) is to click on the column letter at the top of the column. For instance, if you want to select column C, you would click on the C column header. The other method works great if you are working in a particular column and your hands are on the keyboard. Press Ctrl+Space Bar and the entire column is automatically selected.

Using the AutoFill Handle in Excel
Whenever you select a cell or a range of cells, the bottom right corner always has a small black square. This is called the AutoFill Handle. If you enter the number 1, in say cell A1, then select A1 you can left click on the AutoFill Handle. Holding down the left mouse button as you drag down will copy the content of the cell down. If you hold down the Ctrl key as you drag, the number will increment.

Bring Recolored Clip Art into Word or Excel
You can recolor your clip art in PowerPoint. To bring a re-colored clip art graphic into Word or Excel select and copy the clip art in PowerPoint and then switch over to Word or Excel. Once there, go to the Edit menu and choose Paste Special. In the Paste Special window, choose (in the center of the window) to bring the clip art in as a Microsoft Clip Gallery Object, Picture or Picture (Enhanced Metafile). Any of these options will paste the clip art with the new colors. Click OK. You should now see your custom colored clip art.

Remove Unused Toolbar Buttons
If you have buttons on your toolbars which you never use, you can remove them. To remove a button from a toolbar in any Office program, hold down the Alt key and drag the button off of the toolbar. If you decide you want the toolbar back to it's original configuration, right click the toolbar, choose customize, select the relevant toolbar on the Toolbar tab and click the reset button.

Finding the End of the Worksheet in Excel
When you press Ctrl+End, Excel takes you to the bottom cell of your worksheet. This is defined as the intersection of the right-most column and the bottom row. If you delete some rows or columns in the worksheet, you would expect that Ctrl+End would still take you to the bottom cell. It does not, however. What it does is take you to the original bottom cell. For instance, if you load a worksheet for which the bottom cell is H20, and then delete three rows and one column, you would expect Ctrl+End to take you to G17. Instead, it still takes you to H20. The only way around this is to save the file. You don't have to close it, just save the file. Doing so causes Excel to recalculate the bottom cell.

Excel Workspace
You can set Excel to open a set of files and place them in the arrangement you choose. Open the needed files and arrange the windows. Then go to the File menu and choose the Save Workspace choice. A Save window will come up where you can name your workspace. (Notice that in the Type Of File box, it says Workspaces instead of Microsoft Excel Workbook. This is what makes it different from saving a workbook. Now it saves the group of workbooks as a workspace.) The next time you need to work on that particular set of files, just open the workspace you created. Excel will automatically open all files and arrange them as you had them at the time of the last workspace save. You can still edit the files separately. This just allows you to open and arrange multiple files with only one open.

Adding Data to an Excel Chart
Suppose you create a chart in an Excel worksheet that uses data in cells A1 through A3. Now, you discover that you need to add new data to your chart. Just entering data in cells B1 through B3 won't work. But, aftr you enter the data, you can select cells B1 through B3 and then move the mouse cursor over the right edge of the selected area until the cursor turns to an arrow. Then, use the mouse to drag the selection to your chart. When you release the mouse button, your new data will appear in the chart.

Freezing Excel Worksheets
When you want an Excel worksheet to scroll without scrolling the headings out of the picture, you can freeze
the cells that contain the headings. Let's say that you have headings in row A. Click cell A2 and choose Window, Freeze Panes. Now, when you scroll down, you will be able to see the headings. To unfreeze panes, choose Window, Unfreeze Panes.

Quickly Add Multiple Excel Rows
To create more rows just like it, select the row and then choose to insert additional rows above the selected row (Insert, Rows). After you insert the first row, just press F4 repeatedly until you have created the number of desired rows.

Keyboard Drawing
Excel's Drawing toolbar offers several tools for adding objects to your worksheets, including WordArt objects, AutoShapes objects, lines, arrows, text boxes, and ovals. Here are two keyboard tips for drawing objects.

If you want the object you're drawing to line up along existing worksheet gridlines, click the drawing object, but before you click in the sheet and start dragging to draw it, press and hold the [Alt] key. As you move the mouse to draw, you'll notice that the object's edges snap to the nearest gridline. You'll get a perfect fit.

You can also customize how Excel creates the drawing object. By default, when you click and drag to draw an object, Excel creates the object by beginning where you clicked and extending it toward the direction in which you move the mouse. If you'd rather Excel draw the object inside out, click the object on the Drawing toolbar, then press and hold the [Ctrl] key before you click and drag.

Select a Column or Row in Excel
This one allows you to select an entire row or column with just two keys - and no mouse. Ctrl + Spacebar will select the column of the cell currently selected. Shift + Spacebar will select the row of the cell currently selected. You can then combine this information with the Shift + Arrow keys to select several rows or columns.

Changing Excel Formats
Changing cell formats in Excel is a common procedure. The standard method of opening the Format Cells dialog box is to select the cell, or range of cells, that you want to modify and then choose Format, Cells. If you'd prefer to open the Format Cells dialog box with the keyboard, select the range of cells and then press Ctrl + 1.

Using the Shrink To Fit Command in Excel
If you have ever tried to fit a really long word or even a couple of words into a cell and still try to make them legible, and not microscopic, you'll want to try the Shrink to Fit Command. Begin by selecting the cell or cells that you want to shrink. Next, choose Format, Cells from the menu bar. Then click on the Alignment tab. Select the Shrink To Fit check box and click OK. The selected text will fit into the cells. Even if you change the text in the cell, the Shrink To Fit feature is still applied to the cell.

AutoFit Excel Data
To AutoFit data in a column you can select the column, go to Format, Column, and choose AutoFit Selection. What this does is make the column just wide enough to fit the longest piece of data in the selection. A faster way to do this is to double-click the column heading. Move your mouse pointer over the column heading to the right-side border. (The little vertical line between the letters.) Your pointer will become a double-sided arrow. Then double-click. The column will resize to fit the data. This also works with multiple columns. Select the columns to change and double-click on the right side of one of the selected column headers.

Show Formulas in Excel Cells
When you need to display formulas (instead of values) simple use Ctrl + ~ (the key above the tab-no shift needed). All of your calculated values should instantly change into their formulas, bypassing the need for the Tools menu. To change formulas back to the calculated values simply repeat the key combination of Ctrl + ~.

Display Keyboard Shortcuts with Screentips
If you move the mouse pointer over a toolbar button and leave it there for a few seconds, you get a ScreenTip that describes what job that button performs. If you'd like to have the ScreenTip also display the shortcut keys, in Word you choose View, Toolbars, Customize. When the Customize dialog box opens, click the Options tab. Then select the Show Shortcut Keys In ScreenTips check box and click Close to save your changes. As a test, move the mouse pointer over the Bold button. The ScreenTip will display Bold (Ctrl+B). When you make this change in Word, it will also apply to the other Office programs.

Quickly Access Recently Used File List
In the MS Office programs there is a list of recently used files at the bottom of the File menu. Most people access these with the mouse. You can also access these files with the keyboard. First press Alt + F (to activate the File menu). Then press the number listed beside the file name in the list. The file will open ready for use.

Hiding Data in Excel Worksheets
Let's say you have some data in cell C5 you would like to hide. Click cell C5 to select it, then choose Format, Cells. When the Format Cells dialog box opens, click the Numbers tab (if necessary) and then select Custom from the Category list. Now double-click the Type entry box and type three semicolons: ;;; Click OK to close the dialog box and accept your new formatting. At this point, the data in cell C5 disappears. It's still there and will work in calculations, but it isn't visible. If you need to check the data, just click the blank cell and the contents appear in the Formula entry box. This only works with numbers.

Counting Columns or Rows in Excel
If you ever need to count the number of selected columns or rows (maybe to set formatting, borders, etc.), there is an easy way to do it besides visually counting them. You can get an instant count while you're selecting the columns or rows. Select four columns by clicking and dragging. You will see a little box that appears above the column letters that says 4C. That means that 4 columns are currently selected. For rows you'll see a number followed by an R.

Office 2000 Personalized Menus (revisited)
One of the new features in the Office 2000 programs is the personalized menu. As you work, Excel remembers the commands you use most and displays those, while hiding commands you rarely use. In other words, the personalized menus adapt to your work habits. The hidden commands are still available--just click the double arrow at the bottom of the menu to see them. (If there's no double arrow, then all of that menu's commands are already displayed.) If you prefer, you can wait just a few seconds for the menu to expand.

You may find this new feature annoying after a while since it creates a second, unnecessary click if you want to access a hidden menu. Fortunately, you can turn off this feature. Choose View, Toolbars, then select Customize. In the Customize dialog box, click the Options tab and deselect the Menus Show Recently Used Commands First option in the Personalized Menus And Toolbars section. If you want to see all of the buttons on your Toolbars, also deselect Standard and Formatting Toolbars Share One Row.

Some Excel Shortcuts
Ctrl + Shift + $ will apply the Currency format, with 2 decimal places and negative values in parentheses, to the currently selected cell(s).
Ctrl + Shift + % will apply the Percentage format, with no decimal places, to the currently selected cell(s).
Ctrl + Shift + ! will apply the Number format, with 2 decimal places, commas for 1000 separators and - for negative values, to the currently selected cell(s).
Ctrl + Shift + & will apply the Outline Border to the currently selected cell(s).
Ctrl + Shift + _ will remove the Outline Border to the currently selected cell(s).
Ctrl + B will toggle to apply or remove Bold formatting to the currently selected cell(s).
Ctrl + I will toggle to apply or remove Italic formatting to the currently selected cell(s).
Ctrl + U will toggle to apply or remove Underline formatting to the currently selected cell(s).
Ctrl + 5 will toggle to apply or remove Strikethrough formatting to the currently selected cell(s).

Quickly Hide Columns or Rows in Excel
Select a cell in each row or column you wish to hide. Then use the appropriate key combination:
Ctrl + 0 (zero) will hide the column(s).
Ctrl + 9 will hide the row(s).

To unhide data, select two cells, one on either side of the hidden row(s) or column(s). Then choose the appropriate key combination:
Ctrl + Shift + ) will unhide the columns between the selected cells.
Ctrl + Shift + ( will unhide the rows between the selected cells.

Entering Duplicate Information into Multiple Cells in Excel
It is not unusual to enter the same information into multiple cells in Excel. To do this:
1. Select all the cells that will contain the information. If the cells are not contiguous, hold down the Ctrl key as you click on each cell in the set.
2. Type the information you want to enter, but don't press Enter.
3. Press Ctrl+Enter.
Every cell you selected now contains the same information.

Move Text to a New Line Within an Excel Cell
Excel is capable of storing large amounts of text data in a cell, but it may not be readable when it's in a long string. To break information within a cell onto multiple lines, 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.

Entering Data in Excel
To prevent moving to the next cell when you finish entering data, click the green check mark on the Formula Bar or press Ctrl + Enter (instead of Enter).

Sizing Text Boxes and Cells the Same
Excel allows you to create text boxes within your worksheets. You may have a need, at some point, to create a text box that is exactly the same size as a particular cell. If you only have one or two such text boxes to create, the easiest way is to follow these steps: 1) Display the Drawing toolbar; 2) Click on the Text Box tool; 3) Hold down the Alt key as you click and drag to create your text box. When you hold down the Alt key, it forces Excel to "snap" the sides of your text box to a drawing grid which just happens to match the cell boundaries in your worksheet. If you size the rows or columns, you also size the text box.

Identifying Margins in Print Preview in Excel
You probably use Print Preview to view your data in printed form before you actually print it. While you're in Print Preview, you can also see where your margins are set in relation to your data. Click the Margins button while in Print Preview, and Excel will display dotted lines to indicate all four margins. You'll also see an additional line above both the top margin and below the bottom margin. The spaces in-between these two dotted lines are the header and the footer areas.

Keep Focus on the Current Cell
By default, Excel moves the cell selector down one row when you press Enter after making an entry. You may prefer that the cell in which you're entering data remain active, so that you can easily format it. If you want, you can completely disable the behavior. To do so, choose Tools, Options from the menu bar. Then, click on the Edit tab, clear the Move Selection After Enter check box, and click OK. If you don't want to permanently change the way Excel handles the Enter key, you can use a previous shortcut. Press Ctrl + Enter after completing an entry. Excel will save your entry, but the focus remains on the current cell instead of moving to the one beneath.

Editing the Contents of a Cell in Excel
When you you need to edit something that is already in a cell, you probably click on the cell, then click into the formula bar to edit the contents. There is an easier way. Click the cell you want to edit and press the F2 key. If the cell contained a formula then you should now see it displayed in the cell and you have a cursor for editing. If the cell contained text or numeric data then you can see all the text or the data with a cursor for editing. When you finish editing, press Enter.

Multiple Format Painting
When you need to copy a format to more than one place, just double-click the Format Painter button on the toolbar. When you double-click, the mouse remains in format paint mode until you click the button again. You can format any number of words, cells, and so on in this mode. The Format Paint button appears depressed as long as you stay in it. Press Esc to turn off the function. This works in Word, Excel, PowerPoint and Publisher.