Susan Harkins describes three ways to ease the data input process in Excel.
By input range, I mean the contiguous area of the sheet where you're entering new records.
On the fly
If your data entry task is a one-time task, the easiest method is to select the data input area and let Excel guide you through it. For instance, suppose you want to enter records for February and March into the sheet below. With only two records to enter, you could just tough it out. You could also select the input area - A3:E4 - and start entering values in row 3. After entering a value in E3 and pressing Tab, Excel selects A4 (not F3). Excel walks you right through the input range.
You must train users to press Tab and not Enter, but it's a small trade-off. It's easy to remember a trick that helps you work more efficiently.
Use a Table object
The Table object is new to Excel 2007 and it has a few limitations, but it certainly aids the data entry process. Once you convert a range to a Table, Excel guides the user through the cells, similarly to the selection process described above. To illustrate, we'll convert a small input range to a Table and then add a new record, as follows:
- Click anywhere inside the data range.
- Click the Data tab.
- Click Table in the Tables group.
- When prompted, respond to the header question appropriately, and click OK. Excel converts the range into a Table object.
- To enter a new record, select the last cell to the right in the last record (E4) and press Tab. Excel will automatically extend the table to the next row and position the cursor in the first cell of this new row.
- Enter a new record, using Tab to move one cell to the right for each new field value. When you reach the last cell and press Tab, Excel will extend the table and select the first cell in the new row.
Enabling a sheet's protection is another easy way to guide users through the data entry process. Using the sample sheet shown below, let's unlock the two input cells, B1:B2 and then enable protection:
- Select B1:B2.
- Right-click the selection and choose Format Cells.
- Click the Protection tab.
- Uncheck the Locked option.
- Click OK.
- Click the Review tab. In Excel 2003, choose Protection from the Tools menu, select Protect sheet, and skip to #8.
- Click the Protect Sheet option in the Changes group.
- In the resulting dialog, enter a password and check the Select Unlocked Cells.
- Click OK and confirm the password.
(The example worksheet uses a COUNTIFS() function, which is new to Excel 2007. If you're using the xls version, it'll return an error, but you can still use it to enable protection.)
After enabling protection, the only cells you can select and alter are B1:B2. This technique is impressive, especially when input ranges aren't contiguous. Pressing Tab cycles through all of the unlocked cells, working as an input guide for the user. When using this method, you also limit access to all cells but the input cells, which you might not want.
If you really want to ease the data entry burden, consider creating a data entry form. For more information on data entry forms and more data entry tips, read Use Excel's built-in features to simplify data entry.