ENSURE STANDARISATION OF DATA IN EXCEL

0

Hi

I am studying bookeeping and I have this question that I have been struggling with.

I have to findĀ  " formula/function or method to ensure standardisation of input, security and accuracy of data"

I am thinking to ensure standardisation of input is to make sure users use same format, same way of entering dates etc.

Security would be to password protect, lock cells etc.

Accuracy would be to use trace dependants, error checking.

Any ideas would be much appreciated.

Answer
Discuss

Discussion

Please don't put the title in all caps.
don (rep: 1705) Mar 28, '18 at 5:41 am
Add to Discussion

Answers

0
Selected Answer

Basic features to use for this:

  • Data Validation
    • Use data validation on unlocked cells that the user will enter data into.
  • Protecting Worksheets
    • Leave only the cells that the user will put data into unlocked.
  • Protecting Workbooks
    • Prevents unauthorized users from easily accessing the file.

Trace dependents is something that you would do while troubleshooting or building the worksheet and it won't prevent a user from doing anything.

Discuss
0

You have three big questions to which, I presume, you would like one easy answer which I can give in two words, "No way!" But I will answer your first question here and, if that satisfies you, I may answer another of your questions in another thread.

To ensure standardisation of input, Excel offers Data Validation. On the ribbon's Data tab, click on Data Validation in the Data Tools menu. There you can set rules, for each cell, which kind of data may be entered, such as a date, or a number (or a range of numbers) or one of a number of words or phrases to choose from etc. When the user enters something wrong an immediate error message is issued while the entry is rejected. You can specify which error message you want to be shown and even an instruction to be displayed before any entry is made.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login