Make Your Data Analysis Easy By Using Excel

Simple Yet Powerful Excel Functions Included

Published in

Analytics Vidhya

5 min readJul 14, 2021

Dataset I am using is a dummy dataset. Here and there I included tips so make sure you keep eye on them. Refer to images for examples to get an idea of how exactly formulas can be used.

1. CONCAT()

Joins the text from multiple ranges or strings. Can specify any kind of delimiters while joining the text.

Syntax: CONCAT(Text1, [Text2],…)In the example space delimiter used

Tip: Common delimiters are space, comma(,), semicolon(;), pipes(|), slashes ( / \ ), Hyphen(-). It all depends on the requirment what kind of delimiter want to use.

2. TRIM()

Removes leading and trailing spaces from the text except for single spaces between words. Often happens when you get data from other sources it has lots of white space and you have to remove those spaces otherwise it will considered as unique entry.

Syntax: TRIM(Text)In the example there is leading space

3. VALUE()

Converts numbers stored as text into numbers.

Syntax: VALUE(Text)In the example see Age column value that stored as text

Tip: By default number is always right side of the cell but when number stored as text it is on left side.

4. LOWER(), UPPER(), PROPER()

These three functions converts text to lower, upper and sentence case respectively (First letter of each word capital). It will be helpful in converting different cases into single case while doing data analysis project.

Syntax: LOWER(Text)/ UPPER(Text)/ PROPER(Text)

Image by Author: UPPER(),LOWER(),PROPER() in Excel

5. LEN(), LEFT(), RIGHT(), MID(), FIND()

LEN() function returns the length of the string.

Syntax: LEN(Text)

LEFT(), RIGHT(), MID() function returns the specified number of characters from the string.

Syntax: 
LEFT(Text, [Length])  - Returns from left side
        
RIGHT(Text, [Length]) - Returns from right side
        
MID(Text, Start, Num) - Returns specified number of characters

FIND() function returns the position of the string. It can be used with len, left, right and mid function to get desired output

Syntax: FIND(Find_text, Within_text, [Start_num])

FIND(what you want to find, where you want to find, From where to start)

Image By Author: LEN(), LEFT(), RIGHT(), MID(), FIND() in Excel

6. COUNTIFS(), SUMIFS()

COUNTIFS() function counts the number of items that meet criteria and can specify any criteria.

Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

SUMIFS() function adds all the values that meet criteria, can specify any number of criteria.

Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Tip: Criteria can be
1) Wildcards (*, ?)
2) Expression ( “>20”, “<5”, “=15", “<>RED")
3) Numeric Value (10, 1/10, 23.2)
4) Text String (“string”)
5) Boolean Value (TRUE or FALSE)

Image by Author: COUNTIFS(), SUMIFS() IN Excel

Let me explain one of the example in detail

=COUNTIFS(E2:E501,"ON",F2:F501,">5000",C2:C501,K8)Counts whose age is 33 from ON Province and sales greater than $5000. 33 is in another cell (K8), you can use cell reference as well. See picture for reference.

Tip: Select entire column-Ctrl+Shift+↓
To select entire column first select cell the from where you want to start the selection, then press Ctrl+Shift and down arrow.

7. IF(), NestedIFS, IFERROR()

IF() checks whether a condition is met, and returns one value if TRUE, another value if FALSE. Nested IFS is also possible.

Syntax: IF(logical_test, [value_if_true], [value_if_false])

IFERROR() checks the formula if it evaluates error then it returns another value you specified.

Syntax: IFERROR(value, value_if_error)

Image by Author: IF(), IFERROR() IN Excel

Now let us dig into an example

=IF(F8<2000,"NoDiscount",IF(F8<=2500,"2% Discount",IF(F8<=4000,"5% Discount",IF(F8<=7000,"10% Discount","12% Discount"))))This complex nested IF statement follows a straightforward logic:If the sales (in cell F8) is less than 2000, then they gets "NoDiscount"If the sales is less than or equals to 2500, then they gets "2% Discount"If the sales is less than or equals to 4000, then they gets "5% Discount"If the sales is less than or equals to 7000, then they gets "10% Discount"Otherwise "12% Discount"

8. VLOOKUP(), MATCH(), INDEX()

VLOOKUP() looks for a specified value in a specified table array and returns the value that you are looking for. It is one of the most popular, widely used function in excel.

Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP(what you want to look up, where you want to lookup, column number in the range containing the value to return, approximate -TRUE/exact match — FALSE)

MATCH() returns the position of the value in a given range.

Syntax: MATCH(lookup_value, lookup_array,[match_type])

MATCH(what you want to look up, where you want to look up, 0 exact match/ 1 less than/ -1 greater than)

Image by Author: VLOOKUP(), MATCH() in Excel

Tips:
1. Vlookup works vertically (See image for reference)
2. Always makesure lookup_value is always on left side of the table and value you want to return is always on right side of the table, otherwise excel throughs error.

In the example, lookup_value(First_Name) is on the left side and the value to return (Sales) is on the right side. Position of the column returned dynamically by using MATCH() see above picture for reference.

Now its time to go into a detailed explanation

=VLOOKUP(I1,A2:F501,MATCH(H11,A1:F1,0),FALSE)VLOOKUP() looks for I1(Hui) value in the A2:F501 table array and returns value based upon the column index num returned by MATCH()MATCH() looks for H14(Sales) value in the A1:F1 table array (First_Name,Last_Name,Age,City,Province,Sales) and returns the column number

INDEX() returns the specific value in the two-dimensional range. Works with both row and column.

Syntax: INDEX(array, row_num, [col_num])

Image by Author: INDEX(), MATCH() in Excel

Example detail explanation

=INDEX(B2:D6,MATCH(B12,A2:A6,0),MATCH(B11,B1:D1,0))B2:D6 -> Returns value from this array, no need to include row and column headingsMATCH(B12,A2:A6,0) -> Returns row number
Looks for B12(Banana) in the A2:A6 array (Apple,Orange,Banana       Water Melon,Mango) and returns the row number when exact match foundMATCH(B11,B1:D1,0) -> Returns column number
Looks for B11(Jan) in the B1:D1 array(Jan,Feb,Mar) and returns the column number when exact match found

Wrapping up,

Data that you get is not always the way you want, need to clean your data to get accurate insights by using functions covered in the post. If your master COUNTIFS(), VLOOKUP(), MATCH(), NestedIFS and text functions will surely make your data analysis process faster.

Thank you for reading :)