For business users of Microsoft Excel Free guides and templates
Home >  Excel Help Questions > 

Excel Data Entry

How to Tab From Cell to Cell in an Excel Worksheet Form

In Word, we can set up custom forms, and then tab through them to enter our data. Here's how to do the same thing in spreadsheet cells.

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

As a general rule, it's a good idea to find a way to import data from some other source, rather than entering the data manually. But when you do have to manually enter your data, using a spreadsheet form is a great approach.

Charley Kyd can personally help you to apply the Excel methods in this article to your own organization.

Click here to learn more.
In a new worksheet, let's simulate a form. To do so, first select any four discontiguous cells...that is, four cells that don't touch each other.

For example, select some cell near the top right corner of your sheet. Hold down your Ctrl key then click on a cell several rows below it. With Ctrl still down, click on a cell several rows below that. Then with Ctrl still down, click on some other cell near the bottom.

These are the cells where data will be entered in our simulated form.

With these four cells selected, assign a yellow fill color to the cell. The color won't affect the solution; they merely allow us to see what's happening. And I always use yellow to show where data will be entered. You can use any color you want.

To assign the color, use the Fill Color paint bucket icon in the Home, Font group.

With these four cells still selected, press Ctrl+1 to launch the Format Cells dialog. In the Protection tab, you'll see that the cells are locked. Unlock them by clicking the check box to uncheck them. Then choose OK.

The last step is to protect your worksheet. Choose Review, Changes, Protect Sheet. In the Protect Sheet dialog, uncheck Select Locked Cells. Then choose OK. (In Excel 2003, choose Tools, Protection, Protect Sheet. Then choose OK.)

Now, when you press Tab, Excel will jump from colored cell to colored cell, working from top to bottom, then jumping to the top again. That is, Excel tabs among the cells in your simulated form.

To return to the original condition, choose Review, Changes, Unprotect Sheet. (In Excel 2003, choose Tools, Protection, Unprotect Sheet.)

By the way, you might take the time explore the Protect Sheet dialog. It allows you to specify how seriously you want to protect your locked cells. These settings might be useful, depending on what your are trying to achieve.


Free Excel Dashboards

Charley's SwipeFile charts