PRACTICAL ADVANCED SPREADSHEETS TOOLS
Aditi Kaythwal
CSC/23/40
1.Test Functions
Test functions check the type of data in a cell and return TRUE/FALSE.
1. ISNUMBER()
● Meaning: Checks if the value is a number.
● Example: =ISNUMBER(100) → TRUE (because 100 is a number)
2. ISTEXT()
● Meaning: Checks if the value is text.
● Example: =ISTEXT("Rahul") → TRUE (because "Rahul" is text)
3. ISBLANK()
● Meaning: Checks if the cell is empty (blank).
● Example: =ISBLANK(A1) → TRUE (if A1 has nothing inside)
4. ISERROR()
● Meaning: Checks if the value is an error (like #VALUE!, #DIV/0!, etc.).
● Example: =ISERROR(5/0) → TRUE (because division by zero is an
error)
2.TEXT FUNCTIONS
TEXT Functions are used to manipulate text data in various ways. Here
are the common TEXT functions you might use in Excel:
● UPPER()
This function converts all letters in the text to uppercase.
Syntax: =UPPER(text)
Example: =UPPER(A2) would convert the name in cell A2 to
uppercase.
● LOWER()
Converts all letters in the text to lowercase.
Syntax: =LOWER(text)
Example: =LOWER(A2) would convert the name in cell A2 to
lowercase.
● PROPER()
Capitalizes the first letter of each word in the text.
Syntax: =PROPER(text)
Example: =PROPER(A2) would capitalize the first letter of each
word in the name.
● LEFT()
Extracts a specified number of characters from the beginning
(left side) of a string.
Syntax: =LEFT(text, num_chars)
Example: =LEFT(A2, 3) would return the first 3 characters from
the name in cell A2.
● RIGHT()
Extracts a specified number of characters from the end (right
side) of a string.
Syntax: =RIGHT(text, num_chars)
Example: =RIGHT(A2, 3) would return the last 3 characters from
the name in cell A2.
● MID()
Extracts characters from the middle of a string, based on a
starting point and the number of characters to extract.
Syntax: =MID(text, start_num, num_chars)
Example: =MID(A2, 2, 3) would return 3 characters starting from
the 2nd character of the name in A2.
● LEN()
Returns the number of characters in a text string.
Syntax: =LEN(text)
Example: =LEN(A2) would return the number of characters in the
name in cell A2.
● CONCAT()
Joins two or more text strings into one string.
Syntax: =CONCAT(text1, text2, ...)
Example: =CONCAT(A2, B2) would join the name in cell A2 with
the text in cell B2.
3.DATE AND TIME FUNCTION
1. TODAY() Function
● What It Does: Returns the current date (no time).
● How to Use:
○ Type =TODAY() in a cell.
○ This will show the current date, and it updates
automatically.
2. NOW() Function
● What It Does: Returns the current date and time.
● How to Use:
○ Type =NOW() in a cell.
○ This will show the current date and time, and it updates
automatically.
3. YEAR() Function
● What It Does: Extracts the year from a date.
● How to Use:
○ Type =YEAR([date_cell]) in a cell.
○ For example, if the date is in cell G2, use =YEAR(G2).
4. MONTH() Function
● What It Does: Extracts the month from a date.
● How to Use:
○ Type =MONTH([date_cell]) in a cell.
○ For example, if the date is in cell G2, use =MONTH(G2).
5. DAY() Function
● What It Does: Extracts the day from a date.
● How to Use:
○ Type =DAY([date_cell]) in a cell.
○ For example, if the date is in cell G2, use =DAY(G2).
6. DATEDIF() Function
● What It Does: Calculates the difference between two dates.
● How to Use:
○ Type =DATEDIF([start_date], [end_date],
"unit") in a cell.
○ Example: =DATEDIF(G2, TODAY(), "Y") to calculate
the age based on Date of Birth.
1. INDEX Function
The INDEX function is used in Excel to return the value of a cell
within a specified range based on row and column numbers.
Formula:
INDEX(array, row_num, [column_num])
Example:
To retrieve the "Salary" of the employee named "Rahul" (Row 1), the
formula would be:
=INDEX(D2:D6, 1)
This will return 25000, as Rahul is in the first row of the range.
Explanation:
● array refers to the range of cells from which data is to be
extracted.
● row_num refers to the row number in the range from which
data is to be extracted.
● column_num (optional) is used if you're working with a
multi-column range.
2. MATCH Function
The MATCH function searches for a value in a range and returns the
relative position of that value.
Formula:
MATCH(lookup_value, lookup_array, [match_type])
Example:
To find the position of "Rahul" in the Name column, the formula
would be:
=MATCH("Rahul", B2:B6, 0)
This will return 1, as Rahul is in the first position.
Explanation:
● lookup_value is the value you want to search for (e.g.,
"Rahul").
● lookup_array is the range of cells to search in.
● match_type is an optional argument where 0 indicates an
exact match.
3. OFFSET Function
The OFFSET function returns a reference to a range that is a
specified number of rows and columns from a given reference.
Formula:
OFFSET(reference, rows, columns, [height], [width])
Example:
To find the salary of "Priya," you can use:
=OFFSET(D2, 1, 0)
This will return NIL (since the second row of the Salary column is
"NIL").
Explanation:
● reference is the starting cell.
● rows indicates the number of rows to move down or up.
● columns indicates the number of columns to move left or
right.
height and width are optional and define the size of the
returned range.
4. EXACT Function
The EXACT function compares two text strings and returns TRUE if
they are exactly the same, and FALSE if they are not.
Formula:
EXACT(text1, text2)
Example:
To compare if the name "Rahul" in cell B2 is the same as "Rahul" in
cell B3, the formula would be:
=EXACT(B2, B3)
This will return TRUE if both cells contain the same text.
Explanation:
● text1 and text2 refer to the two text strings you want to
compare.
5. ISERROR Function
The ISERROR function checks whether a formula results in an
error and returns TRUE if it does, or FALSE if it doesn't.
Formula:
ISERROR(value)
Example:
If the formula to find a student's percentage results in an error
due to zero marks, the formula would be:
=ISERROR(E2)
If there is an error in cell E2 (e.g., "Invalid Marks"), this function
will return TRUE.
Explanation:
● value refers to the result of a formula or expression you
want to check for an error.
6. IFERROR Function
The IFERROR function is used to return a value if a formula results
in an error; otherwise, it returns the result of the formula.
Formula:
IFERROR(value, value_if_error)
Example:
To handle errors in percentage calculation:
=IFERROR(E2, "Invalid Marks")
If the formula in cell E2 results in an error (like dividing by zero), it
will return "Invalid Marks."
Explanation:
● value refers to the formula or expression to evaluate.
● value_if_error refers to the value you want to return if
there is an error.
7. Handling Zero Marks with IF
You can use the IF function to handle special conditions, such as
when marks are zero or invalid.
Formula:
IF(logical_test, value_if_true, value_if_false)
Example:
To calculate percentage and handle zero marks:
=IF(B2=0, "Invalid Marks", B2/C2)
This formula checks if the marks are zero and returns "Invalid
Marks." Otherwise, it calculates the percentage.
Explanation:
● logical_test is the condition you want to check.
● value_if_true refers to what to return if the condition is
true.
● value_if_false refers to what to return if the condition is
false.
8. FORM CONTROL (Dynamic Charting & Formula Auditing)
Form controls are used to create interactive elements, such as
buttons, checkboxes, and drop-down lists, in Excel. They allow
users to interact with data dynamically.
● Dynamic Charting: You can link form controls like
drop-down lists to dynamic charts. For example, using a
drop-down list to select different cities and automatically
updating a chart based on the selection.
● Formula Auditing: Formula auditing tools help track the flow
of formulas and understand dependencies between cells.
Tools like "Trace Precedents" and "Trace Dependents" make it
easy to understand the relationships between different cells
and their formulas.
Explanation:
● Dynamic Charting allows for creating charts that update
automatically as inputs change.
● Formula Auditing ensures formula accuracy by tracing their
origins and dependencies.
FORMULA AUDITING
