1 / 36

A few tricks to take you beyond the basics of Microsoft Office

Learn how to control and restrict data input in Excel cells to ensure accuracy and efficiency in shared worksheets. Step-by-step guide included.

archerj
Download Presentation

A few tricks to take you beyond the basics of Microsoft Office

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. A few tricks to take you beyond the basics of Microsoft Office GCPS Tech Tips Microsoft Excel: Restricting Cell Input Data.

  2. EXCEL: Restricting Cell Input Data Some of the Excel worksheets you create will be shared by other people... perhaps even used to gather responses from them. You can specify certain cells where others will enter the data you are seeking.

  3. EXCEL: Restricting Cell Input Data And you can set up your answer cells so that the data others might enter will be exactly the kind you need and in the format you want.

  4. EXCEL: Restricting Cell Input Data Controlling entries in this way is referred to as “data validation” which means you can set up Excel to reject any data that someone might try to enter in a form you don’t want. Data Validation

  5. EXCEL: Restricting Cell Input Data For example, if an answer going into the cell is not supposed to exceed a certain dollar amount, you can set it to reject any entry above that amount.

  6. EXCEL: Restricting Cell Input Data Or... suppose you want entries to be only in text... no numerals... you can set it up just that way.

  7. EXCEL: Restricting Cell Input Data Or... suppose the entries have to be within a certain range of numbers... you can set it up that way. That could be helpful if you are searching for respondents in a specific age range... or who have a certain grade point average.

  8. EXCEL: Restricting Cell Input Data Here are the easy steps required to restrict the kind of data that can be entered into a cell. First, select the cell or cells you want to work on.

  9. EXCEL: Restricting Cell Input Data Go up to the top of your Excel screen and click on “Data.”

  10. EXCEL: Restricting Cell Input Data That will drop down a list of options.

  11. EXCEL: Restricting Cell Input Data Click the option called “Validation.”

  12. EXCEL: Restricting Cell Input Data That will open up a box called “Data Validation.”

  13. EXCEL: Restricting Cell Input Data The default type of data allowed is “Any value”...

  14. EXCEL: Restricting Cell Input Data but you can click on this little pointer to drop down a list of other kinds of data to be entered.

  15. EXCEL: Restricting Cell Input Data You can restrict entries into the cell to “Whole Numbers”...

  16. EXCEL: Restricting Cell Input Data a “decimal” entry...

  17. EXCEL: Restricting Cell Input Data a “List” of values...

  18. EXCEL: Restricting Cell Input Data or a “Date” or a “Time.”

  19. EXCEL: Restricting Cell Input Data You can set the “Text Length” of an entry.

  20. EXCEL: Restricting Cell Input Data Or you can enter a “Custom” formula that returns some logical value... maybe like true or false. When you click on one of these types of restrictions,

  21. EXCEL: Restricting Cell Input Data the dialog box changes its blanks to ask the specific questions you need to answer to set up that kind of restriction.

  22. EXCEL: Restricting Cell Input Data In the blank labeled “Source,” you can enter the formula that refers Excel to the location where you have entered the possible answers that can be entered into the cell.

  23. EXCEL: Restricting Cell Input Data When you click “OK”... Excel assigns your chosen kind of data validation to that cell or group of cells.

  24. EXCEL: Restricting Cell Input Data In this example, the user can select the input data from a list of two available choices... “True” or “False.”

  25. EXCEL: Restricting Cell Input Data The tabs at the top of the dialog box give you the opportunity to create boxes that will pop up when a user selects an answer cell...

  26. EXCEL: Restricting Cell Input Data pop-up boxes that will advise them in advance of the type of data that must be entered.

  27. EXCEL: Restricting Cell Input Data To do that, you click the “Input Message” tab at the top of the dialog box...

  28. EXCEL: Restricting Cell Input Data type in a “Title” and a “Message”...

  29. EXCEL: Restricting Cell Input Data and check the box that will cause the message to be shown as soon as the cell is selected.

  30. EXCEL: Restricting Cell Input Data Another tab at the top of the dialog box lets you create an error message...

  31. EXCEL: Restricting Cell Input Data that will pop up whenever somebody enters the wrong kind of data.

  32. EXCEL: Restricting Cell Input Data To make up a pop-up error message, you just click the “Error Alert” tab.

  33. EXCEL: Restricting Cell Input Data A box opens where you can type in a “Title” and a “Message”...

  34. EXCEL: Restricting Cell Input Data So that error message will pop up whenever someone enters the wrong type of data.

  35. EXCEL: Restricting Cell Input Data Data Validation is a useful tool. With a little practice, you can quickly and easily set up much more efficient Excel forms that deliver data in the form you need it. Data Validation Data Validation Data Validation Data Validation

  36. EXCEL: Restricting Cell Input Data That’s all there is to it. Now, if you would like a copy of the step-by-step directions for restricting cell input data to Excel worksheets, just print this last page. Have fun restricting!

More Related