Microsoft_Excel_2013_logo.svg

3 Excel Formulas You Must Know For Your Next Interview

As you prepare for your next job interview or promotion, there are three Excel formulas that you must know if you want to impress your interviewer. These three Excel formulas are simple to learn, easy to implement into almost any project, and sure to impress your prospective boss. In addition, these formulas are great time-savers and will make your job easier, wherever you end up!

1. VLOOKUP

This is one of the most crucial Excel formulas to learn, yet it is also one that a lot of people have trouble mastering. So, if you can master this formula, it’s a good way to showcase your Excel skills. Once you know how to use it, you’ll also find that VLOOKUP has a broad range of uses and numerous applications. Knowing how to use the VLOOKUP formula is a foundational Excel skill that will open many doors for you.

2. SUMIF/COUNTIF

Technically, SUMIF and COUNTIF are two different Excel formulas, but their structure and function is similar. Rather than doing a simple add or count, these two functions allow you to apply conditions to your formula. So, if you have a column of data, and you only want to add or count lines that meet a particular set of criteria, you can use these two functions to accomplish that. Aside from Excel formulas job interviewshowing your future boss that you know how to use Excel, demonstrating an understanding of these two Excel formulas shows that you know how to use Excel efficiently. These two formulas allow you to integrate a conditional (if) statement and an addition command into one formula, turning what would normally be a two-step process into a one-step process. This saves you time and allows you to work more efficiently. If you know how to use these Excel formulas (and others like) them, you will demonstrate that you are a smart Excel user.

3. IF/AND/OR

Again, this is not technically “one” Excel formula, but the use and function of these three formulas is very similar. IF, AND, and OR form the basis of conditional statements in Excel. These three formulas allow you to evaluate data and other formulas against a set of given criteria. They allow you segregate your data and to pull out and evaluate only that data which you really need. If you know how to use these three Excel formulas, you can build extensive logic trees and other complicated formula strings. If you can demonstrate to a future boss that you can use these three formulas proficiently and creatively, you will be sure to impress.

Learn More Than Just How to Use  Excel Formulas

Get even more free tips and tricks when you signup for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.

Microsoft_Excel_2013_logo.svg

How to Use the Excel IF Function Like Batman

If you know how to use the Excel IF Function, you can easily write formulas that do such things as determine whether to offer someone a special discount, flag a series of test scores as Pass/Fail, or even override #N/A or #DIV/0 error messages. Once you’ve learned the basics of how to create a simple conditional statement using the Excel IF Function, you can also create logic trees by nesting the formulas. The IF Function is one of the simplest and easiest functions to learn, but is also one of the most flexible and powerful.

How the Excel IF Function Works

The Excel IF Function is actually fairly straight-forward and only consists of three arguments:

=IF(Conditional-Statement,Value-If-True,Value-If-False)

First, you create a conditional statement. This would be something to the effect of “If A2 is greater than 3″. Excel will then evaluate that statement as either TRUE or FALSE. If the statement is TRUE (e.g. if A2 = 5, then A2>3 will evaluate as TRUE), the function will return the second argument (Value-If-True) as the formula result. If the statement evaluates as FALSE (e.g. A2 = 1), then the function will return the third argument (Value-If-False) as the formula result. So, for the formula, =IF(A2>3,”A”,”B”), where A2 = 5, the formula will return “B”. If A2 = 1, the formula will return “A”.

Taking Your Excel IF Function to the Next Level

The real power of the Excel IF Function lies in its flexibility. In the example above, we used “A” and “B” as our Value-If-True and Value-If-False values. However, you can use nearly anything for those arguments (just be sure to put text in quotes). Instead of using “A” and “B”, we could have used “Over” and “Under” or “Seattle” and “Washington, DC” or simply used a number.

We can also use functions instead of text or number values. For example, we could say

=IF(A2>3,SUM(A2:A5),SUM(A6:A10))

Excel IF Function

=IF(A2=”Joker”,”I am the Knight”,”Bruce Wayne”)

So, in this situation, if A2 = 5, then the function will add all of the values in cells A2 through A5 together and report the sum. On the other hand, if A2 = 1, then it will add all of the values in cells A6 through A10 and report the sum. We can do this with nearly any function (this is called “nesting”). We can even use other IF functions as our arguments, as in

=IF(A2>3,IF(A=5,"A","C"),"B")

So, in this situation, if A2 = 5, then the formula will evaluate the first function (A2>3) as TRUE, which will cause it to evaluate the second argument (the IF function) as TRUE, which results in “A”. If A2 = 1, then the first function evaluates as FALSE, which directs Excel to skip over the second IF function to the Value-If-False argument, which is “B”.

Don’t Stop Here! Learn Even More!

While nesting functions may seem overwhelming at first, it doesn’t have to be. With additional study and practice, you’ll soon be creating complicated logic trees and evaluating complex conditional statements.

To learn more about formula nesting, sign up for our free newsletter. When you sign up, you’ll get free Excel tips and tricks directly in your inbox. You’ll also be the first to receive exclusive discounts on our training classes.

Microsoft_Excel_2013_logo.svg

4 Advanced Excel Formulas Everyone Should Know

Whether you are applying for a new job or looking to get ahead in your current one, there are four key advanced Excel formulas that you should know in order to be successful. The following four formulas are extremely flexible and extremely powerful. Used by themselves or with others, these four advanced Excel formulas will enable you to amplify your data analysis.

1. VLOOKUP Formula

The VLOOKUP formula is one of Excel’s most useful functions. Consequently, knowing how to design and use VLOOKUPs is an Excel skill frequently requested by hiring managers. If you are confronted with a large set of data, a VLOOKUP allows you to pull select values out of the data set by defining a look-up value (such as a name or other unique key). The look-up value tells Excel which row in your data set to pull data from. Once you’ve specified the row you need via your look-up value, you simply need to tell Excel which column to pull data from. The VLOOKUP formula will pull the data at the intersection of the row and column you have specified. So, if your data set is very large, the VLOOKUP formula makes it very easy to quickly find the specific data you need. This makes the VLOOKUP one of the most important advanced Excel formulas you can learn.4 key advanced excel formulas

2. IF Formula

The IF formula allows you to insert logic into your data analysis. It functions as a simple “If…, then…” statement does in regular speech. Using the IF formula, you can create a logical test (such as A1>0), have Excel evaluate your statement/test, and then create two outcomes (based on whether the statement/test you have created evaluates as true or false). The real power in this particular advanced Excel formula lies in nesting the functions, which enables you to create logic trees. You can create an initial IF formula and then build additional IF formulas to serve as the outcomes of the initial formula. So, if A1>0 evaluates as true, you can tell Excel to evaluate the IF formula A2>10, and so on. Using the IF formula, it is very easy to design very complicated decision-making sequences.

3. CONCATENATE Formula

The CONCATENATE formula is a text formula, and its use is tied to the most important advanced Excel formula (the VLOOKUP). CONCATENATE enables you to combine two or more strings of text (or numbers) together. For example, if cell A1 contains “First” and cell A2 contains “Last”, then you can use CONCATENATE to create the value “FirstLast” in cell A3. This is especially helpful if you need to create unique look-up values (or keys) for a VLOOKUP formula. And if you have trouble remembering how to spell “Concatenate”, you can simply use the “& “symbol between your cell references (i.e. use A1&A2 rather than CONCATENATE(A1,A2)).

4. AND/OR Formulas

AND and OR formulas are two very similar types of logical formulas (much like the IF formula). The reason they are included on our list of advanced Excel formulas is because when they are used in conjunction with the IF formula, the flexibility of the IF formula increases exponentially. AND works just like you would expect it to. If everything inside of the formula evaluates as true, then the entire formula evaluates as true. If even one function inside the formula evaluates as false, the entire formula evaluates as false. OR works very similarly, except as long as at least one function inside the formula evaluates as true, the formula will evaluate as true. Only if every function inside OR evaluates as false will the entire formula evaluate as false. So, if you nest an AND or OR formula inside an IF formula, you can create extremely powerful advanced Excel formulas.

Learn Even More Advanced Excel Formulas

While these four formulas are some of the most important formulas to learn, there are many other advanced Excel formulas that will enable you to unlock the full potential of Microsoft Excel. Signup for one of our classes or upcoming webinars to learn about even more formulas and how you can use them in your everyday work.

You can also join our mailing list to receive free Excel tips and exclusive discounts on our training classes right in your inbox!