Microsoft

Three ways to guide users through an Excel input range

Susan Harkins describes three ways to ease the data input process in Excel.
Many users input new records. If they do so often, their number one complaint is positioning the cursor at the beginning of the next record. It's a nuisance whether you're entering a few records or dozens. There are several ways to accommodate data entry and today, we'll discuss a three codeless methods.

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:

  1. Click anywhere inside the data range.
  2. Click the Data tab.
  3. Click Table in the Tables group.
  4. When prompted, respond to the header question appropriately, and click OK. Excel converts the range into a Table object.
  5. 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.
  6. 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.

Protection

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:

  1. Select B1:B2.
  2. Right-click the selection and choose Format Cells.
  3. Click the Protection tab.
  4. Uncheck the Locked option.
  5. Click OK.
  6. Click the Review tab. In Excel 2003, choose Protection from the Tools menu, select Protect sheet, and skip to #8.
  7. Click the Protect Sheet option in the Changes group.
  8. In the resulting dialog, enter a password and check the Select Unlocked Cells.
  9. 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.

Demonstration Excel files are available for download.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

3 comments
McGnity
McGnity

Hello Susan, As always your tips are valuable. Concerning to this topic I have a question that I hope you can answer. I use tables very often. Recently I needed to share a worksheet where I have some columns with validation and calculations based on data from other columns in same line (something month and quarter based on data). I want to allow user to add new lines to this table. Excel copies rules from line above and this is works fine. In don’t want to allow users to override formulas but to input new lines. When I protect worksheet user can not add new lines! Do you know how to deal with this? Thanks

Marshwiggle
Marshwiggle

I have a worksheet to which I often want to add new lines, w/ selective copying of formulas, formatting, etc. for certain columns. To do that, I created a User Control (button) and attached a VBA macro that will prompt me w/ an Input Box for how many rows to add, then add them w/ the proper formatting, etc. If you are familiar w/ recording and modifying macros, it should be relatively easy to create something similar for your purpose that will first unprotect the sheet, then re-protect it after the rows have been added.

ssharkins
ssharkins

You can't insert new rows into a Table object in a protected sheet.

Editor's Picks