Excel: Quick Tips

These are some extremely quick tips and formulas for excel.

FIND IF IT’S REPEATED

=COUNTIF(A:A,A1)

 

SUM TEXT

=A1&""&B1&""&C1

 

IF STATEMENTS TEXT

=IF(C1>500,"TRUE","FALSE")
=IF(A1="","","-")
=IF(A1="^","TRUE","FALSE")

 

IF STATEMENTS CALCULATION

=IF(EW1<100,((1.6*EW1)+10),1.2*EW1)
=IF(F2<100,((1.5*F2)+10),1.1*F2)
=IF(BM1<60,82,BM1*1.2)

 

RETRIEVE INFORMATION FROM ANOTHER CELL

=LOOKUP(B2,E2:E3410,F2:F3410)
=LOOKUP("What are you looking for?","Search it in these column","Print same data from another column")

 

SEARCHES FOR "EXP" ON A COLUMN TRUE/FALSE

=IF(ISNUMBER(SEARCH("EXP",A1)),"EXP", "TEC")

 

FIND MINIMUM OR MAXIUMUM NUMBER COMPARING 2 COLUMNS

=MIN($A1:$B1)
=MAX($A1:$B1)

 

GET THE FIRST OR LAST WORD FROM A CELL

=IF(ISERR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1))
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

 

CHANGE TEXT CASE
Converts all uppercase letters in a text string to lowercase:

=LOWER(A1)

Converts all lowercase letters in a text string to uppercase:

=UPPER(A1)

Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters:

=PROPER(A1)

 
FIND AND REPLACE
Convert line breaks to <br> on a cell:

=SUBSTITUTE(A1, CHAR(10), "<br>")

This code will find "Hello" and replace it with "Goodbye"

=SUBSTITUTE(A1, "Hello", "Goodbye")

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>