If you’re anything like me, you spend a good chunk of your day working in Excel. Whether it’s for work, school, or personal use, this program has become a staple in our lives. And while I’m definitely not an expert (yet), I’ve slowly been learning new tips and tricks to make my life a little bit easier.
“INDEX/MATCH is especially useful if you have a large data set that you need to search through frequently. Trust me; your future self will thank you for using this function!”
One thing I’ve learned is that there are certain functions that can make working in Excel much simpler. In this article, I’ll be sharing 10 of those functions with you so that, hopefully, your Excel experience can be a little more enjoyable too!
1. INDEX/MATCH
2. VLOOKUP
3. COUNTIF/COUNTIFS
4. SUMIF/SUMIFS
5. CONCATENATE
6. TEXTJOIN
7. TRANSPOSE
8. IFS
9. MAXIFS/MINIFS
INDEX/MATCH
If you’ve never used INDEX/MATCH before, you’re in for a treat. This function allows you to look up values in a table based on the criteria you specify. For example, let’s say you have a list of employee names and ID numbers, but you want to be able to look up an employee’s name based on their ID number (instead of scrolling through the entire list to find the right name).
To do this, you would use the INDEX function to find the value in a specific row and column and the MATCH function to specify which row you want. So in our employee example, you would use MATCH to find the ID number of the employee you’re looking for and then INDEX to return the name from that same row.
INDEX/MATCH is especially useful if you have a large data set that you need to search through frequently. Trust me; your future self will thank you for using this function!
VLOOKUP
VLOOKUP is very similar to INDEX/MATCH, but it only allows you to look up values from left to right (instead of up and down like with INDEX/MATCH). This function is best used when you have a table of data that’s sorted horizontally, and you need to look up values from it.
For example, let’s say you have a list of employees and their ID numbers, but this time the ID numbers are in the first column, and the names are in the second column. If you wanted to use VLOOKUP to find an employee’s name based on their ID number, you would specify the ID number as the lookup value and tell Excel which column the name is in. Then, whenever you enter an ID number into your formula, it would return the corresponding name.
COUNTIF/COUNTIFS
The COUNTIF function is a handy way to quickly count the number of cells that meet certain criteria. For example, let’s say you have a list of 100 names, and you want to know how many of those names start with the letter “A.” To do this, you would use COUNTIF to specify the range of cells you want to search through (in this case, the entire list of names) and what criteria you’re looking for (names that start with “A”).
COUNTIFS works similarly but allows you to specify multiple criteria. So if you wanted to know how many names in your list started with “A” AND were four letters long, you could use COUNTIFS to do that.
SUMIF/SUMIFS
SUMIF and SUMIFS are very similar to COUNTIF and COUNTIFS, but instead of counting the number of cells that meet certain criteria, they sum the values in those cells. So if you had a list of numbers and you wanted to know the sum of all the numbers that were greater than 5, you could use SUMIF to do that.
CONCATENATE
The CONCATENATE function allows you to combine text from multiple cells into one cell. This is especially useful if you need to combine data from separate cells but don’t want to use the & operator (which can get confusing if you’re concatenating a lot of cells).
For example, let’s say you have a list of first names in one column and last names in another column. If you wanted to combine those into a single cell that said “First Last,” you could use CONCATENATE to do that. You would just need to specify the range of cells for each column, and Excel would do the rest.
TEXTJOIN
TEXTJOIN is very similar to CONCATENATE, but it has a few handy features that make it worth using instead. First, TEXTJOIN allows you to specify a delimiter – this is the character that will be used to separate the text from each cell. Second, it can automatically skip over empty cells, which is really helpful if your data is messy.
For example, let’s say you have a list of first names in one column and last names in another column, but some of the cells are blank. If you wanted to use TEXTJOIN to combine the contents of those cells into a single cell, you would just need to specify the range of cells for each column and what character you want to use as a delimiter (in this case, a space). Then TEXTJOIN would take care of the rest.
TRANSPOSE
The TRANSPOSE function is really handy if you need to change the orientation of your data – that is, if you need to convert a column into a row (or vice versa). This is especially useful when you need to copy data from one place to another, but the destination only supports one orientation.
For example, let’s say you have a list of names in a column, and you want to paste that into a row. Normally, if you just copied and pasted the data, Excel would keep it in a column. But if you used TRANSPOSE to first convert the column into a row, then copied and pasted it, Excel would keep it in a row.
IFS
The IFS function is a great way to check multiple conditions at once and return a different result for each one. This is especially handy when you have a complex set of conditions that you need to check – it’s much easier to read than a nested IF statement.
For example, let’s say you have a list of numbers and you want Excel to return “high” if the number is greater than 5, “medium” if the number is between 2 and 5, and “low” if the number is less than 2. You could use the IFS function to do that by specifying the three conditions and what Excel should return for each one.
MAXIFS/MINIFS
MAXIFS and MINIFS are similar to MAX and MIN, but they allow you to specify criteria that your data must meet in order to be considered. So if you had a list of numbers and you wanted to find the highest number that was less than 5, you could use MAXIFS to do that.
