Data Entry Validation - Data Science - ثاني ثانوي

Link to digital lesson Lesson 3 Data Entry Validation Data Entry Validation Data entry validation refers to any activity whose purpose is to verify that the entered data conforms to a set of approved values and to the accepted rules for the data, and that data can also be subjected to some corrective processes and actions. If the data complies with the rules it will be accepted, otherwise it will be rejected. An acceptable range of recorded air temperature values from a temperature sensor could be between -89.2 degrees Celsius (lowest temperature) and 58 degrees Celsius (highest temperature). A temperature sensor shouldn't register air temperature values of 100 °C. The appearance of such data registration in the sensor's registered results indicates a malfunction of the sensor and the value should be rejected. Data validation: www.ien.edu.sa The process of ensuring the accuracy and the quality of data, implemented by building several checks to ensure the logical consistency of input and stored data. Types of Data Entry Validation There are many types of validation that we can use to validate the entered data, and there are many applications that can help us implement data entry validation rules, for example, Microsoft Excel. Most data validation procedures will perform one or more of the following checks in order to ensure that the data is correct before storing it. Common types of data validation check are: the LookUp check, the Presence check, the Length check, the Range check, the Format check, the Type check. File Get Data Home Insert Page Layout Formulas Data Review View Help D B D Refresh All Get & Transform Data Queries & Connections [] Stocks (En... Geography... 24 42 Sort Clear Filter Reapply Advanced Text to Columns Data Types Sort & Filter 5 Comments Share What-if Forecast Outline Analysis Sheet Data Data Validation... A3 2 Month 23 456 7 8 6 10 11 12 13 2456 15 16 وزارة التعليم Ministry of Education 2024-1446 B Circle Invalid Data Clear Validation Circles LI E Weather and events in Saudi Arabia City Maximum temperature (°C) Average precipitation (in millimeters) Number of events Data Validation Settings Input Message Error Alert This is an example of data validation in Excel. Validation criteria Allow: List Data between Source: ignore blank In-cell dropdown January February March April May June July August Septem OK Cancel Apply these changes to all other cells with the same settings Clear All Figure 2.18: Example of data validation in Excel 65

Table 2.7: Validation types LookUp check Presence check Length check Range check Format check Type check The LookUp validation check helps reduce errors by using a limited list of predefined values. Instead of typing the name of an airport, an airline employee can select the airport from a list with the airport codes. Besides having fewer data entry errors, the process will also be faster. The Presence validation check makes the entry mandatory in the cell, ensuring that it is not left blank. Important data must be entered, otherwise data integrity is compromised. For example, the fields for a person's name and surname cannot be empty. The Length validation check aims to ensure that characters and symbols are entered within a specific length range. For example, ISBNs, currency symbols or ISO country codes have a fixed length of 13, 3 and 2 digits or characters, respectively. The Range validation check is used to ensure that entered numbers fall within a certain range. This includes the set of two limits: a minimum and a maximum value. If you want to enter a person's age, the system will have to accept only positive numbers and an upper limit, such as 140. Anything else beyond this range is invalid data. The Format validation check ensures that the data is entered in a predefined format. Any other format being entered in the cell will not be allowed. These checks are useful when the data fields are used for zip codes or telephone numbers. In both cases, the system will expect a very specific data format. The Type validation check will ensure that users enter the correct type of value in a given field. For example, if a data field is characterized as a number field, you will not be able to store a text value. Check Digit The check digit is used for data validation on fixed length numbers. It can be one or two redundant digits used extensively in banking applications where bank accounts and cheque identification numbers that are entered manually need a simple error detection check. An algorithm calculates the check digit from the other digits of the number entered and compares it with the digits typed. If there is a mistyped or missing digit, the system will display a data validation warning. ISBNs, ticket numbers and a wide range of barcodes include a check digit. In recent years, where data is mainly entered via scanners, cameras and automated processes, the importance of the check digit is decreasing. Ministry of Education 66 2024-1446 ISBN: 978-603-511-139-3 97860351113931 Figure 2.19: Check digit in an ISBN Check digit

Data Validation Example Let's suppose that some students are working as travel agents, and they want to create a tourist campaign for the cities of Jeddah and Riyadh, where important events will take place throughout the year. A crucial factor of the tourist campaign organization is to follow the weather conditions in each city, in order to inform tourists on how to prepare for these conditions so they can enjoy the event as much as possible. So, as travel agents, the students have already visited the site of the National Center for Meteorology (https://ncm.gov.sa) and they have downloaded temperature and precipitation data for the cities of Jeddah and Riyadh as illustrated in the table below. Now, they have to open a validation program like Excel, for example, create five columns labeled months, cities, maximum temperature in degrees Celsius, average precipitation in millimeters and Number of events, program the data validation in each column and then in the cells of each column enter the data obtained from the National Center for Meteorology. Table 2.8: Weather and events in Saudi Arabia Excel rows A B C D E Excel columns 2 Month City Maximum temperature (°C) Average precipitation (in millimeters) Number of events 3 January Jeddah 28.8 12.50 2 4 January Riyadh 20.7 14.80 5 5 February Jeddah 29.8 3.30 1 6 February Riyadh 23.7 8.30 8 7 March Jeddah 25.5 2.60 1 80 March Riyadh 28 19.90 ས 7 9 April Riyadh 33.6 23.70 1 10 May Jeddah 30.7 0.10 1 11 May Riyadh 39.5 5.60 1 12 June Jeddah 38.2 0.00 1 13 July Jeddah 39.4 0.40 2 14 September Riyadh 32.8 0.00 4 15 October Riyadh 27.5 1.50 4 16 November Jeddah 27.6 27.10 1 November Riyadh 20.4 20.00 5 17وزارة التعليم Ministry of Education 2024-1446 67

A summary of the data validation procedure that will be followed is shown in the following graphic: LookUp check Set predefined values for the column "months". Presence check Ensure that every cell has a registered value. Length check Range check The name of the registered cities must be three to six characters long. The registered temperature values must be between 20 and 45 degrees Celsius. Format check The registered precipitation values can be in decimal form. Type check Figure 2.20: Data validation steps وزارة التعليم Ministry of Education 68 2024-1446 Only a numerical value can be accepted for certain fields.

LookUp and Presence Validation Check in Excel By performing the LookUp check in the first column labeled "Month", we set all the names of the months as predefined values in order that the user can enter the data by just selecting each month from the predefined list. In the validation check, we set the program not to accept empty cells, which means cells with no value. To start Data Validation in Excel: > Go to the "weather and events" Excel spreadsheet. 1 > Select cells A3 to A17. ② > Select the Data tab. 3 > In the Data Tools group 4, click Data Validation. 5 > The Data Validation window appears. 6 To select a group of cells, you can select the first cell then use Shift + arrow keys. AutoSave Off Book1 3 Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help Binary Academy BA Comments Share Get Data Refresh All Stocks (En... Geography... 21 Clear Z↓ 4 Sort Filter Reapply Advanced Get & Transform Data Queries & Connections Data Types Sort & Filter Text to Columns What-If Forecast Outline Analysis Sheet Data Data Validation... 5 A3 fix A B C Circle Invalid Data D Clear Validation Circles ய E 1 2 Month City Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events 3 4 6 5 6 Data Validation Settings Input Message validation criteria 7 8 Allow: Any value 9 2 Data 10 between 11 12 13 14 Clear All 15 16 17 18 weather and events 1 Ready Accessibility: Good to go وزارة التعليم Ministry of Education 2024-1446 Error Alert Ignore blank Apply these changes to all other cells with the same settings OK Cancel 目 Figure 2.21: Start data validation in Excel E + +110% 4 69

To apply LookUp and Presence checks in Excel: > In the Data Validation window 1, select the Settings tab. 2 > In the Allow box, select List. 3 > In the Source box, type the months. 4) > Uncheck the Ignore blank option. 5 The Presence validation check is achieved when we uncheck the "Ignore blank" option. Data Validation 2 Error Alert Settings Input Message Error Validation criteria 1 The LookUp validation check is achieved when we add the names of the months in the Source: box. It is very important to prevent the user from entering the wrong type of data. For this purpose, we will set invalid input and error messages in order to inform the user during the process of entering the data. Allow: 3 List Data between 4 Source: ignore blank. In-cell dropdown 5 January February March April May June July August Septem± Apply these changes to all other cells with the same settings To enter the list of months in Arabic, you need to write the first month name in Arabic then change the language to English to enter ";" then change it back to Arabic to enter the next month and so on. Clear All OK Cancel Figure 2.22: Apply lookup and presence checks in Excel To set an Invalid Input message: > In the Data Validation window 1, select the Input Message tab. 2 > In the Title box, write "Data entry instruction". 3 > In the Input message box, write "Choose one of the months from the list". 4 To set an Error message: > In the Data Validation window 1, select the Error Alert tab. 2 > In the Style box, select Stop. 3 > In the Title box, write "Invalid Input". 4 > In the Error message: box, write "You must choose one of the months from the list". 5 > Click OK. 6 وزارة التعليم Ministry of Education 70 2024-1446 3 Data Validation 2 Settings Input Message Error Alert Show input message when cell is selected When cell is selected, show this input message: Title: Data entry instruction Input message: Choose one of the months from the list Clear All 4 1 OK Cancel Figure 2.23: Set an Invalid Input message Data Validation 2 Settings Input Message Error Alert Show error alert after invalid data is entered When user enters invalid data, show this error alert: 3 Style: Stop Clear All Title: Invalid Input 4 Error message: 1 You must choose one of the months from the list 6 OK Figure 2.24: Set an error message Cancel 5

Now that we've finished with the LookUp validation check and the Presence validation check, we can start entering the data. For every cell (from A3 to A17) in which we enter a value, we can see that a drop down list appears, showing us the predefined values for the months column. We can choose one of the months from the drop down menu or we can just type the month in the cell. While we enter the month values, we can see that the input message "Data entry instruction" is displayed. Also, if we enter a value by mistake that is different from the predefined ones, the error message that we've previously set will appear on the screen. A7 B C 1 Weather and events in S 2 Month City Maximum temperature (°C) Average 3 January 4 January Data entry instruction Choose one of the months 5 February from the list 6 February 7 January February March April May June July August 13 14 15 16 D The input message will be shown constantly while we type the names of the months in the column "Month". We can see the drop down list showing the predefined month values. We can either type the name of the month or just choose it from the list. Figure 2.25: Drop-down list of the months C D ய E Number of events ய E 4 Weather and events in Saudi Arabia City Maximum temperature (°C) Average precipitation (in millimeters) Number of events A B 1 2 Month 3 January 4 January 5 February Data entry instruction Choose one of the months from the list 6 February 7 Riyadh 8 9 10 11 12 13 14 15 16 17 18 weather and events وزارة التعليم Ministry of Education 2024-1446 Invalid Input X You must choose one of the months from the list Betry Cancel Help If we accidentally type something in the months column that is not included in the predefined value list, Excel will display the error message that we set during the validation. Figure 2.26: Lookup validation error message 4 71

AutoSave Off Book1 File Home Insert Page Layout Formulas Data Search (Alt+Q) Review View Help 區 Get Data Refresh All Get & Transform Data Queries & Connections H1 A B 1 2 Month City 3 January 4 January 5 February 6 February 7 March 8 March 9 April 10 May 11 May 12 June 13 July 14 September 15 October 16 November 17 November Binary Academy BA Comments Share Stocks (En... Geography... 21 42 ZJ Clear Sort Filter Reapply Advanced Text to Columns What-If Forecast Outline Analysis Sheet Data Types Sort & Filter Data Tools Forecast C D LLI E Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events 18 weather and events Ready Accessibility: Good to go Figure 2.27: Validated data table After entering all the months into the cells A3 to A17, the "weather and events" Excel spreadsheet will look like this. وزارة التعليم Ministry of Education 72 2024-1446 E 110%

Length Validation Check in Excel Now we will continue with the second column, which is the "City" column. Before entering the names of the cities, we will perform the length validation check, so that we can only enter values from 3 to 6 characters in length. To start the validation process: > Go to the "weather and events" Excel spreadsheet. 1 > Select cells B3 to B17. 2 > Select the Data tab. 3 > In the Data Tools group 4, click Data Validation. 5 > The Data Validation window appears. 6 AutoSave Off H Book1 3 Search (Alt+Q) Binary Academy BA File Home Insert Page Layout Formulas Data Review View Help Comments Share B N 2↓ Clear 蛋 4 68 Get Data Refresh All Sort Stocks (En... Geography... Filter Reapply Advanced Text to What-If Forecast Outline Columns Analysis Sheet Get & Transform Data Queries & Connections Data Types Sort & Filter Data Data Validation... 5 Circle Invalid Data C D Clear Validation Circles E B3 A B 1 2 Month City 3 January 4 January 5 February 6 February 7 March 8 March Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events Data Validation Settings Input Message Error Alert Validation criteria Allow: Any value 6 Ignore blank 9 April Data: 2 between V 10 May 11 May 12 June 13 July Apply these changes to all other cells with the same settings 14 September 15 October 16 November 17 November 18 weather and events Ready Accessibility: Good to go وزارة التعليم Ministry of Education 2024-1446 1 Clear All Figure 2.28: Start the validation process OK Cancel 但 + 110% 73

1 To apply the Length validation check in Excel: > In the Data Validation window 1, select the Settings tab. 2 > In the Allow box, select Text length. 3 > In the Data box, select between. 4 > In the Minimum box, type 3 and in the Maximum box, type 6. ⑤ > Uncheck the Ignore blank option. 6 Data Validation 2 Settings Input Message Validation criteria Allow: Error Alert 3 Text length Ignore blank 6 Data: between 4 Minimum: 3 5 Maximum: Apply these changes to all other cells with the same settings To set an Invalid Input message: > In the Data Validation window ①, select the Input Message tab. 2 > In the Title box, write "Data entry instruction". ③ > In the Input message box, write "Enter a city name between 3 and 6 characters". 4 To set an Error message: > In the Data Validation window 1, select the Error Alert tab. 2 > In the Style box choose Stop. 3 Clear All 1 1 OK 3 X Cancel Figure 2.29: Apply length validation check in Excel Data Validation 2 Settings Input Message Error Alert Show input message when cell is selected 1 When cell is selected, show this input message: Title: 3 Data entry instruction Input message: Enter a city name between 3 and 6 characters 4 Clear All OK ? Figure 2.30: Set an Invalid Input message 1 Data Validation 2 Settings Input Message Error Alert Show error alert after invalid data is entered When user enters invalid data, show this error alert: > In the Title box write "Invalid Input". ④ 3 Style: Stop > In the Error message box, write "The name of the city should be between 3 and 6 characters". 5 > Click OK. 6 x Title: Invalid Input 4 Cancel ? Error message: The name of the city should be between 3 and 6 characters 5 ليم The "Warning" style discourages the entry of invalid data. The error message icon is a yellow triangle with a black exclamation mark. Clear All 6 OK Cancel The "Information" style announces the entry of invalid data. The error message icon is a white speech bubble, with a blue lower-case "i". Ministry of Education 74 2024-1446 Stop Stop Warning Information < Figure 2.31: Set an error message

Now that we've finished with the Length validation check, we can start entering the city data. For every cell (from B3 to B17) in which we enter a value, we are allowed to write a city that consists of 3-6 characters. The input message is constantly shown and if we accidentally enter a value that is less than 3 characters or more than 6 characters, the error message that we've previously set will appear on the screen. If we accidentally type a value in the City column that does not meet the criteria that we set, Excel will display the Error message that we set during the validation. The input message will always appear while we type the names of the cities in the column "City". D E Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events A B C 1 2 Month 3 January 4 January 5 February City Jeddah Riyadh Riyadhh Data entry instruction Enter a city name between 3 and 6 characters 6 February 7 March 8 March 9 April 10 May 11 May 12 June 13 July 14 September 15 October 16 November 17 November 18 weather and events Ready Accessibility: Good to go وزارة التعليم Ministry of Education 2024-1446 Invalid Input × The name of the city should be between 3 and 6 characters Betry Cancel Help Figure 2.32: Input and error message of length validation E 4 + 110% 75 75

AutoSave Off Book1▾ Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help Binary Academy BA Comments Share 21 A Clear Get Data Refresh All Stocks (En... Geography... Z↓ Sort Filter Reapply Advanced Text to Columns What-If Forecast Outline Analysis Sheet Get & Transform Data Queries & Connections Data Types Sort & Filter Data Tools Forecast C D LLI E Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events H1 A B 1 2 Month 3 January City Jeddah 4 January Riyadh 5 February Jeddah 6 February Riyadh 7 March Jeddah 8 March Riyadh 9 April Riyadh 10 May Jeddah 11 May Riyadh 12 June Jeddah 13 July Jeddah 14 September Riyadh 15 October Riyadh 16 November Jeddah 17 November Riyadh 18 weather and events Ready Accessibility: Good to go Figure 2.33: Validated data table After entering all the cities into the cells B3 to B17, the "weather and events" Excel spreadsheet will look like this. وزارة التعليم Ministry of Education 76 2024-1446 E 110%

Range Validation Check in Excel Now we will continue with the third column, which is the "Maximum temperature (°C)" column. Before entering the temperature values, we will perform the Range validation check, so that we can only enter temperature values from 20 to 45 degrees Celsius. To apply the Range validation check in Excel: > Go to the "weather and events" Excel spreadsheet. > Choose cell C3. ② > From the Data tab, in the Data Tools group, click Data Validation. 3 > In the Data Validation window, select the Settings tab. 4 > In the Allow box, choose Custom. 5 > In the Formula box, type =AND(C3>20;C3<45). 6 > Uncheck the Ignore blank option and click OK. 8 > Use the Autofill tool to apply the validation to cells C4 through C17. 9 The "=AND(C3>20;C3<45)" formula means that the value that we will enter in the cell C3 must be greater than 20 degrees Celsius and less than 45 degrees Celsius. AutoSave Off Book1 Search (Alt+Q) Binary Academy BA File Home Insert Page Layout Formulas Data Review View Help Comments Share 2 24 Clear ?, Solver Get Data Refresh All Stocks (En... Geography... Sort Filter Reapply Advanced Get & Transform Data Queries & Conne... Data Types Sort & Filter Text to Columns Data Data Validation... What-if Forecast Outline Analysis Sheet 3 Analyze Circle Invalid Data C Clear Validation Circles E Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events C3 X A B 1 2 Month 3 January City Jeddah 4 January Riyadh 5 February Jeddah 6 February Riyadh 7 March Jeddah 8 March Riyadh 9 April Riyadh 10 May Jeddah 11 May Riyadh 12 June Jeddah 13 July Jeddah 14 September Riyadh 15 October Riyadh 16 November Jeddah 17 November Riyadh 18 weather and events 1 Enter Accessibility: Good to go وزارة التعليم Ministry of Education 2024-1446 2 Data Validation 4 Settings Input Message Error Alert Validation criteria Allow: 5 60 Custom Data between Formula: =AND(C3>20:C3<45) ignore blank 7 Apply these changes to all other cells with the same set 8 Clear All ? 4 OK Cancel E 110% 77

1 2 Month City 3 January Jeddah 4 January Riyadh 5 February Jeddah 6 February Riyadh 7 March Jeddah 8 March Riyadh 9 April Riyadh 10 May Jeddah 11 May Riyadh 12 June Jeddah 13 July Jeddah 14 September Riyadh 15 October Riyadh 16 November Jeddah 17 November Riyadh Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events Figure 2.34: Apply range validation in Excel To set an Invalid Input message: > In the Data Validation window 1, select the Input Message tab. ② > In the Title box, write "Data entry instruction". 3 > In the Input message box, write "Temperature data must be within a specific range of values". 4 9 Data Validation 2 Settings Input Message Error Alert Show input message when cell is selected When cell is selected, show this input message: Title: 3 Data entry instruction Input message: 1 Temperature data must be within a specific range of values Clear All OK 4 Figure 2.35: Set an Invalid Input message 1 Data Validation 2 Settings Input Message Error Alert Show error alert after invalid data is entered When user enters invalid data, show this error alert: To set an Error message: > In the Data Validation window 1, select the Error Alert tab. 2 > In the Style box select Stop. 3 3 Style: Stop > In the Title box write "Invalid Input". 4 > In the Error message box, write "Temperature value must be between 20 and 45 degrees Celsius". 5 > Click OK. 6 وزارة التعليم Ministry of Education 78 2024-1446 Clear All Itle: Invalid Input 4 Error message: ? Cancel Temperature value must be between 20 and 45 degrees Celsius 5 6 OK Cancel Figure 2.36: Set an error message X X

Now that we've finished with the Range validation check, we can start entering the data for the maximum temperature (°C) values. For every cell (from C3 to C17) we are allowed to enter a temperature value that is in the range of 20 degrees Celsius to 45 degrees Celsius. The input message is shown constantly while we type, and if we accidentally enter a value that is less than 20 degrees Celsius or greater than 45 degrees Celsius, the error message that we previously set will appear on the screen. AutoSave Off H Book1 Search (Alt+Q) Binary Academy BA File Home Insert Page Layout Formulas Data Review View Help Comments Share Clear Get Data Refresh All Stocks (En... Geography... 21 Sort Filter Reapply Advanced Text Colun The input message will constantly be shown while Get & Transform Data Queries & Connections Data Types Sort & Filter fx 10 we type the maximum temperature (°C) values in the cells C3:C17. A B C D 1 2 Month 3 January City Jeddah 4 January Riyadh 20.7 5 February Jeddah 10 Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events 28.8 Data entry instruction Temperature data must be within a specific range of values 6 February Riyadh 7 March Jeddah 8 March Riyadh Invalid Input 9 April Riyadh 10 May Jeddah Temperature value must be between 20 and 45 degrees Celsius 11 May Riyadh Betry Cancel Help 12 June Jeddah 13 July Jeddah 14 September Riyadh 15 October Riyadh 16 November Jeddah 17 November Riyadh 18 weather and events Ready Accessibility: Good to go Figure 2.37: Input and error message for range validation وزارة التعليم Ministry of Education 2024-1446 目 B E If we accidentally type a value in the maximum temperature column that does not meet the criteria that we have set, Excel will display the Error message. 110% 79

AutoSave Off Book1▾ Search (Alt+Q) Binary Academy BA 13 File Home Insert Page Layout Formulas Data Review View Help 2 21 Clear Get Data Refresh All Get & Transform Data Queries & Connections Stocks (En... Geography... Data Types NA Sort Z↓ Filter Reapply Advanced Text to Columns Comments Share 目 What-if Forecast Outline Analysis Sheet Sort & Filter Data Tools Forecast 4 G1 A B C E 1 2 Month 3 January 28.8 Weather and events in Saudi Arabia City Maximum temperature (°C) Average precipitation (in millimeters) Number of events Jeddah 4 January Riyadh 20.7 5 February Jeddah 29.8 6 February Riyadh 23.7 7 March Jeddah 25.5 8 March Riyadh 28.0 9 April Riyadh 33.6 10 May Jeddah 30.7 11 May Riyadh 39.5 12 June Jeddah 38.2 13 July Jeddah 39.4 14 September Riyadh 32.8 15 October Riyadh 27.5 16 November Jeddah 27.6 17 November Riyadh 20.4 18 weather and events Ready Accessibility: Good to go Figure 2.38: Validated data table After entering all the temperature values into the cells C3 to C17, the "weather and events" Excel spreadsheet will look like this. وزارة التعليم Ministry of Education 80 2024-1446 E 110% 4

Format Validation Check in Excel Now we will continue with the fourth column, which is the "Average precipitation (in millimeters)" column. Before entering the precipitation values, we will perform the Format validation check, so that we can enter not only integer values, but also decimals. This is a procedure that will require a minimum and a maximum value to be set, so we will set the minimum precipitation average value equal to 0 and the maximum precipitation average value equal to 30. To start the validation process: > Go to the "weather and events" Excel spreadsheet. > Select cells from D3 to D17. 2 > Select the Data tab. 3 > In the Data Tools group 4, click Data Validation. ⑤ > The Data Validation window appears. 6 AutoSave Off Book1 Save 3 Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help Get Data Refresh Stocks (En... Geography... 21 2 Z↓ Clear ↑ 111 4 Sort Filter Reapply Advanced Text to Columns Get & Transform Data Queries & Connections Data Types Sort & Filter D3 A B 1 2 Month 3 January City Jeddah 4 January Riyadh 5 February Jeddah 6 February Rivadh Data Validation 7 March 8 March 9 April 10 May 11 May 12 June 13 July 14 September 15 October C Binary Academy BA Comments Share 目 What-If Forecast Outline Analysis Sheet Data Data Validation... 5 Circle Invalid Data Clear Validation Circles E Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events 28.8 Settings Input Message Validation criteria Allow: Any value Data between Error Alert 20.7 9.8 6 7 ignore blank Apply these changes to all other cells with the same settings 16 November 17 November Clear All OK Cancel Riyadh 20.4 18 weather and events 1 Ready Accessibility: Good to go وزارة التعليم Ministry of Education 2024-1446 Figure 2.39: Start data validation in Excel B E 2 + + 110% 4 81

1 Data Validation To apply the Format validation check in Excel: 2 Settings Input Message Error Alert > In the Data Validation window 1, select the Settings tab. 2 , Validation criteria Allow: > In the Allow box, select Decimal. ③ 3 Decimal Ignore blank Data: > In the Data box, select between. 4) 4 between > In the Minimum box, type 0 and in the Maximum box, type 30. ⑤ Minimum: > Uncheck the Ignore blank option. 6 Maximum: 30 Apply these changes to all other cells with the same settings Clear All 1 5 1 OK Figure 2.40: Apply format validation in Excel 1 To set an Invalid Input message: > In the Data Validation window 1, select the Input Message tab. 2 > In the Title box, write "Data entry instruction". 3 > In the Input message: box, write "Precipitation values must be in decimal form". 4 Data Validation 2 Settings Input Message Error Alert Show input message when cell is selected When cell is selected, show this input message: Title: Data entry instruction 3 Input message: Precipitation values must be in decimal form 4 To set an Error message: > In the Data Validation window 1, select the Error Alert tab. 2 > In the Style box, select Stop. 3 > In the Title box, write "Invalid Input". 4 > In the Error message box, write "Precipitation values are not in decimal form". 5 > Click OK. 6 وزارة التعليم Ministry of Education 82 2024-1446 Clear All OK Figure 2.41: Set an Invalid Input message 1 Data Validation 2 Settings Input Message Error Alert Show error alert after invalid data is entered When user enters invalid data, show this error alert: 3 Style: Stop Clear All Itle: Invalid Input Error message: 4 ? Cancel Cancel Precipitation values are not in decimal form 5 6 OK Cancel Figure 2.42: Set an error message X х

Now that we've finished with the Format validation check, we can start entering the precipitation data. For every cell (from D3 to D17) in which we enter a value, we are allowed to enter a precipitation value that is in decimal form and between the range of 0 mm to 30 mm. The input message is constantly shown and if we accidentally enter a value that is less than 0 mm or greater than 30 mm, the error message that we previously set will appear on the screen. Stocks (En... Geography... Data Types AutoSave Off H Book1 Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help Get Data Refresh All N Sort 24 Clear Reapply Advanc Filter The input message will constantly be shown while we type the average precipitation values (mm) in the cells D3:D17 ments Share Outline Get & Transform Data Queries & Connections Sort & Filter Data Tools Forecast D6 fx -8 A B C D E LLI 1 2 Month City 3 January Jeddah 28.8 4 January Riyadh 20.7 Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events Data entry instruction Precipitation values must be in decimal form 5 February Jeddah 29.8 6 February Riyadh 23.7 12.50 14.80 3.30 -8 7 March Jeddah 25.5 8 March Riyadh 28.0 9 April Riyadh 33.6 Invalid Input X 10 May Jeddah 30.7 11 May Riyadh 39.5 12 June Jeddah 38.2 Precipitation values are not in decimal form Betry Cancel Help 13 July Jeddah 39.4 14 September Riyadh 32.8 15 October Riyadh 27.5 16 November Jeddah 27.6 17 November Riyadh 20.4 18 weather and events Ready Accessibility: Good to go Figure 2.43: Input and error message for range validation وزارة التعليم Ministry of Education 2024-1446 甲 If we accidentally type a value in the precipitation's column that does not meet the criteria that we set, Excel will display the Error message that we set during the validation. E 110% 83

AutoSave Off File Get Data Book1 Home Insert Page Layout Formulas Data B Refresh All Stocks (En... Geography... Get & Transform Data Queries & Connections Data Types Search (Alt+Q) Review View Help 21 Clear 32 ག、, Binary Academy BA Comments Share Sort Filter Z↓ Reapply Advanced Text to Columns What-If Forecast Outline Analysis Sheet Sort & Filter Data Tools Forecast H2 A B C D LLI E 1 2 Month City Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events 3 January Jeddah 28.8 12.50 4 January Riyadh 20.7 14.80 5 February Jeddah 29.8 3.30 6 February Riyadh 23.7 8.30 7 March Jeddah 25.5 2.60 8 March Riyadh 28.0 19.90 9 April Riyadh 33.6 23.70 10 May Jeddah 30.7 0.10 11 May Riyadh 39.5 5.60 12 June Jeddah 38.2 0.00 13 July Jeddah 39.4 0.40 14 September Riyadh 32.8 0.00 15 October Riyadh 27.5 1.50 16 November Jeddah 27.6 27.10 17 November Riyadh 20.4 20.00 18 weather and events Ready Accessibility: Good to go Figure 2.44: Validated data table After entering all the precipitation values into the cells D3 to D17, the "weather and events" Excel spreadsheet will look like this. وزارة التعليم Ministry of Education 84 2024-1446 E 110%

Type Validation Check in Excel Now we will continue with the fifth column, which is the "Number of events" column. Before entering the number of events for each city, we will perform the Type validation check, so that we cannot enter negative values. This is a procedure that requires a minimum value to be set, so we will set a minimum value equal to 1 because, apart from negative values, we also don't want the event values to be equal to zero. To start the validation process: > Go to the "weather and events" Excel spreadsheet. > Select cells from E3 to E17. 2 > Select the Data tab. 3 > In the Data Tools group 4, click Data Validation. ⑤ > The Data Validation window appears. 6 Binary Academy BA Comments Share 目 AutoSave Off Book1-3 Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help Get Data Refresh Stocks (En... Geography... 21 2 Z↓ Clear ↑ 111 4 Sort Filter Reapply Advanced Text to Columns Get & Transform Data Queries & Connections Data Types Sort & Filter What-If Forecast Outline Analysis Sheet Data Data Validation... 5 4 E3 A B 1 2 Month 3 January 4 January Riyadh 5 February 6 February Jeddah Rivadh Data Validation 7 March C D Circle Invalid Data Clear Validation Circles E 28.8 20.7 9.8 6 7 Weather and events in Saudi Arabia City Maximum temperature (°C) Average precipitation (in millimeters) Number of events Jeddah 12.50 14.80 3.30 8.30 2.60 8 March 9 April 10 May 11 May Settings Input Message Error Alert 19.90 2 Validation criteria Allow: Any value Data 23.70 ignore blank 0.10 5.60 between 12 June 13 July 14 September 0.00 0.40 0.00 15 October Apply these changes to all other cells with the same settings 1.50 16 November 17 November Clear All OK Cancel 27.10 Riyadh 20.4 20.00 18 weather and events 1 Ready Accessibility: Good to go B E Figure 2.45: Start data validation in Excel وزارة التعليم Ministry of Education 2024-1446 110% 85

To apply the Type validation check in Excel: > In the Data Validation window 1, select the Settings tab. 2 > In the Allow box, select Whole number. 3 > In the Data box, select greater than or equal to. 4 > In the Minimum box, type 1.5 > Uncheck the Ignore blank option. 6 1 X 2 Settings Data Validation Input Message Error Alert Validation criteria Allow: 3 Whole number Ignore blank Data: greater than or equal to Minimum: 4 5 60 Apply these changes to all other cells with the same settings Clear All OK Figure 2.46: Apply type validation in Excel To set an Invalid Input message: Data Validation 2 > In the Data Validation window 1, select the Input Message tab. 2 Settings Input Message Error Alert Show input message when cell is selected When cell is selected, show this input message: > In the Title box, write "Data entry instruction". 3 > In the Input message box, write "Enter a non-negative integer number". 4 Title: 3 Data entry instruction Input message: Enter a non-negative integer number To set an Error message: > In the Data Validation window 1, select the Error Alert tab. 2 > In the Style box, select Stop. 3 > In the Title box, write "Invalid Input". 4 > In the Error message box, write "The number of events can't be negative". 5 > Click OK. 6 وزارة التعليم Ministry of Education 86 2024-1446 Clear All 1 4 OK Cancel Figure 2.47: Set an Invalid Input message 1 Data Validation 2 Settings Input Message Error Alert Show error alert after invalid data is entered When user enters invalid data, show this error alert: 3 Style: Stop Clear All Title: Invalid Input 4 Error message: X Cancel The number of events can't be negative 5 X 6 OK Cancel Figure 2.48: Set an error message

Now that we've finished with the Type validation check, we can start entering the event data (based on the table 2.8). For every cell (from E3 to E17) we enter a value, in which we are allowed to enter a number that is equal to or greater than 1. The input message is shown constantly and if we accidentally enter a value that is less than 1, the error message that we previously set will appear on the screen. AutoSave Off H Book1 Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help Binary Academy BA Comments Share DA B Get Data Refresh All Stocks (En... Geography... 330 NK 249 Clear Sort Get & Transform Data Queries & Connections Data Types E7 fe -5 The input message will be constantly shown while we Reapply Filter Text to What-If Forecast Outline Analysis Sheet Forecast A B C type the number of events. E 1 2 Month City 3 January Jeddah 28.8 4 January Riyadh 20.7 5 February Jeddah 29.8 Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events 12.50 Data entry instruction 14. Enter a non-negative integer number 2 5 3.3 6 February Riyadh 23.7 8.3 7 March Jeddah 25.5 2.60 185 -5 8 March Riyadh 28.0 19.90 9 April Riyadh 33.6 Invalid Input X 10 May Jeddah 30.7 11 May Riyadh 39.5 × The number of events can't be negative 12 June Jeddah 38.2 Betry Cancel Help 13 July Jeddah 39.4 14 September Riyadh 32.8 0.00 15 October Riyadh 27.5 1.50 16 November Jeddah 27.6 27.10 17 November Riyadh 20.4 20.00 18 weather and events Ready Accessibility: Good to go وزارة التعليم Ministry of Education 2024-1446 B Figure 2.49: Input and error message of type validation E +110% If we accidentally type a value in the events column that does not meet the criteria that we set, Excel will display the Error message that we set during the validation. 87

AutoSave Off Book1▾ File Home Insert Page Layout Formulas Data Search (Alt+Q) Review View Help 21 Clear 22 Get Data Refresh All- Get & Transform Data Queries & Connections H2 A B Stocks (En... Geography... Data Types C NA Binary Academy BA Comments Share Sort Filter Reapply Advanced B Text to Columns What-If Forecast Outline Analysis Sheet Sort & Filter Data Tools Forecast D E Weather and events in Saudi Arabia Maximum temperature (°C) Average precipitation (in millimeters) Number of events 2 Month City 3 January Jeddah 28.8 12.50 2 4 January Riyadh 20.7 14.80 5 5 February Jeddah 29.8 3.30 1 6 February Riyadh 23.7 8.30 8 7 March Jeddah 25.5 2.60 1 8 March Riyadh 28.0 19.90 7 9 April Riyadh 33.6 23.70 1 10 May Jeddah 30.7 0.10 1 11 May Riyadh 39.5 5.60 1 12 June Jeddah 38.2 0.00 1 13 July Jeddah 39.4 0.40 2 14 September Riyadh 32.8 0.00 4 15 October Riyadh 27.5 1.50 4 16 November Jeddah 27.6 27.10 17 November Riyadh 20.4 20.00 1 5 18 weather and events Ready Accessibility: Good to go Figure 2.50: Validated data table After entering all the events into the cells E3 to E17, the "weather and events" Excel spreadsheet will look like this. وزارة التعليم Ministry of Education 88 2024-1446 E 110%

Exercises 1 Read the sentences and tick ✓ True or False. 1. Data validation refers to the procedure that automatically deletes any raw data that does not meet certain criteria. 2. There are only five types of data entry validation. 3. The Presence check helps us reduce errors by using a limited list of predefined values. 4. The LookUp check aims to ensure that characters and symbols have specific lengths. 5. The Range check is used to ensure that the entered numbers fall within a certain range. 6. The Format check ensures that data has a specific format. 7. The Type check helps us reduce language errors. 8. The Check digit is used if we want to ensure that a set of numbers is entered correctly. 9. Microsoft Excel is the only tool for data validation. 10. Data validation can be performed after we enter the values in a data validation program. وزارة التعليم Ministry of Education 2024-1446 True False 89

2 Briefly explain what data entry validation is. 3 Explain the steps that a user follows in Microsoft Excel in order to perform data validation. وزارة التعليم Ministry of Education 90 2024-1446

4 Create an address book table of your friends' information which will include the fields: Name, Telephone, Home address, Email address, Birthday, Hobby. Next, write down what type of data validation should be performed on each field. 5 Compare the following: (a) Length check vs Range check, (b) Format check vs Type check. Give examples of the use of each validation type. وزارة التعليم Ministry of Education 2024-1446 91