IF Statements In Excel... And Other Cool Tricks
Published
IF you've ever wished Excel would do a little more for you, this may be what you're looking for.
“IF” Statements in Excel
And other cool tricks.
Ever wish you could only grab SOME of the data? IF statements work like this:
=if(cell to look in=condition you’re looking for, return this, if it doesn’t have that item return this)
In practice, let’s say we have a super simple excel sheet that has only one column, full of types of fruits. You want to count instances of the word “banana”. Your if statement might look like this:
=if(A1 = “banana”, 1, 0) (hey did you notice I put quotes around that string?)**
Fill this formula down. You’ll get a 1 in the column each time “banana” appears, and a 0 every time it’s some other fruit, making for easy counting. I’ll leave it to you to figure out how else you can use this tool! (Hint, you can return strings, and nest more functions into your “IF” statement.)
Some ways to capitalize on your new “IF” function (using Exceljet!):
PS—for the advanced, try your hand at indexing with MATCH and INDEX.
PPS—author’s favorite Excel function, the modest PROPER.
**Important items to note:
Excel is for numbers, but a lot of the time we’re using it for words. Excel calls words “strings” like a “string” of characters,
and it needs you to tell it when you’re looking for a “string”. Any time you’re working with strings (words), be sure to put quotes around it (like this: “banana”) or you’ll get errors.
If this function still gives you trouble, make sure your number format is in “general”. If you don't know what a number format is check out this guide.
And other cool tricks.
Ever wish you could only grab SOME of the data? IF statements work like this:
=if(cell to look in=condition you’re looking for, return this, if it doesn’t have that item return this)
In practice, let’s say we have a super simple excel sheet that has only one column, full of types of fruits. You want to count instances of the word “banana”. Your if statement might look like this:
=if(A1 = “banana”, 1, 0) (hey did you notice I put quotes around that string?)**
Fill this formula down. You’ll get a 1 in the column each time “banana” appears, and a 0 every time it’s some other fruit, making for easy counting. I’ll leave it to you to figure out how else you can use this tool! (Hint, you can return strings, and nest more functions into your “IF” statement.)
Some ways to capitalize on your new “IF” function (using Exceljet!):
- ISNUMBER(SEARCH)
- For if you want to count instances of “banana” but you aren’t sure where it might occur in the cell, like if you have a survey of board members listing which snacks they want to see at the next meeting.
- IF(AND)
- For when you need MORE if statements.
- LEFT, RIGHT, and CENTER
- Slice some strings into pieces! Sometimes you just need the leftmost four characters, or the center two, or the right one.
- &
- Connect things!
PS—for the advanced, try your hand at indexing with MATCH and INDEX.
PPS—author’s favorite Excel function, the modest PROPER.
**Important items to note:Excel is for numbers, but a lot of the time we’re using it for words. Excel calls words “strings” like a “string” of characters,
and it needs you to tell it when you’re looking for a “string”. Any time you’re working with strings (words), be sure to put quotes around it (like this: “banana”) or you’ll get errors.
If this function still gives you trouble, make sure your number format is in “general”. If you don't know what a number format is check out this guide.
News
Tips, Tricks, and Timesavers!
04/29/2019 5:34pm EDT
Leave a Comment