How to exclude "0" from MIN formula Excel

=MIN(If(A1:E1>0,A1:E1))

Read More

How To Prevent Duplicates On Entry

To prevent duplicate entries in a range of cells, you can use Excel's Data Validation feature.

Let's assume you want to prevent duplicate entries in cells B5:B50.

1) Select cells B5:B50;

2) From the Data tab click Data Validation;

3) Click the Settings tab;

4) Click the drop down arrow in the Allow box and select Custom. A new Formula box appears.

5) Click in the Formula box and enter this formula...

=COUNTIF($B$5:$B$50,B5)=1

The COUNTIF part of this formula looks in the range B5 to B50 and counts the number of cells that are equal to the value in cell B5. The =1 part of the formula says that any value in this range may occur only once. Therefore 1=1 equals TRUE and the entry is allowed. When you try to enter a duplicate value, the result of the COUNTIF part of the formula is 2. The 2=1 equals FALSE and the error is detected.

Note that the formula uses absolute references for the range ($B$5:$B$50) you want to apply the Data Validation criteria to and relative referencing for the input cell (B5) you want to evaluate. This is critical for this to work
Absolute Referencing uses $ symbols to lock the column and/or row references so they do not adjust when a formula is moved or copied. Relative Referencing doesn't use $ symbols, therefore when a formula is move or copied, the references adjust based on the relative position of the rows and columns.

6) Click on the Error Alert tab and type 'Duplicate Entry' in the Title box;

7) In the Error message box you can type a custom message if you want, such as 'You cannot enter duplicate values in this list!';

8) Click OK and try entering a duplicate value to test it.

Cautions: The Data Validation feature is not foolproof. This feature works only on direct user input. Duplicate values resulting from (a) data being pasted in to the range, (b) Find & Replace operations, (c) the result of calculations or (d) changes made by macros (VBA) code will override this feature.

Read More

Remove/Delete Duplicate Values From Your Data

1) From the Data tab, in the Data Tools group, click Remove Duplicates;

2) In the Remove Duplicates dialog, all columns are selected by default. This means that if there are rows with identical values in all columns, duplicate rows subsequent to the first instance are removed from the Table or selection.

To remove duplicate records for rows where only certain columns contain duplicate values, uncheck the columns you don't want to check for duplicates. If your data contains a lot of columns and you want to select only a few columns, you may find it easier to click the Unselect All button first and then select only those columns;

3) Click OK. A message will be displayed indicating how many duplicate values were removed and how many unique values remain. 

Note that at this point you do have the option to Undo the Remove Duplicates action.

Also note that you use this Remove Duplicates feature on data that contains subtotals or is outlined. You will have to remove the subtotals and outlines first.

Read More

Automatically Flag Duplicate Values On Input

Preventing duplicate values from being entered in a range of cells may be a requirement in some of your worksheets. Although not obvious, enabling this capability in Excel is very easy.

Here's how... 1) Select the range of cells where you want to prevent duplicate values (e.g. B3:B20); 

2) From the Data tab click Data Validation in the Data Tools group; 

3) On the Settings tab click the Allow dropdown and select Custom from the list. A Formula field will appear; 

4) In the Formula field type=COUNTIF($B$3:$B$20,B3)<2 .="" 2="" a="" absolute="" accepted.="" active="" adjust="" alert="" allow="" and="" appears="" as="" b3:b20.="" b3="" cell.="" cell="" count="" critical="" data="" display="" e.g.="" entered="" error="" evaluated.="" evaluates="" example="" for="" formula="" how="" if="" in="" into="" is="" it="" less="" many="" message.="" nbsp="" need="" otherwise="" p="" range="" referencing="" relative="" required.="" s="" says="" than="" that="" the="" this="" times="" to="" true="" use="" validation="" value="" when="" will="" you="" your="">

5) In the Data Validation dialog, click the Input Message tab and, if desired, type a message that will be displayed when the user selects a cell within the range of the Data Validation rule. Otherwise, if you don't want a message to appear every time someone selects a cell in the range, clear the 'Show input message...' option. 

6) Click the Error Alert tab and type the message that will be displayed if the user enters a duplicate value in the range;

7) Click OK.

Read More

Excel REPLACE Function

When data is imported or copied into an Excel spreadsheet unwanted characters or words can sometimes be included with the new data.

Excel's REPLACE function can be used to replace these unwanted characters with good data or with nothing at all.

The REPLACE function makes it easy to quickly correct long columns of imported data. As with most functions, create the function for the first entry and then use the fill handle or copy and paste to copy the function to all other cells.

The syntax for the REPLACE function is:

=REPLACE(Old_text, Start_num, Num_chars, New_text)

Old_text - the piece of data you want to change. This can be a cell reference indicating where the data is stored.

Start_num - specifies the start position (from the left) of the characters in old_text that you want to replace.

Num_chars - specifies the number of characters to be replaced from the Start_num specified above.

New_text - specifies the new data to be added. This argument can be left blank if you just want to remove unwanted characters.

Example: Using Excel's REPLACE Function

1. Enter the following data into cell D1: ^& # 24,398. The ^, & , and # characters are located above the numbers 6, 7, and 3 on the top row of the keyboard.
   
2. Click on cell E1 in the spreadsheet - this is where the function will be located.
   
3. Click on the Formulas tab of the ribbon menu.
   
4. Choose Text from the ribbon to open the function drop down list.
   
5. Click on REPLACE in the list to bring up the function's dialog box.
   
6. In the dialog box, click on the Old_text line.
   
7. Click on cell D1 in the spreadsheet since the data we want to alter is in cell D1.
   
8. Click on the Start_num line.
   
9. Enter the number 1 on this line to indicate we want to start our replacement from the first character on the left.
   
10. Click on the Num_chars line.
    
11. Enter the number 3 on this line to replace the first three characters.
    
12. Click on the New_text line.
    
13. Type a dollar sign ( $ ) so that we replace the three characters with the dollar sign.
    
14. Click OK.
    
15. The amount $24,398 should appear in cell E1.
    
16. When you click on cell E1 the complete function = REPLACE ( D1 , 1 , 3 , " $ " ) appears in the formula bar above the worksheet.

http://spreadsheets.about.com/od/excelfunctions/qt/080817_replace.htm?nl=1

Read More