Looking to excel in Excel? You should read this…
Microsoft Excel is an essential tool in many job roles, from finance and marketing, to data analysis and administration. While Excel offers a vast array of functionalities, mastering just a few of its key formulas can significantly boost your efficiency and productivity.
Whether you’re a beginner or looking to enhance your spreadsheet skills, here are 10 Microsoft Excel formulas everyone should know:
1. VLOOKUP
VLOOKUP (Vertical Lookup) is one of the most widely used Excel functions. It searches for a value in the first column of a range and returns a value in the same row from another column.
Function: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
How to use VLOOKUP:
- Select the cell you want the result to appear in
- Type =VLOOKUP(
- Enter the value you want to search for (e.g., A2)
- Enter the range of cells to search within (e.g., B2:D10)
- Enter the column number from which to return the value (e.g., 3 for the third column)
- Specify TRUE for an approximate match or FALSE for an exact match (e.g., FALSE)
- Press Enter
2. SUMIF
SUMIF is used to sum values in a range that meet a specific condition or criteria.
Function: SUMIF(range, criteria, [sum_range])
How to use SUMIF:
- Imagine you have a sales list where column A has product names and column B has sales figures
- To sum the sales of a particular product, use: =SUMIF(A2:A10, “ProductName”, B2:B10)
- This adds up only the sales figures for “ProductName”
3. IF
The IF function performs a logical test and returns one value for a TRUE result and another for a FALSE result.
Function: IF(logical_test, value_if_true, value_if_false)
How to Use IF:
- To assign a “Pass” or “Fail” based on scores in column A, use: =IF(A2>=50, “Pass”, “Fail”)
- This checks if the score in A2 is 50 or above and returns “Pass”; otherwise, it returns “Fail”
4. INDEX & MATCH
INDEX and MATCH are powerful alternatives to VLOOKUP, offering more flexibility. INDEX returns a value from a specific row and column within a range, while MATCH finds the position of a value within a range.
Function: INDEX(array, row_num, [column_num]) + MATCH(lookup_value, lookup_array, [match_type])
How to use INDEX & MATCH:
- First, find the position of a value with MATCH: =MATCH(“ProductName”, A2:A10, 0)
- Then, use INDEX to find the value in the corresponding row and a different column: =INDEX(B2:B10, MATCH(“ProductName”, A2:A10, 0))
- This combination returns the price of “ProductName” from column B
5. CONCATENATE (or CONCAT)
CONCATENATE (or the newer CONCAT) joins two or more text strings into one.
Function: CONCATENATE(text1, [text2], …) or CONCAT(text1, [text2], …)
How to use CONCATENATE (or CONCAT):
- To combine first names in column A and last names in column B into a full name, use: =CONCATENATE(A2, ” “, B2)
- This joins the first name and last name with a space in between
6. TEXT
The TEXT function converts a number to text in a specified format.
Function: TEXT(value, format_text)
How to use TEXT:
- To format a date in “DD/MM/YYYY” format, use: =TEXT(A2, “DD/MM/YYYY”)
- This changes the date in A2 to the desired format
7. SUMPRODUCT
SUMPRODUCT multiplies corresponding elements in arrays and then sums those products.
Function: SUMPRODUCT(array1, [array2], …)
How to use SUMPRODUCT:
- If column A contains units sold and column B contains the price per unit, calculate total revenue with: =SUMPRODUCT(A2:A10, B2:B10)
- This multiplies each unit sold by its price and then sums the total
8. COUNTIF
COUNTIF counts the number of cells in a range that meet a specific condition.
Function: COUNTIF(range, criteria)
How to use COUNTIF:
- To count how many times a specific product appears in column A, use: =COUNTIF(A2:A10, “ProductName”).
- This returns the number of times “ProductName” is listed.
9. LEFT, RIGHT, MID
These functions extract a specified number of characters from a text string, starting from the left, right, or a specific position (MID).
Functions: LEFT(text, [num_chars]), RIGHT(text, [num_chars]), MID(text, start_num, num_chars)
How to use LEFT, RIGHT, MID:
- To extract the first three characters of a product code in A2, use: =LEFT(A2, 3)
- To get the last four digits of a phone number in B2, use: =RIGHT(B2, 4)
- To extract characters from the middle of a string, starting at the 2nd character and taking the next 4 characters, use: =MID(A2, 2, 4)
10. LEN
LEN returns the number of characters in a text string.
Function: LEN(text)
How to Use LEN:
- To find the length of a product code in cell A2, use: =LEN(A2)
- This will return the total number of characters in that string
Final thoughts
Mastering these Excel formulas will not only save you time, but also make you more effective in handling data – even if it isn’t your main job.
Whether you’re working with simple datasets or complex models, these functions provide a strong foundation for any Excel user.
Need more help? Take a Microsoft Excel course today.
