Enhance your data analysis skills by mastering Excel's true-false statements, a powerful method for quickly verifying and comparing data. Learn practical methods to automate data validation, saving time and improving accuracy in your spreadsheets.
Key Insights
- Utilize Excel's true-false statements by combining the equals sign and comparison operators (such as equal to, greater than, or less than) to automatically verify data like employee rates, locations, and hours worked.
- Employ autofill and filtering techniques to efficiently highlight and organize results, enabling quick identification of discrepancies between large sets of data from different years or periods.
- Apply Excel's true-false functionality practically by categorizing employees based on specific criteria—such as identifying full-time staff working 32 or more hours and recognizing employees exceeding 40 hours to address overtime concerns.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
The true-false statement. What does the true-false statement do? Well, it checks to see if something is either true or false. Now, to make this more exciting than it may actually be, I tell my students that this is Excel's lie detector machine.
Excel can tell whether a statement is true or false. So, you might say, how does it do that? Well, we'll use the equals sign, and we'll use a comparison operator. And you'll say, what is a comparison operator? I'm going to head over to this section here, where I can take a look at the operators that are available to us.
We have the equals sign, so we can check to see if a1 is equal to b1. We also have the greater than symbol, and we can check to see if one value is greater than another, less than another, or greater than or equal to, less than or equal to, or even not equal to. So, let's take a look at exercise one and two.
We have two statements that we want to verify, and check to see if that statement is either true or false. The first statement said Peter earns a rate of $24. So, I'm going to type the equals sign, and I'm going to go to the cell that contains his rate.
Once I have that selected, I will use my comparison operator to verify Peter's rate. That comparison operator is equal, and what I want to check to see about that cell is I want to check to see if that cell is equal to 24. So, I'll type 24.
When I press ENTER, Excel says that is a true statement. Somebody else or another statement we can look at that may be said by someone else is Peter works in Ohio. Well, I'm not just going to take their word for it.
I'm actually going to test to see if Peter works in Ohio. So, I'll type in equal. Now, I need to go to the cell that contains his location, or else I can't make the comparison.
So, I'm going to go to the cell that's directly under state, and then I'm going to type the equals sign, and then I want to see if it's equal to Ohio. Since this is text, I need to use double quotes. You'll need to remember that Excel is like a calculator.
It doesn't understand words. So, in order to make comparisons with text, you have to put it in double quotes. I'll press ENTER.
Excel says that is false. So, at this point, if someone's sitting in the class, they could very well raise their hand and thank me for wasting their time. Why would they say that? Well, they might say to me, Garfield, I don't know if you realize this about me, but I have these two things in my face called eyes, and I can clearly see that Peter earns a rate of $24 and that he works in Vermont.
I don't see why I would ever need to type an Excel function in Excel to tell me something that I can tell just by looking at the spreadsheet. I would say you bring up a very valid point. Let me show you how this might be useful, because it's not going to be useful just for one value.
So, if I head over to the right, I want to compare values from year one and year two. Now, in this first situation, I want to see if the values are equal to each other. So, I'll type in equal and I'll select year one's value, and then I'll check to see if it's equal to year two's value.
When I press ENTER, that is true. It's not a big deal, but if I have a lot of values that I want to compare, one double click will be able to instantly tell me where the values are true and where they're false. All I would need to do now is do a filter on that table, and I can easily pick out those values.
Let's say I want to do another type of comparison. I'm going to go to the equal to, and I'm going to click the drop down here and choose greater than. So, the information we're looking for this time is whether or not year one is greater than year two.
So, I'll type in equal, I'll select year one again, but this time I'm not going to use the equals sign, I'm going to use the greater than symbol, and I want to see if year one is greater than year two. When I press ENTER, that of course is false because they're equal to each other. But I'm going to go over to that formula statement and double click on the black plus sign.
Now it's going to be very easy for me to pick out where year one was greater than year two, and I won't have to look at every single value in those two columns. So, if we go back to an exercise that we would conduct in class, I would ask the students to work with exercise three and four. We want to see who is working full-time and who is working overtime.
So, we're going to need to make comparisons with the hours of the people working at the company and the criteria for full-time and part-time. For the first exercise, full-time is considered as any hours that are 32 or more. So, I'll type equal, I will select the cell that contains the total amount of hours for Mary, the first employee, and check to see if her hours are greater than or equal to 32.
When I press ENTER, Excel says that is not greater than or equal to 32. Now I want to check for everyone else down the list. I can double click.
Everywhere I see true, those are full-time employees. So, if I wanted to send an email to them or make some kind of announcement, I could filter for everyone who is listed as true, and I don't have to worry about people who are categorized as false. Now I want to see who worked overtime.
This might be necessary. Maybe we've recommended that people not work overtime, so there are some people who have. I may want to find out why.
I need to make a comparison between the current amount of hours and the overtime hours. Now, overtime is considered as anything greater than 40. So, I'll type in equal.
I'll select the hours for the first employee, and check to see if they are greater than 40. And if they are, then they are working overtime. I'll press ENTER.
Mary is not working overtime because she's only working 30 hours. I'll go over to the autofill handle, double click. Now I see two trues in this list.
So, the two people working overtime are Greg and also Doug. So, now I can select them from the list and then send them an email if I need to because I can easily pick them out from 10 employees or 1,000 employees. So, that is the true false statement.
It helps you to find criteria that matches by creating a result that says either true or false based on that criteria.