The IF statement is one of the most used functions in Excel, Access, and various programming languages, allowing you to setup a logical comparison for your data and identify two possible outcomes.
At it’s simplest form, the IF statement can be broken down into:
If something is true, then do it, otherwise do something else
The formula itself is as simple as:
=IF( logic , true , false )You will see this setup commonly referred to as an If Then Else statement
A real world example would be:
If it is raining outside, wear a raincoat, otherwise wear a t-shirt
=IF( raining , wear raincoat , wear t-shirt )
Few real world scenarios are as simple as the above raincoat / t-shirt choice. What if it’s snowing outside? You’d likely want to put something on other than a raincoat or t-shirt.
You can also combine IF statements together. This is called Nesting.
Let’s add in the option for a Heavy Coat by saying:
If it is raining outside , wear a raincoat, IF it is snowing outside, wear a Heavy Coat, otherwise wear a t-shirt
=IF( raining, wear raincoat, IF( snowing , wear heavy coat, wear t-shirt ))
IF in Excel…
Use the below file if you’d like to follow along
Starting File: ExcelIfThenElse
Let’s apply what we just learned to columns G and H in the attached spreadsheet.
IF 1: Column G
I want to identify every person that worked Full Time this week.
IF 2: Column H
I also want to take PTO into account if billed hours were not 40.
IF 1 Steps:
F3>=40to make your logic statement and a
,to end it
"Yes",to place what value you wish to see if
F3is greater than or equal to
"No"to place what value you wish to see if
F3is not greater than or equal to
- Close your formula with a closing parenthesis
G3formula should look like:
- Fill this formula down
IF 2 Steps (nested):
F3>=40to make your logic statement and a
,to end it
"Yes",to place what value you wish to see if
F3is greater than or equal to
- Instead of typing
"No"this time, we want to add in another
IFstatement for PTO hours
IF(E3>0, "Yes", "No"
- This time, close your formula with two closing parenthesis
)), one for each IF statement.
H3formula should look like:
- Fill this formula down
Your first IF statement flagged Doe,John and Gram,Lilly as not working a full time week. While this is true, Doe,John did not work full time because he was out part of the week on Paid Time Off.
If you are looking to identify which of your employees did not work a full time week, the second, nested IF statement, might be more accurate for you. It all depends on the metric you are trying to define.
It is also worth noting that if you changed all of the
1 and all of the
0, you could SUM the numbers together to figure out the number of people that did or did not work Full Time; very useful if you’re working with hundreds or thousands of employees.
An Absolute is a type of reference you can make in Excel. At it’s simplest form, a Reference can be as easy as
=A2, where we are referencing whatever value
A2 may hold and making it our current cell’s value. Formulas we have covered so far such as SUMS and VLOOKUPS are examples of references.
The below SUM formula references Cells
E3, E4, E6, E7, and
The below VLOOKUP formula references Cell
A2, and Columns J through M.
There are three types of references in Excel, Relative, Absolute, and Mixed. If you have been following the prior tutorials, you have been working with Relative references, Excel’s default reference type.
Absolute references require a
$ to be put before either the Column reference, Row reference, or both. When you write a formula and drag it either up or down, the referenced cells and columns in that formula will move along with it. An Absolute reference will stop that movement.
For example, I have a formula that says
If I extend this formula to the right once, the new formula will be
If I extend this formula down once, the new formula will be
Making my original formula
=$A$2 causes both movements to stay as 5
An Absolute reference can be made by placing a dollar sign
$ in your formula, or by pressing
F4. Let’s go over how this works and how it changes the SUM formula we have used so far.
We will be using the below screenshot for reference in this example.
- We have the total number of hours billed every month (275)
- We also have the percent of time our employees spend doing non-billable work per month
- Let’s total these together to get the Total Hours billed per month
- Click in Cell
- The result is 55, this is not the result you want. Since the Non-Billable number is a percent, you will want to multiply by 1.20 instead of 0.20 to get your Total Hours.
- Change your formula to
- Now that your formula is set, drag it to the right to fill in the Total Hours for February and March.
Your February and March data should be showing as 0 which is not right.
Why is it wrong?
Double click in
I2 to look at your February formula.
Notice how your formula is looking at
F10 instead of the 275 in
E10. This happened because you did not place an Absolute reference to
E10 in your original formula.
- Go back to your
- Click between the
- You will see a
$appear to the left of
$appear to the left of
10. This will “Lock In”, make Absolute, both the column and the row reference so that no matter where you drag the current formula, it will always reference
- Hit Enter to get out of your formula
- Drag your formula to the right again and you will see the correct Total Hours for February and March
Congratulations! You have made and Absolute reference and completed the calculation for Total Hours Billed!
In the prior Vlookup post we reviewed how to create a basic Vlookup formula; combining employee hours with employee addresses so that we can pay employees for the number of hours billed. The formula however was not perfect as a few cells resulted in an
#N/A error. Let’s continue to develop this formula and go over why we encountered this error, other errors we may encounter, and how to handle them.
End file from prior post:
#N/A is the error excel provides when a formula result cannot be found. In the attached example we see this error for Bookerson,Greg. Comparing the Hours Billed and Address data sets, we can see that his employee ID, 22222 was not provided in the Address data set.
We could leave the formula as is, but lets change the error to a Blank
"" cell to make things look a bit cleaner.
There are two primary formulas that can help us do this:
The easiest of the two formulas,
IFERROR()) allows us to replace the #N/A error with a value of our choosing.
For text type fields I choose “” for blank/null
For number type fields I choose 0 so that I can sum them easily later on
- Click in the Vlookup formula in
- After the equal sign type
- You will see excel will prompt you with
Value, Value If Error. The Vlookup formula result is going to be our Value
- Place your cursor to the far right of the formula
- Press comma ,
""and close your parenthesis
Your formula result should look like:
- Fill the formula down to remove the errors
This function is similar to
IFERROR() in that it looks for
#N/A errors, except that it only returns TRUE and FALSE if it finds one. For example, if I place the formula in
G2 and have it reference
F2, I will get the below:
Let’s insert the
ISNA() function into our Vlookup in column F.
In order to do this, we will need to create an IF-Then-Else statement for our Vlookup. More on IF statements later on.
The formula we will use is:
Let’s break this formula down…
Logically, we are saying:
IF the Vlookup result is an error, then show it as blank.
IF the Vlookup is not an error, then show the Vlookup result.
It is important to note that lookups should be done using unique identifiers. A Unique Identifier is something that identifies an object or entity and is unique to that object or entity. Think, social security number.
For the above lookup examples we used the Employee ID as our Unique Identifier as no employee ever has the same Employee ID.
Let’s show a Vlookup error that could result from not using employee ID.
In the above example, I’ve changed our:
Range_Lookup stays at
Note how Row 7, Page, Holly has a Vlookup error now. This is due to Holly’s name having a space between the Comma and her first name whereas her other records in hours billed and addresses do not have that space.
Different systems you work in may log names differently. Some may be
while others may include employee preferred names where
“Chan, Pang Lee” would show as “Chan, Steve”
Always, Always, Always, use a unique identifier when available. If it is not available, request it from the person who sent you the data and/or IT.
The VLOOKUP is part of a series of lookup and reference functions that allow you to pull information based on data in a particular row. VLOOKUP is my most used function in Excel as I am constantly working with separate data sets that I need to join together to pull over relevant information.
You have a spreadsheet with an Employee ID, Name, and Hours Billed per month. You have a second spreadsheet with Employee ID, Name, and Address. You need to bring this address over to the first spreadsheet in order to send these employee’s a paycheck based on their billed hours.
If the list of employees is small, you could filter by Employee and copy over each address individually; however, this is time-consuming and prone to user error. Let’s write a Vlookup formula instead to bring address over.
For Easier viewing, I have combined both sets of data into one sheet. In my experience however, you will get sent this information via two separate Excel workbooks. You could also combine these data sets via a query, but this type of access is typically restricted to IT and those in an Analyst type role.
Below is our starting point. We will be putting the Vlookup formula into column F to bring over address.
If you would like to follow along, please download the file attached below.
Writing a VLOOKUP
- Label Column F as “Address”
- Hit the
- Search for Vlookup and hit go
- Select Vlookup and hit OK
Your screen should look like the below
This is going to be your Employee ID, Column A.
Since we are placing this formula in cell
F2, you need to reference the ID in
A2 or left mouse click on cell
This is going to be the set of Data or “Range” that you want to find your address in; in this case,
Using the lessons learned in Navigate and Select Data, you can either:
- Type in
J2with your mouse and drag the selection over to
J2and press c
trl+shift+downarrow and then
- Select columns J through M or
For the purposes of this exercise, I will do the last option and explain why further on.
This refers to the column in your Table Array that you want to see. In this example, Column M.
M is the 13th column on the spreadsheet, but it is the 4th column in your Table_Array so we will type in 4 to reference the 4th column.
This refers to whether you want Excel to find an exact match “0” or an approximate match “1” I have never found a use for the latter so I always specify 0 in the box.
- Hit OK to complete your formula and you can see that it has brought over an address for Doe,John.
- Double click or drag down the black box in the bottom right corner of cell
F2. Your cursor will change to a black
+sign to let you know that you are on it
Your screen should look like the below.
In the next post, we will go over ways to handle the
#N/A formula errors as well as best practices for Vlookups.
In the previous post we showed how moving data around can impact a sum formula. Below we will go over what a Sum is and the various ways that you can construct it.
A sum function is one of the basic Excel math functions that you can use to add data together to get a total. You can add together specific cells and ranges of cells.
You can find the function by either:
- Typing into a cell or in the formula bar and typing
- Clicking on the function
- Choosing AutoSum from the menu
The below data will be used for the examples in this post and we will be summing data in the highlighted cell
Typing Into a Cell or the Formula Bar
I want to encourage beginners in Excel to use this option as it allows the most flexibility in letting you sum the data that you need to. Most users want to default to the aforementioned AutoSum which does the summing automatically for you, but learning how to type the formula out will help you in the long run.
Click in cell
E10 to select the cell.
As you type, you will see excel display the rage in your formula with a colored outline
Note that when you click on E10 again it will show you your formula in the formula bar. You can also double click E10 to enter the cell and see the outlined sum range
Clicking the function button will allow you to perform a search for various functions in excel. If you do not already see SUM, type it into the top search box and hit “Go” to search for it.
Select SUM and hit OK
This will take you to a dialog box which allows you type in multiple sum formulas.
You can type in
E2:E9 in the first box to get the same SUM result we got in the earlier example.
Alternatively, let’s sum values with the name “Gram,Lilly”.
Number 1 will be
Number 2 will be
Number 3 will be
The formula result should be:
Note how the cells are separated by commas and lack the colon “:” from the
E2:E9 example. The colon defines a range while the comma separates pieces of the formula.
Let’s SUM the values with the names “Gram,Lilly” and “Page,Holly”.
You could set your formula like this, referencing the individual cells:
or you could set it like this, combining the cells into a range using the colon.
Note how the different formulas change the outline color of the cells.
AutoSum is my least preferred method of summing information as Excel does the sum automatically for you, offering you the least control. Granted, you can always change the formula once the AutoSum is done, but if you are going to do that, you might at well just make the formula yourself to save time. AutoSum will default to trying to sum cells above where you are placing the formula. It will stop if there is a blank cell. If there is no data above, then it will try and sum cells to the left of the formula.
On the Home tab, find AutoSum in the editing section. Click it once to show the range it is going to sum. Hit enter to finalize it.
The below GIF will show three variations of the AutoSum formula:
AutoSum of the entire column
AutoSum that stops at first blank cell
AutoSum that Sums left instead of above
As we continue to lay a baseline for working with your data, we will now cover how you can start manipulating it to make the data work for you.
Before we start moving things around, let’s make sure we can see all of the data we are working with.
The names in column B below are being cut off by column C because column B is not wide enough to display all of the information in it. To fix this, we will Autofit all of our columns which will change their size to the max width of the data; in this case, Bookerson,Greg.
Click the “Select All” button (Ctrl+A)
Double left click in between any column letter. I prefer A and B
Copy and Paste
Knowing how to copy and paste data is integral to anyone using excel as it allows you to easily transfer information between your worksheets and workbooks, and also to other programs on your computer.
There are three options for copying.
-Choose Copy from the menu
-Right Click your selected data and choose Copy
Now that your data is copied, let’s paste it.
Pasting can be a bit more complicated depending on the types of data you are working with. Data stored as Numbers or Text can change depending on how and where you are pasting, but let’s hold on that discussion for another time.
For now, paste the data
There are four options for pasting.
-Choose Paste from the menu
-Right Click where you want to paste and choose the Paste option
-Right Click where you want to paste and choose the Paste Special
Once you have pasted the data, you may notice that it did not keep certain attributes such as the column widths we had from our Autofit. You can repeat the Select All process we did earlier to fix this, or you can:
-Right Click where you just pasted
-Choose Paste Special
-Choose Column Widths
This while copy and paste over the same column widths from your copied data
Pretty short and sweet, but just be careful when you are moving data around, especially if the data is in a template that has formulas, pivots, and other functions tied to it.
Quick example below shows how moving the data can adversely affect a template.
Note how my yellow highlighted cell is summing the Hours in Range E3 through E10. Moving some of my data around changes that formula result.
This information is to help you lay a baseline of skills that will benefit you in more complicated work later on. I’m sure we all know that one person that lists themselves as “Experienced” in Excel, but can’t make a basic pivot table. Don’t do that. Establish your baseline of skills and knowledge of how excel works and then work up from there.
The first thing you should know is that there is always more than one way of doing something in Excel. I like to think of data in my spreadsheet as a Rubix Cube like puzzle where I have my data, I have my end goal, and its all about the clicks, twists, turns, formulas, and formatting in order to reach that goal. Your way may not be the same as someone else’s, and that’s okay.
The column and row headers are your best friend. They make selecting data quick, and any changes you apply to your selection will apply to everything in that column or row.
Clicking on column B for example will select your data in that column, and all of the empty cells past it. The same goes for selecting row 2 and clicking the Select All box (left of column A and above row 1).
Note that there are disadvantages to selecting data in this manner. Let’s say that I got this file as a raw data export and I need to paste it into a template that has formulas in columns E and F. Doing the above selections would remove those formulas because you are pasting the blank raw data cells on top of the ones in the template.
Selecting only data
My preferred method of selecting data is to click with my left mouse button and then drag my mouse cursor to form the selection I want. Only do this when the selections are small, i.e. you can see everything you need to select on your screen already. If your row count is in the hundreds or thousands, do one of the below methods.
Select A1 if you want your headers
Select A2 if you only want your data
For the below examples, let’s assume you have selected A2
Selects all data from A2 to your last column, last row of data
I get a lot of questions from users on where the “End” key is. On most keyboards it is located near the backspace key, often on it’s own “Control Pad” between the alphanumeric keys and the numpad
Selects all data from A2 to the last cell in Column A
Selects all data from A2 to the last cell in Row 1
The arrow key methods will stop at the first blank cell they encounter