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.

  4. Wonderful facts. Thank you pertaining to providing you such a beneficial facts. Keep up the good function in addition to continue providing you much more high quality facts every now and then.Data entry

  5. This personas intended for “spring” (é, quan), “a little” (1, dian) in addition to “sweet” ( , tian) noise hublot replica sale. Besides would be the time period simple claim in addition to recall, it is rhyming program likewise leads this several text to bolster it is heavily weighed: Nongfu’s mineral water incorporates a trace connected with sweetness on account of it is one of a kind spg supplier. To sell it is baked cocoa beans rolex replica, meal supplier Heinz capitalized with it is A language like german founder’s foreign-sounding label with the tagline “Beanz Meanz Heinz. ” That resourceful by using rhyming—not a single article this extraordinary consonance connected with “z” sounds—proved hugely effective in addition to seemed to be voted by far the most hublot replica uk brand from the Promotion Arena connected with Reputation. Coined text, puns, jingles and in some cases made-up text (including Louis Vuitton’s “Epileather”) can also be means to have a tagline keep. Purely currently being simple recall isn’t plenty of. Taglines should likewise assemble constructive model ideas as a result of imparting beneficial inner thoughts. Evocative words is usually just one rolex replica strategy to build significance. Due to the fact slogans are designed intended for faster managing advertising campaigns, it is vital them to be based on the market you work in. Taglines, in contrast, should never aim to possibly be cartier replica for the possibility connected with appearing old in the long run.