Advanced Excel Tutorials

Data validation is a feature in Excel used to control what a user can enter into a cell. Data validation is a feature available in Microsoft Excel. It allows you to do the following: Make a list of the entries that restricts the values allowed in a cell. Create a prompt message explaining the kind of data allowed in a cell. Create messages that appear when incorrect data has been entered. Check for incorrect entries by using the Auditing toolbar. Set a range of numeric values that can be entered in a cell. Determine if an entry is valid based on calculation in another cell. Data Validation Example In this example, we restrict users to enter a whole number between 0 and 10. Create Data Validation Rule To create the data validation rule , execute the following steps. 1. Select cell C2. 2. On the Data tab, in the Data Tools group, click Data Validation. On the Settings tab: 3. In the Allow list, click Whole number. 4. In the Data list, click between. 5. En...

This example teaches you how to find duplicates (or triplicates) in Excel . Go here to remove duplicates. 1. Select the range that you want to find the duplicates. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Highlight Cells Rules, Duplicate Values. 4. Select a formatting style and click OK. Result. Excel highlights the duplicate names. Note: select Unique from the first drop-down list to highlight the unique names. As you can see, Excel highlights duplicates (Juliet, Delta), triplicates (Sierra), quadruplicates (if we have any), etc. Execute the following steps to highlight triplicates only. 5. First, clear the previous conditional formatting rule. 6. Select the range that you want to find the triploites. 7. On the Home tab, in the Styles group, click Conditional Formatting. 8. Click New Rule. 9. Select 'Use a formula to determine which cells to format'. 10. Enter the formula =COUNTIF($A$1:$C$10,A1)=3 ...