Search Not Just Numbers

Thursday 22 November 2012

EXCEL TIP: How to make data entry screens easy to use

Most spreadsheets require some kind of data to be entered into cells by the user and there are a couple of simple steps you can take to make this a lot easier for the user to do.

Use colour
First of all, use colour to make it easy to see which cells require data entry. I find that using a quite light colour for the rest of the sheet and "No Fill" for the data entry cells tends to suggest that these are the ones that require input.

Take a look at the example below:

Use protection
No, this is not a safe sex tip. You can use the protection feature in Excel to guide people to those cells by stopping them being able to click in the other cells on the sheet.

Do this by highlighting the data entry cells, selecting "Format Cells", and on the Protection tab, unticking the box that says "Locked".

Then select "Protect Sheet" from the Review tab on the ribbon (or from Tools -> Protection in older versions of Excel). Then untick the box under "Allow all users of this worksheet to:" that says "Select locked cells" (it is up to you whether you use a password, but it is not necessary for this purpose).

This will stop the user from clicking in any of the other cells on the sheet, and will even allow you to use the TAB key to toggle through the data entry cells.

That's it for this week. Short but sweet!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".


  1. Hi Glen
    I often advise my students to (where appropriate) set aside a sheet into which data can be entered free from any other distraction. This makes for fast data entry and again, where appropriate, can seperate data entry from data results where confidentiality is important. I have even set up seperate workbooks simply for data entry which are "read" by the sheet(s) that use this data for calculations. Further, I advise many students to create seperate data output sheets where data to be printed can be organised into attractive printouts free from extraneous materials.

  2. Alan

    Good tips. I teach my OAP approach that splits your Excel task into 3 steps:

    Obtain the Data
    Analyse the Data
    Present the Results

    By breaking it down like this, your solution can be so much more powerful and flexible.

    It sounds like you have a similar approach.

  3. This makes for quick knowledge entry and once more, wherever acceptable, will separate knowledgedata entry entry from knowledge results wherever confidentiality is very important.