Microsoft Office 2003 Tips and Tweaks (p2)

Unless you’re kin to Rip Van Winkle, you probably know that Microsoft Office 2003 is one of the best productivity suites of all time. But it doesn’t help to know that the suite’s programs are chock-full of features if you don’t understand how to use them. That’s where we can help. We’ve gleaned some great tips for working with Office and are ready to share them. Here are a few of our favorites:


Selecting A Range (Excel)

If you’ve worked with Excel for very long, you’re probably already familiar with the concept of a range: a single cell or group of cells that you select so that you can format it or use it in a formula. What you may not know is that there are a variety of ways to select cells for a range. Of course, you can always drag across the cells to select a contiguous range, but it’s often tricky to select the exact cells you want with this technique. Instead, try this method: Click the first cell in the range (such as the upper-left cell), press and hold the SHIFT key, and then click the last cell in the range (such as the lower-right cell). On the other hand, if you want to select multiple, nonadjacent cells (a noncontiguous range), press CTRL while clicking the cells. You can also select an entire row or column by clicking the appropriate row or column heading (the gray rectangular box with the column’s or row’s title). If you prefer to use the keyboard, you can press CTRL-SPACEBAR to select a column or SHIFT-SPACEBAR to select a row. To quickly select an entire worksheet, click the Select All button (the gray button located at the intersection of the column and row headings) or press CTRL-A.

Find More Functions & Features (Excel)

Excel includes hundreds of worksheet functions. However, you can access even more analytical tools and functions by loading the Analysis ToolPak. This add-in program comes with Microsoft Office but isn’t installed by default. To install the Analysis ToolPak, close out of Excel and then click Start and then Control Panel. Double-click Add Or Remove Programs. On the list in the Add Or Remove Programs dialog box, click Microsoft Office 2003. Click Change to activate the Microsoft Office 2003 Setup dialog box. In the Setup dialog box, choose Add Or Remove Features and then click Next. Make sure the box for Excel is selected on the next page; also check the box for Choose Advanced Customization Of Applications. Click Next and then click the plus symbol (+) next to Excel to display associated options. Click the Add-Ins + to expand the list and then click the icon next to Analysis ToolPak. Choose Run From My Computer and then click Update. When the installation is complete, close all dialog boxes. At this point the Analysis ToolPak is installed on the computer but isn’t in memory. To do this, choose Tools and then Add-Ins In Excel. Check the box for Analysis ToolPak and then click OK.

Find Working Days (Excel)

Another useful DATE function is NETWORKDAYS, which calculates how many business days fall between two dates. This function, which you can only access if you’ve installed the Analysis ToolPak, finds the difference between two dates, excluding weekends. Optionally, you can have Excel exclude holidays, as well. However, because Excel doesn’t have a clue which dates you consider holidays, you have to develop a list in your worksheet with this information. The basic syntax for the NETWORKDAYS function is: =NETWORKDAYS(Start Date,End Date,Holidays). With that in mind, let’s imagine that you want to find how many business days there are in January. First, enter the dates for January holidays as a list in A2:A3. Next, enter the start (1/1/2006) and ending (1/31/2006) dates in cells A9 and B9. Finally, click in the cell where you want to place the function (C9) and then type the function =NETWORKDAYS(A9,B9,A2:A3). If you set up the worksheet correctly, Excel should calculate 21 working days in January 2006.

Getting Around A Selected Range

You can speed up data entry by preselecting a range and then moving within it. Unfortunately, it’s easy to accidentally deselect a range as you move the mouse pointer within it. To prevent this problem, try these techniques: Move the cell pointer downward through a selected range by pressing ENTER; move the pointer upward (or left) by pressing SHIFT-ENTER. To move from left to right within a range, press TAB; to move from right to left, press SHIFT-TAB. Finally, to move from corner to corner within a selected, contiguous range, press CTRL and the period (.) key.

Get A Date

Excel includes a number of functions that can help you run calculations related to dates. For example, you can use the DATE function to determine the number of days you’ve been alive or how many days have elapsed since you began your present job. Excel does this by assigning a sequential serial number to each day, starting with 1900. The program can then use this underlying serial number in calculations, even if Excel displays the serial number in a cell as a date. The DATE function’s syntax is =DATE(year,month,day). Let’s look at an example. To calculate the number of days you’ve been alive, click cell A1 and then enter the current date, such as =DATE(2006,04,30). In A2 use the DATE function to enter the exact day you were born, such as =DATE(1965,03,25). Enter =A1-A2 in cell A3. Finally, apply the General format to the data in cell A3. To do this, choose Format and Cells and then click the Number tab. Click General on the Category list and then click OK. The number of days you’ve been around will display in cell A3.

AutoCorrect Options

Using AutoCorrect in Microsoft Excel can help you save time, especially if you have to enter a great deal of redundant information. To change your AutoCorrect options, click the Tools pull-down menu, and click AutoCorrect Options. In the dialog box that opens, the first tab, AutoCorrect, lists a number of different formatting functions Excel can automatically perform. For example, Excel can fix accidental use of the CAPS LOCK key or capitalize the first letter of a sentence. Excel also has a vast bank of commonly misspelled words along with words it will use to replace them with, such as automatically replacing “abscence” with “absence.” You can add your own words by typing a word in the Replace field and its correction in the With field and clicking the Add button.

Easily Reorder Slides (PowerPoint)

If your experience with PowerPoint is like most people’s, you will probably need to occasionally rearrange slides in your presentation. For example, you may decide that a different sequence is more effective or that you need to include additional information.
To rearrange slides in Normal view, click the Slides tab (in the tabbed pane on the left side of the application window) and then drag a slide up or down to its new position. When a horizontal line displays where you want to place the slide, release the mouse. PowerPoint drops the slide into its new position and automatically renumbers all presentation slides to reflect the new order. You can also use the Slide Sorter view to rearrange slides. To do so, choose View and Slide Sorter. Drag a slide between two other slides; when a vertical line displays between slides, release the mouse to drop the slide into position. You can also select multiple slides by pressing CTRL while clicking them. After you select the slides, you can move them as a group by clicking and dragging any slide in the group.

Get A Better View (PowerPoint)

Sure you can use options on the View menu to quickly switch between PowerPoint’s Normal and Slide Sorter views, but the program includes other methods, too. For example, you can click the Normal View or Slide Sorter View buttons in the lower-left corner of the application window. You can also doubleclick a slide in Slide Sorter view to quickly switch to Normal view.

MO2003 Tips and Tweaks part 1