While some look at it and whimper, others know this spreadsheet program like the back of their hand. And, whether you hate it or love it, there’s a good chance you will at some point have to get close and personal with Microsoft Excel in your career.
Since its creation in 1985, Excel has become ingrained in how we do business. With over a million companies using MS Office globally, it’s no wonder that proficiency in using the suite, including Excel, is desirable in the workplace.
Though widely used by professionals that deal with numbers, like accountants and data analysts, Excel can prove useful to anyone regardless of industry — as it saves time! From project managers to digital marketers to administrative assistants, Excel simply makes life easier. If you know how to use the excel tools, that is.
So, to help you get better acquainted with the number cruncher, we’ve listed 10 powerful Excel functions and tools that will change how you organize, analyze, and view your data.
1. SUM function
If you’re just getting started and looking to learn some basic Excel skills fast, let the SUM function be your starting point. Though straightforward, this useful function will help you consistently save time. In other words, your calculator can officially retire!
As the name suggests, this formula adds up all the numbers in the range of cells you choose. For example, if you have numeric values in cells A1 to A5, you could quickly sum them up entering =SUM(A1:A5) in an empty cell.
Here's how to use the SUM function:
2. AVERAGE function
Much like the SUM function, AVERAGE is one of the most widely used Excel functions out there. As you’ve probably guessed, using this function allows you to quickly calculate the arithmetic mean across any range of cells.
Let’s say you’ve got numbers in cells A1 to A5. Selecting an empty cell, typing in =AVERAGE(A1:A5), and hitting “enter” would give you the average value for those numbers.
Here's how to use the AVERAGE function:
3. IF function
Using an IF statement allows you to quickly compare two values by automatically generating two results: True and False. Of course, these labels can be renamed. (If you’re confused, this will make more sense in a moment!)
Let’s say you’re a teacher grading your students’ tests. In columns A and B, you’ve got your students’ names and surnames, and in column C you’ve got their grades. If a “pass” is a score of 70% or higher, you would type =IF(C1>70,"Pass","Fail") into a new cell. If a student has scored less than 70%, the function will automatically display “fail” next to their mark. If the opposite is true, the cell will display “pass”.
Though that’s the basic idea, IF functions can do a lot more than just compare two values; they can also include calculations or be combined to execute multiple comparisons.
Here's how to use the IF function:
4. CONCATENATE function
CONCATENATE allows you to merge text from various cells into one. For example, if you have a column of surnames (A) followed by a column of first names (B), you could combine these bits of text to form people’s full names in a third column (C). To do this, you would go to column C and type in =CONCATENATE(A1; B1) to form the full name of the first person on your list.
There’s also an alternative (and a little more convenient!) way of doing this. You can combine text items using the ampersand operator. This is what it would look like, in place of the function above: =A1 & B1.
Here's how to use the CONCATENATE function:
5. COUNT function
In a large Excel table or range, some cells might contain digits while others might contain letters. To quickly find out how many of the cells in front of you contain numerical values only, you would use the COUNT function.
To do this, you would click on an empty cell and type in =COUNT(A1:A20) to count how many times numbers appear in the range A1 to A20. If 10 of those cells contain a numerical value, the result will be 10.
Here's how to use the COUNT function:
6. MATCH function
This function is really handy when you’re searching for a specific word or number in dozens of rows or columns.
Let’s say you’ve got a list of twenty cities around the world (column A) alongside their population size (column B) and you’re trying to locate a particular one. Instead of scrolling through your list, you could click on an empty cell and simply type in =MATCH(“London”, A1:A20,0). If the number “18” appeared in the cell, it would mean that the city you were looking for can be found in row 18.
Here's how to use the MATCH function:
7. INDEX function
This function returns a specific value from within a range or table in your data set. Let’s say you have a column of names of marathon runners (A), alongside their ages (B), bib numbers (C), and the time it took each one to complete the race (D).
Clicking on an empty cell, you could type in =INDEX(A1:D20,1,4) to produce the time it took the first runner to finish the race. In this case, 1 is the number of the row, and 4 is the number of the column.
Once you’re comfortable using these formulas, you can try out a combination of the INDEX and MATCH functions. Used together, with MATCH nested inside INDEX, these two functions allow you to execute dynamic, two-way lookups.
Here's how to use the INDEX function:
8. PivotTable tool
This is one of those advanced Excel skills that could look good on your résumé! In simple terms, pivot tables help you analyze, summarize, and identify patterns in your data.
This Excel tool enables you to focus on areas of interest by expanding and collapsing different sets of data, moving rows to columns and vice versa, and filtering, sorting, and grouping information.
Once you’ve prepared your table by clicking the “Insert” tab, you can also create a visual representation of your data with a PivotChart. These customizable charts come in various forms, such as bar, line, area, and pie charts.
Here's how to use the PivotTable tool:
9. Data validation
Data validation provides a way of restricting the values or types of data that can be entered into a cell. Perhaps the most common way of doing this is by using drop-down lists.
To use this data tool, first select the cells you want to create a rule for, and then select “Data Validation”.
In the settings, you can choose from various options, such as “whole number”, to accept only whole numbers being entered; “list”, to only allow data to be selected from a list; and “text length”, to set a limit to the length of the text being typed in.
Here's how to use the data validation tool:
10. Conditional formatting
Learning conditional formatting could really lift your intermediate Excel skills to a more advanced level. Plus, it can make your spreadsheets look extremely professional!
What conditional formatting does is it basically makes specific cells more identifiable based on your set of criteria. For example, you could ask Excel to automatically highlight all cells that contain a particular value.
To do this, you would look at the settings under “Conditional Formatting” and choose to highlight any cell that contains a particular word, number, or range of numbers, such as any digit between 10 and 100.
Here's how to use the conditional formatting tool:
The benefits of developing powerful Excel skills include saving time, enjoying an enhanced level of organization, and arriving at useful insights through automation. This is precisely what renders Excel skills a valuable asset in any industry.
Though the spreadsheet application has hundreds of functions you can use, learning Excel doesn’t have to be a long, frustrating process. Once you’ve familiarized yourself with the most commonly used formulas and tools, you can begin to add new skills little by little. With countless resources, examples, and Excel training videos available on the Microsoft support page, you could become an Excel expert in no time!
How do you find Excel? Are there any handy tricks you’ve learned that you could share? Drop them in the comments and help everyone out!