Prevent duplicate entries in excel sheet
Many of us would have used data validation more to use the list (drop down) tool, the data validation with a minor tweak can prevent you from entering duplicate entries in a specific range of cells. Its pretty easy, heres how
Many of us would have used data validation more to use the list (drop down) tool, the data validation with a minor tweak can prevent you from entering duplicate entries in a specific range of cells. Its pretty easy, heres how
- First select the range of cells you want to apply the Data Validation rule to. Also, note the Active Cell (the unshaded cell in the selected range);
- In Excel 2003: From the Data menu select Data Validation. In Excel 2007: On the Data tab, click the Data Validation dropdown in the Data Tools group and select Data Validation from the options;
- On the Settings tab select Custom from the Allow dropdown;
- In the Formula field enter =COUNTIF(selected_range,active_cell)=1 Make sure you use Absolute References (i.e. dollar signs $A$1:$A$200) ** for the selected_range and a Relative Reference (i.e. no dollar signs A1) for the active_cell;
- Click the Error Alert tab and enter a Title for the error (i.e. Duplicate Entry!) & Click OK.
Identifying Your Conditionally Formatted Cells (XL2003/XL2007)
If you're a fan of Conditional Formatting you likely have struggled with this little problem. Once you have applied Conditional Formatting to a range (or ranges) of cells you may have needed to go back later and make changes to the formatting or the condition used. You need to make the change to all the cells you previously formatted, but how can you tell which cells have Conditional Formatting?Not a problem!
There is an easy way to identify cells that have Conditional Formatting applied.
- Press the F5 key (or click Edit, Go To) and click the Special button at the bottom of the Go To dialog. Click on the Conditional Formats option and another option (All, Same) becomes available.
- To identify which cells have Conditional Formatting the same as the currently Active Cell, click Same and OK. All cells on the worksheet that have the same Conditional Formatting will be selected.
- You can now easily make changes or select additional cells to apply the formatting to.Instead of selecting the Same option, All will help you identify all cells in your worksheet containing Conditional Formatting.
