Seeking to excel in Excel? You must read this…
Microsoft Excel is a vital tool in lots of job roles, from finance and marketing, to data evaluation and administration. While Excel offers an enormous array of functionalities, mastering just a couple of of its key formulas can significantly boost your efficiency and productivity.
Whether you’re a beginner or looking to boost your spreadsheet skills, listed below are 10 Microsoft Excel formulas everyone should know:
1. VLOOKUP
VLOOKUP (Vertical Lookup) is one of the crucial widely used Excel functions. It searches for a price in the primary column of a spread and returns a price in the identical row from one other column.
Function: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Easy methods to use VLOOKUP:
- Select the cell you would like the result to seem in
- Type =VLOOKUP(
- Enter the worth you desire to seek for (e.g., A2)
- Enter the range of cells to look inside (e.g., B2:D10)
- Enter the column number from which to return the worth (e.g., 3 for the third column)
- Specify TRUE for an approximate match or FALSE for an actual match (e.g., FALSE)
- Press Enter
2. SUMIF
SUMIF is used to sum values in a spread that meet a particular condition or criteria.
Function: SUMIF(range, criteria, [sum_range])
Easy methods to use SUMIF:
- Imagine you have got a sales list where column A has product names and column B has sales figures
- To sum the sales of a selected 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 one other for a FALSE result.
Function: IF(logical_test, value_if_true, value_if_false)
Easy methods 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 rating 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 price from a particular row and column inside a spread, while MATCH finds the position of a price inside a spread.
Function: INDEX(array, row_num, [column_num]) + MATCH(lookup_value, lookup_array, [match_type])
Easy methods to use INDEX & MATCH:
- First, find the position of a price with MATCH: =MATCH(“ProductName”, A2:A10, 0)
- Then, use INDEX to search out the worth within the corresponding row and a distinct column: =INDEX(B2:B10, MATCH(“ProductName”, A2:A10, 0))
- This mix returns the worth 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], …)
Easy methods to use CONCATENATE (or CONCAT):
- To mix first names in column A and last names in column B right into a full name, use: =CONCATENATE(A2, ” “, B2)
- This joins the primary name and last name with an area in between
6. TEXT
The TEXT function converts a number to text in a specified format.
Function: TEXT(value, format_text)
Easy methods 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 specified format
7. SUMPRODUCT
SUMPRODUCT multiplies corresponding elements in arrays after which sums those products.
Function: SUMPRODUCT(array1, [array2], …)
Easy methods to use SUMPRODUCT:
- If column A incorporates units sold and column B incorporates the worth per unit, calculate total revenue with: =SUMPRODUCT(A2:A10, B2:B10)
- This multiplies each unit sold by its price after which sums the overall
8. COUNTIF
COUNTIF counts the variety of cells in a spread that meet a particular condition.
Function: COUNTIF(range, criteria)
Easy methods to use COUNTIF:
- To count how repeatedly a particular product appears in column A, use: =COUNTIF(A2:A10, “ProductName”).
- This returns the variety of times “ProductName” is listed.
9. LEFT, RIGHT, MID
These functions extract a specified variety of characters from a text string, ranging from the left, right, or a particular position (MID).
Functions: LEFT(text, [num_chars]), RIGHT(text, [num_chars]), MID(text, start_num, num_chars)
Easy methods to use LEFT, RIGHT, MID:
- To extract the primary three characters of a product code in A2, use: =LEFT(A2, 3)
- To get the last 4 digits of a phone number in B2, use: =RIGHT(B2, 4)
- To extract characters from the center of a string, starting on the 2nd character and taking the subsequent 4 characters, use: =MID(A2, 2, 4)
10. LEN
LEN returns the variety of characters in a text string.
Function: LEN(text)
Easy methods to Use LEN:
- To search out the length of a product code in cell A2, use: =LEN(A2)
- This can return the overall variety of characters in that string
Final thoughts
Mastering these Excel formulas is not going to only prevent time, but additionally make you simpler in handling data – even when it isn’t your foremost job.
Whether you’re working with easy datasets or complex models, these functions provide a powerful foundation for any Excel user.
Need more help? Take a Microsoft Excel course today.
