Software

Quick Tip: Use calculated fields in data entry forms

Use calculated fields in Microsoft Excel

One of the easiest ways to enter information into an Excel spreadsheet is to type some column headers in row 1, go to Data | Form, and start typing in Excel's default data entry form. This form is a low-tech but user-friendly method for entering data.

What do you think of this article format?
Our editors are developing and refining new methods and formats for delivering the solutions you need. Do shorter, step-by-step pieces such as this one help you overcome the IT problems that keep you up at night? Share your thoughts with our editors; you could win a free TechRepublic book or CD of your choice. Content for this article was derived from the TechRepublic Microsoft Office e-newsletter. You can have tips like this delivered via e-mail each week by signing up here.

The problem
What your users may not know about Excel's built-in form is that it supports calculated fields inasmuch as it will copy formulas for them as it enters records. Here's how it works.

The fix
Suppose your sheet includes four labeled columns (A to D): Customer, Quantity, Unit Price, and Total Price. Before you go to Data | Form and start entering records, enter the formula +B2*C2 in cell D2 (under the label Total Price). Click any of the column labels, and go to Data | Form.

When you do, you'll notice that Excel displays four columns, but you can enter data in the first three only. That's because Excel knows you're using the entries in the Quantity and Unit Price columns to calculate the value for the entry in the Total Price column.

Excel won't display the result of that calculation dynamically. But as you add records, Excel will copy the formula in each new row in column D. If you use the form's Find Prev or Find Next buttons to navigate between records, Excel will display the current value of each calculated field as you move between records.
0 comments

Editor's Picks