Data Validation By Nate Moore, CPA, MBA, CMPE ne of the fundamental rules of data analysis is, “Garbage in, garbage out.” If the data that you are trying to analyze is riddled with errors, your analysis will likely be full of problems as well. One way to improve the quality of data entry in Excel is to use a feature called data validation. Data validation allows users to apply a variety of rules to determine whether data entered in a cell is usable and appropriate for the spreadsheet application. Validation Criteria Using a List a common way for data entry problems to occur when entering text is inconsistently entering data. For example, when entering a patient’s city, “st. Louis” could easily be entered as “st. Louis,” “saint Louis,” “st Louis,” “st. Luois,” or many other variations and misspellings. Trying to find all of the patients from st. Louis will be difficult until all of the patients have their city entered the same way. To require a patient’s city to match a preselected list of consistently entered cities, data validation can help. Data validation is on the Data tab in the ribbon in the “Data Tools” area, as shown in Figure 1. select the cell you want to validate and click “Data Validation.” The Data Validation window will appear, as shown in Figure 2. The “settings” tab in the Data Validation window has several options to control what data is allowed in the cell. From the “allow” dropdown menu, choose “List.” In the “source” area, enter the range of cells that have acceptable values for the data input cell, as shown in Figure 3. In this example, cells g1 through g8 have the list of acceptable values for entry in the highlighted cell, D4. users will be limited to one of the eight cities shown when entering data in cell D4. Checking the box next to “In-cell dropdown” will tell excel to put a dropdown arrow next to cell D4 when users click on the cell. users can select an item from the dropdown list instead of typing. Be careful here. If you have a blank cell in your validation range (g1:g8), users might be able to enter invalid entries in cell D4. Remove the check mark from “Ignore blank” to prevent users from entering invalid data in cell D4. Both the “In-cell dropdown” and “Ignore blank” options are circled in red in Figure 4. 40 HBma BILLINg • JaNuaRY.FeBRuaRY.2014 Input Messages and Error Alerts There are two other tabs in the Data Validation window that control the messages excel displays for cells with data validation enabled. The “Input message” tab creates a custom message that appears when a cell with a data validation rule is selected. an example of an input message for cell D4 is shown in Figure 5. Figure 6 shows the “error alert” tab, which controls the severity of any data validation errors and allows users to create a message to be displayed whenever invalid data is entered. Check the “show error alert after invalid data is entered” box to have excel display a message when invalid data is entered. There are three levels of alerts: stop, Warning, and Information. Note that “stop” (shown in Figure 7) will prevent users from entering invalid data in a cell, while “Warning” (shown in Figure 8) and “Information” (shown in Figure 9) will warn users about invalid data but still allow them to enter data that does not meet the data validation criteria. also note that input messages and error alerts appear only when data is typed directly into the cells. If users copy and paste or use a macro to enter data into cells, invalid data can still end up in the spreadsheet. For more control over what users can and cannot do in a workbook, use the protection options on the Review ribbon tab. There are a variety of data validation options available from the “allow” dropdown list on the “settings” tab shown in Figure 2. You can limit entries to whole numbers, decimals, date and time ranges, and more. using data validation can go a long way toward increasing the accuracy and consistency of data in your spreadsheets. excel Video 177 at mooresolutionsinc.com/videos.php has more information and another example using data validation. Nate Moore, CPA, MBA, FACMPE writes custom SQL Server code to connect practice management data to Excel for data mining with practices throughout America. Nate’s first book, Better Data, Better Decisions: Using Business Intelligence in the Medical Practice, was just published by MGMA. His free Excel Videos have been viewed over 600,000 times and are available at mooresolutionsinc.com. Like PivotTableGuy on Facebook or follow PivotTableGuy on Twitter to be notified each time a new Excel Video is released.specific applications of these Excel functions. O
Billing_JanFeb14
To see the actual publication please follow the link above