Skip to content

Excel: Quick Tips

2
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, MIDDLE OR LAST WORD FROM A CELL

=IF(ISERR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1))
=TRIM(MID(G1,FIND(" ",G1),256))
=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")
Read more from Other Tips

Share your thoughts, post a comment.

(required)
(required)

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

*