Write an IF Statement in Excel

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:

  • Type =IF( into cell G3
  • Type F3>=40 to make your logic statement and a , to end it
  • Type "Yes", to place what value you wish to see if F3 is greater than or equal to 40
  • Type "No" to place what value you wish to see if F3 is not greater than or equal to 40
  • Close your formula with a closing parenthesis )
  • Your G3 formula should look like:
    =IF(F3>=40,"Yes","No")
  • Fill this formula down
    Excel_if_then_1

IF 2 Steps (nested):

  • Type =IF( into cell H3
  • Type F3>=40 to make your logic statement and a , to end it
  • Type "Yes", to place what value you wish to see if F3 is greater than or equal to 40
  • Instead of typing "No" this time, we want to add in another IF statement for PTO hours
  • Type IF(E3>0, "Yes", "No"
  • This time, close your formula with two closing parenthesis )), one for each IF statement.
  • Your H3 formula should look like:
    =IF(F3>=40,"Yes",IF(E3+F3>=40,"Yes","No"))
  • Fill this formula down
    Excel_if_then_2

Review:

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 "Yes" to 1 and all of the "No" to 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.

Excel_if_then_3

Working with Absolutes

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 E9
=SUM(E3,E4,E6,E7,E9)

The below VLOOKUP formula references Cell A2, and Columns J through M.
=VLOOKUP(A2,J:M,4,0)

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 =A2.
If I extend this formula to the right once, the new formula will be =B2.
If I extend this formula down once, the new formula will be =A3.
Making my original formula =$A$2 causes both movements to stay as 5
SumAbsolute2


Absolute Example

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.

  1. We have the total number of hours billed every month (275)
  2. We also have the percent of time our employees spend doing non-billable work per month
  3. Let’s total these together to get the Total Hours billed per month

SumAbsolute1

  1. Click in Cell H2
  2. Multiply E10 and B13=E10*B13
  3. 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.
  4. Change your formula to =E10*(1+B13)
  5. 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.
SumAbsolute2

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.

  1. Go back to your H2 formula
  2. Click between the E and the 10
  3. Press F4 once
  4. You will see a $ appear to the left of E and a $ 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 E10
  5. Hit Enter to get out of your formula
  6. 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!

SumAbsolute1

How to Handle Excel VLOOKUP Errors

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:
Excel_VlookupEnd
vlookupfeatured


#N/A

#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:
IFERROR() 
and
IF(ISNA())


IFERROR()

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

  1. Click in the Vlookup formula in Fx
  2. After the equal sign type IFERROR(
  3. You will see excel will prompt you with Value, Value If Error.  The Vlookup formula result is going to be our Value
  4. Place your cursor to the far right of the formula
  5. Press comma
  6. Type "" and close your parenthesis
  7. Press ENTER
    Your formula result should look like:
    =IFERROR(VLOOKUP(A2,J:M,4,0),"")
  8. Fill the formula down to remove the errors
    Vlookup4

ISNA()

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:
=ISNA(F2)
ISNA.jpg

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:
=IF(ISNA(VLOOKUP(A2,J:M,4,0)),"",(VLOOKUP(A2,J:M,4,0)))

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.

isna1


Unique Identifiers

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.Vlookup5

In the above example, I’ve changed our:
Lookup_Value from A2 to B2
Table_Array from J:M to K:M
Col_index_num from 4 to 3
Range_Lookup stays at 0

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
“Last,First”
“Last, First”
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.

How to Write a VLOOKUP Formula in Excel

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.

Setup

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.
Excel_Vlookup

Vlookup 1

Writing a VLOOKUP

Steps:

  1. Label Column F as “Address”
  2. Hit the Fx function button
  3. Search for Vlookup and hit go
  4. Select Vlookup and hit OK

Your screen should look like the below
Vlookup2

Lookup_Value:
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.
Type A2 or left mouse click on cell A2

Table_Array:
This is going to be the set of Data or “Range” that you want to find your address in; in this case, J2 through M5.
Using the lessons learned in Navigate and Select Data, you can either:

  • Type in J2:M5
  • Select J2 with your mouse and drag the selection over to M5
  • Select J2 and press ctrl+shift+end
  • Select J2 and press ctrl+shift+down arrow and then ctrl+shift+right arrow
  • Select columns J through M or J:M

For the purposes of this exercise, I will do the last option and explain why further on.

Col_index_num:
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.

Range_Lookup:
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.

Steps cont’d

  1. Hit OK to complete your formula and you can see that it has brought over an address for Doe,John.
  2. 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.

Vlookup3

In the next post, we will go over ways to handle the #N/A formula errors as well as best practices for Vlookups.

How to write a basic SUM in Excel

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 =Sum
  • Clicking on the function Fx button
  • 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 E10

File: Excel_Sum
DataforSum


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.
Type =Sum(E2:E9)
As you type, you will see excel display the rage in your formula with a colored outline
Hit Enter
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
sum_cellform


Function Button

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 E3
Number 2 will be E6
Number 3 will be E9
The formula result should be:
=SUM(E3,E6,E9)
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:
=SUM(E3,E4,E6,E7,E9)
or you could set it like this, combining the cells into a range using the colon.
=SUM(E3:E4,E6:E7,E9)

Note how the different formulas change the outline color of the cells.


AutoSum

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

autosum


Copy, Paste, and Move Data

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.

Autofit

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
autofit


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.

Copy

There are three options for copying.
-Choose Copy from the menu
-Right Click your selected data and choose Copy
-Press Ctrl+C

Now that your data is copied, let’s paste it.

Paste

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
-Press Ctrl+V
-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

pasteautofit


Moving 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.
moving data

Navigate and Select Data

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.
RubixCube2


Data Selection

Selecting all

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).

ExcelColumnSelectionExcelRowSelectionExcelAllSelection

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

Ctrl+Shift+End:
Selects all data from A2 to your last column, last row of data
ctrlshftdown

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

Ctrl+Shift+Down_Arrow:
Selects all data from A2 to the last cell in Column A

Ctrl+Shift+Right_Arrow:
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

ctrlshftright