RESEARCH PROJECT
CUSTOM DATA VALIDATION
CONDITIONAL FORMATTING
Computer Science 201
Quick Overview
Custom data validation
Custom Data Validation is a feature of Excel that regulates the data a user can insert in certain cells (Cheusheva, 2017). The main advantage of this technique is that it provides accuracy and consistency of the inputs, since only particular type of data can be inserted in the cells (Cheusheva, 2017). For example, a user might want all cells between B1:B10 to contain numeric values. In case someone attempts to input a text value Excel will not accept it, because Custom Data Validation allows only numeric values.
Conditional Formatting
Closely related to Custom Data Validation is another feature of Excel, Conditional Formatting. This characteristic helps individuals to distinguish and analyze data by formatting certain cells (Use conditional formatting to highlight information, 2020). In the previous example the user inserted numeric values between B1:B10. Now these numbers have to be analyzed. All cells that contain values from one to five will be colored green, while the rest will be colored red. Conditional Formatting is the feature that will help tackle this issue. By setting parameters and categorizing each cell in one of the two colors, users will be able to analyze the data based easier and more efficiently.
How the formulas work
Custom data validation guide
If a user aims to use Custom Data Validation, has to go to data page, and click Data Validation. It has specific options ready to use, such as whole number, decimal, date, time, list, and text length. Users can implement the parameters they want in each one of these choices(Cheusheva, 2017). Apart from that, there is also another criterion which is called “custom”. The main idea is that individuals can insert parameters with the use of formulas (Cheusheva, 2017). In case the criteria a user sets are not met, Excel will provide a message that informs about the wrong input. Finally, a person can make a message appear in the beginning to inform users what type of data to insert, while at the same time can regulate what kind of message will appear in case of invalid inputs (Cheusheva, 2017).
Conditional Formatting guide
The main characteristic of this feature is that it categorizes existing data based on the user’s parameters. Conditional Formatting can be found in the home tab of Excel a click “Conditional Formatting” (Use conditional formatting to highlight information, 2020).. At the same time, numerous choices will appear which will suggest how to categorize data.
The first one is Highlighted Cell Rules, which uses comparison criteria (<,=,>) and formats only the cells that are TRUE to the condition a user sets (Use conditional formatting to highlight information, 2020). Furthermore, it’s the Top Bottom Rules, which format only cells that have the top ten values from the bottom or the top(Use conditional formatting to highlight information, 2020). Also Top Bottom Rules have above and below average rules. In addition, Data Bars highlight with a bar the cell and can show at once how big the value is compared to the others which have the same formatting (Use conditional formatting to highlight information, 2020). Color Scale provides a variety of colors that fill cells based on how high or low the value they contain is(Use conditional formatting to highlight information, 2020). Finally, Icon Sets allow users to use icons to divide data in different categories (Use conditional formatting to highlight information, 2020).
Similarly with Custom Data Validation, users can insert their own formulas in order to achieve the desired result. All they have to do is to click on New Rules (under the Icon Sets), and then select the last option, which is “Use a formula to determine which cells to format”. This can result in determining the color of the cells by using various formulas and data from other columns.
Examples of application
These two features can be used in various situations. For example, businesses that sell products to their customers on credit can keep track with the payment activity. If a customer pays then the cell in column sales will turn green, otherwise it will remain empty and red. Also, companies tend to keep large books that contain various data, like customer information, revenues and expenses, sales etc. Such companies can use the capabilities of Excel and regulate which values each cell can accept, in order to avoid confusion.
An issue that might occur and can be solved with the help of Custom Data Validation might be the following: “If A1 is answered yes, then B1 requires a yes/no response. If A1 is answered No, then B1 should be blank.” (Excelforum, 2020). In order to solve such problem with this feature of Excel, individuals have to go to Custom and insert the following formula:” =AND (A1=”Yes”, OR (C1=”Yes”, C1=”No”))” (Excelforum, 2020).
Sources
Cheusheva, S., 2017. Data Validation In Excel: How To Add, Use And Remove. [online] Excel tutorials, functions and formulas for beginners and advanced users – Ablebits.com Blog. Available at: <https://www.ablebits.com/office-addins-blog/2017/08/16/data-validation-excel/#what-is-data-validation> [Accessed 4 June 2021].
Excelforum.com. 2020. If/Then. [online] Available at: <https://www.excelforum.com/excel-formulas-and-functions/884456-if-then.html> [Accessed 4 June 2021].
Support.office.com. 2020. Use Conditional Formatting To Highlight Information. [online] Available at: <https://support.office.com/en-us/article/use-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f> [Accessed 4 June 2021].
