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

Why You Should Learn to Use the Excel Index Match Function

The Excel Index Match function is one of Excel’s most flexible and powerful lookup functions and has several advantages over the traditional VLOOKUP function. The Excel Index Match function is much more flexible than the VLOOKUP function and is able to overcome many of the VLOOKUP’s structural limitations. Index Matching also requires less processing power from your computer, which is very important if you are building a spreadsheet with thousands of lines. However, very few people know how to create an Index Match function, much less use one. In the article below, I will show you how to create Excel Index Match functions and how you can use them to solve complicated lookup problems.

The Excel Index Match Function: A Summary

The Excel Index Match function is actually a combination of two different functions: INDEX and MATCH. The basic way it works is that you define the array with the item you want to pull in the INDEX function, and then you use the MATCH function to identify the row. Here’s the “official” Excel syntax:

=INDEX(Array,MATCH(Lookup_Value,Array,0))

Here’s the function in plain English:

=INDEX(Column-You-Will-Pull-Value-From,MATCH(Lookup-Value,Column-You-Will-Find-Lookup-Value-In,0))

How the Function Works in Real Life

The most important advantage of the Excel Index Match function is that, unlike the VLOOKUP function, you can use Index Matching to lookup values to the left and the right of your reference column. In the example below, if you wanted to pull the ID number of a particular fruit using the fruit name in a VLOOKUP, you couldn’t do it. You would have to move the ID column to the right of the Fruit column in order to avoid generating an error with a VLOOKUP.

Excel Index Match Example

However, if you use Index Matching (as in the example below), it is very easy to pull values to the left of your reference column.

Excel Index Match Example

In this example, the column we want to pull our final result (or value) from is Column A, ID. So, that goes into the first part of our function. Then our lookup value is simply D2, which contains the word Apples. Next, we define the column we want to find the lookup value in as Column B, Fruit. Finally, we set the last function option as 0, which tells Excel we want an exact match to our lookup value in the lookup. Here’s what our final formula looks like:

=INDEX(A:A,MATCH(D2,B:B,0))

The formula works by identifying the row number in the lookup column (in this example Column B) by finding the lookup value in that column. It then returns whatever row number the lookup value is in as the result. So, in this example, “Apple” was in Row 2 of the lookup column. So, the MATCH function returns a value of 2. Then, in the INDEX portion of the function, Excel takes the row number identified in the MATCH function (in our example, 2) and pulls whatever value is in row 2 of the column you want to pull a value out of. So, in this example, since the column we want to pull a value out of is Column A and the row number (defined by the MATCH function) is 2, Excel pulled the item in row 2 of Column A, “1”. Below is a graphic demonstration of how the function operates:

Excel Index Match Explanation

Once you are able to master Index Matching, you will be able to perform much more complicated data lookups than you would with a simple VLOOKUP.

Don’t Stop Here

There are many more valuable lookup functions that you can use in Excel beyond VLOOKUPs and Index Matching. Sign up for our free email newsletter to learn more about these functions and other advanced functionality in Excel. You’ll also get exclusive discounts for our training classes and webinars.

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!

Microsoft_Excel_2013_logo.svg

Five Advanced Excel Skills Every Job Applicant Should Know

Find a new job with advanced Excel skillsAdvanced Excel skills are among the most commonly requested job skills hiring managers request of new employees. Many times, a job posting will simply ask for “Advanced Excel Skills” among the list of job requirements. What are these skills? What does it take to become an advanced Excel user? What follows is a list of key Excel skills that every advanced user should know.

1. Pivot Tables

Pivot Tables are among Excel’s most mysterious functions. Yet, learning how to use them is fairly straight-forward. Pivot Tables simply summarize data, and they provide you with additional functionality to present your data in almost any way you can imagine. Want to quickly get a count of how many employees work in each department in your office? Create a Pivot Table. Want to figure out what the average performance score is across several different work units? Create a Pivot Table. Need to analyze a complex report with dozens (or hundreds) of data columns? Create a Pivot Table. Pivot Tables are flexible and dynamic, which makes them extremely useful in the business world. Knowing how to create Pivot Tables is one of the most crucial advanced Excel skills you can learn. Become a master of Pivots, and you’ll be sure to impress any hiring manager.

2. VLOOKUP Function

If Pivot Tables are used more frequently than any other Excel feature in business offices every day, VLOOKUPs are a close second. Whether you’re validating financial reports or simply summarizing and analyzing data, you’ll probably use the VLOOKUP function several times in the course of your daily work. The real power of the VLOOKUP function lies in its simplicity, enabling you to quickly find and pull the exact data you need out of large data sets. Once you learn how the VLOOKUP works, you’ll find an increasing number of opportunities for its application in your everyday work. Along with Pivot Tables, VLOOKUPs are a key advanced Excel skill. If you know nothing else, knowing how to create Pivot Tables and use VLOOKUPs will put you ahead of most applicants in the job market.

3. Paste Special

Paste Special is another key advanced Excel skill. Any job that involves Excel (which will be most jobs) will involve moving data around. Paste Special allows you to unlock the power of the Cut/Paste functions. Maybe you only need to copy the formatting of a cell and not its content, or maybe you need to copy the output value of a formula without copying the formula itself. Paste Special allows you to do all of this and more! Everyone knows how to cut and paste, very few know how to use Paste Special.

4. Macros

Knowing how to automate tasks with code will set you far and above the competition when you apply for jobs. In Excel, you can do this with Macros, and you don’t even need to know anything about code to get started! Learning how to use Macros will enable you to write code that will automatically complete repetitive tasks for you. So, if you need to run reports every week (or every day), you can write some Macros to automate things such as the formatting and even some of the analysis. This will enable you to complete your work faster and more consistently. Learn how to use Excel to write a few simple Macros, and you will be able to impress hiring managers with your ability to fly through work assignments using your advanced Excel skills.Creating powerful charts is an advanced Excel skill

5. Charts

One of the final advanced Excel skills that will set you apart from the competition is your ability to design useful and informative charts. Anyone can create a chart (or graph). It is much more difficult to create a chart that provides a useful insight into some data. Knowing how to stack charts on top of each other and how to use both the primary and secondary axis are advanced Excel skills that make a new employee very valuable. If you intend to do any sort of data analysis, knowing how to create dynamic and informative charts is crucial.

Advance Your Job Search with Advanced Excel Skills

Knowing how to use Excel is an important part of most jobs you will apply to. Having a set of advanced Excel skills, like those discussed above, will set you apart from the legions of applicants you are competing against. If you master these skills, you will stand apart and above most of your competition.

Join our mailing list to learn how to use these and other advanced Excel skills. You’ll get free tips directly in your inbox that will help you become an Excel master, and you’ll also receive exclusive discounts to our training classes.

Microsoft_Excel_2013_logo.svg

How to Use the VLOOKUP Function

Check out our latest video, which shows you how to use a VLOOKUP function to ensure the contents of two different lists match. The VLOOKUP is one of Excel’s most useful functions. Watch this video to learn how to use it in your everyday work.