Top 5 Things to Know in Excel

I remember listing “Microsoft Excel” as a skill on my resume just out of college. The only functions I knew how to use were SUM() & Concatenate(). I also knew that to start an equation, I needed to put an equals sign at the beginning of the cell. “Why would I need to know anything else?” I thought.

Since then, I have slowly expanded my knowledge of Excel and present to you what I now believe are the top five things to know in Excel if you want to list it as a “skill” on your resume:

1. Pivot Tables

If there were only one feature of Excel you could learn in the 15 minutes before a job interview, it would be Pivot Tables. This allows you to summarize hundreds or thousands of lines of data in a few moments. Add, Subtract, Divide, Count, & Parse data with this feature and impress the higher ups.

Articles to Check Out:

2. INDEX-MATCH and/or VLOOKUP

VLOOKUP

VLOOKUP is a function you can use to lookup data from a table. Think of sales data. Imagine you had a table listing the dollar amount of sales by the date. VLOOKUP would allow you to lookup the sales $ on a specific day. There are certain limitations to this function, very specific ways the data must be structured, and quite frankly a little confusing given that there are usually easier ways to find the same information.

Check out: VLOOKUP Article

INDEX & MATCH

In my opinion these are the formulas you should learn before learning VLOOKUP. With these two functions together, you can not only lookup data from a data set, but you can match two sets of data together for QA purposes, ensure data is consistent across multiple reports, and much more.

Articles to Check Out:

3. Informational Functions & Boolean Logic

Boolean Logic can be distilled down into two words: TRUE & FALSE. Boolean Logic is a form of logic where you’re testing whether or not an argument is TRUE or FALSE. Based on the outcome of that test, you can then test for other criteria, write a certain Text string, or perform a function.

Articles to Check Out:

Once you’ve learned to evaluate whether or not something is TRUE or FALSE, you can follow that up by wrapping the result in an IF() statement, i.e. IF this statement is TRUE, do THIS. IF not, do THAT.

4. Basic Text Formulas

There are a large group of TEXT() formulas that are built specifically to deal with, you guessed it, TEXT. These functions are very useful for pulling out only the part of the string you most need from a cell.

The most commonly used formulas (based on my experience) are found below:

5. How to use the IF formula

The IF function is vital in the Excel Ninja’s repertoire. It allows you to easily add conditions to your formulas. If the formula is TRUE, do this. IF it’s FALSE, do that.

NESTED IF statements are when a user stacks multiple IF statements on top of each other. Imagine you had a statement that could be TRUE or FALSE, and you built an IF statement around that. Then imagine that if that statement evaluates to TRUE, there would be another two situations that could occur. You would have to “nest” your IF statement within the first IF statement.

 

RANDBETWEEN – Excel Function

What it does: The RANDBETWEEN() Function generates a random number between two integers.

Plain English: Rather than generating a random number between 0 and 1, RANDBETWEEN() generates an integer between a given range of integers that you provide. For example, you may want to pick 6 random lottery numbers between 1 and 35. This formula could help generate those.

Arguments: RANDBETWEEN(lower number, higher number)

  • Lower number – the lowest integer possible you want to generate
  • Higher number – the highest integer possible you want to generate

Blog Posts using RANDBETWEEN()

  • Article 1

Videos using RANDBETWEEN()

  • Video 1

RAND – Excel Function

What it does: The RAND() Function generates a random number between 0 and 1.

Plain English: The random function is a brother to the RANDBETWEEN() function. It generates a number in between 0 and 1, like .6231. This is a great function for creating sample data or running simulations where you need a random number generated.

Syntax: RAND()

  • (no arguments needed for this function)

Blog Posts using RAND()

  • Article 1

Videos using RAND()

  • Video 1