Open multiple Word 2003 documents in one window
Thursday, March 26, 2009 12:44 PM
Consolidate and streamline your Word document display to avoid the clutter of individual application windows.
Microsoft Word
Open multiple Word 2003 documents in one window
Unlike its Office 2003 counterparts Excel and PowerPoint, Word 2003 does not let you rearrange document windows within a single program window.
Unless you change the default view option, each Word documents exists in its own window with its own menus and toolbars.
Consequently, when you use Arrange All to compare three documents on the screen at once, it looks like Figure A.
Figure A
To remove the screen clutter, follow these steps:
- Go to Tools | Options.
- Click the View tab, then deselect the Windows In Taskbar check box (Figure B) and click OK.
Figure B
Now all three documents will display without the clutter, as shown in Figure C.
Figure C
Microsoft Access
Instead of queries, use the Access 2007 PivotTable view
With Access 2007, you no longer need to build a query to create a pivot table. You can create one straight from a table by using PivotTable view. For example, suppose you want to know how many orders were shipped each quarter by country. Follow these steps:
- Open the table, go to the Views group, click the arrow, and select Pivot table.
- Click the Field List button, if the field list is not visible (Figure A).
- Click and drag the ShipCountry field to the row area (Figure B).
- Click Order Data By Month in the field list and then click and drag Quarters to the row area (Figure C).
Figure A
Figure B
Figure C
- Click Order ID in the field list and drag it to the filter area.
- Right-click the filter area, point to AutoCalc, and then select Count (Figure D).
Figure D
- Click Count Of Order ID under Totals in the field list.
- Click the drop-down arrow of the Pivot Table areas list and select Data area.
- Click the Add To button (Figure E).
- Click anywhere in the Qtr column and drag it to the row area (Figure F).
Figure E
Figure F
You can analyze the data further by applying filters to the pivot table. For example, by clicking the drop-down arrow of the ShipCountry field, you can limit the results to a specific country or countries. No additional queries are required.
Microsoft Excel
Let Access enter today's date automatically
Chances are, many of your users spend at least some of their time entering data into Excel worksheets. The easier you make the task, the more users will enter accurate data. You can create user forms and write VBA to help the process along. But before you do, review a few of the built-in features that ease the data entry burden. Excel's list, AutoComplete, and data validation features will reduce keystrokes and prevent errors.
Use lists to reduce keystrokes
One of the simplest ways to control data entry is to let Excel enter as much of the data as possible using the list feature. Lists reduce keystrokes and typos. Creating a list is simple enough, and you can work with an existing worksheet or create a new one. The only requirement is that each list (column) heading be unique. If there are no headers, Excel will create generic ones.
To demonstrate the process, we've imported the sample Access database, Northwind. Here are the steps for creating a list:
- Select any cell inside the worksheet for which you want to define a list.
- Choose List from the Data menu and then select Create List. Or press [Ctrl]+L. Excel will display the Create List dialog box and display the range for the worksheet, as shown in Figure A. (If the range isn't correct, check for a blank row in the worksheet. Excel's list feature can accommodate blank cells, but Excel interprets a blank row as the end of the active data.) If necessary, select the My List Has Headers option. (Usually, it's selected by default.)
Figure A
Excel intuitively selects the list range, which usually consists of multiple columns.
- Click OK, and Excel will create the list shown in Figure B by adding drop-down controls to each header cell. (The arrows are an AutoFilter feature.)
Figure B
The lists drop-down arrows let you quickly filter records by existing values.
To use the list to enter data, navigate to the bottom of the worksheet, where an asterisk character (*) serves as a placeholder for the new record row. That's where you'll enter the next record. As you enter data, Excel searches the existing list (column) items, looking for an item that uniquely matches the characters you enter. When it finds a match, the AutoComplete feature finishes the entry. For instance, Figure C shows what happens when you enter the characters Mo into the ProductName cell in the new record row. Excel completes the entry — Mozzarella di Giovanni. To accept the completed item, press Enter; to reject it, just keep typing.
Figure C
Excel's AutoComplete feature can enter data for you.
When you reach the end of the new record, Excel is smart enough to know you've completed the record. When you press Enter, Excel selects the first cell in the newly inserted record row -- this is one of the few times Excel ignores the cursor movement setting (see the next section). If AutoComplete doesn't work, someone has probably disabled it. To check, choose Options from the Tools menu and click the Edit tab. The Enable AutoComplete For Cell Values option must be checked for AutoComplete to work.
Control cursor movement
When inserting data, pressing Enter moves the cursor down one cell by default. When entering new records, that behavior can be counterproductive. Most likely, users will want to complete each record by moving to the right. Fortunately, you can change the cursor's movement pattern. First, choose Options from the Tools menu. Then, click the Edit tab and do one of these things:
- Choose Move Selection After Enter and then select a direction.
- Clear the Move Selection After Enter check box to inhibit any movement when pressing Enter.
Providing efficient data entry methods is great, but validating data is also important. You can control both by restricting data entry to a specific list, thereby limiting possible entries. First, you need a list like the one shown in Figure D. This list identifies all the possible categories for the worksheet — each record will have a category value and it will be restricted to the items in this list. In other words, users will be allowed to enter only those items in this list.
Figure D
Enter only the items you want the list to store.
Be sure to create this list in an out-of-the-way spot. If you delete it or accidentally write over it, the restricted list will stop working. However, the list items must reside in the same sheet as the restricted list or you must assign a range name to the list items.
With the list in place (see steps 1 and 2 in the section "Use lists to reduce keystrokes"), select the list you want to restrict. In this case, that's column D, or cells D2:D80. With the list selected, you're ready to enable data validation as follows:
- Choose Validation from the Data menu to display the Data Validation dialog box.
- Click the Settings tab.
- Choose List from the Allow drop-down list.
- Use the point-and-click tool (to the right of the Source control) to select the original list. In this case, that's cells M2:M9, as shown in Figure E. Or enter a range name, including an equals sign. Click OK to return to the worksheet.
Figure E
Identify the list of items that determine the items the list will store.
Now, go to the new record row and click the category cell (column D). As you can see in Figure F, the list (which you can see in Figure D) is available for data entry. (If you imported the example data from Northwind, as we did, the original numeric values will generate an error once you enable data validation. Simply delete the imported values.)
Figure F
Use the drop-down list to enter a category for each record.
Not only is the list available to facilitate data entry, the feature rejects any entry that's not in the list. Excel displays the error shown in Figure G if you try to enter invalid data (an item not in the list).
Figure G
Excel rejects invalid entries.
This feature doesn't force users to use the drop-down list; users can manually enter an existing item via the keyboard if they prefer. If you want the drop-down list in a particular order, sort the original list (Figure D). You can do so before or after enabling validation.
Create a data entry form
Lists are great for reducing the amount of data users have to re-enter. But to enter different items, users might benefit from a data entry form. Start with a list. (Again, just repeat steps 1 and 2 from the "Use lists to reduce keystrokes" section.) Then, choose Form from the Data menu. Excel will construct a data entry form, like the one shown in Figure H.
Figure H
Many users find a data entry form easier to use than entering data directly into a worksheet.
Initially, the form displays the first record in the list. Click New to display a blank form, enter data, and press Enter. Excel will transfer the data from the form to the list (worksheet) and expand the list by one record. You can use a data entry form to enter new records, edit existing data, and delete existing records.
Unfortunately, Excel's automated data entry forms don't support AutoComplete the way lists do. However, your data entry form will inherit data validation settings. In the case of the example list, the data entry form's CategoryID field will reject invalid data, just as the worksheet does.
Easy data entry
Users who spend a lot of time entering new data will appreciate your efforts to increase their productivity and accuracy. Combine Excel's list, AutoComplete, and data validation settings to reduce keystrokes and restrict entries to protect the validity of your data. Or use a quick data entry form to automate a data entry task.
There are currently no comments for this post.