"Day 14: Excel Essentials Unveiled - Sharing Today's Insights on My Learning Adventure! 📊🚀 #ExcelSkills #LearningJourney"
Tags: data, dataengineering, datascience, analyst
Published: 2024-01-19 13:48:00

EXCEL - 13


DATA Manipulation FUNCTION :- 

MOST OF THEM ARE REPEATING BUT IN THIS ALL INFORMATION  WILL BE PROVIDED.
CONCATENATE or CONCAT
LEFT, RIGHT, MID
LEN
FIND and SEARCH
SUBSTITUTE
IF
IFERROR
TEXT
CONVERT
TRANSPOSE
SUMIF and SUMIFS
INDEX and MATCH
VLOOKUP
IF, ISNUMBER, and SEARCH Combination
DATE and TIME Functions
CONVERT Function
RANK
ROUND
TEXTJOIN
IF, ISBLANK, and IFERROR Combination


CONCATENATE or CONCAT:
Combines multiple text strings into one.
=CONCATENATE(text1, text2, ...)

LEFT, RIGHT, MID:
Extracts a specified number of characters from the left, right, or middle of a text string.
=LEFT(text, num_chars)
=RIGHT(text, num_chars)
=MID(text, start_num, num_chars)
LEN:
Returns the number of characters in a text string.
=LEN(text)




FIND and SEARCH:
Searches for a specific text within another text string. SEARCH is case-insensitive, while FIND is case-sensitive.
=FIND(find_text, within_text, [start_num])
=SEARCH(find_text, within_text, [start_num])




SUBSTITUTE:
Replaces occurrences of a specified substring with another substring.
=SUBSTITUTE(text, old_text, new_text, [instance_num])

IF:
Performs a logical test and returns one value if the test is true and another value if the test is false.
=IF(logical_test, value_if_true, value_if_false)

IFERROR:
Returns a specified value if a formula results in an error.
=IFERROR(formula, value_if_error)

TEXT:
Converts a value to text in a specified number format.
=TEXT(value, format_text)

CONVERT:
Converts a number from one measurement system to another.
=CONVERT(number, from_unit, to_unit)



TRANSPOSE:
Transposes rows and columns in a range.
=TRANSPOSE(array)
SUMIF and SUMIFS:
Adds the values in a range that meet specific criteria.
=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, criteria_range1, criteria1, ...)


INDEX and MATCH:
Returns the value in a specified row and column intersection based on the matching criteria.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))





VLOOKUP:
Searches for a value in the first column of a range and returns a value in the same row from a specified column.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

IF, ISNUMBER, and SEARCH Combination:
Checks if a text contains a specific substring and returns a value based on the condition.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])


DATE and TIME Functions:
Functions for working with date and time values.
=DATE(year, month, day)
=TIME(hour, minute, second)

CONVERT Function:
Converts a number from one measurement unit to another.
=CONVERT(number, from_unit, to_unit)


RANK:
Returns the rank of a number in a list.
=RANK(number, ref, [order])




ROUND:
Rounds a number to a specified number of digits.
=ROUND(number, num_digits)

TEXTJOIN:
Joins together text strings from a range using a specified delimiter.
=TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)


IF, ISBLANK, and IFERROR Combination:
Checks if a cell is blank or contains an error, and returns a value based on the condition.=IF(ISBLANK(cell), value_if_blank, IFERROR(formula, value_if_error))

These functions are fundamental for various tasks in Excel, including text manipulation, logical operations, conditional formatting, and data analysis.

Follow me on this where every day will be added if i learn something new about it  :- https://dev.to/nitinbhatt46



Thank you for your time.