By Paul Cornell

Learn how to enter repetitive data quickly, share a workbook, restrict the value of data in certain cells, and more, with these five tips for Microsoft Excel 2002 power users.

Applies to
Microsoft Excel 2002

---

See all Power User columns
See all columns

---

Does your job require you to track a fair amount of data? If so, you probably spend a fair amount of time working with Microsoft Excel. There are many tips and shortcuts you can use in Excel to make working with data even faster. In this article, learn how to improve your productivity in Excel 2002 by:

• Entering repetitive data quickly without typing.
• Calculating the number of days between two dates.
• Sharing a workbook to enable quicker data updates by others.
• Reducing data-entry errors by restricting the cell data input to certain values.
• Quickly summing values on a worksheet without typing formulas in cells.

Enter repetitive data quickly

If you need to type repetitive data such as some numbers or dates into a worksheet, you can use the AutoFill feature in Excel to help you. Here's an exercise you can try to practice entering repetitive data quickly.

First, quickly fill a group of cells with the names of the calendar months:

1. Start Excel. A new, blank workbook appears.
2. In cell A1, type January and then press ENTER.
3. Click anywhere inside cell A1, and rest the mouse pointer on the square at the lower right-hand corner of cell A1. The mouse pointer changes into a plus sign (+).
4. Press and hold the right (alternate) mouse button, drag the mouse pointer to cell A12, and release the right (alternate) mouse button. A menu appears.
5. Click Fill Months. The names of the months February, March, and so on appear in cells A2 through A12.

Next, quickly fill in several cells with the same value:

1. In cell B1, type 1999 and press ENTER.
2. Click anywhere inside of cell B1 and rest the mouse pointer on the square at the lower right-hand corner of cell B1. The mouse pointer changes into a plus sign (+).
3. Press and hold the right (alternate) mouse button, drag the mouse pointer to cell B12, and release the right (alternate) mouse button. A menu appears.
4. Click Copy Cells. The value 1999 appears in cells B2 though B12.

Finally, quickly fill in several cells with a range of numbers:

1. In cell C1, type 10000.
2. Click anywhere inside of cell C1 and rest the mouse pointer on the square at the lower right-hand corner of cell C1. The mouse pointer changes into a plus sign (+).
3. Press and hold the right (alternate) mouse button, drag the mouse pointer to cell C12, and release the right (alternate) mouse button. A menu appears.
4. Click Series.
5. In the Step value box, type 125 and click OK. The Series dialog box disappears, the value 10125 appears in cell C2, and the number increases by 125 in each cell in column C up to an ending value of 11375 in cell C12.

Calculate the number of days between two dates

Because I write Web articles that adhere to strict publishing schedules, I frequently need to know the number of days between two dates, such as the number of days between a technical review deadline and a grammar edit deadline. Here's an easy exercise you can try to display the number of days between two dates:

1. Start Excel. A new, blank workbook appears.
2. In cell A1, type 1 June 2002, and press ENTER.
3. In cell B1, type 15 April 2001, and press ENTER.
4. In cell C1, type =A1-B1, and press ENTER.
5. Right-click cell C1, and click Format Cells.
6. On the Number tab, in the Category list, select General, and then click OK.

Cell C1 displays the number of days between June 1, 2002, and April 15, 2001, which is 412.

Share a workbook

Several years ago, I asked a coworker to help me type numbers into a worksheet. I typed a few numbers, and then I saved and closed the workbook. I had to wait for the coworker to type his numbers and then save and close the workbook before I could take another turn. Now I am a little wiser; I learned that my coworker and I could have typed numbers into the worksheet at the same time. Here's how:

1. With an Excel workbook open, on the Tools menu, click Share Workbook.
2. On the Editing tab, select Allow changes by more than one user at the same time. This also allows workbook merging.
3. On the Advanced tab, select the Automatically every option, and then click OK.

When you save the workbook, the workbook is now shared with others who have access to it, and changes to the workbook are updated every 15 minutes by default.

Restrict cell data input to certain values

Excel allows you to restrict data that is typed into cells to certain values. This is especially helpful for reducing typos when you are absent-mindedly typing a lot of data values.

First, assign a validation condition to a group of cells:

1. Start Excel. A new, blank workbook appears.
2. Select cells A1 through B10, inclusive.
3. On the Data menu, click Validation.
4. On the Settings tab, in the Allow list, select Whole number.
5. In the Data list, select less than.
6. In the Maximum list, type 100.
7. On the Input Message tab, in the Title box, type Testing Validation.
8. In the Input message box, type Type a number less than 100.
9. On the Error Alert tab, in the Title box, type Failed Validation.
10. In the Error message box, type You must type a number less than 100.
11. Click OK to assign the validation condition and close the Data Validation dialog box.

Next, type numbers in specific cells to see how validation works:

1. In cell D1, type 105 and press ENTER. No validation message is displayed because cell D1 does not contain any validation conditions.
2. In cell A1, type 95 and press ENTER. No validation message is displayed because the value of cell A1 is less than 100.
3. In cell B10, type 105 and press ENTER. A validation message appears, stating that you must type a number less than 100.
4. Click Retry to try typing a different value.
5. In cell B10, type 99 and press ENTER. No validation message appears because the value of cell B10 is less than 100.

Now remove the validation condition:

1. Select cells A1 through B10, inclusive.
2. On the Data menu, click Validation.
3. Click Clear All. All validation conditions are removed from cells A1 through B10.
4. Click OK to close the Data Validation dialog box.

Finally, type numbers in specific cells to make sure that the validation condition was removed:

• In cell A1, type 105 and press ENTER. No validation message is displayed because cell A1 does not contain any validation conditions.

Quickly sum cell values without modifying a worksheet

I usually don't type formulas in worksheet cells unless I need to. Fortunately, there's a quick way to display summary formulas in the Excel status bar (the thin bar between an Excel workbook and the Start menu):

1. Start Excel. A new, blank workbook appears.
2. In cells A1 through A10, type a number in each cell.
3. If the status bar is not visible, on the Tools menu, click Options. Then, on the View tab, select the Status bar check box.
4. Select cells A1 through A10, inclusive.
5. Right-click anywhere in the status bar and click Sum in the list that appears.

The sum of cells A1 through A10 appears in the status bar to the right, and the layout of the original worksheet is not modified.

Keep sending that e-mail!

We look forward to receiving your e-mail messages at pwruser@microsoft.com. We really want this to be your column, so please send us your comments and favorite handcrafted Office solutions. Remember, we will not be able to feature every Office solution that we receive, we will not have the time to respond to all of your e-mail, and we are not technical support representatives. But we may feature your solution in an upcoming column.

---

About the author

Paul Cornell works for the Office Help team. In addition to writing the Office Power User Corner column, Paul contributes to the Office Talk column on the Microsoft Developer Network (MSDN). He is the author of the book Accessing and Analyzing Data with Microsoft Excel.

If you like this column and want to hear about more fun and useful Office offerings, sign up for our newsletter.

See all Power User columns
See all columns

---